# Projecting GPS point to point in closest pgrouting road (map matching)?

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. Feb 22, 2018 at 11:42

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. 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`. Mar 2, 2018 at 12:47

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
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

--2/3 get Small line segment at right 90 degress to line, crossing at closest point to input point
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
{"ft_lang_label":"__label__en","ft_lang_prob":0.5097399,"math_prob":0.95660174,"size":2722,"snap":"2023-40-2023-50","text_gpt3_token_len":757,"char_repetition_ratio":0.16188374,"word_repetition_ratio":0.012345679,"special_character_ratio":0.25716385,"punctuation_ratio":0.24827586,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99284184,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-09-28T19:00:09Z\",\"WARC-Record-ID\":\"<urn:uuid:417af5c1-0232-40eb-804c-62613ad3eac5>\",\"Content-Length\":\"189372\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:72cd0dba-5457-4005-a946-e5aeb7c8c1df>\",\"WARC-Concurrent-To\":\"<urn:uuid:9486da3c-31fe-4cc1-8197-f836a8f8fdd5>\",\"WARC-IP-Address\":\"104.18.11.86\",\"WARC-Target-URI\":\"https://gis.stackexchange.com/questions/272246/projecting-gps-point-to-point-in-closest-pgrouting-road-map-matching\",\"WARC-Payload-Digest\":\"sha1:BQN6SOFQQXOOS4EIQO6AWQFSVSHRHZJX\",\"WARC-Block-Digest\":\"sha1:Q6HGSEK6ONS6FX5XYASRAO6QQKA6SMQU\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-40/CC-MAIN-2023-40_segments_1695233510427.16_warc_CC-MAIN-20230928162907-20230928192907-00660.warc.gz\"}"}