[postgis-users] st_makeline group by issues

[Jeff Adams]

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