[postgis-users] st_makeline group by issues

[Jeff Adams]

Thanks Regina. I imagine it is a bit quicker too. I will give it a try...

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
Corporation
Sent: Tuesday, October 18, 2011 10:08 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] st_makeline group by issues

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-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Jeff Adams
> Sent: Tuesday, October 18, 2011 1:35 PM
> To: postgis-users at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users