I would like to know if there is a function at pgRouting that given a point (lon/lat) can give you the projection of that point to the road network .

Thanks in advance john

Advertisement

I would like to know if there is a function at pgRouting that given a point (lon/lat) can give you the projection of that point to the road network .

Thanks in advance john

There are linear referencing functions in PostGIS to help you out project the location of a point along a line. For example, if you have a road, and a point of interest (POI) that is along the side of the road, you can:

- Use ST_Line_Locate_Point to get the fraction along the road closest to the POI; then
- Use ST_Line_Interpolate_Point to interpolate the POI on the road.

Here is the SQL to extract the interpolated POI on the road:

```
WITH data AS (
SELECT 'LINESTRING (50 40, 40 60, 50 90, 30 140)'::geometry AS road,
'POINT (60 110)'::geometry AS poi)
SELECT ST_AsText(
ST_Line_Interpolate_Point(road, ST_Line_Locate_Point(road, poi))) AS projected_poi
FROM data;
```

Returns `POINT(44.4827586206897 103.793103448276)`

, which is close to the POI, but projected on the road.

The tricky things you might run into is to locate the closest road LINESTRING and/or, your road network might be a MULTILINESTRING, which needs to be broken down into LINESTRINGs in order to work with the above. Also, linear referencing systems work best with projected (i.e., non-Lat/Lon) data, particularly if your data are far north/south from the equator.

```
WITH data AS (
SELECT 'MULTILINESTRING ((20 30, 40 70, 40 110),
(40 110, 70 160, 80 190),
(40 110, 25 118, 10 140))'::geometry AS road_network,
'POINT (40 130)'::geometry AS poi)
SELECT ST_AsText(ST_ClosestPoint(road_network, poi)) AS closest_poi
FROM data;
```

(Also, as a side note, I'm using a CTE or "WITH" query to supply data. Your query only needs to use the "SELECT" part.)