Oracle Spatial - Home Page

1 - About

Oracle Spatial is an integrated set of functions and procedures that enables spatial data to be stored, accessed, and analyzed quickly and efficiently in an Oracle database.

Geographic features can be represented in:

  • vector
  • or raster format,
  • or both.

With vector data, points are represented by their explicit x,y,z coordinates, lines are strings of points, and areas are represented as polygons whose borders are lines. This kind of vector format can be used to record precisely the location and shape of spatial objects.

With raster data, you can represent spatial objects by assigning values to the cells that cover the objects, and you can represent the cells as arrays. This kind of raster format has less precision than vector format, but it is ideal for many types of spatial analysis. In the raster geographic information systems (GIS) world, this kind of raster data is normally called gridded data.

Oracle Locator is a cut down version of Oracle Spatial Standard Edition (SE/SE1) database installations.

3 - What is it ?

Oracle Spatial consists of a set of:

  • object data types,
  • type methods,
  • and operators,
  • functions,
  • and procedures that use these types.

A geometry is stored as an object, in a single row, in a column of type SDO_GEOMETRY. Spatial index creation and maintenance is done using basic DDL (CREATE, ALTER, DROP) and DML (INSERT, UPDATE, DELETE) statements.

Spatial (like locator) is not designed to be an end-user application, but is a set of spatial capabilities for application developers.

The goal is to answer to any “WHERE” question, for example :

  • where the cola is most preferred by residents,
  • where the manufacturer believes the cola has growth potential, and so on …

4 - SDO Geometry Method

Get_Dims NUMBER Returns the number of dimensions of a geometry object, as specified in its SDO_GTYPE value. In Oracle Spatial, the Get_Dims and ST_CoordDim methods return the same result.
Get_GType NUMBER Returns the geometry type of a geometry object, as specified in its SDO_GTYPE value.
Get_LRS_Dim NUMBER Returns the measure dimension of an LRS geometry object, as specified in its SDO_GTYPE value. A return value of 0 indicates that the geometry is a standard (non-LRS) geometry, or is an LRS geometry in the format before release 9.0.1 and with measure as the default (last) dimension; 3 indicates that the third dimension contains the measure information; 4 indicates that the fourth dimension contains the measure information.
Get_WKB BLOB Returns the well-known binary (WKB) format of a geometry object. (The returned object does not include any SRID information.)
Get_WKT CLOB Returns the well-known text (WKT) format (explained in Section 6.7.1.1) of a geometry object. (The returned object does not include any SRID information.)
ST_CoordDim NUMBER Returns the coordinate dimension (as defined by the ISO/IEC SQL Multimedia standard) of a geometry object. In Oracle Spatial, the Get_Dims and ST_CoordDim methods return the same result.
ST_IsValid NUMBER Returns 0 if a geometry object is invalid or 1 if it is valid. (The ISO/IEC SQL Multimedia standard uses the term well formed for valid in this context.) This method uses 0.001 as the tolerance value. (Tolerance is explained in Section 1.5.5.) To specify a different tolerance value or to learn more about why a geometry is invalid, use the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function, which is documented in Chapter 15.

5 - Unit of Measurement Support

The default unit of measure is the one associated with the georeferenced coordinate system.

The unit of measure for most coordinate systems is the meter, and in these cases the default unit for distances is meter and the default unit for areas is square meter. By using the unit parameter, however, you can have Spatial automatically convert and return results that are more meaningful to application users, for example, displaying the distance to a restaurant in miles.

SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo, 'unit=KM')
  FROM cola_markets_cs c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE';

Spatial uses the information in the SDO_UNITS_OF_MEASURE table.

For convenience, you can also use the following legacy views to see the angle, area, and distance units of measure:

  • MDSYS.SDO_ANGLE_UNITS
  • MDSYS.SSDO_AREA_UNITS
  • MDSYS.SSDO_DIST_UNITS

6 - Release of Spatial

To check which release of Spatial you are running, use the SDO_VERSION function. For example:

SQL> SELECT SDO_VERSION FROM DUAL;
 
SDO_VERSION
------------------------------------------
10.2.0.4.0

7 - Configuration Verification

CONNECT / AS sysdba
 
SELECT PARAMETER,VALUE FROM v$option WHERE parameter= 'Spatial';
 
SELECT comp_name, status, SUBSTR(version,1,10) AS version FROM dba_registry;
 
SELECT comp_name, status, SUBSTR(version,1,10) AS version FROM dba_registry
WHERE comp_name='Spatial';
 
SELECT COUNT(*)
FROM dba_objects WHERE owner='MDSYS';
 
-- Get a summary count
SELECT object_type, COUNT(*)
FROM dba_objects WHERE owner='MDSYS'
GROUP BY object_type;
 
-- Any invalid objects ?
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner='MDSYS'
AND status <> 'VALID'
ORDER BY object_name;
 
-- List all spatial objects
SELECT object_name, object_type, status
FROM dba_objects WHERE owner='MDSYS'
ORDER BY object_name;

8 - Test Example

And test with this simple example : Inserting, Indexing, and Querying Spatial Data

-- Create a table for cola (soft drink) markets in a
-- given geography (such as city or state).
-- Each row will be an area of interest for a specific
-- cola (for example, where the cola is most preferred
-- by residents, where the manufacturer believes the
-- cola has growth potential, and so on).
-- (For restrictions on spatial table and column names, see 
-- Section 2.6.1 and Section 2.6.2.)
 
CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape SDO_GEOMETRY);
 
-- The next INSERT statement creates an area of interest for 
-- Cola A. This area happens to be a rectangle.
-- The area could represent any user-defined criterion: for
-- example, where Cola A is the preferred drink, where
-- Cola A is under competitive pressure, where Cola A
-- has strong growth potential, and so on.
 
INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
          -- define rectangle (lower left and upper right) with
          -- Cartesian-coordinate data
  )
);
 
-- The next two INSERT statements create areas of interest for 
-- Cola B and Cola C. These areas are simple polygons (but not
-- rectangles).
 
INSERT INTO cola_markets VALUES(
  2,
  'cola_b',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
    SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
  )
);
 
INSERT INTO cola_markets VALUES(
  3,
  'cola_c',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
    SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
  )
);
 
-- Now insert an area of interest for Cola D. This is a
-- circle with a radius of 2. It is completely outside the
-- first three areas of interest.
 
INSERT INTO cola_markets VALUES(
  4,
  'cola_d',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle
    SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)
  )
);
 
---------------------------------------------------------------------------
-- UPDATE METADATA VIEW --
---------------------------------------------------------------------------
-- Update the USER_SDO_GEOM_METADATA view. This is required
-- before the Spatial index can be created. Do this only once for each
-- layer (that is, table-column combination; here: COLA_MARKETS and SHAPE).
 
INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'cola_markets',
  'shape',
  SDO_DIM_ARRAY(   -- 20X20 grid
    SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  NULL   -- SRID
);
 
COMMIT;
 
-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
CREATE INDEX cola_spatial_idx
   ON cola_markets(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- Preceding statement created an R-tree index.
 
-------------------------------------------------------------------
-- PERFORM SOME SPATIAL QUERIES --
-------------------------------------------------------------------
-- Return the topological intersection of two geometries.
SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape, 0.005)
   FROM cola_markets c_a, cola_markets c_c 
   WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_c';
 
-- Do two geometries have any spatial relationship?
SELECT SDO_GEOM.RELATE(c_b.shape, 'anyinteract', c_d.shape, 0.005)
  FROM cola_markets c_b, cola_markets c_d
  WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';
 
-- Return the areas of all cola markets.
SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM cola_markets;
 
-- Return the area of just cola_a.
SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM cola_markets c 
   WHERE c.name = 'cola_a';
 
-- Return the distance between two geometries.
SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005)
   FROM cola_markets c_b, cola_markets c_d
   WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';
 
-- Is a geometry valid?
SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005)
   FROM cola_markets c WHERE c.name = 'cola_c';
 
-- Is a layer valid? (First, create the results table.)
CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('COLA_MARKETS', 'SHAPE', 
  'VAL_RESULTS', 2);
SELECT * FROM val_results;
 
--Get the WKT (well-known text string)
SELECT c.shape.Get_WKT()  FROM cola_markets c WHERE c.name = 'cola_b';
--POLYGON ((5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0))

9 - Spatial Application and Tool

10 - SQL Developer

Support for Spatial Data Management

  • Metadata creation
  • Index creation
  • Validating the Spatial data
  • MapViewer metadata support

Support for Spatial Data Querying

  • Query results can be viewed graphically

11 - Example

11.1 - From the User Guide Example

From the User Guide Example ( Rectangle, Section, Polygon with a Hole, Compound Line String, Compound Polygon, Point, Oriented Point, Type 0 (Zero) Element, Several Geometry Types )

An example of a "polygon with a hole" might be a land mass (such as a country or an island) with a lake inside it. Of course, an actual land mass might have many such interior polygons: each one would require a triplet element in SDO_ELEM_INFO, plus the necessary ordinate specification.

Exterior and interior rings cannot be nested. For example, if a country has a lake and there is an island in the lake (and perhaps a lake on the island), a separate polygon must be defined for the island; the island cannot be defined as an interior polygon ring within the interior polygon ring of the lake.

rectangle Polygon with a Hole Compound Line String Compound Polygon Point Oriented Point Type Zero Element

Statement for Several Geometrie Type :

  • Point
  • Line segment
  • Arc segment
  • Line string
  • Arc string
  • Compound line string
  • Closed line string
  • Closed arc string
  • Closed mixed line
  • Self-crossing line',
  • Polygon
  • Arc polygon
  • Compound polygon
  • Rectangle Circle
  • Point cluster
  • Multipoint
  • Multiline
  • Multiline - crossing
  • Multiarc
  • Multiline - closed
  • Multiarc - touching
  • Multipolygon - disjoint
  • Multipolygon - touching
  • Multipolygon - tangent * INVALID 13351
  • Multipolygon - multi-touch
  • Polygon with void
  • Polygon with void - reverse
  • Crescent (straight lines) * INVALID 13349
  • Crescent (arcs) * INVALID 13349
  • Heterogeneous collection
  • Polygon+void+island touch'
-- Validation
COMMIT;
SELECT i, d, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT (g, 0.5) FROM t1;

11.2 - From the Companion CD

Oracle Spatial provides examples that you can use to reinforce your learning and to create models for coding certain operations. If you installed the demo files from the Companion CD, several examples are provided in the following directory:

$ORACLE_HOME/md/demos/examples

12 - Documentation / Reference

oracle_spatial/oracle_spatial.txt · Last modified: 2011/05/23 21:23 by gerardnico