[postgis-users] How does makeline work?

[Guido Lemoine]

Hi all,

I am having some trouble getting makeline to work correctly. According
to the manual, it will accept a set of point geometries. For my application,
it is important to keep the order of the points  (ultimately, I use it
to create a closed polygon that describes the outer bounds of a 
satellite image
ground control points. I get the GCPs from gdalinfo).

My GCP table is of type (gcp_id integer not null, gcp_geom geometry). gcp_id
is a unique sequential number, gcp_geom is a point (lon, lat).

 select astext(makeline(gcp_geom)) from testset;

works fine, but I want to revert the order of the point sequence.

select astext(makeline(gcp_geom)) from testset order by gcp_id desc;
ERROR:  column "testset.gcp_id" must appear in the GROUP BY clause or be 
used in an aggregate function

this doesn't work, but that is to be expected for an aggregate function.

select astext(makeline(gcp_geom)) from testset where gcp_geom IN (select 
gcp_geom from testset order by gcp_id);

this works, and produces the same line as when not using the where.

select astext(makeline(gcp_geom)) from testset where gcp_geom IN (select 
gcp_geom from testset order by gcp_id desc);

Now, I would expect the same line, but in reverse order. But this is not 
the case (it's the same as before).

The manual says "You might want to use a subselect to order points 
before feeding them to this aggregate",
and this gave me hope. My current workaround is a plpgsql function that 
solves the problem, but it's a bit
awkward.

Any help appreciated

Guido Lemoine.