11 - Spatial & Temporal Data
Slide Exercises

What is the area of Northern Ireland?
SELECT ST_AREA(boundpath)*1406 
	as "Area (km^2)" from political_unit JOIN boundary 
ON unitname = 'Northern Ireland'
WHERE political_unit.unitcode = boundary.unitcode;
How close is the computed value to that reported in Wikipedia?
Wikipedia reports 13,843 km2. A finer grid would increase accuracy.

What is the eastern most city in Northern Ireland?
SELECT east.cityname FROM city east JOIN political_unit
ON east.unitcode = political_unit.unitcode 
WHERE unitname = 'Northern Ireland' 
(SELECT * FROM city other WHERE ST_X(other.cityloc) > ST_X(east.cityloc));

Modify the example geometry database design to include

  • Historic buildings in a city
  • Walking paths in a city
  • Use of the MULTIPOLYGON data type to indicate a political regionís boundary
CREATE TABLE political_unit (
  unitname          VARCHAR(30),
  unitcode          CHAR(2),
  unitpop           DECIMAL(6,2),
  boundaries        MULTIPOLYGON NOT NULL,
  PRIMARY KEY (unitcode));

  cityname          VARCHAR(30),
  cityloc           POINT NOT NULL,
  unitcode          CHAR(2),
  historicbuildings MULTIPOINT NULL,
  walkingpaths      MULTILINESTRING NULL,
  PRIMARY KEY (cityname, unitcode),
	CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) 
		REFERENCES political_unit(unitcode));

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016