On Sun, Jan 31, 2010 at 8:19 AM, james pruett <gpscru...@gmail.com> 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
