2

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?

  • 1
    Is that the entire query? I ask because that will just give you a syntax error. What error are you getting? – Richard Law Oct 11 '16 at 23:16
  • 1
    Did you try to run the query in postgre first before try it in mapserver ? – Shiko Oct 12 '16 at 1:22
  • yes I did run it in pg – Mike Louis Oct 12 '16 at 1:57
1

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.

  • thanks for the response. But My problem is adding the query as a layer in a gis map. Understand? – Mike Louis Oct 11 '16 at 23:36
  • OK so this depends a little on what you're using to display layers. Most likely you just need to store this result as a table (or a view). I'll edit this in. – Richard Law Oct 11 '16 at 23:41
  • Sorry, I just noticed the mapfile declaration for MapServer (I think? — I've never used that). I would still be inclined to store this query as a view, and refer to it in that declaration, which is essentially what your're doing already with the query inside that text file, but at the database level. – Richard Law Oct 12 '16 at 0:10
  • Did you test the query that selects the convex hull "as the_geom" in your map file? Your title and tags are a bit misleading if the main problem is how to use subquery in MapServer mapfiles. – user30184 Oct 12 '16 at 4:10
  • Yes i did test the query. The query is/was unsuccessful. – Mike Louis Oct 12 '16 at 14:25

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.