Spatial Information in an Oracle Database

SDO_GEOMETRY

This is here for me to remember how much magic is used in inserting spatial (geometry) information into an Oracle database¹.

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);

A point geometry:

MDSYS.SDO_GEOMETRY(
    2001, -- 2-dimensional (2), non-linear referencing geometry or default (0), point (01)
    28992, -- Amersfoort / RD New
    NULL, 
    MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
    MDSYS.SDO_ORDINATE_ARRAY(81431.756, 455218.921))

A polygon geometry:

MDSYS.SDO_GEOMETRY(
    2003, -- 2-dimensional (2), non-linear referencing geometry or default (0), polygon (03)
    28992, -- Amersfoort / RD New
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- first coordinate (1), exterior (1003), polygon (1)
    MDSYS.SDO_ORDINATE_ARRAY(
        -1041172.16,1407540.16,
        -958596.8,-591471.68,
        1363835.2,-625878.08,
        1319106.88,1428184,
        -1041172.16,1407540.16) -- coordinates
)

A multipolygon geometry:

MDSYS.SDO_GEOMETRY(
    2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), MULTIPOLYGON or MULTISURFACE (07)
    28992, -- Amersfoort / RD New
    NULL, 
    MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1), 
    MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78, 43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5))

The only thing that makes sense among all the magic numbers are the coordinates. The last coordinate is the same as the first coordinate to close the polygon.

I since have found some more information after some digging regarding the SDO_ELEM_INFO.

It's an array of triplets, where a triplet consists of:

SDO_STARTING_OFFSET

offset within the coordinates array, the first triplet usually has "1" as being the first coordinate in the coordinate array

SDO_ETYPE

type of element

SDO_INTERPRETATION

depends on if SDO_ETYPE is a compound element

I found more information in [2].

WKT - Well-known text representation of geometry

"POLYGON ((194232.738 467652.498, 194232.774322728 467652.19885542, 194232.881179968 467651.917096035, 194232.738 467652.498))"

"POINT (31256.383 393077.6)"

"MULTIPOLYGON(((42 78, 41 85, 46 82.7, 45 79, 42 78),(43.5 72.5, 52.5 72.5, 54.5 79.5, 79 60, 93 46, 53 32, 54 25, 49 22, 45 17, 41 16, 37 23, 33 24, 25 20, 33 46, 39 53, 43.5 72.5)))"

It's possible to create SDO_GEOMETRY in PL/SQL based on WKT strings.

Like so:

SELECT SDO_GEOMETRY('MULTIPOLYGON(((42 78, 41 85, 46 82.7, 45 79, 42 78),(43.5 72.5, 52.5 72.5, 54.5 79.5, 79 60, 93 46, 53 32, 54 25, 49 22, 45 17, 41 16, 37 23, 33 24, 25 20, 33 46, 39 53, 43.5 72.5)))') FROM DUAL;

Convenient if you want to do something quick, but you cannot provide additional information regarding the dimensions, and coordinate system, etc.

Let me rephrase that sentence. It means your resulting geometrie has NO SRID, and you'll get in trouble using it in geometry queries! This is not a joke!

The reverse (if you want to find out the geometrie in WKT format, basically because it reads easier) is of course also possible, like so:

SELECT SDO_UTIL.TO_WKTGEOMETRY(geometry) FROM buildinggeo;

Geometry Functions

So I was playing around with ConvexHull, ConcaveHull and ConcaveHull-Boundary functions.

I took the MultiPolygon in the paragraph above as an example.

SELECT SDO_GEOM.SDO_CONVEXHULL(MDSYS.SDO_GEOMETRY(
2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), multipolygon (07)
28992, -- Amersfoort / RD New
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1), -- first coordinate (1), Simple polygon whose vertices are connected by straight line segments (1003,1), 11th coordinate (11), Simple polygon whose vertices are connected by straight line segments (1003,1)
MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78,
43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5) -- coordinates
), 1)
FROM dual;
-- creates MDSYS.SDO_GEOMETRY(2003, 28992, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(41, 16, 45, 17, 93, 46, 79, 60, 54.5, 79.5, 41, 85, 25, 20, 41, 16))

SELECT SDO_GEOM.SDO_CONCAVEHULL(MDSYS.SDO_GEOMETRY(
2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), multipolygon (07)
28992, -- Amersfoort / RD New
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1), -- first coordinate (1), Simple polygon whose vertices are connected by straight line segments (1003,1), 11th coordinate (11), Simple polygon whose vertices are connected by straight line segments (1003,1)
MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78,
43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5) -- coordinates
), 0.1)
FROM dual;
-- creates MDSYS.SDO_GEOMETRY(2003, 28992, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(41, 85, 42, 78, 43.5, 72.5, 39, 53, 33, 46, 25, 20, 41, 16, 45, 17, 54, 25, 53, 32, 93, 46, 79, 60, 54.5, 79.5, 46, 82.7, 41, 85))

SELECT SDO_GEOM.SDO_CONCAVEHULL_BOUNDARY(MDSYS.SDO_GEOMETRY(
2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), multipolygon (07)
28992, -- Amersfoort / RD New
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1), -- first coordinate (1), Simple polygon whose vertices are connected by straight line segments (1003,1), 11th coordinate (11), Simple polygon whose vertices are connected by straight line segments (1003,1)
MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78,
43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5) -- coordinates
), 0.1, 0.01)
FROM dual;
-- creates MDSYS.SDO_GEOMETRY(2003, 28992, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(41, 85, 33, 46, 25, 20, 41, 16, 45, 17, 54, 25, 53, 32, 93, 46, 79, 60, 54.5, 79.5, 41, 85))

I will expand on this blog post, as my knowledge in this area is expanded.

References

[1] Spatial and Graph Developer's Guide - 2.2 SDO_GEOMETRY Object Type

https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#SPATL489

[2] Spatial and Graph Developer's Guide - 2.2.4 SDO_ELEM_INFO

https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#SPATL494

Spatial and Graph Developer's Guide - 2.1 Simple Example: Inserting, Indexing, and Querying Spatial Data

https://docs.oracle.com/database/121/SPATL/simple-example-inserting-indexing-and-querying-spatial-data.htm#SPATL486

Logic in Epimenides Paradox

The Epimenides paradox reveals a problem with self-reference in logic¹.

Epimenides was a Cretan who made one immortal statement:

“All Cretans are liars.”

Now then, there are two statements that are part of the paradox:

• Epimenides was a Cretan.
  
• All Cretans are liars.

If we assume that Epimenides said "All Cretans are liars.", there are exactly four possible outcomes:

Epimenides was a Cretan	All Cretans are liars
False	False
False	True
True	False
True	True

The Paradox is only visible if both statements be true. In all other cases, there is no paradox, and things go merrily on their way.

Thomas Fowler (1869) made a major mistake in determining the logic negation of the statement "All Cretans are liars.".

He assumed that the opposite of "All Cretans are liars." is "All Cretans speak the truth.". We, hardcore software designers, of course, do not fall for this trap.

The logical negation of "All Cretans are liars." is "Not all Cretans are liars." This, consequently, can be rewritten as "There is at least one Cretan who speaks the truth."

References

[1] Wikipedia - Epimenides Paradox

https://en.wikipedia.org/wiki/Epimenides_paradox