# How to merge close points into polygon?

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 ?

• 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?) Sep 10, 2013 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, 2013 at 13:41
• Here are a few PostGIS clustering examples. gis.stackexchange.com/questions/11567/… Sep 10, 2013 at 15:07

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

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

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)

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

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.

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: