Build a geospatial platform on Oracle Autonomous Database

Use the spatial features of Oracle Autonomous Database to incorporate location intelligence into business processes and applications. Oracle enables you to bring location into the mainstream of your data management with features to load, prepare, process, analyze, and publish geospatial data, all within the context of your departmental and enterprise data stores.

This reference architecture positions the technology solution within the overall business context:

Description of the illustration data-driven-business-context.png

Virtually all business data contains location in some form. Since everything happens somewhere, business areas such as finance, supply chain, sales, marketing, customer service, all manage information tied to location. Location originates in forms such as place names, addresses, coordinates, and IP addresses. By turning that raw reference to location into a proper spatial data type with analytic capabilities, a wealth of location-based insights and value can be gained.

You can easily incorporate location and location relationships into business operations, answering questions such as:

• Do we have assets located in a flood zone?
• Where are our nearest competitors?
• Are health services located where they are needed the most?

Many organizations have historically managed geospatial data in specialized proprietary data structures, outside of their central enterprise data stores and accessible only through a specific toolset. With Oracle Autonomous Database, geospatial data like geocoded addresses and region shapes are 1st class data elements, accessed with standard SQL to perform native spatial operations such as proximity search and distance calculations.

Oracle Autonomous Database tools provide low code data preparation and application development that supports natively managed geospatial data. The ecosystem of specialized software (i.e., GIS software and developer toolkits) supports Oracle's spatial platform so you have the flexibility to mix and match other tools to best fit your organization’s needs. By maintaining geospatial data natively in Oracle Autonomous Database, a wide array of analytics and data warehousing, transaction processing, and mixed workload use cases are enabled, and efficiencies are gained while preserving existing investments in other tools and development.

In this reference architecture, we focus on the use case of adding location intelligence to your data mart or data warehouse through native spatial data management, self-service spatial analysis, and low-code application development. At a conceptual level, the technology solution addresses the problem as follows:

Description of the illustration oci-geospatial-adw-overview.png

Architecture

This architecture uses Oracle Autonomous Database for analytics and warehousing (ADW) where the location components of business data like place names, addresses, co-ordinates, and so on are converted to a native spatial data type and combined with geospatial reference data to enable location-based insights. The architecture also includes Oracle Spatial Studio and Oracle Application Express (APEX), both no-cost features of Oracle Autonomous Database, for low-code spatial data preparation, analysis, visualization, and application development.

The following diagram illustrates this reference architecture.

Description of the illustration oci-geospatial-adw-arch.png

oci-geospatial-adw-arch-oracle.zip

The architecture focuses on the following logical divisions:

• Data Refinery

  Ingests and refines data for use in each of the data layers in the architecture. The shape is intended to illustrate differences in processing costs for storing and refining data at each level and for moving data between them.

• Data Persistence Platform (curated information layer)
  Facilitates access and navigation of data to show the current business view.

  • Curated information: For relational technologies, data may be logically derived or physically structured in simple relational, longitudinal, dimensional or OLAP forms. For non-relational data, this layer contains one or more pools of data, either output from an analytical process or data optimized for a specific analytical task.
  • Raw information: Stored, granular data used as input to produce curated data. Data is stored in the format and schema derived from the source.
• Access and Interpretation

  Abstracts the logical business view of the data for consumers. This abstraction facilitates agile approaches for development, migration to the target architecture, and the provision of a single reporting layer from multiple federated sources.

The architecture has the following components:

• Data integration

  Oracle Autonomous Database for analytics and warehousing includes tools to acquire, load, and transform data for many scenarios.

  For advanced use cases, you can use Oracle Cloud Infrastructure Data Integration, a fully managed, serverless, native cloud service that helps you with common extract, load, and transform (ETL) tasks such as ingesting data from different sources, cleansing, transforming, and reshaping that data, and then efficiently loading it to target data sources on Oracle Cloud Infrastructure.

  Oracle Spatial Studio includes features to prepare data for spatial operations by geocoding addresses and place names, indexing coordinate columns, and enriching locations with the administrative areas that contain them. Oracle Spatial Studio also includes features to load spatial data to ADW directly from the common formats, such as spreadsheets, GeoJSON, Shapefiles, and CSV.

• Autonomous Data Warehouse

  Oracle Autonomous Data Warehouse is a self-driving, self-securing, self-repairing database service that is optimized for data warehousing workloads. You do not need to configure or manage any hardware, or install any software. Oracle Cloud Infrastructure handles creating the database, as well as backing up, patching, upgrading, and tuning the database.

• Oracle Spatial Studio

  Oracle Spatial Studio provides no-code (self-service) spatial analysis and map visualization directly from ADW. Business users and analysts can visualize their business data on interactive maps and uncover location relationships and patterns.

• Oracle APEX

  Oracle APEX on Oracle Autonomous Database provides a preconfigured, fully managed, and secured environment to develop and deploy applications. This includes built-in support for location-based reporting, charting, and map visualization of spatial data natively managed in ADW.

Recommendations

Use the following recommendations as a starting point to load and configure data from multiple sources to location-enable your data mart or data warehouse. Your requirements might differ from the architecture described here.

• Data Refinery

  Oracle Spatial Studio provides the address geocoding and coordinate indexing required to enable location information for spatial analysis and map visualization. Spatial Studio can prepare existing data in ADW, for example, tables having addresses or coordinates, and can also load from common file formats to ADW. For more flexibility in loading files to ADW, use the Autonomous Database Tools functionality embedded in Oracle Autonomous Database. For loading data from common spatial file formats such as spreadsheets, GeoJSON, shapefiles, KML and CSV, Oracle Spatial Studio loads directly to the spatial data type in Autonomous Database.

Considerations

When loading and configuring data from multiple databases and file sources into a centralized data warehouse enabled for location analysis, consider these implementation options.

Guidance	Data Refinery	Data Persistence Platform	Access & Interpretation
Recommended	Oracle Autonomous Database Tools Oracle Spatial Studio	Oracle Autonomous Database (ADW or ATP)	Oracle Spatial Studio Oracle APEX
Other Options	Oracle Cloud Infrastructure Data Integration 3rd Party	Oracle Autonomous Database - Dedicated Oracle Database Exadata Cloud Service	Oracle REST Data Services (ORDS) Oracle Analytics Cloud 3rd Party
Rationale	Oracle Autonomous Database includes tools for ingestion of a variety of Data Sources such as CSV files and relational data sources. Spatial Studio handles spatial-specific loading and preparation steps. A variety of 3rd party (commercial and open-source) also support loading of spatial data to ADW.	Oracle Autonomous Database is an easy-to-use, fully autonomous database that includes native support for spatial data management, processing, and analysis. ADW Dedicated and Oracle Database Exadata Cloud Service include the same spatial capabilities plus additional specialized spatial features in dedicated cloud databases.	Oracle Spatial Studio and APEX combine to provide location-based analysis and mapping through self-service, custom applications, and direct SQL. Location analysis in ADW may also be leveraged in Analytics Cloud and a variety of 3rd party (commercial and open-source) tools and toolkits.

Deploy

The code required to deploy this reference architecture is available in GitHub. You can pull the code into Oracle Cloud Infrastructure Resource Manager with a single click, create the stack, and deploy it. Alternatively, download the code from GitHub to your computer, customize the code, and deploy the architecture by using the Terraform CLI.

• Deploy using the sample stack in Oracle Cloud Infrastructure Resource Manager:
  1. Click

     If you aren't already signed in, enter the tenancy and user credentials.

  2. Select the region where you want to deploy the stack.
  3. Follow the on-screen prompts and instructions to create the stack.
  4. After creating the stack, click Terraform Actions, and select Plan.
  5. Wait for the job to be completed, and review the plan.

     To make any changes, return to the Stack Details page, click Edit Stack, and make the required changes. Then, run the Plan action again.

  6. If no further changes are necessary, return to the Stack Details page, click Terraform Actions, and select Apply.
• Deploy using the Terraform code in GitHub:
  1. Go to GitHub.
  2. Clone or download the repository to your local computer.
  3. Follow the instructions in the README document.

Explore More

Review these additional resources to learn more about the features of this architecture and start a guided workshop.

• Best practices framework for Oracle Cloud Infrastructure
• Load and analyze data in Autonomous Database Workshop
• Introduction to Oracle Spatial Workshop
• Introduction to Oracle Spatial Studio Workshop
• Advanced Low Code Development: Oracle APEX on ADB Workshop
• Get Started with Maps and Spatial in APEX Workshop

Acknowledgements

• Authors: David Lapp, Neelima Tadikonda, Jean Ihm, Jesus Vizcarra
• Contributor: Anupama Pundpal