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.
- Do we have assets located in a flood zone?
- Where are our nearest competitors?
- Are health services located where they are needed the most?
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
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.
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.
- 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
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.
- 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.
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|
||Oracle Autonomous Database (ADW or ATP)||
|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.|
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
If you aren't already signed in, enter the tenancy and user credentials.
- Select the region where you want to deploy the stack.
- Follow the on-screen prompts and instructions to create the stack.
- After creating the stack, click Terraform Actions, and select Plan.
- 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.
- 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:
- Go to GitHub.
- Clone or download the repository to your local computer.
- Follow the instructions in the
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