Finding points along line closest to known point

Question

I'm working on a TV graphics system which will be used on a broadcast from train ride.

With this system I want to automatically be able to list interesting POIs along our route.

We have a large postgis table of POIs which are within 2km of the train route, and we have a GPS fitted to the train, so we know both our current position, the polyline of the train route and the lat/lng of the POIs close to the route.

I've been wrestling with understanding how I can most easily get a hold of the POIs which are closest to my current posistion at any given time.

I have two PostGIS tables:

The first is called 'tracks' with two columns:

date (date) | track (polyline)

The other is called 'pois', and has several columns, but the relevant one is

the_geom

I have a GPS connected by socket which I can read whenever.

So the query I'm interested in formulating is something like:

"Give me the POIs closest to my current GPS position along this linestring"

I've been looking at using ST_BUFFER in combination with ST_DWITHIN to get it up and running, but I'm obviously not at a high enough competency level to get it working. Seeing as both my GPS position and the POIs position probably will never be exactly on top of the linestring, will this maybe complicate things further?

Has anyone done something like this?

Answer 1

ST_DWithin can get you all POIs within a given distance (2000m in a circle in this case) from your current position. A basic example would be

SELECT *
FROM   pois
WHERE  ST_DWithin(gps_position, pois.the_geom, 2000)
;

This assumes you have both geometries (you can transfom on-the-fly with ST_Transform) in the same SRID (one with meter as reference unit).

As far as I can judge (in terms of precision), the current GPS position covers the 'along the line' part.