We are pleased to announce that Oracle Autonomous Data Warehouse now comes with spatial intelligence! If you are completely new to Oracle Autonomous Data Warehouse (where have you been for the last 18 months?) then here is a quick recap of the key features:
Oracle Autonomous Data Warehouse provides a self-driving, self-securing, self-repairing cloud service that eliminate the overhead and human errors associated with traditional database administration. Oracle Autonomous Data Warehouse 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.
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.
The core set of Spatial features have been enabled on Oracle Autonomous Data Warehouse. 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 Data Warehouse documentation.
In Oracle Autonomous Data Warehouse, 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:
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 Data Warehouse also supports loading from files stored in Oracle Cloud Infrastructure Object Storage and other cloud file stores. Details can be found here: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/load-data.html.
Routine Spatial data configuration is performed using Oracle SQL Developer GUIs or SQL commands for:
The Spatial features enabled for Oracle Autonomous Data Warehouse support the most common use cases in data warehouse contexts. Organizations such as insurance, finance, and public safety require data warehouses to perform a wide variety of analytics. These data warehouses provide the clues to answer questions such as:
In all of these data warehouse scenarios, location is an important factor, and the Spatial features of Oracle Autonomous Data Warehouse enable building and analyzing the dimensions of geographic data. Using the insurance scenario as an example, the major steps for location analysis are:
For example lets find the number of restaurants within 5 miles, and the distance to the nearest restaurant:
-- Count within distance
-- Use a SQL statement with SDO_WITHIN_DISTANCE
-- and DML to build the result data
SELECT policy_id, count(*) as no_restaurant_5_mi
FROM policies, businesses
WHERE businesses.type = 'RESTAURANT'
'distance=5 UNIT=mile') = 'TRUE'
GROUP BY policy_id;
Now we can expand the above query to use the SDO_NN function to do further analysis and find the closest restaurant within the group of restaurants that are within a mile radius of a specific location. Something like the following:
-- Distance to nearest
-- The SDO_NN function does not perform an implicit join
-- so use PL/SQL with DML to build the result data
FOR item IN (SELECT * FROM policies)
'SELECT sdo_nn_distance(1) FROM businesses '||
'WHERE businesses.type = ''RESTAURANT'' '||
'''sdo_batch_size=10 unit=mile'', 1) = ''TRUE'' '||
INTO distance_mi USING item.geometry;
-- Calculate containment
-- The SDO_ANYINTERACT function performs an implicit join
-- so, use a SQL statement with DML to build the result data
SELECT policy_id, zone
FROM policies, geo_hierarchy
WHERE SDO_ANYINTERACT(policies.geometry, geo_hierarchy.geometry) = 'TRUE';
With these and similar operations, analytics may be performed including the calculation of additional location-based metrics and aggregation by geography.
For important best practices and further details on the use of these and many other Spatial operations, please refer to the Oracle Autonomous Data Warehouse documentation: https://www.oracle.com/database/autonomous-database.html.