Thin out many ST_Points

I have many (1.000.000) ST_Points in a postgres-db with postgis extension. When i show them on a map, browsers are getting very busy. For that I would like to write an sql-statement which filters the high density to only one point.

When a User zoom out of 100 ST_Points, postgres should give bac开发者_C百科k only one. But only if these Points are close together.

I tried it with this statement:

select a.id, count(*)
from points as a, points as b
where st_dwithin(a.location, b.location, 0.001)
and a.id != b.id
group by a.id

I would call it thin-out but didnot find anything - maybe because I'm not a native english speaker. Does anybody have some suggestions?

I agree with tcarobruce that clustering is the term you are looking for. But it is available in postgis.

Basically clustering can be achieved by reducing the number of decimals in the X and Y and grouping upon them;

select 
  count(*),
  round(cast (ST_X(geom) as numeric),3)
  round(cast (ST_Y(geom) as numeric),3)
from mytable 
group by 
round(cast (ST_X(geom) as numeric),3), 
round(cast (ST_Y(geom) as numeric),3)

Which will result in a table with coordinates and the number of real points at that coordinate. In this particular sample, it leaves you with rounding on 3 decimals, 0.001 like in your initial statement.

You can cluster nearby Points together using ST_ClusterDBSCAN

Then keep all single points and for example:

1. Select one random Point per cluster

or

2. Select the centroid of each Point cluster.

I use eps 300 to cluster points together that are within 300 meters.

create table buildings_grouped as
SELECT geom, ST_ClusterDBSCAN(geom, eps := 300, minpoints := 2) over () AS cid
FROM buildings

1:

create table buildings_grouped_keep_random as
select geom, cid from buildings_grouped
where cid is null
union
select * from 
(SELECT DISTINCT ON (cid) *
FROM buildings_grouped
ORDER BY cid, random()) sub

2:

create table buildings_grouped_keep_centroid as
select geom, cid from buildings_grouped
where cid is null
union
select st_centroid(st_union(geom)) geom, cid
from buildings_grouped
where cid is not null
group by cid

The term you are looking for is "clustering".

There are client-side libraries that do this, as well as commercial services that do it server-side.

But it's not something PostGIS does natively. (There's a ticket for it.)

You'll probably have to write your own solution, and precompute your clusters ahead of time.

ST_ClusterDBSCAN- and KMeans- based clustering works but it is VERY SLOW! for big data sets. So it is practically unusable. PostGIS functions like ST_SnapToGrid and ST_RemoveRepeatedPoints is faster and can help in some cases. But the best approach, I think, is using PDAL thinning filters like sample filter. I recommend using it with PG Point Cloud.

Edit: ST_SnapToGrid is pretty fast and useful. Here is the example query for triangulation with optimizations:

    WITH step1 AS 
    (
        SELECT geometry, ST_DIMENSION(geometry) AS dim FROM table
    )
    , step2 AS 
    (
        SELECT ST_SIMPLIFYVW(geometry, :tolerance) AS geometry FROM step1 WHERE dim > 0
        UNION ALL
        (WITH q1 AS
        (
            SELECT (ST_DUMP(geometry)).geom AS geometry FROM step1 WHERE dim = 0
        )
        SELECT ST_COLLECT(DISTINCT(ST_SNAPTOGRID(geometry, :tolerance))) FROM q1)
    )
    , step3 AS 
    (
        SELECT ST_COLLECT(geometry) AS geometry FROM step2
    )
    SELECT ST_DELAUNAYTRIANGLES(geometry, :tolerance, 0)::BYTEA AS geometry
    FROM step3 
    OFFSET :offset LIMIT :limit;