Is the below query correct to get a convex hull shape:

DATA "the_geom from (select ST_ConvexHull(ST_Collect(the_geom)) As the_geom FROM xyz) as foo using unique id using SRID=XXXX"

  • Apologies, I have just realized this was about mapserver. Mapserver needs a gid, so I think you will need to use st_dump on the results of convex hull, to get the underlying geometries and an id. I will write up a better answer later. – John Powell Oct 8 '14 at 6:29
  • I have updated the answer based on realizing this is a MapServer question. I don't have MapServer to test, but I am sure about the convex hull subquery, and read the docs on Mapserver to see how to create a DATA element. Hope this helps. – John Powell Oct 8 '14 at 10:44

EDIT: Update, having belatedly realized this is a Map Server question.

In general, to get the convex hull, you will want to group by id/gid, as producing the hull(s) is an aggregate operation. So, without the Mapserver part, the query would look like:

SELECT id, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from xyz group by id;

Now, to get MapServer to understand this in a DATA definition, you would do:

DATA "the_geom FROM (
   SELECT id as oid, 
   ST_ConvexHull(ST_Collect(the_geom)) as the_geom 
   FROM xyz group by id
) as some_table USING UNIQUE oid USING SRID=4326"

where I have assumed your SRID is 4326.

Original, incorrect answer:
You don't need the SRID at all, unless you wish to transform from one coordinate system to another, or you only want to select geometries with a certain srid in the query -- but it would be a bit unusual to have mixed srid geometries in the same column. There is no need for a sub-query, you can just write,

SELECT ST_ConvexHull(ST_Collect(the_geom)) as the_hull 
FROM xyz WHERE id=someid;

where I have changed the output to the_hull to differentiate from the input the_geom. If you really mean to only include geometries with a certain srid, add and st_srid(the_geom)=some_srid` at the end of the where clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.