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?

  • 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. – ThingumaBob Feb 22 '18 at 11:42
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))

  • This worked like a charm. – Marcos Roriz Junior Feb 23 '18 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. – ThingumaBob Mar 2 '18 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
0

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

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • 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? – PolyGeo Feb 22 '18 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.