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: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
orINSERT 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:You can also predefine a schema and indexes and use
INSERT INTO
.