1

I have a PostgreSQL PostGIS point table (not multi-points). I want to convert the points to polygons (using convex-hull?) grouping the points by shared attributes from 2 columns.

points to polygon example

In this example, 'alpha' and 'beta' are text from a VARCHAR 'column1' and the numbers are from an id integer type 'column2'.

What is the best way to go about this? Please keep in mind the final table I'll be working on is large (30 million points (100s of points making up each grouping), 6GB table size).

|improve this question
  • @BERA yeah sorry that's just a mistake in my drawing and understanding of convex hull. – Theo F Jul 15 at 9:54
  • 3
    Have you tried ST_ConvexHull and grouping by the two columns? Should work – BERA Jul 15 at 9:56
  • Questions seeking the "best" of anything are problematic here, since they imply an opinion-based component. I suggest you build a covering index on the attribute used to union the points, union the points, and compute the convex hull. If you encounter a problem, then you'll have a question which isn't future-dependent. – Vince Jul 15 at 11:06
1
CREATE TABLE schema.polytable AS
SELECT column1, column2, ST_AsText(ST_CONVEXHULL(ST_COLLECT(geomcolumn))) AS newgeomcolumn
FROM schema.sourcetable
GROUP BY column1, column2

As suggested by someone else, this works in this scenario.

|improve this answer
  • You should really have accepted the answer of @bm13563 rather than copying it and saying it works. – John Powell Jul 15 at 11:17
  • 1
    @JohnPowell his answer was removed for some reason. – Theo F Jul 15 at 11:25
  • 2
    Ah, ok, fair enough, and a bit strange, seeing as it was correct. Apologies for false accusation. – John Powell Jul 15 at 11:28
  • Maybe in this case it's better to create an index on the columns first ? like a btree(column1, column2). If there is performance issues on a very big table that can be worth the try – robin loche Jul 19 at 13:06

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.