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.
This is example of the points, and borders which I want to find:
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:
ST_Expand
to make rectangular polygons from a center point. – dbaston Jul 10 '18 at 13:53