5

I've a Postgis table of around 80,000 points and want to be able to populate a second table summarizing the data with convex hulls based on points with a common attribute.

For example, the data contains the telephone exchange which each point is associated with - I want to create a convex hull for all points in each telephone exchange. It ought to create around 1000 overlapping polygons.

I tried using the normally excellent QGis ftools convex hull plugin with the option to create hulls based on an input field but my computer spent 6-hours maximising 3 of the four cores before crashing QGis.

And I've tried doing it directly in sql:

INSERT INTO new_table(exchange_name, the_geom)
SELECT exchange_name, ST_ConvexHull(ST_Collect(the_geom)) As the_geom
FROM first_table
GROUP BY exchange_name

But this complains about invalid geometry - I've tried geometry constraints of "MULTIPOLYGON" and generic "GEOMETRY" so not sure why this should happen - the source table loads perfectly in QGis.

The Grass "v.hull" tool only creates a convex hull for all points and not based on an attribute.

Any tips or suggestions would be very welcome!

Improve this question
2
  • This might be a dumb question - with the added validation, there appears to be nothing wrong with the SQL syntax - running the statement without the INSERT so its just a SELECT creates what to the eye looks like a perfectly good result. So my logic suggests perhaps there may be something wrong with the geometry CONSTRAINT on the target table. What geometry type is created by ST_Convexhull? I've assumed MULTIPOLYGON and tried GEOMETRY.
    – Adrian
    Aug 14, 2011 at 9:49
  • Just to add one more detail. When I remove the geometry constraint from the target table and run the statement it successfully populates the new table, and loads perfectly in QGis. But if I try to add the constraint, if gives an error: ALTER TABLE bt_nga_exchange_mar11 ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); ERROR: check constraint "enforce_geotype_the_geom" is violated by some row
    – Adrian
    Aug 14, 2011 at 10:07

2 Answers 2

Reset to default
7

my guess is that you get invalid polygons because one or more groups only have one or two points. that can not make a convexhull polygon.

if that is the problem you can exclude the problematic groups with something like

INSERT INTO new_table(exchange_name, the_geom)
SELECT exchange_name, ST_ConvexHull(ST_Collect(the_geom)) As the_geom
FROM first_table
GROUP BY exchange_name 
having count(*)>=3;

if there is rows in the original table with multipoints you will need to count the points in the collection instead:

INSERT INTO new_table(exchange_name, the_geom)
SELECT exchange_name, ST_ConvexHull(ST_Collect(the_geom)) As the_geom
FROM first_table
GROUP BY exchange_name 
having st_npoints(st_collect(the_geom))>=3;

I am not by a computor to try, but I think it should work.

HTH Nicklas

Improve this answer
5
  • Thanks - a very good point and one that makes me feel slightly foolish that I hadn't thought of. I ran the SELECT with both your suggestions which worked perfectly but there were no collections with 2 or fewer points - most have several hundred.
    – Adrian
    Aug 14, 2011 at 9:31
  • What do you get from: SELECT ST_NPoints(st_collect(the_geom)) FROM first_table GROUP BY exchange_name order by ST_NPoints(st_collect(the_geom)) LIMIT 1;
    – Nicklas Avén
    Aug 14, 2011 at 10:32
  • Just "1" - I'm afraid my knowledge of PostGis has just expired - what does this tell me?
    – Adrian
    Aug 17, 2011 at 8:39
  • it just tells you that you have at least 1 group with only one point. it was a double check that you said you had no group with 2 or less points. apparently you ddo and that seems to be your problem. to see all problematic groups and their exchange_name you can run: SELECT exchange_name, ST_NPoints(st_collect(the_geom)) FROM first_table GROUP BY exchange_name having st_npoints(st_collect(the_geom)) < 3;
    – Nicklas Avén
    Aug 17, 2011 at 10:16
  • Thank you for your patience! I don't know what was going on but I re-ran the SQL you suggested and it worked perfectly this time with no complaints. The problem was clearly somewhere between the keyboard and my chair.
    – Adrian
    Aug 18, 2011 at 11:27
1

Have you tried running the input data against ST_IsValid?

Example: select exchange_name from first_table where ST_IsValid(the_geom)=false

If any rows are returned then you have invalid geometries and you have to either fix them or exclude them from the above query.

Improve this answer
1
  • Thank you - I did as you said, and found a small number of points with no geometry as opposed to corrupt geometry but sadly it made no difference to the result. As a newly learnt example of good practice, I've added the line ST_IsValid(the_geom)=TRUE to the WHERE clause so it makes sure I'm only working with good geometry while I continue to debug.
    – Adrian
    Aug 14, 2011 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.