Banner

 

11 - Spatial and Temporal Data Management

Answers to 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,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:

  

  
  
  

    

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

  

  
  

    

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

  

  
  

    

      
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; 

  

  
  

    

      
 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((x2 - x1)2 +
      (y2-y1)2) 

  

  
  

    

      
 7. 

    

    When might you use transaction time and when valid time? 

  

    
  

  
  

    

      
 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. 

  

    
    

  




  

    
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