Why do you need to read this post?

Having knowledge of the different types that represent a geometry can give you the opportunity to visualise more and highly complex geometries on your APEX Maps. Furthermore, at the end of this article you will know where each type has its best usage and how to convert data from one type to another.

For me personally, this blog post is a way to better understand all the benefits we get by using the APEX Map Region on top of Oracle Database and especially Spatial.

Happy reading everyone!

Map Region Layer Types

Points: Points (e.g. customer or supplier locations) are displayed as markers. The marker shape and colour is configurable.
Lines: Lines represent features like roads or paths. Stroke style and color is configurable.
Polygons: Polygons represent areas like parcels, states or countries. Style and color are configurable for stroke and fill.
Heat Map: Heat Maps are used to visualize the density of point features. The more points are clustered together, the more intense the color gets. Typically used to visualize the spatial distribution of population or incidents.
Extruded Polygons: Similar to Polygons, but the polygons are displayed as three-dimensional, "extruded", objects. The height of the 3D object visualizes a column value.

Geometry Column Data Types

APEX allows the use of three different data types to represent your objects on a Map Layer. This happens in the "Column Mapping" section of the Map Layer. Changing the Type will provide different options for selecting the Geometry Columns.

GeoJSON
SDO_GEOMETRY
Longitude/Latitude

Geometry Data Types

Each of the three data types can be used to represent points layer. GeoJSON and SDO_GEOMETRY can be used to represent all 5 different types of Map Layer.

Data Types usage, pros and cons

Longitude/Latitude is the most popular and easy to understand data type. It represent the 2D coordinates of a point on the map. Most people are already familiar with it and most data sources available can provide you these two.
GeoJSON, as the name suggests is a geographical data, represented in a JSON format. It is following a special global standard (GeoJSON format) and is easy to read. Oracle has special datatype to store such information and can index it. It is particularly useful, when working with Javascript and is used when modifying information on the map. It can represent all types of geometries - points, polygons, lines and so on.
SDO_GEOMETRY is a special Oracle Object type which is used by Oracle Spatial. It contains information about the coordinate system used, the type of geometry, the dimensions, data points information as array, etc. Perfect for all kinds of Map Layes, supported in APEX and especially useful when more complex tasks are solved. In use by the SDO_UTIL and SDO_SAM packages, which can determine distances between points on the map, do analysis and others calculations. It can represent all types of geometries - points, polygons, lines and so on.

Data Types Description

See how the datatypes look like for a single point and a polygon:

-- *** Single Point ***        

-- GeoJSON
{"type": "Point", "coordinates": [-0.051, 51.50254]}    

-- SDO_GEOMETRY
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-0.051, 51.50254, NULL), NULL, NULL);

-- Longitude/Latitude
Longitude: -0.051
Latitude: 51.50254

-- *** Polygon ***        

-- GeoJSON
{"polygon":{"datapoints":[[-119.48,33.20],[-115.15,36.08],[-117.15,33.62]]}}

-- SDO_GEOMETRY
MDSYS.SDO_GEOMETRY(2002, NULL, NULL, 
                   MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), 
                   MDSYS.SDO_ORDINATE_ARRAY(-119.48,33.20, -115.15,36.08, -117.15,33.62));

SDO_GEOMETRY

Here is how the SDO_GEOMETRY object looks like (and some variants of it above):

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

Understanding SDO_GTYPE

D identifies the number of dimensions (2, 3, or 4)
L identifies the linear referencing measure dimension for a three-dimensional linear referencing system (LRS) geometry, that is, which dimension (3 or 4) contains the measure value. For a non-LRS geometry, specify 0.
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.)

In above examples:

2001 - Two Dimensional, non-LRS geometry, POINT
2002 - Two Dimensional, non-LRS geometry, LINE

Understanding SDO_SRID

If SDO_SRID is null, no coordinate system is associated with the geometry.

The Oracle Spatial coordinate system support is based on, but is not always identical to, the European Petroleum Survey Group (EPSG) data model and dataset. The WGS 84 (longitude/latitude) coordinate system has the EPSG SRID value of 4326 (same as the example above).

*By default, Oracle APEX uses the same value (4326) as SRID (see documentation).

Understanding SDO_POINT

CREATE TYPE sdo_point_type AS OBJECT (
   X NUMBER,
   Y NUMBER,
   Z NUMBER);

-- in the example above
MDSYS.SDO_POINT_TYPE(-0.051, 51.50254, NULL)

