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 .
With "projection", do you mean "find the spatial reference" or "find the nearest point projected on the road network"?– Mike TApr 15, 2012 at 21:40
Thanks for your reply Mike .I think the second one ! but what do you mean by spatial reference?– johnApr 16, 2012 at 17:48
spatial reference systems are things like UTM vs latitude/longitude vs Google Mercator, etc. Some folks call these "projections"– Mike TApr 16, 2012 at 22:38
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;
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.
Update An easier function that can use other geometries, including MULTILINESTRINGs is ST_ClosestPoint. For example, the closest POI on a road network is found using:
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.)
Thanks a lot I 'll give it a try .My road network is MULTILINESTRING (i used osm2pgrouting to import my data to the database)is there an easy way to break my MULTILINESTRING to LINESTRING (I will google it but if you know you will spare me some time :) )?– johnApr 18, 2012 at 13:38
Can we do the same with stored procedures in pl/pgsql and get the geometry mike? Jun 4, 2012 at 21:03
@OpenGIStech you can do pretty much anything with PL/pgSQL– Mike TJun 4, 2012 at 21:53