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
3
  • @BERA yeah sorry that's just a mistake in my drawing and understanding of convex hull. Commented Jul 15, 2019 at 9:54
  • 3
    Have you tried ST_ConvexHull and grouping by the two columns? Should work Commented Jul 15, 2019 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. Commented Jul 15, 2019 at 11:06

1 Answer 1

Reset to default
2 
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
4
  • You should really have accepted the answer of @bm13563 rather than copying it and saying it works. Commented Jul 15, 2019 at 11:17
  • 1
    @JohnPowell his answer was removed for some reason. Commented Jul 15, 2019 at 11:25
  • 2
    Ah, ok, fair enough, and a bit strange, seeing as it was correct. Apologies for false accusation. Commented Jul 15, 2019 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 Commented Jul 19, 2019 at 13:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.