2

I'm quite new to PostGIS and pgRouting.

I'm interested in doing a simple map matching. Precisely, finding the closest point in a given OSM road network.

I’ve successfully imported the OSM network with osm2pgrouting. After some googling and lots of read I was able to find how to get the closest road vertex from a given point P (-49.239099 -16.667802) using the following query:

SELECT * FROM ways_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('POINT(-49.239099 -16.667802)',4326) LIMIT 1

However, what I'm interested in on the project point of P that should be located on the closest edge in the road network.

How can I do that?

Improve this question
1
  • sidenote: allthough I also haven't covered that in my answer, for large tables with proper index to be distance ordered performantly, it is advisable to include a radius search (i.e. WHERE ST_DWithin(...)) with a decent value to preselect only those geometries that are within range to the point. Commented Feb 22, 2018 at 11:42

3 Answers 3

Reset to default
3

Use your query structure to get the closest edge to P and project a closest point P' from P on the edge with ST_ClosestPoint:

SELECT edge.id, ST_ClosestPoint(edge.geom, <P>) AS "P'" FROM ( SELECT <edges_id> AS id, <edges_geom> AS geom FROM <edges_table> ORDER BY ST_Distance(<P>, <edges_geom>) LIMIT 1 ) AS edge

Note: there might be better solutions if you are actually interested in the distance along the line to the line's node you need as an entry point for routing.

UPDATE:
ST_Distance in conjunction with LatLon (e.g. EPSG:4326) is a tricky business. While it might work here, with sufficiently small distances, do at least not use it to derive metric distances (this does equally apply to the KNN operators, e.g. <->). For a more robust and precise solution, consider casting to geography prior to the calculations (e.g. ST_Distance(<P>::geography, <edges_geom>::geography))

Improve this answer
2
  • This worked like a charm. Commented Feb 23, 2018 at 4:09
  • @MarcosRorizJunior as a sidenote: this works only for one pre-selected point <P>. if you want that query dynamically create respective projected points on multiple lines from an input point table, you could use a LATERAL JOIN. Commented Mar 2, 2018 at 12:47
2

I've recently worked on a similar problem as this.

My methodology was as follows:

  1. Find nearest line to the input point(s)
  2. Get a small subsection of this line
  3. Rotate the subsection 90 degrees around a point on the line closest to the input point (allow us to cut and or create point at intersection)

To this methodology we can add a way to cut get a point when these two lines intersect... For that we can use some thing like this:

ST_CollectionExtract(ST_Intersection(a.geom, b.geom), 1)

I was working with a table of many input points but it should be fine for just one point (we need a geom and id column in the netowrk and point tables).

Full code here:

--1 Closest line to point(s) CREATE TABLE schema.test_closestline AS SELECT pid ,lid, distance_m, a.geom FROM (SELECT q.id pid, a.id lid, ROUND(ST_Distance(q.geom, a.geom)::NUMERIC, 2) as distance_m, ROW_NUMBER() OVER (PARTITION BY q.id ORDER BY ST_DISTANCE(a.geom,q.geom)) as row_number, a.geom FROM schema.road_network a INNER JOIN schema.input_points q ON ST_DWITHIN(a.geom,q.geom,100) -- use a sensible distance to restrict the result set without losing records ) a WHERE a.row_number = 1 DROP TABLE IF EXISTS schema.test_blade_rotate; --2/3 get Small line segment at right 90 degress to line, crossing at closest point to input point CREATE TABLE schema.test_blade_rotate AS SELECT line.id, ST_Rotate(line.geom, 1.5708, ST_Centroid(line.geom)) geom --1.5708 radians = 90 degrees FROM ( SELECT paired.pid id, CASE WHEN ( round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) + 1 = 1 ) THEN ST_LineSubstring(paired.lgeom, round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2), round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2)+ 0.01) WHEN ( round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) + 1 = 2) THEN ST_LineSubstring(paired.lgeom, round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) - 0.01, round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2)) ELSE ST_LineSubstring(paired.lgeom,(round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) - 0.01), (round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2)+0.01)) END AS geom FROM (SELECT line.pid, (ST_DUMP(line.geom)).geom lgeom, (ST_DUMP(point.geom)).geom pgeom FROM schema.test_closestline as line, schema.input_points as point WHERE line.pid = point.id) as paired ) as line; --4 POINT FROM INTERSECT OF TWO LINES: CREATE TABLE schema.closest_point AS SELECT b.id, ST_CollectionExtract(ST_Intersection(a.geom, b.geom), 1) as geom FROM schema.road_network a, schema.test_blade_rotate b
Improve this answer
0

Use a Buffer to find nearest line string point of intersect. The point on the line path is then easily identified.

Improve this answer
1
  • 2
    What happens if your buffer is not large enough to reach the "nearest line string point of intersect"? Are you planning to just keep trying larger buffer sizes until one is? What happens if a larger buffer size finds more than one? Commented Feb 22, 2018 at 6:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.