SELECT arc_id
FROM road_table
ORDER BY ST_Distance(road_table.geom, 'gps-point in wkt')
LIMIT 1;
Since PostGIS 1.5 you also have the option of using ST_ClosestPoint.
Some discussions of how to use it can be found here:
http://blog.jordogskog.no/2010/02/07/how-to-use-the-new-distance-related-functions-in-postgis-part1/
About ST_ClosestPoint you can read more in the PostGIS documentation:
http://postgis.net/docs/ST_ClosestPoint.html
As I understand your query correct you don't have any line ready but a list of points in a table.
What you need to do then is writing a query that creates lines from those points and use ST_ClosestPoint on those lines. You do it all in one query. To do that you can design your query in several ways and approaches depending on in more detail what you want to get. If it is just the distance you want, then forget about ST_ClosestPoint then use ST_Distance to get the distance. If you just want the closest Point on the edge and/or the distance, then you can build the whole linestring with ST_MakeLine
If you also want to find the points defining the edge that is closest it is probably easiest to make a self join that builds lines from each opint pair in the table instead and use ST_Distance and /or ST_ClosestPoint on that two point lines.
This I guess looks quite messy, but that is because there is a lot of possibilities, what you actually want to get.
I think edge is a more correct word than arc.
Ok, from your comment I guess that what you want is a query that self-joins to create edges and returns the closest edge and distance.
You will need a integer field to make it work like this with integer values ordered as the gps-points and without holes in the series. I call id gid here.
Then you can run something like:
SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table a INNER JOIN point_table b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;
a.geom references geom in the table that I have put the alias a on. I shouldn't have left out the "AS" key-word, then it looks like this:
SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table AS a INNER JOIN point_table AS b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;
To get a short intro to sql in general you can test the tutorial on PostGISonline
To see some spatial examples you can try:
mixed examples
You can use this following queries (these operations use a temporary table):
-- add distance_min and nearest_id columns
alter table tab_Cities add column distance_min float;
alter table tab_Cities add column nearest_idpoint varchar(10);
--create temp table
DROP TABLE IF EXISTS table_temp;
select a1.gid as id_1, a2.gid as id_2, ST_Distance(a1.geom, a2.geom) as distance
INTO table_temp
from tab_Cities a1 , tab_Cities a2
where ST_DWithin(a1.geom, a2.geom, 5000)
-- 5000 is like a buffer of 5000 meters around your point
-- You can use a suitable radius for your points
and a1.gid <> a2.gid ;
--update distance_min
update tab_Cities set distance_min =
(
select distance
from table_temp
where tab_Cities.gid = table_temp.id_1
order by distance limit 1
),
nearest_idpoint =
(
select id_2
from table_temp
where tab_Cities.gid = table_temp.id_1
order by distance limit 1
);
--DROP TABLE temp
DROP TABLE IF EXISTS table_temp;
Best Answer
Paolo Corti provides an excellent answer on the postgis mailing list
Use ST_Segmentize and then ST_DumpPoints, like this: