PostgreSQL complex query to find closest matching values for two fields

Question

Hi I have a database with the following structure:

Table 1:

time(type timestamp)        longitude(type string)        latitude(type string)
2:00                        -110.4365                     38.7463
2:00                        -110.2743                     38.7983
2:00                        -102.4434                     36.9438
3:00                        -112.3254                     39.2222
etc                         etc                           etc

Now I have some code that plots values and when it does so it loses some accuracy.The user can click the points and query the database for further information on the point. However as the plotting function lost some accuracy I need to account for this in the query.

So the idea is that I would like to query for time = x and also (latitude = closest value to y and longitude = closest value to z). You see the closest value is dependent on both latitude and longitude and this is where I'm getting confused as to how to proceed.

Lets say I was looking for:

Time = 2:00
Longitude = -110.3421
Latitude = 38.7587

Now if I did a query for:

Time = 2:00 and Longitude = closest to -110.3421

then the result would be row 2.

However if I did:

Time = 2:00 and Latitude = closest to 38.7587

then the result would be row 1.

If I do a query on:

Time = 2:00 and Longitude = closest to -110.3421 and Latitude = closest to 38.7587

then the result would be row 1. This is the type of query that I require...

I found the following post useful for a query on the closest value in one field:

https://stackoverflow.com/a/6103352/1800665

Thanks, James

EDIT: I have the following:

(SELECT * FROM (
(SELECT * FROM Table1 WHERE cast(latitude as double precision) >= '38.7587' AND healthtime = '2:00' AND cast(longitude as double precision) >= '-110.3421') 
UNION ALL 
(SELECT * FROM Table1 WHERE cast(latitude as double precision) < '38.7587' AND healthtime = '2:00' AND cast(longitude as double precision) < '-110.3421')
) as nearest ORDER BY ( abs('38.7587'-cast(latitude as double precision)) + abs('-110.3421'-cast(longitude as double precision))) LIMIT 1)

Thanks, but ideally I'd like to not have to install additional software. Also the latitude and longitude fields are actually of type string and not a geographical data type. — James Elder, Dec 14 '12 at 15:16
Also note, "(closest to X, closest to Y)" is not the same as "closest to (X, Y)". — Alex Howansky, Dec 14 '12 at 15:21

Ihor Romanchenko · Accepted Answer · 2012-12-14T16:53:19.390

You can try something like this:

SELECT *
FROM table1
WHERE healthtime = '2:00'
ORDER BY ((longitude::double precision - (-110.3421))^2 +
          (latitude::double precision -  (38.7587  ))^2)
LIMIT 1

It will return you the point with minimum "distance" : ( (lon-lon1)^2 + (lat-lat1)^2 -> min ) .

My SQLFiddle with example.

UPD Test query:

SELECT *, ((longitude::double precision - (-110.3421))^2 +
           (latitude::double precision -  (38.7587  ))^2) as distance,
          (abs(longitude::double precision - (-110.3421)) +
           abs(latitude::double precision -  (38.7587  ))) as distance2
FROM table1
WHERE time = '2:00'
ORDER BY ((longitude::double precision - (-110.3421))^2 +
          (latitude::double precision -  (38.7587  ))^2)

LIMIT 2

Gives 2 best results:

| TIME | LONGITUDE | LATITUDE |   DISTANCE | DISTANCE2 |
--------------------------------------------------------
| 2:00 | -110.2743 |  38.7983 |   0.006165 |    0.1074 |
| 2:00 | -110.4365 |  38.7463 | 0.00906512 |    0.1068 |

DISTANCE = (lon-lon1)^2 + (lat-lat1)^2 (my variant).

DISTANCE2 = abs(lon-lon1) + abs(lat-lat1) (your variant).

Your variant gives different "closest". You can use any variant of "distance" calculation, but I prefer "classical" way - root((lon-lon1)^2 + (lat-lat1)^2).

New SQLFiddle

Thanks, but I'm wondering why you get a different answer than me? I got row 1 as the closest match when I worked it out manually... — James Elder, Dec 14 '12 at 16:07

PostgreSQL complex query to find closest matching values for two fields

1 Answers1