Banner

 

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' 
AND NOT EXISTS    
(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));

CREATE TABLE city (
  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 (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 02-Dec-2022