-1

I am using PostGIS ST_ClusterWithin function for clustering:

SELECT
    row_number() over () AS id,
    ST_NumGeometries(gc) as total,
    ST_AsGeoJson(gc) as geo_collection,                                    
    ST_GeomFromGeoJSON(ST_AsGeoJson(gc)) as points,
    ST_AsEWKT(gc) AS geom_collection,
    ST_AsGeoJson(ST_Centroid(gc)) AS centroid,
    ST_MinimumBoundingCircle(gc)::geometry AS geo_circle,  
    ST_AsEWKT(ST_MinimumBoundingCircle(gc)::geometry) AS circle,  
    sqrt(ST_Area(ST_MinimumBoundingCircle(gc)) / pi()) AS radius,
    ST_MinimumBoundingRadius(gc) as bounding_radius
FROM (
    SELECT unnest(ST_ClusterWithin(ST_Transform(ST_SetSRID(geometry,4326),4326)::geometry,0.04505)) gc
    FROM geo_table
    WHERE <some_conditions>
) f;

The next step is to retrieve the single points clustered as single geometry.

I tried to search for some function to make some sort of "IN" inside a GeometryCollection but I can't find a solution.

ST_Contains (as Google suggest) return a boolean if a geometry is INSIDE another. That's is not what I'm looking for.

So I tried ST_Dump that splits collection to single rows. After some tries, I got the result with this query:

SELECT * FROM <geo_table> WHERE ST_SetSRID(geometry,4326) IN (                      
SELECT ST_SetSRID(a.geom,4326) FROM (
SELECT (ST_Dump(ST_GeomFromGeoJSON('<geo_collection>'))).geom as geom) a);

Is there a better way to get this result?

  • 1
    ...much of what you are doing there doesn´t really make sense ,) despite a lot of intermediate weirdness, if I´m not mistaken you just get all rows from <geo_table> in the last step...rendering your clustering useless. what exactly do you want to extract? – ThingumaBob Jun 18 '18 at 13:45
  • The clustering query is correct to generate a cluster Polygon. Unfortunally that query groups rows so I got the total rows and a collection of geometries. On Map, when I click on cluster, I have to show all the single elements merged in cluster so I need a specific query to get columns from <geo_table> so I need an "IN" query using geo_collections because is the aggregate geometry I already have from clustering query. – EviSvil Jun 18 '18 at 14:22
  • 1
    I also can't follow it -- what is all this json, sqrt stuff. Please remove all the extraneous stuff from the query, so that there is one clear question about clustering. – John Powell Jun 18 '18 at 14:50
  • The problem is not the Clustering. Forget about it and forget about format for a while. I have a COLLECTIONS of geometries. Just to make it more readable, take a GeoJSON: '{"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[16.1663818359375, 53.82011176955965]},{"type":"Point","coordinates" [...]' As you can see, I have a collection of POINTS. I have a geo_table with a lot of POINTS and I need to take the POINTS IN the collections. So I have to retrieve all points comparing their geometry with the geometry in the collections. – EviSvil Jun 18 '18 at 15:25
  • got ot to work? – ThingumaBob Jun 20 '18 at 10:23
1

tl;dr: my point will be to avoid GEOMETRYCOLLECTIONs (and ST_ClusterWithin) and go for ST_ClusterDBSCAN instead.


Since you seem to need aggregating data over each cluster (e.g. the radius of the minimal bounding circle), while apparently you also want to access the column data, or let's call them attributes, of each initial point, you have two general options:

  • aggregate the attributes of its components to each cluster (e.g. array_agg(<attribute>))

    or

  • append the clusterwise aggregated data (radius etc.) to each point.

I dare say most of the times you need spatial data clustered, it is pretty handy to have access to the individual geometric components with a reference to the cluster it belongs to ... and, unfortunately, that is cumbersome with the first of the options above, representing what you have tried in your queries using ST_ClusterWithin.

However, simply appending the same data for each cluster to each of its components generates tons of noise and is a waste of resources.

I´d say try ST_ClusterDBSCAN instead and produce the clusterwise stats on demand (I don't use any SRID definition; see notes below). For example would

SELECT ST_ClusterDBSCAN(geometry, 0.04505, 1) OVER() AS clst_id,
       *
FROM <geo_table>

add a column clst_id, with an integer representing the cluster number each row/geometry belongs to, into the selection with all the corresponding data/rows from <geo_table>, without aggregating.

Let's assume you want to actually update that table with those cluster ids. Run:

ALTER TABLE <geo_table> ADD COLUMN clst_id INT;

WITH
  a AS (
    SELECT ST_ClusterDBSCAN(geometry, 0.04505, 1) OVER() AS clst_id,
           <id>
    FROM <geo_table>
  )

UPDATE <geo_table> AS b
  SET clst_id = a.clst_id
  FROM a
  WHERE a.<id> = b.<id>;

to append the cluster ids to the original table. Neat. If your table is large, add an index on clst_id to speed up clusterwise selections:

CREATE INDEX <geo_table>_clst_idx
    ON <your_schema>.<geo_table>
    (clst_id)
    TABLESPACE pg_default;

From here, you can do all sorts of aggregation and statistics on those clusters; simply group your table by clst_id and collect the respective geometries, e.g.:

WITH                                                    -- add the uppermost query here to get 
  clst_geom AS (                                        -- the clusters if you don´t want to update your table
    SELECT clst_id,
           count(*) AS total,
           ST_Collect(geom) AS geom
    FROM <geo_table>
    GROUP BY clst_id
  ),

  clst_mb AS (
    SELECT clst_id,
           ST_MinimumBoundingRadius(geom) AS mbr        -- Note: using a record returning function
    FROM clst_geom                                      -- in SELECT is not encouraged!
  )

SELECT a.total,
       (b.mbr).radius AS radius,
       (b.mbr).centroid AS centroid,
       ST_Buffer((b.mbr).centroid, (b.mbr).radius) AS geo_circle,
       a.geom
FROM clst_geom AS a
JOIN clst_mb AS b
  ON a.clst_id = b.clst_id;

Some notes on CRS:

  • if your geometries are in EPSG:4326, why not simply updating the geometry column instead of setting the SRID each time? Also, you absolutely do not need to transform them again into the same CRS.
  • then, if you intend on using EPSG:4326 throughout your calculations, be aware that its units (degrees) do not represent the same metric distance for different latitudes! This affects the distance threshold for your clustering. Better project into a suitable projection with meter as units.

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.