ST_MakeLine agregation error in order

[arturbac]

There is a bug in ST_MakeLine agregate version. According to ​http://postgis.refractions.net/documentation/manual-1.5/ST_MakeLine.html

Such agregation works by ony when I dont agregate anything other that geometry on big table (with milions of records )

	select
		wayid,
		sub_seq,
		/*count(*)*/ 0 as points,
		st_makeline( geom ) as geom
	from (
		select
			wayid, sub_seq, geom
		from osm.segments_ways_t h
			join osm.node_t n using( nodeid )
		order by wayid, sub_seq asc, seq asc	
	) ws
	group by wayid, sub_seq;

If I uncomment "count(*)" mots of linestrings have wrong vertex order , it looks like the order by in subquery stops working. Another fact is when I restrict query to single wayid before order by for example by where wayid=xxxxxx, agregation of other things don't distorts vertex order

[robe]

Arturbac,

I fear this is not really a bug. The example in the docs usually works, but there is nothing in the ANSI-SQL specs that guarantees the order of the sub query is preserved so on occasion the planner may choose not to respect the order. I've just never noticed it in PostgreSQL changing the order.

If you are running PostgreSQL 9.0+, the guaranteed way is to use the new ORDER BY clause within the aggregation. I've updated the svn manual to reflect the new way. Sadly it won't work unless you are using 9.0+ (the postgis version doesn't matter)

​http://www.postgis.org/documentation/manual-svn/ST_MakeLine.html

Give that a try and see if that works better

[arturbac]

Yes, thanks it looks like it works ok, even with other aggregating functions.