Into the Nth Dimension - PostGIS - page 384

Attributing polygons with the color
Ournextstepistoattributeournewpolygonswiththecolorinformationassociated
with the original points. This can be accomplished with a simple ST_Intersects
query, but first indexes in order to be efficient.
CREATE INDEX uas_the_geom_gist
ON chp07.uas
USING gist
(the_geom );
CREATE INDEX uas_voronoi_the_geom_gist
ON chp07.uas_voronoi
USING gist
(the_geom );
Next,wewillperformthejoinitself.Duetosomeissueswithnon-voronoipolygons
returning,wewillfirstcounthowmanypointsarepresentineachpolygonandonly
return those polygons that have a single point.
DROP TABLE IF EXISTS chp07.uas_voronoi_count
CASCADE;
CREATE TABLE chp07.uas_voronoi_count AS
WITH distinction AS (
SELECT DISTINCT ON (uv.gid) uv.gid,
uv.the_geom, COUNT(uas.*)
FROM chp07.uas_voronoi uv
LEFT JOIN chp07.uas_subset uas
ON ST_Intersects(uv.the_geom, uas.the_geom)
GROUP BY uv.gid, uv.the_geom
)
SELECT gid, the_geom, count FROM distinction
WHERE count = 1
AND
ST_Covers(ST_Envelope( ((SELECT
ST_Union(the_geom) FROM chp07.uas_subset)) ),
the_geom)
;