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"
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
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
If you really mean to only include geometries with a certain srid, add
at the end of the where clause.