Reprojecting / snapping point-geometries onto a given line-geometry using PostGIS

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:

The points from A-I are being projected onto the blue Line resulting in points A' - I'

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;

10.000 reddish points snapped to the yellow line. The reprojected points are displayed as bigger green circles and it shows that there is no maverick.

Seems to work well. As usual with PostgreSQL / PostGIS 🙂

Changes:
09:10h Updated image captions

Advertisement