5

I have many polygons stored in a table. I want to filter out polygons where they separate too far away (say 1km).

My approach is: calculate the minimum distance between a particular polygon to other polygons, and store the value in a new column in the table.

Then filter out polygon with minimum distance larger than 1 km.

I have tried to use:

ALTER TABLE "int_20190124" ADD "nearestDistance" float8;
INSERT INTO "int_20190124"("nearestDistance")
VALUES
(
SELECT ST_Distance("int_20190124".geom, "int_20190124".geom)
FROM "int_20190124"
ORDER BY ST_Distance("int_20190124".geom, "int_20190124".geom)
LIMIT 1
)

I am very new to postgis.

7

You can use ST_ClusterDBSCAN to group nearby geometries together and assigning them a cluster id. Cluster ID will be null for single geometries not within the specified distance of another.

Image taken from the help section showing cluster IDs:

enter image description here

This query should return only the records within 1000 m of Another. minpoints := 2 is to prevent single points from getting a cluster ID (since they are always within the distance of themselves):

SELECT * FROM
   (SELECT *, ST_ClusterDBSCAN(geom, eps := 1000, minpoints := 2) OVER () clusterid
      FROM int_20190124) t1
  WHERE t1.clusterid IS NOT NULL;

Example with points and 10000 m distance: enter image description here

  • Why minpoints := 2 and not minpoints := 1? – John Powell Feb 14 at 7:28
  • With 1, single records with no other Points nearby will also get a cluster id. Dont you agree? – BERA Feb 14 at 7:41
  • 2
    Yes, probably. I have always put array_agg(cluster_id) as cluster_ids in the inner select and then used WHERE array_length(cluster_ids, 1) > 1 for this kind of logic, but your approach is simpler. You should probably explain it in the answer, though, as ClusterDBScan is a bit non-obvious when you first see it +1 anyway, this really is one of my favourite functions, it has so many cool uses, that are really painful to do the old way with spatial self joins. – John Powell Feb 14 at 8:00
  • @BERA Thank you! Although I still don't understand the query completely, it works! I will study more PostGIS later. – JOHN Feb 14 at 8:37
  • The underlying theory for DBScan en.wikipedia.org/wiki/DBSCAN is worth a read. This is a somewhat tricky query as it also involves a window function, postgresql.org/docs/current/tutorial-window.html, so don't feel too bad if you don't get it immediately. The benefit of a window function is that you can return the actual geometry IDs in each cluster, which was not the case with the original Postgis clustering algorithms, such as, ST_ClusterIntersecting and ST_ClusterWithin. – John Powell Feb 14 at 9:09
2
SELECT id
FROM polygons
WHERE id IN (
    SELECT
        p1.id
    FROM polygons p1
    JOIN polygons p2 ON (ST_DWithin(p1.geom, p2.geom, 1000))
    WHERE p1.id <> p2.id
);

You can join polygons on ST_Dwithin to find these being at most one km far from others. Note distance being used is given in SRID units, so be careful when using degrees.

I assume GIST index on geom field as well as PRIMARY KEY on id field exists. As stated in docs, ST_Dwithin includes bbox comparison and generally speaking, it should be faster than building buffers around the input geometries.

  • this feature ST_DWithin automatically includes a bounding box comparison, which is why its use is less accurate, which is why I've abandoned it, but your solution seems interesting to me too, and the abundance of answers enriches the question, respectfully... – Cyril Feb 15 at 10:38
  • 1
    Bounding box comparison doesn't imply function to be less accurate. It implies function to use bbox for prefiltering the data. – Michal Zimmermann Feb 15 at 12:26
  • Moving on to the function ST_Expand - what she does ...: -)... – Cyril Feb 15 at 13:13
  • I may be wrong, but there's buffer bbox is not? – Cyril Feb 15 at 13:20
  • @Cyril of course there is, but its usage depends on gist built on that buffer. – Michal Zimmermann Feb 18 at 9:56
2

Here is my approach,

It is called "buffer clustering at the required distance"

(do not beat off the fish from the pack at the specified distance, and then we will eat you :-)),

It consists of 6 points, swam...

The source data shown in figure 1 enter image description here

It is polygons of polygon type. Restrictions, example, are shown without object semantics, so that you can save it yourself.

1) Create a buffer equal to almost half the required distance, for example 501 m:

create table polygons_byf as SELECT ((ST_Buffer(geography(geom),501))::geometry) as geom FROM polygons;

2) Dissolving polygon:

create table polygons_byf_dump as select st_buffer( (st_dump( st_union( st_buffer(geom, 0.0000000001)))).geom, -0.0000000001) as geom FROM polygons_byf;

(John Powell :-)...)

see figure 2

enter image description here

3) Create the median point to the original polygon:

create table polygons_byf_centr as SELECT ST_PointOnSurface(geom) as geom FROM polygons;

see figure 3

enter image description here

4) Count the number of points that fall into the combined polygons:

create table polygons_pt_count as SELECT b.geom, count (*) as cnt FROM polygons_byf_centr a, polygons_byf_dump b WHERE st_intersects(a.geom, b.geom) GROUP BY b.geom ORDER BY b.geom;

see figure 4

enter image description here

5) Delete polygons that have only one point:

DELETE from polygons_pt_count WHERE cnt = '1';

see figure 5

enter image description here

6) Select only those polygons that meet our condition:

create table polygons_sel as SELECT ST_Intersection(a.geom, b.geom) as geom FROM polygons_pt_count a, polygons as b WHERE ST_Intersects (a.geom, b.geom)

see figure 6

enter image description here

7) That's all; this is our result.

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.