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.
|
1 2 3 4 5 6 |
CREATE TYPE sdo_geometry AS OBJECT (SDO_GTYPE NUMBER, SDO_SRID NUMBER, SDO_POINT SDO_POINT_TYPE, SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, SDO_ORDINATES SDO_ORDINATE_ARRAY ); |
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.
- Go to https://github.com/oracle/dino-date
- Fork the repository.
- Clone the repository.
- Follow the DinoDate install instructions.
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
declare procedure exec_dml (stmt in varchar2, ignore_code in number default null) as begin execute immediate stmt; exception when others then if ignore_code is null or sqlcode != ignore_code then raise; end if; end; begin /* delete tutorial objects from dd.dd_locations */ delete from dd_locations where LOCATION_NAME in ('dino-island', 'asteroid-zone'); commit; /* Remove dd.dd_members_t.sptut_location column index*/ exec_dml('drop index dd_member_sptut_location_sx', -1418); /* Remove dd.dd_members_t.sptut_location column*/ exec_dml('alter table dd.dd_members_t drop column sptut_location', -904); /* add dd.dd_members_t.sptut_location column*/ exec_dml('alter table dd.dd_members_t add sptut_location sdo_geometry'); /* rebuild dd.dd_members view*/ exec_dml('create or replace editioning view dd_members as select * from dd_members_t'); /* set up oracle spatial for dd.dd_members_t.sptut_location */ delete from user_sdo_geom_metadata where table_name='DD_MEMBERS_T'; insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values ('DD_MEMBERS_T', 'sptut_location', sdo_dim_array (sdo_dim_element ('X', -180, 180, 1), sdo_dim_element ('Y', -90, 90, 1)), 4326); commit; /* create spatial index on dd.dd_members_t.sptut_location */ exec_dml('create index dd_member_sptut_location_sx on dd.dd_members_t (sptut_location) indextype is mdsys.spatial_index', -955); /* rebuild index on location table, just in case */ exec_dml('drop index dd_location_sx', -1418); exec_dml('create index dd_location_sx on dd_locations_t (geometry) indextype is mdsys.spatial_index', -955); end; / show errors |
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.
|
1 |
exec dbms_output.put_line(dd_admin_pkg.generate_members(1000)); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
declare procedure exec_dml (stmt in varchar2, ignore_code in number default null) as begin execute immediate stmt; exception when others then if ignore_code is null or sqlcode != ignore_code then raise; end if; end; begin /* delete tutorial objects from dd.dd_locations */ delete from dd_locations where location_name in ('dino-island', 'asteroid-zone'); commit; /* remove dd.dd_members_t.sptut_location column index*/ exec_dml('drop index dd_member_sptut_location_sx', -1418); /* remove dd.dd_members_t.sptut_location column*/ exec_dml('alter table dd.dd_members_t drop column sptut_location', -904); /* rebuild dd.dd_members view*/ exec_dml('create or replace editioning view dd_members as select * from dd_members_t'); /* remove oracle spatial data for dd.dd_members_t.sptut_location */ delete from user_sdo_geom_metadata where table_name='DD_MEMBERS_T'; /* rebuild index on location table */ exec_dml('drop index dd_location_sx', -1418); exec_dml('create index dd_location_sx on dd_locations_t (geometry) indextype is mdsys.spatial_index', -955); end; / show errors |
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.
|
1 2 3 4 5 |
MDSYS.SDO_GEOMETRY(2001, --2 dimensions, 0 LRS, 01 is a point 4326, --SRID for Earth lat/lon system MDSYS.SDO_POINT_TYPE(0, 0,NULL), --point located at Lon-0,Lat-0 NULL, --not used for a point NULL) --not used for 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.
Create a Line
Select the following example from dual to create a line.
|
1 2 3 4 5 6 7 8 |
MDSYS.SDO_GEOMETRY(2002, --2 dimensions, 0 LRS, 02 is a line 4326, --SRID for Earth lat/lon system NULL, -- only used for a point MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1<sup>st</sup> oordinate 2, --a straight line 1), --a simple straight line MDSYS.SDO_ORDINATE_ARRAY(0,0, -–start coordinates 0.9,0)) -–end coordinates |
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)
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.