A common application within GIS is to create buffer / catchment areas – an interesting analysis is then to see how many overlaps occur with the each catchment indicating whether a site is under or over provisioned. This can be achieved using Postgis and the following fantastic tutorial.
This can you allow you to produce maps like the following:
Below gives a snapshot of the process:
create table boundaries_allot as select ST_Union(ST_ExteriorRing(geom)) from (select (ST_DumpRings((st_dump(geom)).geom)).geom as geom from allotment_buffer) q
This has been altered from the tutorial to include multi polygons. ST_Dump breaks down multi polygons into single polygons and ST_DumpRings provides the interior and exterior rings of polygons (for complex polygons)
CREATE SEQUENCE polyseq_allots; CREATE TABLE polys_allots AS SELECT nextval('polyseq_allots') AS id, (ST_Dump(ST_Polygonize(st_union))).geom AS geom FROM boundaries_allot;
This creates individual polygons for each multi-linestring and uses ST_Dump to switch any multi polygons to single polygons.
ALTER TABLE polys_allots ADD COLUMN count INTEGER DEFAULT 0; UPDATE POLYS_allots set count = p.count FROM ( SELECT count(*) AS count, p.id AS id FROM polys_allots p JOIN allotment_buffer c ON ST_Contains(c.geom, ST_PointOnSurface(p.geom)) GROUP BY p.id ) AS p WHERE p.id = polys_allots.id;
Once we have the individual polygons, by using the centroids of the new small polygons with the set of original circles, we can calculate how many circles contain each centroid point.