1

I have a simple SpatiaLite DB containing one table with point features. I would like to generate a Minimum Convex Hull around that features. I could do that easily with one of the Convex Hull tools from GDAL/SAGA/GRASS/R-Script... from processing toolbox. But I would like to have a dynamic SQL solution. So every time a new point feature is added, the Convex Hull should automatically be updated. So I thought about a SQL-View:

I tried around following this instructions: http://postgis.net/docs/ST_ConvexHull.html

CREATE VIEW birdsmcp AS 
SELECT 
  ST_ConvexHull(ST_Collect(geom)) 
FROM birds GROUP BY bird;

I get a table, but no geometry. What do I have to do to get shapes as a result? I'm not very familiar with SQL queries.

EDIT: Thats my result in QGIS DB-Manager: enter image description here

enter image description here

  • Why do you think that you don't get a geometry back? – Nicklas Avén Jun 17 '17 at 8:11
  • There is no Preview available. See my EDIT please – MAP Jun 17 '17 at 8:40
  • what do you see under the table view? and do you see anything if you add the view to the canvas? I managed to see similar behaviour on a virtual scratch point layer (which uses spatialite, I think) but was able to see it on adding to the canvas. – Steven Kay Jun 17 '17 at 10:05
  • 2
    I don't know much about Spatial Lite, but in general it is a good idea to put some kind of id (primary key) column into anything you generate. A shp file, produced like this, will show blank. So, add a row_number() over() as id to your select for starters. Also, trying to view things in GUIs can be difficult, especially if they are large binary fields. To prove something is there, try wrapping in ST_Centroid, or ST_NPoints or some other function that will return a short, human readable value. – John Powell Jun 19 '17 at 9:36
  • 1
    You could try something like this: CREATE VIEW birdsmcp AS SELECT row_number() over() as id, bird, ST_ConvexHull(ST_Collect(geom)) FROM birds GROUP BY bird; Then you have an ID and even the name of the bird. @MAP Does this work? – coding_bird Jun 19 '17 at 9:58

Your Answer

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

Browse other questions tagged or ask your own question.