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
Best Answer
You can create a "Virtual Layer", that will join the two layers by plot
"ID"then compute the distance:Go to the menu
Layer > Add Layer > Add/Edit Virtual Layer...and enter the query: