2

I have a regular grid, each point in the grid belongs to some cluster, and I need to find geometry of this clusters.

My tables look like so (only necessary fields):

grid_nodes( *id uuid, geom GEOMETRY(Point) ) clusters( *id uuid ) grid_node_cluster( grid_node_id uuid, cluster_id uuid )

I have not experience with Postgis and GIS, so I've been googling for couple of hours, and found a few similar (but not the duplicates) questions:

where I found some clues, but not found the answer.

At the end I wrote SQL query like this, but unsuccessfully:

SELECT ST_ConcaveHull(geom, 0.8, true) as geom, st_geometrytype(ST_ConcaveHull(geom, 0.9, true)), uuid_generate_v4() as id FROM ( SELECT unnest(ST_ClusterWithin(gn.geom, 0.025)) as geom FROM ds_forecast_territory.grid_nodes as gn JOIN ds_forecast_objects.grid_node_clusters as gnc ON gn.id = gnc.grid_node_id AND gnc.cluster_id = 'ef2e3314- d0c9-4751-9b0f-cd5b7cf3c4e5') as clusters WHERE st_geometrytype(ST_ConcaveHull(geom, 0.9, true)) = 'ST_Polygon'

where clause in the query and uuid are just dirty hacks for QGIS. This query somehow works, but resulting geometry is messy and inaccurate.

This is example of the points, and borders which I want to find: what I want

And this is the result I was able to get with my query:

what I can

Can I solve my issue using Postgis, or is it impossible and I need to use some spatial libraries for Python?

I've found it's probably called alpha shape, but I still can't solve the issue.

With help from @Spacedman and @dbaston, the answer; 

DO $$ DECLARE _cluster_id UUID; _cluster_geometry GEOMETRY; BEGIN FOR _cluster_id IN SELECT id FROM ds_forecast_objects.clusters LOOP SELECT st_union(st_expand(gn.geom, 0.0041, 0.0023)) INTO _cluster_geometry FROM grid_nodes as gn JOIN grid_node_clusters as gnc ON gn.id = gnc.grid_node_id AND gnc.cluster_id = _cluster_id; INSERT INTO clusters_geometry(cluster_id, geom) VALUES (_cluster_id, _cluster_geometry); END LOOP; END $$

And the result in QGIS:

result

Improve this question
5
  • 2
    Outline: from each point create a square polygon of the size of the grid separation, then merge them. You might need to make them a bit bigger than the grid separation so they definitely overlap. You might be able to make square polygons from points using a buffer with a tiny corner radius... Commented Jul 10, 2018 at 7:52
  • Use ST_Expand to make rectangular polygons from a center point. Commented Jul 10, 2018 at 13:53
  • @Spacedman thanks for idea, but I need more accurate method. If I'm just expanding each point and find the union, resulting multipolygon covers nearest points of neighbour cluster. It's unacceptable in my case. Commented Jul 10, 2018 at 15:48
  • @dbaston look my comment above, please, I couldn't mention more than one user in it. Commented Jul 10, 2018 at 15:50
  • "more accurate"? how? my method results in multiple polygons where each polygon covers a set of points that are exactly one grid separation apart, which seems to be what your diagram is. Commented Jul 10, 2018 at 18:08

1 Answer 1

Reset to default
2

Here's a set of points on a grid of separation 3 units defined by the polygon that you see surrounding it:

enter image description here

If I buffer the points by 1.5 units with square ends and merge everything I get these boundaries in blue:

enter image description here

which seems to be the boundaries you want.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.