Find closest Table Points to input Search Points

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Find closest Table Points to input Search Points

Paul & Caroline Lewis

Basic problem is that I have a large table of GPS points and another input data set of GPS points.

For each input GPS point I want to find the closest 1 to it in the GPS table.

The GPS table can, handily, be constrained by 2 other relational tables that link to the GPS data

I've got the below query to work for me when searching the GPS table for 1 input point.

So I can put this into a scripted processing loop to get every input point by making many SQL queries.

However, I'm wondering if its possible to use an array of the input search points to do the complete search in 1 DB Query request.

Working SQL:

SELECT R.hash_id, N.gps_time FROM nav AS N, recording AS R, relation_recording_group AS G WHERE G.group_id = 52 AND G.recording_id = R.recording_id AND N.recording_id = R.recording_id AND R.formats < 10 AND R.status != 3 ORDER BY N.wgs_geom <-> ST_GeomFromText('POINT(LONGITUDE LATITUDE)',4326) LIMIT 1;

postgis-users mailing list
[hidden email]