Thread: Problem with retrieving records using double precision fields

In two separate databases that are configured to have latitude and
longitude as double precision fields, I'm having trouble retrieving
records using "between" on the longitude field. I know that I have data
within range, but any query involving the longitude field fails to find
records.

Here's a quick example table; it's not the actual table in either
database, but it's close enough to demonstrate my point on PostgreSQL
8.3.5 on Fedora 10 x86_64:

test=# create table coordtest (id serial, latitude float, longitude float);
NOTICE:  CREATE TABLE will create implicit sequence "coordtest_id_seq"
for serial column "coordtest.id"
CREATE TABLE
test=# insert into coordtest(latitude,longitude) values
(42.38013,-83.05175),(42.411143,-82.943461);
INSERT 0 2
test=# select * from coordtest where latitude between 42.0 and 42.5 and
longitude between -83.0 and -84.0;
 id | latitude | longitude
----+----------+-----------
(0 rows)



test=# select * from coordtest;
 id | latitude  | longitude
----+-----------+------------
  1 |  42.38013 |  -83.05175
  2 | 42.411143 | -82.943461
(2 rows)

test=# select * from coordtest where latitude between 42.0 and 42.5 ;
 id | latitude  | longitude
----+-----------+------------
  1 |  42.38013 |  -83.05175
  2 | 42.411143 | -82.943461
(2 rows)

test=# select * from coordtest where longitude between -83.0 and -84.0;
id | latitude | longitude
----+----------+-----------
(0 rows)


Any idea what's going on here and why I'm not getting results?
Thanks,
Raymond

On Wed, Jan 21, 2009 at 12:22:14PM -0500, Raymond C. Rodgers wrote:
> test=# select * from coordtest where latitude between 42.0 and 42.5 ;

The LHS value of a BETWEEN operator has to be of smaller value than the
RHS's value.  You've got it correct above, but it's not correct here:

> test=# select * from coordtest where longitude between -83.0 and -84.0;

-83 is greater than -84.


--
  Sam  http://samason.me.uk/

Raymond C. Rodgers wrote:
> In two separate databases that are configured to have latitude and
> longitude as double precision fields, I'm having trouble retrieving
> records using "between" on the longitude field. I know that I have data
> within range, but any query involving the longitude field fails to find
> records.

> test=# select * from coordtest where longitude between -83.0 and -84.0;

Order of between arguments is important.

richardh=> SELECT 2 between 1 and 3;
 ?column?
----------
 t
(1 row)

richardh=> SELECT 2 between 3 and 1;
 ?column?
----------
 f
(1 row)

richardh=> SELECT -2 between -1 and -3;
 ?column?
----------
 f
(1 row)

--
  Richard Huxton
  Archonet Ltd

Richard Huxton wrote:

Raymond C. Rodgers wrote: 

In two separate databases that are configured to have latitude and
longitude as double precision fields, I'm having trouble retrieving
records using "between" on the longitude field. I know that I have data
within range, but any query involving the longitude field fails to find
records.   

test=# select * from coordtest where longitude between -83.0 and -84.0;   

Order of between arguments is important.

richardh=> SELECT 2 between 1 and 3;?column?
----------t
(1 row)

richardh=> SELECT 2 between 3 and 1;?column?
----------f
(1 row)

richardh=> SELECT -2 between -1 and -3;?column?
----------f
(1 row)
 

Ok, so I made a simple math error (-83 being larger than -84) and didn't catch it.

Thanks,
Raymond