Untitled Document

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?).

2.

A national tourist bureau has asked you to design a database to record details of items of interest along a scenic road. What are some of the entities you might include? How would you model a road? Draw the data model?

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,ST_GeomFromText('POLYGON((12 12, 13 12, 13 11, 12 11, 12 12))'),'ad');
INSERT INTO boundary VALUES (4,ST_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,ST_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',ST_GeomFromText('POINT(2 10)'),'pt');
INSERT INTO city VALUES ('Porto',ST_GeomFromText('POINT(1 9)'),'pt');
INSERT INTO city VALUES ('Lisbon',ST_GeomFromText('POINT(1 5)'),'pt');
INSERT INTO city VALUES ('Santiago de Compostela',ST_GeomFromText('POINT(2 12)'),'es');
INSERT INTO city VALUES ('Oviedo',ST_GeomFromText('POINT(5 12)'),'es');
INSERT INTO city VALUES ('Santander',ST_GeomFromText('POINT(7 12)'),'es');
INSERT INTO city VALUES ('Vitori',ST_GeomFromText('POINT(8 11)'),'es');
INSERT INTO city VALUES ('Pamplona',ST_GeomFromText('POINT(9 11)'),'es');
INSERT INTO city VALUES ('Logrono',ST_GeomFromText('POINT(8 11)'),'es');
INSERT INTO city VALUES ('Valiadoti',ST_GeomFromText('POINT(6 10)'),'es');
INSERT INTO city VALUES ('Saragossa',ST_GeomFromText('POINT(10 10)'),'es');
INSERT INTO city VALUES ('Barcelona',ST_GeomFromText('POINT(13 10)'),'es');
INSERT INTO city VALUES ('Madrid',ST_GeomFromText('POINT(7 8)'),'es');
INSERT INTO city VALUES ('Toledo',ST_GeomFromText('POINT(7 7)'),'es');
INSERT INTO city VALUES ('Valencia',ST_GeomFromText('POINT(11 6)'),'es');
INSERT INTO city VALUES ('Murcia',ST_GeomFromText('POINT(10 4)'),'es');
INSERT INTO city VALUES ('Sevilla',ST_GeomFromText('POINT(4 3)'),'es');
INSERT INTO city VALUES ('Gibralta',ST_GeomFromText('POINT(5 1)'),'uk');
INSERT INTO city VALUES ('Andorra la Veila',ST_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 ST_Distance(orig.cityloc, dest.cityloc)*75 as "Distance (kms)"
	FROM city orig, city dest
		WHERE orig.cityname = 'Lisbon'
		AND dest.cityname = 'Madrid';

3b.

What is the furthermost Spanish city from Barcelona?

SELECT dest.cityname FROM city orig, city dest
	WHERE orig.cityname = 'Barcelona'
	AND ST_Distance(orig.cityloc, dest.cityloc) =
	(SELECT MAX(ST_Distance(orig.cityloc, dest.cityloc)) 
		FROM city orig, city dest 
			WHERE orig.cityname = 'Barcelona' AND dest.unitcode = 
				(SELECT unitcode FROM political_unit WHERE unitname = 'Spain'));

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  ST_Distance(cityloc, ST_GeomFromText('POINT(3 9)'))
		= (SELECT MIN(ST_Distance(cityloc, ST_GeomFromText('POINT(3 9)'))) FROM city);

3d.

Are there any Spanish cities west of Braga?

SELECT cityname FROM city 
   WHERE ST_X(cityloc) <   
	  (SELECT ST_X(cityloc) FROM city WHERE cityname = 'Braga')
	  AND unitcode = 
				(SELECT unitcode FROM political_unit WHERE unitname = 'Spain');

What about if you look for a city as far west as Braga?

3e.

What is the area of Portugal?

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

3f.

What is the southernmost city of Portugal?

SELECT south.cityname FROM city south
	WHERE unitcode = (SELECT unitcode FROM political_unit WHERE unitname = 'Portugal') 	
	AND NOT EXISTS
	(SELECT * FROM city other WHERE ST_Y(other.cityloc) <  ST_Y(south.cityloc)
		AND unitcode = (SELECT unitcode FROM political_unit WHERE unitname = 'Portugal'));

4.

Redesign the data model for political units (Figure 14-1) assuming that your relational database does not support point and path data types.

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 distance covered is not large (i.e., assume the earth is flat for distances of a few hundred kilometers or miles)

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

6.

For more precision and to be meet universal standards, it would be better to use latitude and longitude to specify points or paths. You should also recognize that the earth is a globe and not flat. How would you enter latitude and longitude in MySQL? How do you compute the distance between two points on a globe? For some help, see http://www.realestate3d.com/gps/world-latlong.htm and http:// jan.ucc.nau.edu/~cvm/latlon_formula.html.

Latitude and longitude are measured in degrees, minutes, and, seconds relative to Greenwich and the equator. For example, Narembeen in Western Australian is at Latitude : 32° 4' S Longitude : 118° 24' E. To use these data in MySQL, we would record the location in coordinate format as (-32.0667,114.4) (.0667 = 4/60). Conventionally, south and west are negative (think of the compass points relative to (0,0) in a coordinate system).

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).

8.

Design a database to report scores in a basketball game. How would you record time?

The database needs to store a time instant (when the score was made) and the appropriate datatype is TIMESTAMP.

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.

10.

An online auction site wants to keep track of the bids for each item that a supplier sells. Design the database.

11 - Spatial and Temporal Data Management

Answers to exercises

Date revised: 10-Dec-2021