21. Geometry Constructing Exercises¶
Here’s a reminder of some of the functions we have seen. Hint: they should be useful for the exercises!
sum(expression) aggregate to return a sum for a set of records
ST_Area(geometry) returns the area of the geometry
ST_Centroid(geometry) returns the
ST_Transform(geometry, srid) converts
geometriesinto different spatial reference systems
ST_Buffer(geometry, radius) returns an expanded
ST_Contains(geometry1, geometry2) returns truw if geometry1 contains geometry2
ST_Union(geometry) returns the aggregate union of all geometries in the group
ST_GeometryType(geometry) returns the type of the geometry
ST_NumGeometries(geometry) returns the number of geometries in a collection or 1 for simple geometries
ST_Intersection(geometry, geometry) returns the area that the two input geometries share in common
Remember the tables we have available:
name, popn_total, boroname, geom
name, type, geom
name, boroname, geom
How many census blocks don’t contain their own centroid?
SELECT Count(*) FROM nyc_census_blocks WHERE NOT ST_Contains( geom, ST_Centroid(geom) );
Union all the census blocks into a single output. What kind of geometry is it? How many parts does it have?
CREATE TABLE nyc_census_blocks_merge AS SELECT ST_Union(geom) AS geom FROM nyc_census_blocks; SELECT ST_GeometryType(geom) FROM nyc_census_blocks_merge;
SELECT ST_NumGeometries(geom) FROM nyc_census_blocks_merge;
What is the area of a one unit buffer around the origin? How different is it from what you would expect? Why?
SELECT ST_Area(ST_Buffer('POINT(0 0)', 1));
A unit circle (circle with radius of one) should have an area of pi, 3.1415926… The difference is due to the linear stroking of the edges of the buffer. The buffer has a finite number of edges. Increasing the number of edges in the buffer will get the value closer to pi, but it will always be smaller due to the linearization.
The Brooklyn neighborhoods of ‘Park Slope’ and ‘Carroll Gardens’ are going to war! Construct a polygon delineating a 100 meter wide DMZ on the border between the neighborhoods. What is the area of the DMZ?
CREATE TABLE brooklyn_dmz AS SELECT ST_Intersection( ST_Buffer(ps.geom, 50), ST_Buffer(cg.geom, 50)) AS geom FROM nyc_neighborhoods ps, nyc_neighborhoods cg WHERE ps.name = 'Park Slope' AND cg.name = 'Carroll Gardens'; SELECT ST_Area(geom) FROM brooklyn_dmz;
It is easy to buffer both the neighborhoods of interest, but to get the intersection requires a self-join of the table, creating one relation (
ps) with just the “Park Slope” record and another (
cg) with just the “Carroll Gardens” record. Note that the area of the intersection is in square meters because we are still working in UTM 18 (EPSG:26918).