3

I have a PostGIS 2.4 database with the table that contains more than 300 000 Point features (with big density on the map on high zooms like 10-12 if you will draw them).

So I need to query that table and return the points that are separated from each other by a distance (say, 10 km) to reduce this density. I was wondering if I can do this with the ST_DWithin function or anyone else, but I can not figure out how to do it. Moreover, it needs to work fast (1-2 seconds is the max time for the query). Maybe somebody can help me?

Jack Owels
  • 31
  • 1
  • I would either use ST_Dwithin as you advised, or ST_Distance since you might need the real distance between each point in order to do what you're looking for. – Moreau Colin Nov 17 '17 at 12:02
  • You want to find these that are over 10km away from other points? Or find those that are within 10km of other points? I don't think you can do this fast either. But it could be precalculated. – HeikkiVesanto Nov 17 '17 at 12:05
  • @heikkivesanto I wanna to exclude some number of points that are close to some base points on some distance (say 10 km). So I wanna to reduce the points density – Jack Owels Nov 17 '17 at 13:37
  • What is the use case? Could perhaps be done on client side if web mapping: https://github.com/mapbox/supercluster – HeikkiVesanto Nov 17 '17 at 14:20
  • I use vector tiles, not raw GeoJSON (it size is more than 1.5 Gb). So I can't use mapbox clustering – Jack Owels Nov 17 '17 at 15:10

2 Answers2

2

You can use the PostGIS function ST_ClusterWithin(). That way, you can determine the threshold at which the clusterering happens (100m, 1km, 10km, ...). Then you compute the centroid of the minimum bounding circle and map this result instead.

SELECT row_number() over () AS id,
  ST_NumGeometries(gc),
  ST_Centroid(ST_MinimumBoundingCircle(gc)) AS cluster_centroid,
  sqrt(ST_Area(ST_MinimumBoundingCircle(gc)) / pi()) AS radius
FROM (
  -- Change 100 with the distance you want (units from the EPSG you are using)
  SELECT unnest(ST_ClusterWithin(geom, 100)) gc
  FROM rand_point
) f;

The code is pretty much from Mike T's answer here: Spatial clustering with PostGIS

You will have to change a few things to get the desired behaviour.

There may be another simpler function if you have PostGIS 2.3, but I never used it myself. https://postgis.net/docs/ST_ClusterDBSCAN.html

TurboGraphxBeige
  • 1,467
  • 10
  • 24
  • The problem of this solution is executing time. I launched the query and wait for 30 minutes, but didn't get some result (even when I limit sub-query to 1000 records ) :( – Jack Owels Nov 17 '17 at 13:41
  • 1
    @JackOwels that runtime is surprising; I've run this function on datasets of millions of points quite quickly. Are you sure the distance threshold is correct (i.e, if your data are in geographic coordinates, are you using degrees instead of meters here?) – dbaston Nov 17 '17 at 15:43
  • @JackOwels Could that be because there is no index on the geometry field? – TurboGraphxBeige Nov 17 '17 at 18:10
  • @GobTron Columns: wkb_geometry | geometry(Point,4326), Indexes: "table_pkey" PRIMARY KEY, btree (ogc_fid) "table_wkb_geometry_geom_idx" gist (wkb_geometry) – Jack Owels Nov 20 '17 at 07:20
  • Well by the look of it it seems that your geometry is in 4326 so if you are trying to make a cluster of "100", it's 100 degrees. It probably takes a long time to complete the request. Did you try projecting you geometries in something where units are meters? – TurboGraphxBeige Nov 20 '17 at 11:06
  • No, unfortunately I have very less experience with the projection math and PostGIS :( – Jack Owels Nov 20 '17 at 12:27
  • Try using 0.00001 or something instead of "100". Or make a copy of the table with a projected geometry column (and create and GiST index on that field). You can use DBManager from QGIS for that. – TurboGraphxBeige Nov 20 '17 at 12:52
1

I'm not sure but I think you could use a delete (or just a select statement) function combined with an over partition function (of course with the st_Dwithin function) .

If I'm right, by this way, you could delete the points which are located under 10 km from another point and then reduce the density.

I found this way : 

update test.points q set todelete = 1 

from (
    SELECT
         p1.gid p1_gid
        ,p2.gid p2_gid
        ,ST_Distance(p1.geom, p2.geom) dist
        ,rank() OVER (PARTITION BY p1.gid ORDER BY ST_Distance(p1.geom, p2.geom)) AS pos
    from test.points p1
    left join test.points p2 on st_dwithin(p1.geom, p2.geom, 10)
    where p2.gid is not null and p1.gid <> p2.gid and ST_Distance(p1.geom, p2.geom) > 0
    order by p1.gid
) as n 


where n.p2_gid = q.gid;

The problem is that if you've got several points close together, you'll lost all of them... I'm working on a test which can check the combinations and keep 1 of n nearest points...

Keiko
  • 319
  • 1
  • 5
  • Yep, I can't lost them, I need to save one of the points and then show it on the map. Moreover, it must be as fast as possible (not longer than 10 minutes in any case).. – Jack Owels Nov 17 '17 at 13:45