In this lab, you will be performing spatial queries on a dataset acquired from Natural Earth. The dataset is already in the Oracle database under the TA's account (csci5715). You will be able to query from the TA's tables, no need to import the data yourself (details below).
The dataset contains 3 tables, COUNTRIES, CITIES, and RIVERS. The data characteristics are as follows:
The "COUNTRIES" is a geospatial dataset describing countries in the world. The columns are:
"name" which indicates the name of countries (e.g., Qatar, Romania);
"region_un" which indicates the continent the countries belong to;
"pop2005" which indicates the (approximate) population of countries;
"ISO2" which indicates ISO 3166-1 Alpha-2 country code for the countries;
"geom" which indicates the geometry information. (The Geometry column in this table is of type Polygon.)
The "CITIES" is a geospatial dataset describing populated cities and towns in the world. The columns are:
"name" which indicates the name of cities (e.g., Athens, Belgrade);
"pop2015" which indicates the population of cities in 2015;
"timezone" which indicates the timezone of cities;
"geom" which indicates the geometry information. The Geometry column in this table is of type Point, representing locations of city centers.
The "RIVERS" is a geospatial dataset describing famous rivers centerlines in the world. The columns are:
"name" which indicates the name of rivers (e.g., Nile, Amazon);
"geom" which indicates the geometry information. The Geometry column in this table is of type Line String, representing center lines of rivers.
These three views can be referenced from the TA account, csci5715 by prefixing the table name with the TA's account name (i.e., csci5715). For example:
SELECT * FROM csci5715.RIVERS;
Many Geometric Analysis functions (e.g., Area, distance) ask for a tolerance and unit(optional). The tolerance parameter specifies accuracy requirement for the result Please see Section 1.5.5 of Oracle Spatial Developer's Guide for more details on tolerance. The tolerance parameter can be specified explicitly as illustrated in the example below:
What is the area of Italy?
SELECT n.name, SDO_GEOM.SDO_AREA(n.GEOM, 0.5) as area
FROM csci5715.COUNTRIES n
WHERE n.name = 'Italy';
Alternatively, the tolerance may be stored in USER_SDO_GEOM_METADATA table with other metadata by running the following 3 SQL INSERT STATEMENTS:
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('COUNTRIES', 'GEOM', 8307,
SDO_DIM_ARRAY
(SDO_DIM_ELEMENT('LONGITUDE', -180, 180, 0.5),
SDO_DIM_ELEMENT('LATITUDE', -90, 90, 0.5)));
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('RIVERS', 'GEOM', 8307,
SDO_DIM_ARRAY
(SDO_DIM_ELEMENT('LONGITUDE', -180, 180, 0.5),
SDO_DIM_ELEMENT('LATITUDE', -90, 90, 0.5)));
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('CITIES', 'GEOM', 8307,
SDO_DIM_ARRAY
(SDO_DIM_ELEMENT('LONGITUDE', -180, 180, 0.5),
SDO_DIM_ELEMENT('LATITUDE', -90, 90, 0.5)));
Note: The column "SRID" indicates the coordinate system. In our case, we use "8307" which means Geodetic coordinate system.
The column "DIMINFO" indicates the range of our coordinate system and the tolerance. In our case, longitude is set between -180 to 180 degrees, latitude is set between -90 to 90 degrees, tolerance is set to 0.5 meters.
Below is an example showing the alternative way to do spatial query using USER_SDO_GEOM_METADATA.
What is the area of Italy?
SELECT n.name, SDO_GEOM.SDO_AREA(n.GEOM, m.DIMINFO) as area
FROM csci5715.COUNTRIES n, user_sdo_geom_metadata m
WHERE m.table_name = 'COUNTRIES'
AND m.column_name = 'GEOM'
AND n.name = 'Italy';
http://www.spatial.cs.umn.edu/Courses/Fall22/5715/index.php?page=homeworks/lab