Data Management: Chapter 14 exercises

1.

What circumstances will lead to increased use of spatial data?

As customers expectations for location-based services grow, firms will need to add spatial information to the databases. For example, increasingly tourists will expect to be able to use their mobile phone to locate services near to their current locations (e.g., Where is the nearest Italian restaurant?).

3.

Using the following map of the Iberian peninsular, populate the spatial database with details of Andorra, Portugal, and Spain. Answer the following questions.

INSERT INTO political_unit VALUES ('Spain', 'es', 4.9);
INSERT INTO political_unit VALUES ('Portugal','pt', 10);
INSERT INTO political_unit VALUES ('Andorra','ad', .067);
INSERT INTO political_unit VALUES ('United Kingdom','uk', 58.79);

INSERT INTO boundary VALUES (3,GeomFromText('POLYGON((12 12, 13 12, 13 11, 12 11, 12 12))'),'ad');
INSERT INTO boundary VALUES (4,GeomFromText('POLYGON((1 10, 4 10, 4 9, 3 3, 1 3, 1 5, 0 5, 0 6, 1 8, 1 10))'),'pt');
INSERT INTO boundary VALUES (5,GeomFromText('POLYGON((1 12, 3 13, 12 12, 12 11, 15 11, 15 11, 11 7, 11 5, 9 2, 5 1, 4 1, 3 3, 4 9, 4 10, 1 10, 1 12))'),'es');

INSERT INTO city VALUES ('Braga',GeomFromText('POINT(2 10)'),'pt');
INSERT INTO city VALUES ('Porto',GeomFromText('POINT(1 9)'),'pt');
INSERT INTO city VALUES ('Lisbon',GeomFromText('POINT(1 5)'),'pt');
INSERT INTO city VALUES ('Santiago de Compostela',GeomFromText('POINT(2 12)'),'es');
INSERT INTO city VALUES ('Oviedo',GeomFromText('POINT(5 12)'),'es');
INSERT INTO city VALUES ('Santander',GeomFromText('POINT(7 12)'),'es');
INSERT INTO city VALUES ('Vitori',GeomFromText('POINT(8 11)'),'es');
INSERT INTO city VALUES ('Pamplona',GeomFromText('POINT(9 11)'),'es');
INSERT INTO city VALUES ('Logrono',GeomFromText('POINT(8 11)'),'es');
INSERT INTO city VALUES ('Valiadoti',GeomFromText('POINT(6 10)'),'es');
INSERT INTO city VALUES ('Saragossa',GeomFromText('POINT(10 10)'),'es');
INSERT INTO city VALUES ('Barcelona',GeomFromText('POINT(13 10)'),'es');
INSERT INTO city VALUES ('Madrid',GeomFromText('POINT(7 8)'),'es');
INSERT INTO city VALUES ('Toledo',GeomFromText('POINT(7 7)'),'es');
INSERT INTO city VALUES ('Valencia',GeomFromText('POINT(11 6)'),'es');
INSERT INTO city VALUES ('Murcia',GeomFromText('POINT(10 4)'),'es');
INSERT INTO city VALUES ('Sevilla',GeomFromText('POINT(4 3)'),'es');
INSERT INTO city VALUES ('Gibralta',GeomFromText('POINT(5 1)'),'uk');
INSERT INTO city VALUES ('Andorra la Veila',GeomFromText('POINT(12 11)'),'ad');

Notes:

Gibraltar belongs to the United Kingdom
One unit on the grid is about 75 kms

3a.

What is the direct distance, or bee line, from Lisbon to Madrid?

SELECT GLENGTH(LINESTRINGFROMWKB(LINESTRING(ASBINARY(orig.cityloc), ASBINARY(dest.cityloc))))*75 as "Distance (kms)"
				 FROM city orig, city dest
				  WHERE orig.cityname = 'Lisbon'
				  AND dest.cityname = 'Madrid';

3c.

Imagine you get lost in Portugal and your geographic positioning system (GPS) indicates that your coordinates are (3,9). What is the nearest city?

SELECT cityname FROM  city WHERE  GLENGTH(LINESTRINGFROMWKB(LINESTRING(ASBINARY(cityloc), ASBINARY(GeomFromText('POINT(3 9)')))))
= (SELECT MIN(GLENGTH(LINESTRINGFROMWKB(LINESTRING(ASBINARY(cityloc), ASBINARY(GeomFromText('POINT(3 9)')))))) FROM city)

3e.

What is the area of Portugal?

SELECT AREA(boundpath)*5625 as "Area (km^2)" from political_unit, boundary
					WHERE unitname = 'Portugal'
	 AND political_unit.unitcode = boundary.unitcode;

5.

How would you compute distance if your relational database does not have a distance function?

You could need to use the Euclidean distance if the area covered is not large.

Distance = sqrt((x₂ - x₁)² * (y₂-y₁)²)

7.

When might you use transaction time and when valid time?

Transaction time is used when you want to record when an event occurred.
Valid time is used to specify when an item's value becomes valid (e.g., a press release).

9.

A supermarket chain has asked you to record what goods customers buy during each visit. In other words, you want details of each shopping basket. It also wants to know when each purchase was made. Design the database.

Chapter 14

Answers to exercises