Imagine you have a line — a street for example — and point measurements taken while being on that street. Due to certain circumstances — e.g. GPS-coorrdinates are slightly inaccurate –, the coordinates of your measurements are not exactly on the street.
We had this problem just recently and needed to reproject the points onto the said line, here is what we wanted to achieve:
Thanks to the PostGIS Mailing list, here is a way to do just that
SELECT ST_Line_Interpolate_Point( line_to_project_onto.the_geom, ST_Line_Locate_Point( line_to_project_onto.the_geom, points_to_project_onto_line.the_geom ) ) FROM line_to_project_onto, points_to_project_onto_line;
Note: “line_to_project_onto” is a table containing one record: the line-geometry, “points_to_project_onto_line” is a table where all points are stored which should be snapped to the line, “points_projected_onto_line” — from the next example — is the table to store the reprojected points in. Here are short definitions from the PostGIS-SVN-Documentation
- ST_Line_Interpolate_Point – Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing fraction of total length of linestring the point has to be located (Documentation).
- ST_Line_Locate_Point – Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length (Documentation).
Let’s test it with 10.000 random points:
TRUNCATE points_to_project_onto_line; TRUNCATE points_projected_onto_line; INSERT INTO points_to_project_onto_line ( the_geom ) SELECT st_makePoint( random() * 10, random() * 10) FROM generate_series(1,10000); INSERT INTO points_projected_onto_line ( the_geom ) SELECT line_interpolate_point( line_to_project_onto.the_geom, line_locate_point( line_to_project_onto.the_geom, points_to_project_onto_line.the_geom ) ) FROM line_to_project_onto, points_to_project_onto_line;
Seems to work well. As usual with PostgreSQL / PostGIS 🙂
09:10h Updated image captions
2010-01-24 at 16:58
You have great blog and this post is good!
best regards, Greg
[selectoid]: Unsure if this was written by a bot. Removing email and URL. Contact me if you think that was a mistake.
2010-03-04 at 16:43
[…] уроков по PistGIS: проецирование точек на полилинию; пользовательская функция (рисуем […]
2010-06-10 at 21:39
Exactly what I was looking for. Very simple way of doing map matching.
2012-04-26 at 21:04
Hi, can this work for more than one road record, i.e. an entire road network?
2012-07-31 at 07:56
I’d say yes: if the road network is collected / coimbined in a previous step. Also one could use a user defined function which would first select the nearest road fo a point and then do the snapping as described.