5

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
  • 3
    Here are a few PostGIS clustering examples. gis.stackexchange.com/questions/11567/… – klewis Sep 10 '13 at 15:07
1

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.

4

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

0

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

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.