Calculating distance of point from arc/polyline using PostGIS?

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


I think you should use ST_Line_Interpolate_Point function for finding closest point on a polyline and ST_Distance function for calculating distances between the closest point and polyline.

what i understood from your question is that you meant something like this:

interpolation

ST_Line_Locate_Point — Computes de fraction of a Line from the closest point on the line to the given point. link

ST_Line_Interpolate_Point — Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing fraction of total length of linestring the point has to be located. link

ST_Distance — For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters. link

Example for calculating closest point

SELECT ST_Line_Locate_Point(ST_GeomFromEWKT('LINESTRING (29 35, 29 21, 11 21)'),ST_GeomFromEWKT('POINT (20 28)'));
--Result
0.71875

-- Using ST_Line_Interpolate_Point (see graphic below)
SELECT ST_AsText(ST_Line_Interpolate_Point(line,ST_Line_Locate_Point(line,point))) as interpolate 
FROM (SELECT ST_GeomFromText('LINESTRING (10 11, 15 26, 29 35)') as line, 
      ST_GeomFromText('POINT (27 27)') as point) 
as foo;

--Result
                  interpolate
-----------------------------------------------
 POINT (23.945848375451263 31.750902527075812)
(1 row)

Example for calculating distance

-- Geography example -- same but note units in meters - use sphere for slightly faster less accurate
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist 
FROM (SELECT
    ST_GeographyFromText('SRID=4326;POINT(-72.1235 42.3521)') As gg1,
    ST_GeographyFromText('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)') As gg2
    ) As foo  ;

  spheroid_dist   |   sphere_dist
------------------+------------------
 123.802076746848 | 123.475736916397

i think you can calculate what you want with this examples. i am not on my own computer which has postgres on it for writing certain sql for you...

and you can read Solving the Nearest Neighbor Problem in PostGIS and PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution for gaining idea and experience for Nearest Neighbor Problem.

Tags:

Postgis

Proximity

Recent Posts