Banner

 

11 - Spatial & Temporal Data
Skill Builders

If you have access to MySQL, create the three tables for the example and insert the rows listed in the preceding SQL code.

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');	

What is the are of the border of Northern Ireland? Because Northern Ireland is part of the United Kingdom and miles are still often used to measure distances, report the area in square miles.

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

What is the direct distance from Belfast to Londonderry in miles?

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

What is the northernmost city of the Republic Ireland?

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');

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