Oracle Spatial - loading geographical data - Points

Following the previous post - Oracle Spatial - SQL based Geographic data - Intro, few examples of loading data points of geographical data.

I will show 2 examples:

a simple case and using a function instead of creating specific SDO column.

Data points example

Step 1: Lets assume we have a Warehouse table with warehouse name, Latitude and Longitude.

Step 2: Lets create a column  named Geometry with the Long & Lat column set as spatial data.  I will use the SDO_GEOMETRY function. 

We can alter the table and add a column named Geometry:

 ALTER TABLE WAREHOUSES ADD (
    GEOMETRY SDO_GEOMETRY
    );

Next update it using SDO_GEOMETRY function while passing the 2 columns as parameters:

UPDATE WAREHOUSES
SET GEOMETRY = SDO_GEOMETRY(
                 2001,                                                             -- 2001 - point
                 4326,                                                             -- 4326 - Longitude & Latitude WSG 84 system
                 SDO_POINT_TYPE(LON, LAT, NULL),      -- LON & LAT columns, the null is for  3-DIM   
                 NULL, NULL);                                              -- used for lines / polygons.

Actually, we don't want wrong data to be inserted so the following is better:

UPDATE WAREHOUSES
SET GEOMETRY = SDO_GEOMETRY(
                 2001,
                 4326,
                 SDO_POINT_TYPE(LON, LAT, NULL),
                 NULL, NULL)
WHERE LON IS NOT NULL
AND LON BETWEEN -180 AND 180
AND LAT IS NOT NULL
AND LAT BETWEEN -90 AND 90 ;

Step 3:  -- Before creating a spatial index, we must insert a row of spatial metadata. Every user has an updatable view called USER_SDO_GEOM_METADATA for their spatial metadata.  This is a user view on a centralized table storing spatial metadata for the entire database instance. 

 INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'WAREHOUSES',  -- table name
  'GEOMETRY',    -- geometry column name
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', -180, 180, 0.005),  -- 'X',[min x],[max x],[tolerance]
    SDO_DIM_ELEMENT('Y', -90, 90, 0.005)),    -- 'Y',[min y],[max y],[tolerance]
   4326           -- indicates longitude/latitude coordinates (coordinate system)
 );

The tolerance value (0.005 here) represents the level of precision. For geodetic data such as the one we have here, the value is in meters. So 0.005m = 0.5 cm. Meaning if distance between points less than 0.5cm they are considered the same. For non geodetic data it represents the unit that is used.

Step 4: Create a spatial Index for performance

CREATE INDEX WAREHOUSES_SIDX ON
      WAREHOUSES (
          GEOMETRY
      )
          INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

If a spatial index creation statement fails (for example because a previous step was not done correctly), then before retrying you must drop the index since some index artifacts may have been created.
In our case before retrying you should run "DROP INDEX WAREHOUSES_SIDX;".

Please notice that as a result you might discover a table such as  MDRT_1BB0E$ created in the schema. Such tables are managed entirely by Spatial to support spatial indexes and should never be manually dropped. Database users should ignore them.

Data point Example with Function instead of Physical Geometry column

In Oracle DB we can decide to leave the longitude/latitude columns in the original state and use a function instead of physical Geometry column. Please note that such a solution might have performance implications.

Lets repeat the previous Data Points example with a function instead of physical Geometry column.

I will create a table warehouse_f

create table warehouses_F as
select Warehouse_name, Lat, Lon
from WAREHOUSES

and a Generic Function GET_GEOMETRY.

It accepts coordinates and returns a geometry (i.e., a SDO_GEOMETRY value).
The function includes criteria so that a result is only returned for valid input coordinates.

CREATE OR REPLACE FUNCTION GET_GEOMETRY (
      IN_LONGITUDE NUMBER,
      IN_LATITUDE  NUMBER
  ) RETURN SDO_GEOMETRY
      DETERMINISTIC PARALLEL_ENABLE
  IS
  BEGIN
   IF (IN_LONGITUDE IS NOT NULL
      AND IN_LONGITUDE BETWEEN -180 AND 180
      AND IN_LATITUDE IS NOT NULL
      AND IN_LATITUDE BETWEEN -90 AND 90)
   THEN
    RETURN
      SDO_GEOMETRY(
        2001,
        4326,
        SDO_POINT_TYPE(IN_LONGITUDE, IN_LATITUDE, NULL),
        NULL, NULL);
    ELSE RETURN NULL;
    END IF;
  END;
  /

If you want, you can test the function GET_GEOMETRY (SDO_UTIL.TO_GEOJSON function makes the spatial data readable):

 

If we return to the 4 steps of  setting Spatial data. We got 1& 2 covered.

1. Have you data in a table.
2. Set Spatial column.
3. Update the USER_SDO_GEOM_METADATA table about the new spatial data source.
4. Set Spatial Index.

Step 3: Before creating a spatial index, we must insert a row of spatial metadata. The schema/user I'm working here is name SPATIAL

 INSERT INTO USER_SDO_GEOM_METADATA VALUES (
 'WAREHOUSES_F',  -- table name
 'SPATIAL.GET_GEOMETRY(LON,LAT)', -- geometry function. Schema name (spatial) is critical
 SDO_DIM_ARRAY(
  SDO_DIM_ELEMENT('X', -180, 180, 0.005),
  SDO_DIM_ELEMENT('Y', -90, 90, 0.005)),
 4326  -- indicates longitude/latitude coordinates
);

Step 4: Create a spatial Index for performance. In the case of a function-based spatial index, the "column" being indexed is actually the call to the GET_GEOMETRY function.

CREATE INDEX WAREHOUSES_F_SIDX ON
  WAREHOUSES_F ( -- the table name
      GET_GEOMETRY(LON,LAT)  -- the function call instead of the column name
  )
      INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;