P3: Spatial Analysis


By the end of this practical you should be able to:

  1. retrieve information about objects based on their spatial properties and relations; and
  2. analyse spatial relations of several objects.
This practical should require two weeks to complete.


This practical will introduce you to more advanced spatial analysis in SQL. We will use the spatial capabilities of Oracle 10g DBMS to perform spatial operations and analysis on the sample data from the US Census Bureau and the Digital Chart of the World (covered in Kothuri et al., 2004).

Exercise P3.1: Spatial Analysis 

In the previous practicals you should have learned how to determine the basic properties of (mostly) a single spatial object, and perform some simple geometric operations upon those objects (e.g., distances). In the following excercise we will analyse spatial relations of multiple objects of different types (polygons, lines, and points). The term "spatial join" refers to performing a relational join on tables based on some spatial criterion. 

  1. Nearest neighbor: In the previous practical we found the 10 closest restaurants to a particular restuarant using the SDO_DISTANCE function with a nested SELECT statement. The same analysis can be performed using the nearest neighbor function. Doing so requires a spatial index. These indexes have already been created for you, but normally you would create your own spatial index on the "location" column of the us_restaurants table, as in previous weeks' practicals. Perform the same analysis as in P3.1.1 using the following template (nearest neighbor operator):
    SELECT ... FROM ... WHERE ... AND SDO_NN(analysis_spatialcolumn, origin_spatialcolumn)='TRUE' AND ROWNUM <=10;
    Note the simplified syntax of a typical spatial operator. The order of the two geometry columns in the query is always preserved. Also note that by adding a third parameter to the SDO_NN function (param1, param2, 'SDO_NUM_RES=10') you can achieve the same result. This restricts the number of nearest neighbors retrieved (referred to as the k-nearest neighbors problem).
  2. Closest interstate: Use the techniques you have already learned to select the interstate (highway) closest to the restaurant with ID 33625120.
  3. Aggregation of results: Determine the total area in square miles of US states with a population of more than 15 million people. You will select spatial objects based on non-spatial attributes and determine a joint spatial property. Use the SUM operator together with the SDO_AREA function to get the total value.
    SELECT SUM(expression) FROM ...
  4. Relate operation #1: We want to find restaurants within Maryland, using a spatial operation. We want only restaurants lying within Maryland, based on the geometry of the outline of Maryland. This can be achieved using the SDO_RELATE operator, which determines the spatial (topological) relationship between two geometries:
    ... SDO_RELATE(geometry1, geometry2, 'MASK=ANYINTERACT')='TRUE' ...;
    Note that the available interactions (masks) can be more restrictive than ANYINTERACT (e.g., EQUAL, COVERS/COVEREDBY, INSIDE/CONTAINS, TOUCH, DISJOINT, OVERLAPBDYINTERSECT), and can also be combined using the "+" function. For more information on the different topological relationships supported by Oracle, refer to the lecture notes or an Oracle Spatial reference source (such as Kothuri et al., 2004).
    However, a brief description of the possible relationships is given below (see oreillynet.com):
    • DISJOINT: the boundaries and interiors do not intersect
    • TOUCH: the boundaries intersect but the interiors do not intersect
    • OVERLAPBDYDISJOINT: the interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.
    • OVERLAPBDYINTERSECT: the boundaries and interiors of the two objects intersect
    • EQUAL: the two objects have the same boundary and interior
    • CONTAINS: the interior and boundary of one object is completely contained in the interior of the other object
    • COVERS: the interior of one object is completely contained in the interior of the other object and their boundaries intersect
    • INSIDE: the opposite of CONTAINS. A INSIDE B implies B CONTAINS A.
    • COVEREDBY: the opposite of COVERS. A COVEREDBY B implies B COVERS A.
    • ON: the interior and boundary of one object is on the boundary of the other object (and the second object covers the first object). This relationship occurs, for example, when a line is on the boundary of a polygon.
    • ANYINTERACT: the objects are non-disjoint
  5. Relate operation #2: Find all the interstates and rivers that cross (anyinteract) the state of New York.
  6. Relate operation #3: Find all the states that border (touch) the state of New York.http://www.lms.unimelb.edu.au
  7. Relate operation #4: Retrieve the name of the US state in which the "Wah Mee Restaurant" is located (contained), based on the coordinates of its location.
  8. Relate operation #5: List all the states which the "I95" interstate passes through.
  9. Relate operation #6: List all the interstates which cross the "Potomac" river.
  10. Relate operation #7: Find all the US states that border (touch) exactly one other US state. (This is quite tricky!)

Exercise P3.2: Retrieving coordinate information

It is possible to retrieve some of the spatial parameters of spatial objects stored within an SDO_GEOMETRY type in a straightforward way:
  1. Retrive coordinate information: It is possible to retrieve the coordinates of a point geometry in a straightforward way. List the lat, long values of restaurants within New York state in a table containig columns NAME, X, Y. Hint: you have to use the alias notation for the table, otherwise Oracle returns an error. The general pattern of the query function for the coordinates x is:
    SELECT tablealias.geom_column.SDO_POINT.x FROM table tablealias ...;

Exercise P3.3: Overlay operations

As well as using the relate operation to find the relationship between two spatial objects, we can combine spatial objects into new objects using set-based operators, like intersection, union, and difference.
  1. Union: The union of two geometries can be constructed using the SDO_GEOM.SDO_UNION function, with the template below. Create the region that is the union of France and Germany.  Output the lat/long coordinates of the centroid of the union of France and Germany.
    SDO_GEOM.SDO_UNION(geometry1, geometry2, tolerance);
  2. Intersection #1: The intersection of two geometries can be constructed using a similar function, as below. Find the intersection of the "I81" and the "St. Lawrence" river.
    SDO_GEOM.SDO_INTERSECTION(geometry1, geometry2, tolerance);
  3. Intersection #2: Now find the restaurant that is closest to the intersection of "I81" and the "St. Lawrence" river (in one query). How far away is it (in miles)?
  4. Intersection #3: How far away from the intersection of the "I81" and the "St. Lawrence" river is the intersection of the "I95" and the Potomac?

Assessment A3: Spatial Analysis


The questions you must answer for this assignment all concern the sample data that you should now be familiar with in the practicals. Your assignment is to provide SQL/Oracle statements to answer to the following 10 questions (see "Submission" section above for more details).
  1. List in alphabetical order the names of all the states that the "I81" interstate passes through.
  2. List the total population of states that the "I81" interstate passes through.
  3. List in alphabetical order the names of the 5 closest restaurants to the "I95" interstate.
  4. List in alphabetical order the names of all the interstates that cross the "Savannah" river.
  5. List in alphabetical order the names of all the states that border "California". 
  6. Compute the length in miles of the "Mississippi" river that lies within the state of "Mississippi".
  7. Using each country's geometry, compute the total area (in square miles) of all countries with populations of greater that 100 million people. 
  8. Compute the area in square miles of the region that contains all places within 10 miles of the border between Germany and France. 
  9. List the names of all the interstates that connect Florida and Texas (i.e., pass through both states).
  10. List in alphabetical order the names of all the restaurants that are within 100m of the border between "District of Columbia" and "Maryland" states.
Creative Commons License
This work is created by Matt Duckham (http://www.duckham.org) and licensed under a Creative Commons Attribution 3.0 United States License.