If you have some value for SDO_ELEM_INFO and SDO_ORDINATES, the SDO_POINT attribute is ignored by Spatial. If you have only point geometries in a layer (like the example above), it is strongly recommended that you store the point geometries in the SDO_POINT attribute.

Understanding SDO_ELEM_INFO

MDSYS.SDO_ELEM_INFO_ARRAY(SDO_STARTING_OFFSET, SDO_ETYPE, SDO_INTERPRETATION)

-- example
MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1)

-- in the example above:
--  SDO_STARTING_OFFSET - 1 (start from the first element)
--  SDO_ETYPE - 2 (considered a simple element)
--  SDO_INTERPRETATION - 1 (interprets the sequence)

Check the documentation for more info about the interpretation and offset HERE

Understanding SDO_ORDINATES

This is the array of points that describe the object:

{X1, Y1, X2, Y2, X3, Y3, X4, Y4, X1, Y1} represents a polygon whose boundary has four two-dimensional points for example.
{X1, Y1, Z1, X2, Y2, Z2, X3, Y3, Z3, X4, Y4, Z4, X1, Y1, Z1} represents three-dimensional polygon.

MDSYS.SDO_ORDINATE_ARRAY(-119.48,33.20, -115.15,36.08, -117.15,33.62)

-- in the example above - an array of 3 two-dimensional points, forming a polygon

Converting Data Types

-- *** Types conversion examples ***

-- Convert SDO_GEOMETRY to GeoJSON
select apex_json.stringify( 
         mdsys.sdo_geometry( 2001, 4326, sdo_point_type( 10, 50, null ), null, null ) 
       )
from dual;

select apex_json.stringify( 
         MDSYS.SDO_GEOMETRY(2002, NULL, NULL, 
                   MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), 
                   MDSYS.SDO_ORDINATE_ARRAY(-118.4899722, 34.2098056, -115.15225, 36.0800556, -116.1596528, 33.6266667))            
       )
from dual;

select c.geom.Get_GeoJson() GeoJSON_converted
  from (select MDSYS.SDO_GEOMETRY(2002, NULL, NULL, 
                   MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), 
                   MDSYS.SDO_ORDINATE_ARRAY(-118.4899722, 34.2098056, -115.15225, 36.0800556, -116.1596528, 33.6266667)) geom           
        from dual) c;

select SDO_UTIL.TO_GEOJSON(
              MDSYS.SDO_GEOMETRY(2002, NULL, NULL, 
                   MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), 
                   MDSYS.SDO_ORDINATE_ARRAY(-118.4899722, 34.2098056, -115.15225, 36.0800556, -116.1596528, 33.6266667))) geom           
from dual;

-- All of the previous 3 methods to convert SDO_GEOMETRY to GeoJSON will return the same result
-- { "type": "LineString", "coordinates": [ [-118.4899722, 34.2098056], [-115.15225, 36.0800556], [-116.1596528, 33.6266667] ] }

-- Convert GeoJSON to SDO_GEOMETRY
select SDO_UTIL.FROM_JSON('{"type":"Point","coordinates":[10,50]}') from dual;
select SDO_UTIL.FROM_GEOJSON('{"type":"Point","coordinates":[10,50]}') from dual;
-- both functions provide exactly the same result

select SDO_UTIL.FROM_JSON('{"type":"LineString","coordinates":[[-118.4899722,34.2098056],[-115.15225,36.0800556],[-116.1596528,33.6266667]]}') from dual;
select SDO_UTIL.FROM_GEOJSON('{"type":"LineString","coordinates":[[-118.4899722,34.2098056],[-115.15225,36.0800556],[-116.1596528,33.6266667]]}') from dual;
-- both functions provide exactly the same result

Useful Links

APEX_SPATIAL

Check APEX_SPATIAL documentation

SDO_GEOMETRY, SDO_UTIL and SDO_SAM

Check SDO_GEOMETRY documentation

Check SDO_UTIL documentation. The MDSYS.SDO_UTIL package contains spatial utility subprograms.

SDO_SAM. The SDO_SAM package contains functions and procedures for spatial analysis and data mining.

GeoJSON

Using GeoJSON geographic data - Oracle documentation

GeoJSON Data Support in Oracle Database 19c - Oracle Base

GeoJSON format

GeoJSON.io - a tool that helps generate, validates and visualise your GeoJSON on an interactive map

Notes

This is just the first part of my notes on how to better understand and use the APEX Map Region. In the next blog posts I will demonstrate all of the features, learned here, with a demo. Stay tuned!

APEX App Lab - Blog