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:
- polygon from exterior points
- finding the outermost border of a set of geomertries (circles)
- Using postgis ST_ClusterWithin() on a table
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.
And this is the result I was able to get with my query:
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: