Problem with retrieving records using double precision fields

From: "Raymond C(dot) Rodgers" <sinful622(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with retrieving records using double precision fields
Date: 2009-01-21 17:22:14
Message-ID: 497759C6.6020205@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2009-01-21 17:30:41 Re: Problem with retrieving records using double precision fields
Previous Message Albe Laurenz 2009-01-21 17:15:11 Re: How to find how much postgresql use the memory?