X

All Things Database: Education, Best Practices,
Use Cases & More

Autonomous Database - Now with Spatial Intelligence

Keith Laker
Senior Principal Product Manager

We are pleased to announce that Oracle Autonomous Database now comes with spatial intelligence! If you are completely new to Oracle Autonomous Database then firstly: where have you been for the last 18 months?, secondly: here is a quick recap of the key features:

What is  Oracle Autonomous Database

Oracle Autonomous Database provides a self-driving, self-securing, self-repairing cloud service that eliminate the overhead and human errors associated with traditional database administration. Oracle Autonomous Database takes care of configuration, tuning, backup, patching, encryption, scaling, and more. Additional information can be found at https://www.oracle.com/database/autonomous-database.html.

Special Thanks...

This post has been prepared by David Lapp who is part of the Oracle Spatial and Graph product management team.He is extremely well known within our spatial and graph community. If you want to follow David's posts on the Spatial and Graph blog then use this link and the spatial and graph blog is here.

Spatial Features

The core set of Spatial features have been enabled on Oracle Autonomous Database.  Highlights of the enabled features are; native storage and indexing of point/line/polygon geometries, spatial analysis and processing, such as proximity, containment, combining geometries, distance/area calculations, geofencing to monitor objects entering and exiting areas of interest, and linear referencing to analyze events and activities located along linear networks such as roads and utilities. For details on enabled Spatial features, please see the Oracle Autonomous Database. documentation.

 

Loading Your Spatial Data into Autonomous Database

In Oracle Autonomous Database, data loading is typically performed using either Oracle Data Pump or Oracle/3rd party data integration tools. There are a few different ways to load and configure your spatial data sets:

  • Load existing spatial data
  • Load GeoJSON, WKT, or WKB and convert to Spatial using SQL. 
  • Load coordinates and convert to Spatial using SQL. 

Obviously the files containing your spatial data sets can be located in your on-premise data center or maybe your desktop computer, but for the fastest data loading performance Oracle Autonomous Database also supports loading from files stored in Oracle Cloud Infrastructure Object Storage and other cloud file stores. Details can be found here for ATP: https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/load-data.html and here if you are using ADW: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/load-data.html.

Configuring Your Spatial Data

Routine Spatial data configuration is performed using Oracle SQL Developer GUIs or SQL commands for:

  • Insertion of Spatial metadata
  • Creation of Spatial index
  • Validation of Spatial data

 

Example Use Case for ATP

For the purposes of this post lets focus on an ATP-style use case. OLTP applications commonly require calculations that are invoked upon changes to data to support business functions and enforce business rules. For example:

  • Permitting system transactions require validation that the activity complies with regulations
  • Financial transactions require checks against known pattern of fraud
  • Service transactions require determination of optimal resources

In these and many other transaction processing scenarios, location plays an important role and are supported by the Spatial features of Oracle Autonomous Transaction Processing. Using the permitting scenario as an example, the major steps for location-based transaction validations are:

  • Load geospatial reference data for regulation enforcement such as environmentally sensitive areas, school zones, redevelopment zones.
  • In the permit transaction process, capture the proposed activity locations
  • Use Spatial to perform location-based validations of proposed activities, for example the proximity of an activity involving hazardous materials to environmentally sensitive areas:

-- Proximity for validation
--
-- Use a SQL statement with SDO_WITHIN_DISTANCE
-- and geometry constructor to validate the proximity rule
--
SELECT DECODE(count(*), 0, 'PASSED', FAILED') as location_validation
FROM environmental_sensitive_areas
WHERE SDO_WITHIN_DISTANCE(
geometry,
sdo_geometry(2001,4326,sdo_point_type(permit_longitude,permit_latitude,null),null,null),
'distance=5 unit=MILE') = 'TRUE';

  • Use Spatial to determine notification requirements for a permitted activity. For example, notify public safety jurisdictions within the proximity of proposed hazardous materials transport routes:


-- Proximity for notification
--
-- Use a SQL statement with SDO_WITHIN_DISTANCE
-- and route geometries to determine notifications
--
SELECT hazmat_route, jurisdiction
FROM jurisdictions, hazmat_routes
WHERE SDO_WITHIN_DISTANCE(
jurisdictions.geometry,
hazmat_routes.geometry,
'distance=5 unit=MILE') = 'TRUE';

 

ROUTE       JURISDICTION
2017-03-A AL_REGION_7
2017-03-A AL_REGION_9
2017-03-B AL_REGION_9
2017-04-A AL_REGION_2
2017-04-B AL_REGION_3
2017-04-B AL_REGION_9
...

These and any other location-based transactions may be operationalized as triggers and procedures invoking more involved business logic. As a fully integrated feature of Oracle Autonomous Transaction Processing, location-based operations can be seamlessly blended with the mainstream aspects of transaction processing logic.

What about data warehouse use cases?

If you are interested in spatial use cases relating to data warehousing projects then click over to this blog post on the Data Warehouse Insider blog: Autonomous Data Warehouse - Now With Spatial Intelligence

 

Summary

For important best practices and further details on the use of these and many other Spatial operations, please refer to Oracle Autonomous Transaction Processing documentation and the Autonomous Data Warehousing documentation.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.