In today’s data-driven world, enriching location-based datasets with accurate geospatial intelligence is critical for analytics, decision-making, and AI-driven applications. This blog walks through a modern data architecture that leverages Oracle Data Transforms in Autonomous AI Lakehouse (via Data Studio) to process ATM location data from PostgreSQL and evolve it across Bronze, Silver, and Gold layers medallion architecture flows.

We start with a PostgreSQL source table containing ATM location data:

Source Table: ATM_LOCATIONS

  • ATM_ID
  • ATM_LOCATION
  • ATM_ADDRESS
  • BANK_ZONE/CITY
  • BANK_STATE
  • BANK_TYPE
  • PIN_CODE

This dataset contains raw address-level information but lacks precise geographic coordinates (latitude and longitude), which are essential for mapping, proximity analysis, and spatial intelligence.

Architecture Overview

The pipeline is built using:

  • Oracle Data Studio (Data Transforms) for ETL orchestration
  • Autonomous AI Lakehouse for storage and processing
  • A Medallion Architecture:
    • Bronze → Raw ingestion
    • Silver → Data enrichment (Geocoding)
    • Gold → Curated, analytics-ready data

Bronze Layer – Raw Data Ingestion

In the Bronze layer, data is ingested as-is from PostgreSQL using Oracle Data Transforms.

Key Characteristics:

  • No transformations applied
  • Preserves source fidelity
  • Acts as a historical/raw data store

Table: ATM_LOCATIONS_BRONZE

Silver Layer – Geocoding & Enrichment

The Silver layer enhances the raw data by deriving geographic coordinates using Oracle’s spatial capabilities.

🔍 Geocoding with SDO_GCDR.ELOC_GEOCODE

We use the built-in Oracle spatial function and below is the dataflow and backend code generated while ingesting data into SILVER layer:


Function/column mappings




INSERT
/*+ APPEND PARALLEL */
INTO SILVER.ATM_LOCATIONS_DT_LOAD_SILVER
(
HOUSENUMBER ,
STREET ,
SETTLEMENT ,
REGION ,
POSTALCODE ,
COUNTRY ,
X ,
Y ,
MATCHCODE ,
ATM_ID ,
ATM_LOCATION ,
ATM_ADDRESS ,
PINCODE ,
BANK_ZONE ,
BANK_STATE ,
BANK_TYPE
)
SELECT
json_value(g,'$[0].matches[0].houseNumber') ,
json_value(g,'$[0].matches[0].street') ,
json_value(g,'$[0].matches[0].settlement') ,
json_value(g,'$[0].matches[0].region') ,
json_value(g,'$[0].matches[0].postalCode') ,
json_value(g,'$[0].matches[0].country') ,
json_value(g,'$[0].matches[0].x') ,
json_value(g,'$[0].matches[0].y') ,
json_value(g,'$[0].matches[0].matchCode') ,
ATM_ID ,
ATM_LOCATIONS ,
ATM_ADDRESS ,
ATM_PINCODE ,
BANK_ZONE ,
BANK_STATE ,
BANK_TYPE
FROM
(
SELECT
SDO_GCDR.ELOC_GEOCODE(ATM_LOCATIONS_LOAD_BRONZE1.ATM_ADDRESS, ATM_LOCATIONS_LOAD_BRONZE1.BANK_ZONE, ATM_LOCATIONS_LOAD_BRONZE1.BANK_STATE, ATM_LOCATIONS_LOAD_BRONZE1.ATM_PINCODE, 'IN') AS g ,
ATM_LOCATIONS_LOAD_BRONZE1.ATM_ID AS ATM_ID ,
ATM_LOCATIONS_LOAD_BRONZE1.ATM_LOCATIONS AS ATM_LOCATIONS ,
ATM_LOCATIONS_LOAD_BRONZE1.ATM_ADDRESS AS ATM_ADDRESS ,
ATM_LOCATIONS_LOAD_BRONZE1.ATM_PINCODE AS ATM_PINCODE ,
ATM_LOCATIONS_LOAD_BRONZE1.BANK_ZONE AS BANK_ZONE ,
ATM_LOCATIONS_LOAD_BRONZE1.BANK_STATE AS BANK_STATE ,
ATM_LOCATIONS_LOAD_BRONZE1.BANK_TYPE AS BANK_TYPE
FROM
BRONZE.ATM_LOCATIONS_LOAD_BRONZE ATM_LOCATIONS_LOAD_BRONZE1
) GEOCODECLOUD

Extracting Latitude & Longitude

From the GEO_RESULT, we extract:

  • X → Longitude
  • Y → Latitude

Enhancements:

  • Standardized address data
  • Latitude (Y)
  • Longitude (X)
  • Improved data quality

Gold Layer – Curated & Analytics-Ready

The Gold layer provides a clean, enriched dataset ready for consumption by BI tools, dashboards, and AI/ML workloads.

Final Dataset Includes:

  • ATM_ID
  • ATM_LOCATION
  • ATM_ADDRESS
  • BANK_ZONE
  • BANK_TYPE
  • BANK_STATE
  • ATM_PINCODE
  • ATM_PIN_CODE
  • LATITUDE (Y)
  • LONGITUDE (X)

Table: ATM_LOCATIONS_GOLD

Benefits:

  • Fully geocoded dataset
  • Ready for mapping & spatial analytics
  • Optimized for reporting and AI use cases

🔄 End-to-End Data Flow

  1. Extract: Pull ATM location data from PostgreSQL
  2. Load to Bronze: Store raw data without transformation
  3. Transform in Silver:
    • Apply SDO_GCDR.ELOC_GEOCODE
    • Extract LAT/LONG
  4. Load to Gold:
    • Combine original attributes + geospatial data
    • Deliver business-ready dataset

With the data cleansed and enriched through spatial geocoding, the Gold layer becomes a high-quality, analytics-ready dataset that can be seamlessly consumed by downstream application layers.

One compelling implementation of this is in Oracle APEX, where we have developed an ATM Locator application. This application leverages the geocoded latitude and longitude information to enable users to efficiently identify nearby ATMs within a 5 km radius.

This use case highlights the practical value of integrating geospatial intelligence into modern data platforms, transforming raw location data into actionable insights and delivering meaningful, location-aware user experiences. Below is the screenshot of the APEX application


This architecture showcases how raw location data can be transformed into actionable, location-aware insights using Oracle Autonomous AI Lakehouse and Data Transforms. By enriching data with geospatial intelligence, simple address data is converted into precise latitude and longitude coordinates for advanced analytics.

With a structured flow from Bronze to Silver to Gold, the approach ensures scalability, data quality, and readiness for consumption. Applications like the ATM Locator in Oracle APEX further demonstrate its real-world value by enabling proximity-based search, effectively connecting data engineering with business impact.

For a detailed, hands-on walkthrough of implementing spatial geocoding using Oracle Data Transforms end-to-end, explore this video on Oracle Data Transforms from the Oracle Developers channel:
👉 Watch the full tutorial on YouTube