Oracle Spatial Data and SDO_GEOM.SDO_CONVEXHULL fun

In the following post we will introduce basic concepts of spatial data and more specifically of Oracle Spatial Data.

Finally you will be able to understand the example about how the SDO_GEOMETRY function works when we want to know the smallest convex envelope of a set of X points in a map.

Spatial data is used in geocoding, spatial searches, routing and mapping.

The ability to display data using an appropriate visualization is essential to providing insights to Business Intelligence users.

What we mean when we talk about Spatial Data?

Spatial data (also known as geospatial data or geographic information) is the data or information that identifies the geographic location of features and boundaries on Earth.

It is usually stored as coordinates and topology, and is data that can be mapped.

The access, manipulation and analysis of this kind of data usually is get through Geographic Information Systems (GIS).

And…what about GIS?

A Geographic Information System (GIS) links locational (spatial) and database (tabular) information and enables a person to visualize patterns, relationships, and trends. This process gives an entirely new perspective to data analysis that cannot be seen in a table or list format.

GIS allows us to view, understand, question, interpret, and visualize our world in ways that reveal relationships, patterns, and trends in the form of maps, globes, reports, and charts.

Getting to Know Oracle Spatial Data…

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

OSD provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle9i database.

OSD consists of the following components:

  • A schema (MDSYS) that prescribes the storage, syntax, and semantics of supported geometric data types.
  • A spatial indexing mechanism.
  • A set of operators and functions for performing area-of-interest queries, spatial join queries, and other spatial analysis operations.
  • Administrative utilities.

The spatial component of a spatial feature is the geometric representation of its shape in some coordinate space. This is referred to as its geometry.

OSD supports the object-relational model for representing geometries. The object-relational model uses a table with a single column of MDSYS.SDO_GEOMETRY and a single row per geometry instance.

A geometry is an ordered sequence of vertices that are connected by straight line segments or circular arcs. The semantics of the geometry are determined by its type.

OSD supports several primitive types, and geometries composed of collections of these types, including 2-dimensional:

  • Points and point clusters
  • Line strings
  • n-point polygons
  • Arc line strings (all arcs are generated as circular arcs)
  • Arc polygons
  • Compound polygons
  • Compound line strings
  • Circles
  • Optimized rectangles

2-dimensional points are elements composed of two ordinates, X and Y, often corresponding to longitude and latitude. Line strings are composed of one or more pairs of points that define line segments. Polygons are composed of connected line strings that form a closed ring and the interior of the polygon is implied.

Example:

In this post we are going to use the geometry function SDO_GEOM.SDO_CONVEXHULL which returns a polygon-type object than represents the convex hull of 4 coordinates Spanish cities: Bilbao, Badajoz, Valencia and Madrid.

In a database, first of all we will create a new table, for example “Cities”:

CREATE TABLE Cities (

ID int PRIMARY KEY,

City VARCHAR2(15),

Longitude FLOAT(20),

Latitude FLOAT(20)

);

— Insert Rows with the information of the four cities:

INSERT INTO Cities VALUES (

‘1’,

‘Bilbao’,

‘-2.923432’,

‘43.256977’

);

INSERT INTO Cities VALUES (

‘2’,

‘Badajoz’,

‘-6.9706100’,

‘38.8778900’

);

INSERT INTO Cities VALUES (

‘3’,

‘Madrid’,

‘-3.700138’,

‘40.416795’

);

INSERT INTO Cities VALUES (

‘4’,

‘Valencia’,

‘-0.37739’,

‘39.4697500’

);

— Create the “Geometry” column in Cities table. SDO_GEOMETRY is the type of variable

ALTER TABLE Cities ADD (Geometry SDO_GEOMETRY);

— Let’s configure the new column: “Geometry”

UPDATE Cities SET Geometry=SDO_GEOMETRY

(

2001,8307, SDO_POINT_TYPE(Longitude,Latitude,NULL),

NULL,NULL

);

/* In 2001 the 2 indicates two-dimensional, and the 1 indicates a single point.

8307 refers to the SRID (Spatial Reference System Identifier) for ‘Longitude / Latitude (WGS 84)’ coordinate system.*/

Note: The latest revision of WGS (World Geodetic System) is WGS 84 which is the reference coordinate system used by the Global Positioning System (GPS).

/* It is not needed to specify the table in this case because there is only one table. In the case that there were more tables with the same column name, the script would be:

(SELECT SDO_GEOMETRY(2001,8307, SDO_POINT_TYPE(c.Longitude,c.Latitude, NULL), NULL,NULL)

FROM Cities c);

*/

— Let’s insert values into the “Geometry” column

INSERT INTO USER_SDO_GEOM_METADATA VALUES

(

‘Cities’,

‘Geometry’,

MDSYS.SDO_DIM_ARRAY(

MDSYS.SDO_DIM_ELEMENT(‘Longitude’,-180,180,1),

MDSYS.SDO_DIM_ELEMENT(‘Latitude’,-90,90,1)

),8307

            );

/*

In this example, in MDSYS.SDO_DIM_ELEMENT appears the number 1. That is the tolerance. Tolerance is used to associate a level of precision with spatial data. The range of values and the significance of the value depend on whether or not the spatial data is associated with a geodetic coordinate system:

For geodetic data (such as data identified by longitude and latitude coordinates), the tolerance value is a number of meters. For example, a tolerance value of 100 indicates a tolerance of 100 meters.

For non-geodetic data, the tolerance value can be up to 1, referring to the decimal fraction of the distance unit in use. (If a coordinate system is specified, the distance unit is the default for that system.) For example, a tolerance value of 0.005 indicates a tolerance of 0.005 (that is, 1/200) of the distance unit.

In both cases, the smaller the tolerance value, the more precision is to be associated with the data.

*/

— It is needed to create the following index:

CREATE INDEX Cities_idx

            ON Cities(Geometry)

            INDEXTYPE IS MDSYS.SPATIAL_INDEX;

/*

A spatial index, like any other index, provides a mechanism to limit searches, but in this case based on spatial criteria such as intersection and containment. A spatial index is needed to:

  • Find objects within an indexed data space that interact with a given point or area of interest (window query)
  • Find pairs of objects from within two indexed data spaces that interact spatially with each other (spatial join)

*/

— Query the points that the polygon involves:

SELECT SDO_AGGR_CONVEXHULL(SDOAGGRTYPE(Geometry,1))

FROM Cities;

The result will be:

NOTE: Sometimes, the result does not show the points. In this case, if you double click in the [MDSYS.SDO_GEOMETRY] result, the points will show.

Leave a Comment