Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
    
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 '11 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 '11 at 10:07

2 Answers 2

up vote 6 down vote accepted

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

share|improve this answer
    
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 '11 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 '11 at 10:32
    
Just "1" - I'm afraid my knowledge of PostGis has just expired - what does this tell me? –  Adrian Aug 17 '11 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 '11 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 '11 at 11:27

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.

share|improve this answer
    
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 '11 at 9:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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