Re: [sqlite] negative numbers

[P Kishor]

On Sun, Jan 31, 2010 at 9:42 AM, Jay A. Kreibich  wrote:
> On Sun, Jan 31, 2010 at 09:11:21AM -0600, P Kishor scratched on the wall:
>> On Sun, Jan 31, 2010 at 8:19 AM, james pruett  wrote:
>> > I am writing an open source program.
>> >
>> > I am having trouble getting any results using this query. This returns 
>> > none
>
>> > select * from signs where lon>-121 and lon<-119;
>
>> Given your data, you need lon >= -120 and lon < -119
>>
>> You are not comparing with >=. You are using only >
>
>
>  That's true, but he's using 121, not 120.
>


Good grief, I have no idea why I saw >-120.



-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[Jay A. Kreibich]

On Sun, Jan 31, 2010 at 09:11:21AM -0600, P Kishor scratched on the wall:
> On Sun, Jan 31, 2010 at 8:19 AM, james pruett  wrote:
> > I am writing an open source program.
> >
> > I am having trouble getting any results using this query. This returns 
> > none

> > select * from signs where lon>-121 and lon<-119;

> Given your data, you need lon >= -120 and lon < -119
> 
> You are not comparing with >=. You are using only >


  That's true, but he's using 121, not 120.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[P Kishor]

On Sun, Jan 31, 2010 at 8:19 AM, james pruett  wrote:
> I am writing an open source program.
>
> I am having trouble getting any results using this query. This returns 
> none
>
> select * from signs where lon>-121 and lon<-119;
>
> onCreate(SQLiteDatabase db) {
>      db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>            + " VARCHAR(45), " + TAGG
>            + " VARCHAR(45), " + LAT
>            + " FLOAT      , " + LON
>            + " VARCHAR(45), " + COG
>            + " VARCHAR(45), " + MPH
>            + " VARCHAR(45), " + KPH
>            +  " TEXT NOT NULL);");
>   }
>
> Here is the database using RazorSQL v504
> select * from signs;
> _id                  time     tag      lat         lon    cog    mph   kph
> 1    1264890306692    test    20.0    -120.0    0.0    45.0    0.0
> 2    1264890311484    test    20.0    -120.0    0.0    45.0    0.0
> 3    1264890345263    test    20.0    -120.0    0.0    45.0    0.0
> 4    1264890346700    test    20.0    -120.0    0.0    45.0    0.0
>

Given your data, you need lon >= -120 and lon < -119

You are not comparing with >=. You are using only >

See more

sqlite> CREATE TABLE signs (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
sqlite> INSERT INTO signs VALUES (1, 20.0, -120.0);
sqlite> INSERT INTO signs VALUES (2, 20.0, -120.0);
sqlite> INSERT INTO signs VALUES (3, 20.0, -120.0);
sqlite> INSERT INTO signs VALUES (4, 20.0, -119.5);
sqlite> SELECT * FROM signs;
id  lat lon
--  --  --
1   20.0-120.0
2   20.0-120.0
3   20.0-120.0
4   20.0-119.5
sqlite> SELECT * FROM signs WHERE lon > -120.0 AND lon < -119.0;
id  lat lon
--  --  --
4   20.0-119.5
sqlite> SELECT * FROM signs WHERE lon >= -120.0 AND lon < -119.0;
id  lat lon
--  --  --
1   20.0-120.0
2   20.0-120.0
3   20.0-120.0
4   20.0-119.5
sqlite> SELECT * FROM signs WHERE lon >= -120 AND lon < -119;
id  lat lon
--  --  --
1   20.0-120.0
2   20.0-120.0
3   20.0-120.0
4   20.0-119.5
sqlite>


> ---
> I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
> to handle negative numbers correctly.
> Any help appreciated!
>
> Jim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[Simon Slavin]

On 31 Jan 2010, at 2:19pm, james pruett wrote:

> I am having trouble getting any results using this query. This returns 
> none
> 
> select * from signs where lon>-121 and lon<-119;
> 
> onCreate(SQLiteDatabase db) {
>  db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>+ " VARCHAR(45), " + TAGG
>+ " VARCHAR(45), " + LAT
>+ " FLOAT  , " + LON

SQLite does not have a 'FLOAT' type (or indeed a VARCHAR type).  See

http://www.sqlite.org/datatype3.html

and define your 'lon' column as REAL.  Also see further down the page: sqlite 
does not actually do column types: you could define a column as REAL but still 
put a string into that column of one row.  So make sure that your numbers are 
actually being stored as numbers, not strings, by testing the method you use to 
create a row.  You can do this by creating a row, then using the function 
'typeof(lon)'.  If you did it right, that should return 'real'.  If you didn't, 
it might return 'text'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] negative numbers

[D. Richard Hipp]

On Jan 31, 2010, at 9:19 AM, james pruett wrote:

> I am writing an open source program.
>
> I am having trouble getting any results using this query. This  
> returns none
>
> select * from signs where lon>-121 and lon<-119;
>
> onCreate(SQLiteDatabase db) {
>  db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>+ " VARCHAR(45), " + TAGG
>+ " VARCHAR(45), " + LAT
>+ " FLOAT  , " + LON
>+ " VARCHAR(45), " + COG
>+ " VARCHAR(45), " + MPH
>+ " VARCHAR(45), " + KPH
>+  " TEXT NOT NULL);");
>   }
>
> Here is the database using RazorSQL v504
> select * from signs;
> _id  time tag  lat loncog 
> mph   kph
> 11264890306692test20.0-120.00.045.00.0
> 21264890311484test20.0-120.00.045.00.0
> 31264890345263test20.0-120.00.045.00.0
> 41264890346700test20.0-120.00.045.00.0
>
> ---
> I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
> to handle negative numbers correctly.

FLOAT should work.  Are you sure you tried it correctly?

> Any help appreciated!
>
> Jim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] negative numbers

[james pruett]

I am writing an open source program.

I am having trouble getting any results using this query. This returns none

select * from signs where lon>-121 and lon<-119;

onCreate(SQLiteDatabase db) {
  db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
+ " VARCHAR(45), " + TAGG
+ " VARCHAR(45), " + LAT
+ " FLOAT  , " + LON
+ " VARCHAR(45), " + COG
+ " VARCHAR(45), " + MPH
+ " VARCHAR(45), " + KPH
+  " TEXT NOT NULL);");
   }

Here is the database using RazorSQL v504
select * from signs;
_id  time tag  lat loncogmph   kph
11264890306692test20.0-120.00.045.00.0
21264890311484test20.0-120.00.045.00.0
31264890345263test20.0-120.00.045.00.0
41264890346700test20.0-120.00.045.00.0

---
I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
to handle negative numbers correctly.
Any help appreciated!

Jim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users