How to merge close points into polygon?

Question

I have a set of coordinates (points & no overlaps) in a single table,each containing attribute number fields,like in the following image.

enter image description here

I want to merge these points into smallest polygons by closest distance like red lines drawn around them,and aggregate attributes fields with them as well.

How can i do it in PostGIS or Spatialite ?

Do the points you want to cluster all have the same ID? (e.g. are there 6 distinct IDs in your example image, one per polygon you would like to create?) — Darren Cope, Sep 10 '13 at 13:31
The problem is I don't have any fields that I can group,so I must group them at least by distance — Myra, Sep 10 '13 at 13:41
Here are a few PostGIS clustering examples. http://gis.stackexchange.com/questions/11567/spatial-clustering-with-postgis — klewis, Sep 10 '13 at 15:07

score 5 · Answer 1 · answered Sep 12 '13 at 19:39

Here's something you might try: You can create buffers around all the points at a "reasonable" distance that you choose based on the clustering. Then merge the circular buffers together. That should give you polygons enclosing the clusters of points. In spatialite you would do:

Create a polygon table for the buffers and a second one for the merged circles:

CREATE TABLE buffers (pk INTEGER PRIMARY KEY AUTOINCREMENT);
CREATE TABLE merged (pk INTEGER PRIMARY KEY AUTOINCREMENT);
SELECT AddGeometryColumn ('buffers', 'geometry', <your SRID>, 'POLYGON',2); 
SELECT AddGeometryColumn ('merged', 'geometry', <your SRID>, 'MULTIPOLYGON',2);

Now make the buffers, and merge based on intersection:

INSERT INTO buffers (geometry) 
SELECT ST_Buffer(points.geometry, <your reasonable distance>) FROM points;

INSERT INTO merged (geometry)
SELECT ST_Union(b1.geometry) 
FROM buffers AS b1 JOIN buffers AS b2 ON ST_Intersects(b1.geometry, b2.geometry);

Antonio Falciano · Answer 2 · 2013-10-07T20:06:59.097

Using GDAL >= 1.10.0 compiled with SQLite and SpatiaLite, you can:

Given a specific search radius (e.g. 5), calculate the buffers around your points:

ogr2ogr buffers.shp points.shp -dialect sqlite -sql "SELECT ST_Buffer(geometry,5) from points"

Calculate the clusters:

ogr2ogr clusters.shp buffers.shp -dialect sqlite -sql "SELECT ST_Union(geometry) from buffers" -explodecollections

Wrap points.shp and clusters.shp in an OGR VRT file (e.g. test.vrt):

<OGRVRTDataSource>
    <OGRVRTlayer name="points">
        <SrcDataSource>points.shp</SrcDataSource>
    </OGRVRTlayer>
    <OGRVRTlayer name="clusters">
        <SrcDataSource>clusters.shp</SrcDataSource>
    </OGRVRTlayer>
</OGRVRTDataSource>

Finally, calculate polygons.shp:

ogr2ogr polygons.shp test.vrt -dialect sqlite -sql "SELECT ST_ConvexHull(ST_Collect(g1.geometry)), COUNT(g1.PID) AS n FROM points AS g1, clusters AS g2 WHERE ST_Intersects(g1.geometry,g2.geometry) = 1 GROUP BY g2.FID HAVING n>2"

Note: POINT and LINESTRING will be discarded (n>2)

score 1 · Accepted Answer · edited Apr 13 '17 at 12:34

I think the answer that @klewis linked to is probably your best resource, another question that discusses this as well is:

Spatial clustering with PostGIS (as referenced by @klewis)
What are Definition, Algorithms and Practical Solutions for Concave Hull?

You may also want to look at a couple of other PostGIS 2.0 commands, which may meet your needs:

ST_ConcaveHull - represents a geometry that encloses all geometries within the set. You can think of it as shrink wrapping.
ST_ConvexHull - represents the minimum convex geometry that encloses all geometries within the set.

BostonGIS.com has a good visual description of Concave Hull's.

Thank you @klewis This is the answer i needed : http://gis.stackexchange.com/a/11807/72 — Myra, Oct 08 '13 at 05:59

score 0 · Answer 4 · answered May 26 '14 at 23:44

I try this:

cluster some point (from 619 to 42) with this SQL

SELECT bar.mygid, pt_punti.the_geom
FROM pt_punti, ( SELECT min(foo.s1g) AS mygid, foo.s2g
       FROM pt_punti, ( SELECT s1.gid AS s1g, s2.gid AS s2g, s1.the_geom, st_distance(s1.the_geom, s2.the_geom) AS dist
               FROM pt_punti s1
          JOIN pt_punti s2 ON st_dwithin(s1.the_geom, s2.the_geom, 0.04::double precision)
         WHERE st_distance(s1.the_geom, s2.the_geom) > 0::double precision AND st_distance(s1.the_geom, s2.the_geom) < 0.04::double precision) foo
      GROUP BY foo.s2g
      ORDER BY min(foo.s1g)) bar
WHERE bar.mygid = pt_punti.gid
GROUP BY bar.mygid, pt_punti.the_geom;

and make line with this code:

SELECT ter.my2, st_makeline(pt_punti.the_geom, ter.the_geom::geometry) AS st_makeline
FROM pt_punti, ( SELECT min(foo.s1g) AS mygid, foo.s2g AS my2, min(foo.the_geom::text) AS the_geom
       FROM ( SELECT s1.gid AS s1g, s2.gid AS s2g, s2.the_geom, st_distance(s1.the_geom, s2.the_geom) AS dist
               FROM pt_punti s1
          JOIN pt_punti s2 ON st_dwithin(s1.the_geom, s2.the_geom, 0.04::double precision)
         WHERE st_distance(s1.the_geom, s2.the_geom) > 0::double precision AND st_distance(s1.the_geom, s2.the_geom) < 0.04::double precision) foo
      GROUP BY foo.s2g
      ORDER BY min(foo.s1g)) ter
WHERE ter.mygid = pt_punti.gid;

Result:

enter image description here

How to merge close points into polygon?

4 Answers4

Linked