By the end of this practical
you should be able to:
- understand Oracle spatial
- retrieve information about objects based on their spatial properties; and
- create new spatial data.
This practical will introduce
you to the basics of spatial analysis in SQL. We will use the spatial
capabilities of Oracle 10g DBMS to perform basic spatial operations on
data from the US Census Bureau, the Digital Chart of the World
et al., 2004), and some data created by yourself.
Exercise P2.1: Viewing spatial
Oracle Spatial has no
data viewing capabilities. One of the reasons why Oracle Spatial is
such a powerful system is that unlike GIS software packages, Oracle
Spatial treats the storage, querying, and management of spatial data as
completely separate functions from viewing that data. However, when
learning about Oracle spatial it is occasionally nice to be to display
the data you are using graphically. There are many different ways that
you can view Oracle Spatial data, including web mapping using Oracle
Spatial MapViewer, and linking it to GIS software packages like GIS.
However, in this section we use a data viewer called Spatial
Console. Spatial Console is only available in University lab spaces.
However, there is an unsupported, free data viewer you can download
and use at home if you wish. Details are available on the course web
- Prepare data to be viewed: Viewing spatial data in Spatial Console requires that the data is in your schema, has metadata, and has an index. We shall look at creating metadata and indexes later in this practical. However, for now simply follow the steps below to prepare the world_continents data from the "spatial" schema for viewing in Spatial Console. To view other data, you may simply replace "world_continents" in the instructions below with the name of another data set in the spatial schema. However, you have a limited disk quota and so some data sets may be too large to store. When you have finished, you should always delete any data you have copied using the "DROP TABLE" command.
- Log into your account using SQL*Plus in the usual way
- Make a copy of world_continents in your own schema using the command "CREATE TABLE my_world_continents AS SELECT * FROM spatial.world_continents"
- Copy across the metadata for world_continents from spatial's schema using the command "INSERT INTO user_sdo_geom_metadata (SELECT 'my_world_continents', column_name, diminfo, srid FROM all_sdo_geom_metadata WHERE table_name='WORLD_CONTINENTS' AND owner='SPATIAL')"
- Create an index for the data using the command "CREATE INDEX world_continents_index ON my_world_continents(geom) INDEXTYPE IS mdsys.spatial_index"
Spatial Console: The Oracle
Spatial data viewer is called Spatial Console and can be
started using the Spatial Console menu item in Windows.
- Set connection: To view any spatial data, Spatial Console first requires information about the connecting to an Oracle server. Using the connection wizard, or by selecting "Add" from the "Action" menu, must enter the details of the connection to the Oracle server, as used in Practical 1.
- The name can be anything you want (e.g., 451235).
- The host is: ploppa.ecr.unimelb.edu.au
- The port is: 1521
- The SID is: geomatics
- The username is: your Oracle username from last week
- The password is: your
password from last week
- Select the connection (e.g., 451235) from the pull down menu
- Set the table name to be the new data set copied from spatial, i.e., my_world_continents. Spatial Console should automatically propagate the layer details with all the metadata for the data set
- Set the "Unique ID column"
Exercise P2.2: Overview of
This exercise will shows you how to get overview information about
a database's spatial data contents, using the SQL*Plus instant
client. You will retrieve information about the basic spatial
properties of objects described by the database. The focus is on the
geometry processing functions of Oracle. We will use the US
data from the Digital Chart of the World dataset.
- Login to the course database server (in Oracle SQL*Plus instant client). Log in to the server using technique covered in the last practical.
- Get an overview of the available data: List all the data attributed to the owner "Spatial" (this was covered in the last practical). Verify if the following tables are present: world_continents, world_countries, us_cities, us_interstates, us_parks, us_rivers, us_states, us_restaurants.
- Attributes of the table containing spatial data: Find out the name of the column containing attributes of the spatial data type for each of the tables listed above. Provide the answer in the form of a SELECT statement. HINT: The geometry columns in Oracle are of the type SDO_GEOMETRY.
- Information about spatial coordinates: We will find out some basic information about the coordinate systems used to store spatial objects. We will use the state of New York (us_states.state_abrv='NY') and other US states.
- Find out
the type of the spatial object:
one of the most important
information we need about a specific spatial object is what type of
object it is (point, line, polygon, ...). Find out what is the spatial
data type of the state of New York? Use the
following query template:
SELECT a.geom.SDO_GTYPE geom FROM spatial.us_states a WHERE ...;Note the output of the geometry type. The format is D00T. 2003 - '2' identifies the number of dimension of the spatial object (1,2,3 or 4 if temporal), '3' identifies the type of geometry (1 - point, 2 - line, 6 - multiline, 3 - polygon, 7 - multipolygon,...). More information about the codes used for geometry types is contained in the lecture notes and in Kothuri et al., 2004.
HINT: note the use of the alias a for the table us_states. You can use this technique in and SELECT statement to avoid typing long identifiers. Also note the output, where the selected information has a new assigned column name, GEOM. You can use this approach for customized system outputs.
- Find out
the spatial reference system in which the geometry is defined:
The SRID is a code specifying in what coordinate system the
spatial objects are stored. Find out what coordinate system
Georgia's coordinates are stored in.
SELECT a.geom.SDO_SRID FROM spatial.us_states a WHERE ...;
the SRID used: the cryptic
identifiers of the Oracle SRID
can be translated into standard notation and vice versa. Find out the
SRID code for the standard Australian Geodetic 1966 system used for the
Hint: use the query as a template:SELECT wktext FROM MDSYS.CS_SRS WHERE srid='8307';You can use the columns wktext or cs_name to identify the coordinate system. HINT: Use the SQL 'LIKE' operator, if you do not know the exact input string (you can use '%pattern%' if you do want to match only a pattern and not the exact string):SELECT column_name1 FROM table_name WHERE column_name2 LIKE '%pattern%';
properties: In addition to
coordinates, we can find out a
variety of spatial information for our spatial data.
the area of the US state of
NY. HINT: to work with the geometries in Oracle, you have to provide a
spatial function and its parameter as arguments of the SELECT
statement. The usual template is:
SDO_FUNCTION (parameter1,parameter2,...). The area function looks like this:
SELECT SDO_GEOM.SDO_AREA (geometry_column, tolerance, units) FROM ...The units parameter is optional. Set the tolerance to 1 (see below for information about tolerance). Provide the answer in both square yards and square meters in the same table ('unit=sq_yard' and 'unit=sq_meter'). Make sure you give the output columns sensible headings. Once you've got the area of New York, write a query to list the names of all US states, in descending order of area (largest first). What is the fourth largest state?
The function returning length for lines also returns the perimeter for
polygons. The function is
SDO_GEOM.SDO_LENGTH(geometry_column, tolerance, units). Find out the perimeter of the state of New York. Provide the answer in meters and miles (in one query). Now write a query to list the names of all US states, in descending order of perimeter (longest first). What is the state with the fourth longest perimeter?
the bounding box of a spatial object:
Bounding boxes are
an important concept in spatial data handling. They provide the extent
of the object along the ordinates of the coordinate system
(northernmost point of a continent, and so on...). The function
SDO_MBR(geometry) returns the minimum bounding rectangle (MBR) of a
geometry. Find the MBR of New York. You can also retrieve the
individual values along each dimension (x=1,
y=2, z=3; or lon, lat, height).
SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE (geometry_column, dimension_no) FROM ...Retrieve the north-south extent of NY. Write a (single) SQL statement to give the minimum and maximum latitude for each state. Label the columns in your statement NAME, MAXLAT, and MINLAT. HINT: Assign aliases to the outputs of the SELECT statementSELECT (Function1 (parameters...)) alias1, (Function2 (parameters...)) alias2 from ...
Exercise P2.3: Spatial data
In this exercise, you will define and create your own table with sample
spatial data. You will have to create a metadata entry for those
tables: all spatial data in oracle needs metadata to describe its
coordinate system. At the end
of the lab,
don't forget to remove the metadata entries, drop the table and
spatial data: create a table
"petrolstation" with spatial
data in the column location. You will
create a table containing the coordinates of three "petrol stations",
with the following coordinates:
Name (varchar2(8)) ID (NUMBER) SDO_GEOMETRY Lon Lat Agip 1 -77.01669 33.90124 Shell 2 -77.017 33.90120 Texaco 3 -77.018 33.9013
CREATE TABLE petrolstation(
INSERT INTO petrolstation VALUES (
2001, --SDO_GTYPE attribute specifying points of dimension 2
8307, --other parameters of SDO_GEOMETRY, set to 8307 for now
SDO_POINT_TYPE -- point type specification, coordinates follow
(value1, -- value of ordinate 1 (longitude)
value2, -- value of ordinate 2 (latitude)
NULL -- 3rd ordinate, NULL if 2D object
Some of the codes and NULL values in the insert statement may seem a
bit strange. These values are all required by oracle to parameterize
the new geometry. The lectures will provide more information on which
codes to use and exactly what they mean. However, the Oracle Spatial
documentation and Kothuri et al. (2004) are definitive texts to refer
to if in doubt. Note the "--" notation. You can use it to insert
into longer SQL statements for your own orientation. Try to store the
insert segment in a text document with the
extension *.sql. You can load it using:
metadata entry for the spatial table:
treats all the objects with a spatial column in one table as a spatial
layer. If you insert metadata information about this layer in a
metadata table, Oracle will be able to create spatial indexes,
reproject data, and perform more advanced analysis on this objects.
Metadata information in Oracle spatial covers the type of the layer
dimensions), its geographic bounds, tolerance, and spatial reference
INSERT INTO user_sdo_geom_metadata VALUES(Verify the creation of the table by selecting its records. If you like, you should also be able to view your new spatial data using the JBO viewer.
'PETROLSTATION', --table name
'location', -- name of spatial column
SDO_DIM_ARRAY -- DIMINFO attribute, stores dimension bounds and tolerance
'LONGITUDE', -- dimension name for first dimension
-180, -- SDO_LB - lower bound of the dimension values
180, -- upper bound for the dimension values
0.5 -- tolerance for values - 0.5 meters
'LATITUDE', -- dimension name for second dimension
-90, -- SDO_LB - lower bound of the dimension values
90, -- upper bound for the dimension values
0.5 -- tolerance for values - 0.5 meters
8307 -- SRID value for WGS84
Exercise P2.4: Spatial indexesSpatial indexes speed up the retrieval of spatial object based on their geometric properties, and enable faster execution of some spatial analysis functions. Many ingenious spatial indexes have been proposed, some of which we will examine in more detail later in the course. However, it is not necessary to understand how an index works in order to use it in Oracle. In this section we will create an index for our spatial data. Although you won't see and change to your data, creating an index is required for many spatial analyses. (If you don't believe this, you might like to try to skip ahead to P2.5.2 and see what happens!)
- Creation of
Create a spatial index on the location column of the petrolstation
table using the following template:
CREATE INDEX indexname ON tablename(spatial_column) INDEXTYPE IS MDSYS.SPATIAL_INDEX;It is recommended to always perform a:DROP INDEX indexname;statement for a specific index before creating a new one. Note that depending on the nature of the spatial index (Quadtree, R-tree, ...) after deletion/insertion of data in the table the index may need to be re-created. Also, different geometric layouts of the data can justify the use of different indexes. The default index in Oracle Spatial is the R-Tree, but other indexes are also possible. R-trees, quadtrees, and many other indexes will all be covered later in the course.
- List all
Sometimes it is useful to know which indexes have already been created.
The "user_indexes" table contains this information, as shown in the
SELECT index_name, table_name FROM user_indexes;
Exercise P2.5: Simple geometric analysisIn this section we perform some simple geometric analyses covered in the lectures. In the next practical we shall perform some more complex analyses.
distance between any geometry (point, line, or polygon) and point can
be determined using the function SDO_GEOM.SDO_DISTANCE(geom1, geom2,
tolerance, units). Note that in the case of the distance between a line
or polygon and a point the procedure retrieves the minimum distance
notes for more information). For example, try the following query which
will provide the distance in miles between two of the petrol stations.
What are the distances between all pairs of the petrol stations?
SELECT SDO_GEOM.SDO_DISTANCE(a.location, b.location, 1, 'unit=mile') FROM petrolstation a, petrolstation b WHERE a.name='Agip' and b.name='Texaco';
the names of the 10
restaurants from the restaurant POI_ID=33625120 (us_restaurants table).
The general template
for the distance clause is as for that above:
SELECT SDO_GEOM.SDO_DISTANCE(query_objects, origin_object,tolerance) FROM ...;However, you will need to use a nested statement and the operator ROWNUM from Practical 1 to answer this question. Assign an alias 'DISTANCE' to the function SDO_GEOM.SDO_DISTANCE and order by distance for the first subquery.
also find places that are within a certain distance of each other
(assuming an index has been created) using the SDO_WITHIN_DISTANCE
function. Try the following query, inserting a range of sensible values
in place of the distance "x":
SELECT * FROM petrolstation a, petrolstation b WHERE SDO_WITHIN_DISTANCE(a.location, b.location, 'distance=x unit=METER')='TRUE';
creates a new geometry containing all the locations within a specified
distance of a input geometry. Create buffers of 1 mile around each of
the petrol stations, using the following template:
SELECT SDO_GEOM.SDO_BUFFER(geometry, distance, tolerance, units) FROM ...
the centroid (center of gravity) for a polygonal shape using the
SELECT SDO_GEOM.SDO_CENTROID(geometry, tolerance) ...Use this template to find the centroid of the buffer of 1000m for the shell petrol station. What do you notice about this centroid and why?
Assessment A2: Spatial SQL
- Your submission will be
marked out of 10,
with up to one
mark awarded per
- A perfect answer will be
mark. A near-perfect answer
(almost exactly right, but
with some slight mistake or missing step) may be awarded half a mark.
Anything else will be
will be deducted
for any incorrect
submissions (e.g., using a Word or HTML document instead of a plain
text file; files that contain other than 10 answers with one answer or
line; answers not in the correct order; answers that contain non-SQL
parts, such as question numbers, or characters, such as double
quotation " marks instead of single quotation ' marks).
- It is not possible to get negative marks (e.g., an assignment score of 1 out of 10 with 2 marks deducted for incorrect submission will be a final mark of 0, not -1).
AssignmentThe questions you must answer for this assignment all concern the sample data from the US Census Bureau the Digital Chart of the World used 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 country names and spatial object geometry types (SDO_GTYPE) for all the countries beginning with "An".
- List the spatial reference system (SDO_SRID) in which the coordinates of Australia are stored.
- List the official name (not wktext!) of the coordinate system with SRID 2236.
- List the country name (in
alphabetical order), area (in square
kilometers), perimeter (in kilometers), and the maximum latitudes of
all countries beginning with the letter "U". You must give your
column headings meaningful and concise aliases (i.e., "Area",
- List the name, area (in square meters, computed from the geometry) and perimeter (in meters) of all US states with populations ("totpop" attribute) of more than 15 million people. Your list must be in decreasing order of population (most populous first) and must again use sensible aliases for your column headings.
- Find the minimum distance in miles from the city of "Fresno" ("us_cities" table, "city" attribute) to the "Potomac" river ("us_rivers" table, "name" attribute).
- List the names, state
abbreviation, and distances (in miles) of all US cities
that are within 100 miles
of New York city. List your answer in order of
distance (nearest first). Note: you must use the SDO_GEOM.SDO_DISTANCE
function to answer this query instead of the SDO_WITHIN_DISTANCE
function (because you do not have enough privileges to create the index
required for the SDO_WITHIN_DISTANCE function for tables in the
- List the names of the cities in the US state of "Oregon" in order of distance to the center (centroid) of the state (closest first).
- Create a buffer of 100 miles around the "Hudson" river.
- List in alphabetical order the names of the countries whose s have areas that are more than 15% larger than the area reported in the "sqmi_cntry" attribute of the "world_countries" table.