3

I am trying to create a map which shows points that are clustered (or not depending on the distance between the points).

So, I have the following query:

  SELECT
  ST_NumGeometries(gc) as num_geometries,
  ST_AsText(gc) AS geom_collection
  FROM (
    SELECT unnest(ST_ClusterWithin(coordinates, 0.0035)) AS gc FROM (
        SELECT DISTINCT ON (coordinates) coordinates FROM chargepoints
            WHERE coordinates && ST_MakeEnvelope(4.281578063964845, 51.81646817257424, 4.635200500488282, 51.99460541491795, 4326)) AS points
) as result;

I am using ST_MakeEnvelope to retrieve charge points within that rectangular map. ST_ClusterWithin makes geometry collections where each collection holds a set of geometries:

2   GEOMETRYCOLLECTION(POINT(4.29696 51.85363),POINT(4.29721 51.85387))
12  GEOMETRYCOLLECTION(POINT(4.303751 51.854067),POINT(4.30149548 51.8520582),POINT(4.302743 51.853874),POINT(4.303193 51.854307),POINT(4.303058 51.854648),POINT(4.30405397 51.8524191),POINT(4.3007711 51.8529992),POINT(4.30717715 51.85126392),POINT(4.30502108 51.85145128),POINT(4.30586433410644 51.8506546020508),POINT(4.305203 51.852615),POINT(4.305643 51.851641))
1   GEOMETRYCOLLECTION(POINT(4.306028 51.836928))

What can or should I do with each collection to eventually get circled clusters on an map?

Please let me know if you need clarifications.

Using type geometry and SRID 4326, coordinates are in degrees (lat lon)

5

ST_ClusterWithin is an aggregator function, meaning it will not generate a new geometry, but simply output the geometries that were input to it, albeit in a particular grouping (the clusters, in this case).

If you want a visual representation of your clusters, you can do so by feeding your resulting Geometry Collections into ST_ConvexHull. This will give you a polygon encircling your points, though it won't look very good. If you want to further improve it visually, you can wrap it around ST_Buffer, as such:

SELECT ST_Buffer(ST_ConvexHull(geom), 0.001)
FROM geom_collection_table

I did it as a test with the coordinates you posted, with a buffer of 0.001 units, and the result is as follows:

Cluestered geometries

  • Thanks! This answer makes a lot more clear! May I ask what did you use to visualize the ouput. – nelsonvarela Jan 31 '18 at 13:01
  • QGIS, with the DB Management plugin. – Roberto Ribeiro Jan 31 '18 at 13:03
  • Consider also ST_MinimumBoundingCircle – dbaston Jan 31 '18 at 15:26
  • Can you give some more info about the distance used in ST_ClusterWithin? Say I want multiple small clusters in the envelope for the given coordinates. Do I have to calculate the desired distance based on the width of the envelope (xmax - xmin)? – nelsonvarela Jan 31 '18 at 16:48
  • @dbaston ST_MinimumBoundingCircle is an interesting approach, but outliers might produce too big a cluster that would overlap neighbouring clusters. If there was such a thing as ST_MinimumBoundingEllipse, that'd be better. Also, single data point clusters would have a zero geometry cluster, which could cause all sorts of problems. – Roberto Ribeiro Jan 31 '18 at 17:47

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.