ST_Makeline is an aggregate function that takes a sequence of points and strings them together to make a line. In versions of PostGIS prior to 1.2.2, this was called MakeLine. For prior versions - replace ST_MakeLine with MakeLine.

ST_MakeLine Example: Create a line string of Boston Marathon practice route for each day

In this example we have a table of gps point snapshots for our marathon practice for each day broken out by time. We want to convert the points for each day into a single record containing the line path of our run for that day.

NOTE: For this example the trip_datetime field is of type timestamp so records the date and time the gps position was recorded. CAST(trip_datetime As date) (this is the ANSI sql standard) or PostgreSQL specific short-hand trip_datetime::date strips off the time part so we are just left with the day.

We do a subselect with an order by to force the points to be in order of time so that the points of the line follow the path of our run across time.

SELECT St_MakeLine(the_point) as the_route, bp.trip_date
FROM (SELECT the_point, CAST(trip_datetime As date) as trip_date
		FROM boston_marathon_practice 
			ORDER BY trip_datetime) bp 
GROUP BY bp.trip_date;

Convert Parcel points into line segments

In the previous example since our grouping field is in the same order as the datetime, we only needed to order by one field in our subselect. If your groupings are not in the same order as your line order, then you need to do additional orders for each field you plan to group by. If you don't your resulting points may get shuffled. In the below simplified example we have parcel centroids and we want to make line segments that join them such that for each unique street and zip we have one line segment. For extra measure we want our final set to include the start number range and end number for this segment.

This is a failry simplistic example. In reality you would probably need to do a little bit more because street segments have same names in Boston even within the same zip. We are also assuming the line segment drawing should follow the order of the street numbers and our street numbers are numeric.

SELECT p.street, p.zip, ST_MakeLine(p.the_point) As streetsegment, 
          MIN(st_num) as st_num_start, MAX(st_num) As st_num_end
FROM (SELECT street, zip, centroid(p.the_geom) as the_point, st_num  
          FROM parcels ORDER BY zip, street, st_num) p
GROUP BY p.zip, p.street