1

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

  • 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... – Spacedman Jul 10 '18 at 7:52
  • Use ST_Expand to make rectangular polygons from a center point. – dbaston Jul 10 '18 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. – NobbyNobbs Jul 10 '18 at 15:48
  • @dbaston look my comment above, please, I couldn't mention more than one user in it. – NobbyNobbs Jul 10 '18 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. – Spacedman Jul 10 '18 at 18:08
1

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.

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.