Jeff, If you are using PostgreSQL 9.0+ you can use the new ORDER BY for aggregates which is bullet-proof as fara s sorting.
SELECT b.id, b.var1, b.var2, b.var3, round(st_length(st_transform(st_makeline(b.the_point ORDER BY b.time), 2163)) * 0.000539956803::double precision) AS length FROM a AS b GROUP BY b.id, b.var1, b.var2, b.var3 BTW: The PostgreSQL 9.0 syntax is documented in latest PostGIS manual. http://www.postgis.org/documentation/manual-svn/ST_MakeLine.html Hope that helps, Regina http://www.postgis.us > -----Original Message----- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Jeff Adams > Sent: Tuesday, October 18, 2011 1:35 PM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] st_makeline group by issues > > I am having some difficulties getting the st_makeline to work > properly when using a group by clause with multiple items. My > query is of the format: > > SELECT > b.id, > b.var1, > b.var2, > b.var3, > round(st_length(st_transform(st_makeline(b.the_point), 2163)) > * 0.000539956803::double precision) AS length FROM ( > SELECT > a.id, > a.var1, > a.var2, > a.var3, > a.time, > a.the_point > FROM > table a > ORDER BY > a.time > ) b > GROUP BY > b.id, > b.var1, > b.var2, > b.var3 > > When I execute the above, I get incorrect results. If I > combine id, var1, var2, and var3 into a single variable (e.g. > assign unique id's to all unique combinations of the four > variables) and then execute the query below, the query > produces the correct results. > > SELECT > b.combined_id, > round(st_length(st_transform(st_makeline(b.the_point), 2163)) > * 0.000539956803::double precision) AS length FROM ( > SELECT > a.combined_id > FROM > table a > ORDER BY > a.time > ) b > GROUP BY > b.combined_id > > Any thoughts would e greatly appreciated... > > Jeff > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
