Getting Started With Oracle Spatial part 1

In this tutorial, we’ll go over how to create some basic spatial objects such as points, lines and polygons. We’ll create some random objects and run some fun queries.

We’ll be using the Spatial and Graph Developer’s Guide as our reference.

First, let’s take a look at the SDO_GEOMETRY Object.

SDO_GTYPE is a 4 digit number that defines what type of object we’re making, using a format of DLTT

D = Dimensions of the object 2, 3 or 4 (can not mix different dimensions in the same layer)

L = Linear Referencing System. This is a more advanced topic for another day. For our examples, we will be using 0.

TT = Type of Geometry, 00 through 09

We will be using the following Geometry Types. You can find the full list here.

Value Description
DL01 Point Geometry contains one point.
DL02 Line or Curve Geometry contains one line string that can contain straight or circular arc segments, or both. (LINE and CURVE are synonymous in this context.)
DL03 Polygon or Surface Geometry contains one polygon with or without holes,1 or one surface consisting of one or more polygons. In a three-dimensional polygon, all points must be on the same plane.

SDO_SRID is a number referencing the coordinate system used, it must either be NULL or the SRID value from the SDO_COORD_REF_SYS table.  We’ll be using 4326, which is the standard Earth-based Latitude / Longitude system.

SDO_POINT is used to define the object as a point. This is only used if this object is a point, otherwise, this should be NULL and the following two parameters will define the object.

SDO_ELEM_INFO is basically a set of numbers that work like a key for using the information in the SDO_ORDINATES parameter to create the object. These numbers are grouped into one or more sets of 3 numbers or triplets. If we are creating a complex object that has more than one segment, each segment will be defined by its own triplet.

  • The first number in the triplet is the SDO_STARTING_OFFSET and indicates which number in the following SDO_ORDINATE_ARRAY is the starting point for this segment. The first element is 1.
  • The second number is the SDO_ETYPE which is used to define the type of object we are creating in this segment. For example, 2 is a straight line and 1003 is a simple polygon.
  • The third number is the SDO_INTERPRETATION and is used in one of two ways.
    • For simple objects such as a polygon, the SDO_INTERPRETATION will further define the type of polygon. 1 is a simple straight line polygon, 2 uses circular arcs, 3 is a rectangle and 4 is a circle.
    • For complex objects such as a polygon with holes in it, SDO_INTERPRETATION is used to specify how many ordinates are used for this segment.

Please see Table 2-2 Values and Semantics in SDO_ELEM_INFO for further details.

SDO_ORDINATES are pairs of coordinates used along with SDO_ELEM_INFO to define the object

Setup

You’ll need a copy of the git repository.

  1. Go to https://github.com/oracle/dino-date
  2. Fork the repository.
  3. Clone the repository.
  4. Follow the DinoDate install instructions.
  5. Run the following as the new DD user.

This script will add the sptut_location column to our dd_members table, setup the spatial metadata and create the spatial indexes. We will not be covering these steps in the tutorial.

 

This script can also be used to reset the database if something goes wrong.

We’ll need some dinosaurs to locate so let’s randomly generate a few.

Connected as dd run the following command to generate 1,000 dinosaurs.

Cleanup

If you’d like to restore the DD schema to its default state, run this script to remove the new column and re-create the dd_locations index.

View the Objects

For my examples, I will be using Oracle SQL Developer which includes a Map View utility to display the objects.

Create a Point

Select the following example from dual to create a point.

When you run the query you’ll see it generates an SDO_GEOMETRY type object. In SQL Developer you can select the results, right click the object and “Invoke Map View on Results Set” to display the point in the Map Viewer.

Note that there is no background map displayed, just an empty background with a point. If there were a map, this point would be just west of Africa.

Exercise:

Create a point at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920.

If you invoke the map view for both points, you can change the marker type for one to identify which is which. If your Bermuda Triangle point is South East of 0,0 you have your Lat and Lon backward. If this is a consistent issue, you can use named parameters.

Answer

Create a Line

Select the following example from dual to create a line.

This creates a line starting at Lon-0, Lat-0 and ending at Lon-0.9, Lat-0, approximately 100KM East.

Exercise:

Create a line starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and going approximately 100KM North. (0.9 is approximately 100KM)

Answer

Experiment with other lines. Keep in mind if your object is “off the map”, it will be invalid.

In the next post, I’ll go over creating polygons and run some fun spatial queries.

Leave a Reply