Database Reference
In-Depth Information
CREATE TABLE chp02.use_area_alt_p AS
SELECT
ST_SetSRID(ST_PointOnSurface(the_geom), 3734)
AS the_geom FROM
chp02.use_area_alt;
ALTER TABLE chp02.use_area_alt_p ADD COLUMN gid
serial;
ALTER TABLE chp02.use_area_alt_p ADD PRIMARY
KEY (gid);
And, as always, create a spatial index using the following query:
CREATE INDEX chp02_use_area_alt_p_the_geom_gist
ON chp02.use_area_alt_p
USING gist(the_geom);
Using resultant points to query tabular relationships
The centroids then structure our point-in-polygon (
ST_Intersects
) relationship
betweentheoriginaltabularinformationandresultantpolygons,usingthefollowing
query:
CREATE TABLE chp02.use_area_alt_relation AS
SELECT points.gid, cu.location FROM
chp02.use_area_alt_p AS points,
chp02.use_area AS cu
WHERE ST_Intersects(points.the_geom,
cu.the_geom);
How it works...
Our essential approach here is to look at the underlying topology of the geometry
andreconstructatopologythatisnonoverlapping,andthenusethecentroidsofthat
new geometry to construct a query that establishes the relationship to the original
data.
