[GIS] Computing convex hull of table using PostGIS Query in MapServer

convex hullpostgis

I am basically trying to compute the convex hull of a table in postgresql but I can't seem to get the query correct. My ultimate goal is to use this query as a polygon layer. The columns in the table i am trying to query contains:
the_geom, name, gid.

Here is my map file declarations:

 LAYER 
 NAME         res
 DATA         res
 STATUS       ON
 TYPE         POLYGON
 DUMP    TRUE
 CONNECTIONTYPE POSTGIS
 CONNECTION "user=blue password=applesnoranges dbname=dappl host=127.0.0.1"
 DATA "the_geom from (SELECT ST_ConvexHull(ST_Collect(the_geom))
    FROM resturants) as blankers using unique gid using SRID=5570"
 CLASS
   NAME       "Resturants"
   STYLE
     COLOR        232 232 232
     OUTLINECOLOR 32 32 32
   END
 END

END

Here is my query statement:

    the_geom from (SELECT ST_ConvexHull(ST_Collect(the_geom))
    FROM resturants) as blankers using unique gid using SRID=5570;

Can someone tell me where I am going wrong?

Best Answer

The documentation page on ST_ConvexHull gives a good example. You should just need:

SELECT ST_ConvexHull(
    ST_Collect(r.the_geom::geometry)
) AS the_geom
FROM restaurants AS r;

To store the result in a table that can't be visualised in (e.g.) QGIS, you need to use this statement as part of the definition of a view, or as part of a CREATE TABLE or INSERT INTO. Software differs on whether you need to define your own UUID column. There are good answers on StackOverflow about generating these. The simplest way to create a new table with your result is:

CREATE TABLE ch AS
SELECT ST_ConvexHull(
    ST_Collect(r.the_geom::geometry)
) AS the_geom
FROM restaurants AS r;

You can also predefine a schema and indexes and use INSERT INTO.

Related Question