By the end of this practical
you should be able to:
- retrieve information about objects based on their spatial properties and relations; and
- analyse spatial relations of several objects.
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
data from the US Census Bureau and the Digital Chart of the World
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.
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
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
the same analysis as in P3.1.1 using the following template (nearest
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).
the techniques you have already learned to select the
interstate (highway) closest to the restaurant with ID 33625120.
of results: Determine
the total area in square miles of US states with a population of more
than 15 million
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 ...
want to find restaurants within Maryland,
using a spatial operation. We want only restaurants lying within
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
- Relate operation #2: Find all the interstates and rivers that cross (anyinteract) the state of New York.
- Relate operation #3: Find all the states that border (touch) the state of New York.http://www.lms.unimelb.edu.au
- 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.
- Relate operation #5: List all the states which the "I95" interstate passes through.
- Relate operation #6: List all the interstates which cross the "Potomac" river.
- Relate operation #7: Find all the US states that border (touch) exactly one other US state. (This is quite tricky!)
Exercise P3.2: Retrieving
possible to retrieve
some of the
spatial parameters of spatial objects stored within
an SDO_GEOMETRY type in a straightforward way:
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
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
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.
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
the union of France and Germany.
SDO_GEOM.SDO_UNION(geometry1, geometry2, tolerance);
intersection of two geometries can be constructed using a similar
function, as below. Find the intersection of the "I81" and the "St.
SDO_GEOM.SDO_INTERSECTION(geometry1, geometry2, tolerance);
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)?
#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
AssignmentThe 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).
- List in alphabetical order
the names of all the states that the
"I81" interstate passes
- List the total population of
states that the "I81" interstate
- List in alphabetical order
the names of the 5 closest restaurants
to the "I95"
- List in alphabetical order
the names of all the interstates that
cross the "Savannah"
- List in alphabetical order the names of all the states that border "California".
- Compute the length in miles
of the "Mississippi" river that lies within
the state of "Mississippi".
- Using each country's geometry, compute the total area (in square miles) of all countries with populations of greater that 100 million people.
- Compute the area in square miles of the region that contains all places within 10 miles of the border between Germany and France.
- List the names of all the interstates that connect Florida and Texas (i.e., pass through both states).
- List in alphabetical order the names of all the restaurants that are within 100m of the border between "District of Columbia" and "Maryland" states.