Description

ST_MakeLine comes in 3 forms: a spatial aggregate that takes rows of point geometries and returns a line string, a function that takes an array of points, and a regular function that takes two point geometries. You might want to use a subselect to order points before feeding them to the aggregate version of this function.

This function supports 3d and will not drop the z-index.

Availability: 1.4.0 - ST_MakeLine(geomarray) was introduced. ST_MakeLine aggregate functions was enhanced to handle more points faster.

Examples: Spatial Aggregate version

This example takes a sequence of GPS points and creates one record for each gps travel where the geometry field is a line string composed of the gps points in the order of the travel.

-- For pre-PostgreSQL 9.0 - this usually works, 
-- but the planner may on occasion choose not to respect the order of the subquery
SELECT gps.gps_track, ST_MakeLine(gps.the_geom) As newgeom
	FROM (SELECT gps_track,gps_time, the_geom
			FROM gps_points ORDER BY gps_track, gps_time) As gps
	GROUP BY gps.gps_track;

-- If you are using PostgreSQL 9.0+ 
-- (you can use the new ORDER BY support for aggregates)
-- this is a guaranteed way to get a correctly ordered linestring
-- Your order by part can order by more than one column if needed
SELECT gps.gps_track, ST_MakeLine(gps.the_geom ORDER BY gps_time) As newgeom
	FROM gps_points As gps
	GROUP BY gps.gps_track;

Examples: Non-Spatial Aggregate version

First example is a simple one off line string composed of 2 points. The second formulates line strings from 2 points a user draws. The third is a one-off that joins 2 3d points to create a line in 3d space.

SELECT ST_AsText(ST_MakeLine(ST_MakePoint(1,2), ST_MakePoint(3,4)));
	  st_astext
---------------------
 LINESTRING(1 2,3 4)

SELECT userpoints.id, ST_MakeLine(startpoint, endpoint) As drawn_line
	FROM userpoints ;

SELECT ST_AsEWKT(ST_MakeLine(ST_MakePoint(1,2,3), ST_MakePoint(3,4,5)));
		st_asewkt
-------------------------
 LINESTRING(1 2 3,3 4 5)
			

Examples: Using Array version

SELECT ST_MakeLine(ARRAY(SELECT ST_Centroid(the_geom) FROM visit_locations ORDER BY visit_time));

--Making a 3d line with 3 3-d points
SELECT ST_AsEWKT(ST_MakeLine(ARRAY[ST_MakePoint(1,2,3),
				ST_MakePoint(3,4,5), ST_MakePoint(6,6,6)]));
		st_asewkt
-------------------------
LINESTRING(1 2 3,3 4 5,6 6 6)
			

See Also

ST_AsEWKT, ST_AsText, ST_GeomFromText, ST_MakePoint