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.

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 ?

Answer 1

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);

Answer 2

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)

Answer 3

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.

Answer 4

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: