11 - Spatial & Temporal Data
Skill Builders

You can copy and paste the following statements.

CREATE TABLE  political_unit (
 unitname       VARCHAR(30) not null,
 unitcode       CHAR(2),
 unitpop        DECIMAL(6,2),
 PRIMARY KEY(unitcode));

CREATE TABLE boundary (
 boundid        INTEGER,
 boundpath      POLYGON NOT NULL,
 unitcode       CHAR(2),
 PRIMARY KEY(boundid),
 CONSTRAINT fk_boundary_polunit FOREIGN KEY(unitcode)
   REFERENCES political_unit(unitname)) TYPE MYISAM;

CREATE TABLE city (
 cityname       VARCHAR(30),
 cityloc        POINT NOT NULL,
 unitcode       CHAR(2),
 PRIMARY KEY(unitcode,cityname),
 CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) 
   REFERENCES political_unit(unitname)) TYPE MYISAM; 		

INSERT INTO political_unit VALUES ('Republic of Ireland','ie', 3.9);
INSERT INTO political_unit VALUES ('Northern Ireland','ni', 1.7);
INSERT INTO boundary VALUES
  (1,ST_GeomFromText('polygon((9 8, 9 3, 4 1, 2 2, 1 3, 3 5, 3 6, 2 6,
	2 9, 5 9, 5 10, 6 11, 7 11, 7 10, 6 9, 7 8, 7 9, 8 9, 8 8, 9 8))'),'ie');
INSERT INTO boundary VALUES
  (2,ST_GeomFromText('polygon((7 11, 9 11, 10 9, 10 8, 8 8, 8 9, 7 9,
	7 8, 6 9, 7 10, 7 11))'),'ni');
INSERT INTO city VALUES ('Dublin',ST_GeomFromText('POINT(9 6)'),'ie');
INSERT INTO city VALUES ('Cork',ST_GeomFromText('POINT(5 2)'),'ie');
INSERT INTO city VALUES ('Limerick',ST_GeomFromText('POINT(4 4)'),'ie');
INSERT INTO city VALUES ('Galway',ST_GeomFromText('POINT(4 6)'),'ie');
INSERT INTO city VALUES ('Sligo',ST_GeomFromText('POINT(5 8)'),'ie');
INSERT INTO city VALUES ('Tipperary',ST_GeomFromText('POINT(5 3)'),'ie');
INSERT INTO city VALUES ('Belfast',ST_GeomFromText('POINT(9 9)'),'ni');
INSERT INTO city VALUES ('Londonderry',ST_GeomFromText('POINT(7 10)'),'ni');	

SELECT ST_Area(boundpath)*526 
 AS 'Area (sq miles)' from political_unit JOIN  boundary
ON political_unit.unitcode = boundary.unitcode
 WHERE unitname = 'Northern Ireland'; 

SELECT ST_Distance(orig.cityloc,dest.cityloc)*23 as 'Distance (miles)'
 FROM city orig, city dest
 WHERE orig.cityname = 'Belfast' 
 AND dest.cityname = 'Londonderry';

SELECT north.cityname FROM city north
WHERE north.unitcode = 'ie'
AND NOT EXISTS
  (SELECT * FROM city other WHERE ST_Y(other.cityloc) > ST_Y(north.cityloc)
  AND other.unitcode = 'ie');