Oracle ADB 23ai

Chicago Restaurant Violations · Spatial Heat Map Demo

SDO_GCDR.ELOC_GEOCODE_AS_GEOM SDO_UTIL.GETVERTICES SRID 4326 · ORDS
▶ Oracle Spatial Query
WHERE SDO_WITHIN_DISTANCE(
  location, :point,
  'distance=1000 unit=meter'
) = 'TRUE'
Oracle ADB — source table
-- The location column is NULL until geocoded

CREATE TABLE restaurant_inspections (
    restaurant_name VARCHAR2(200),
    address         VARCHAR2(300),
    city            VARCHAR2(100),
    state           VARCHAR2(2),
    zip_code        VARCHAR2(10),
    violation_count NUMBER(3),
    location  MDSYS.SDO_GEOMETRY  -- ← spatial column
);

-- Initial state after INSERT:
SELECT restaurant_name, address, location
FROM   restaurant_inspections;

-- RESTAURANT_NAME    ADDRESS              LOCATION
-- Au Cheval          800 W Randolph St    (null)
-- Green Street       112 N Green St       (null)
-- Alinea             1723 N Halsted St    (null)
The full pipeline
🏢
Raw Address Strings
"800 W Randolph St, Chicago IL"
SDO_GCDR package → ELOC geocoder
📍
SDO_GEOMETRY Point
SDO_POINT_TYPE(-87.646, 41.884, NULL)
SDO_UTIL.GETVERTICES → v.x, v.y
📊
Longitude + Latitude
v.x = -87.646 · v.y = 41.884
ORDS REST → browser JSON
🗺
Leaflet Heat Map
L.heatLayer([lat, lng, intensity])
Where we start

The location column (MDSYS.SDO_GEOMETRY) is NULL until we geocode. This tutorial walks every step from raw address text to interactive heat map — using real Oracle ADB coordinates throughout.

Oracle SQL — SDO_GCDR package
-- SDO_GCDR is an Oracle PL/SQL PACKAGE.
-- It is NOT a REST API — you call it in SQL.
-- On ADB it uses Oracle ELOC (HERE Maps)
-- internally. No API key needed from you.

-- Single address — JSON form (ADB 23ai):
SELECT SDO_GCDR.ELOC_GEOCODE_AS_GEOM(
  JSON_OBJECT('address' VALUE
    '800 W Randolph St, Chicago IL')
) AS location FROM dual;

-- Returns: SDO_GEOMETRY(2001, 4326,
--   SDO_POINT_TYPE(-87.64662, 41.88427, NULL),
--   NULL, NULL)

-- Update all rows in one statement:
UPDATE restaurant_inspections
SET location = SDO_GCDR.ELOC_GEOCODE_AS_GEOM(
    address, city, state, zip_code, 'US'
)
WHERE location IS NULL;
-- 20 rows updated. All geocoded.
SDO_GEOMETRY anatomy
SDO_GEOMETRY(
  2001,             -- 2D Point
  4326,             -- WGS84 SRID
  SDO_POINT_TYPE(
    -87.64662,   -- X = longitude
     41.88427,   -- Y = latitude
    NULL         -- Z = elevation
  ), NULL, NULL
)
⚠ SDO_GCDR is a package, not an API
It runs inside the database as a SQL function call. Your code never makes an HTTP call to a geocoding service — Oracle handles that internally via ELOC.
✓ Included with Oracle ADB
SDO_GCDR package → built in
ELOC / HERE geocoder → included
No extra cost or license
A package, not an API

SDO_GCDR.ELOC_GEOCODE_AS_GEOM() is called in a SQL UPDATE statement — no HTTP request from your code, no API key to manage. The result is stored directly as SDO_GEOMETRY. Oracle calls HERE Maps internally as part of the ELOC service included with ADB.

Oracle SQL — GETVERTICES
-- TABLE(SDO_UTIL.GETVERTICES(geom)) is a
-- lateral join. For a Point: 1 row.
-- For a Polygon: N rows (one per vertex).

-- Single address:
SELECT v.x AS longitude,
       v.y AS latitude
FROM (
  SELECT SDO_GCDR.ELOC_GEOCODE_AS_GEOM(
    JSON_OBJECT('address' VALUE
      '800 W Randolph St, Chicago IL')
  ) AS geom FROM dual
) g,
TABLE(SDO_UTIL.GETVERTICES(g.geom)) v;

-- Result: -87.64662   41.88427

-- From the full table:
SELECT r.restaurant_name,
       v.x AS longitude,
       v.y AS latitude,
       r.violation_count
FROM  restaurant_inspections r,
TABLE(SDO_UTIL.GETVERTICES(r.location)) v
WHERE r.location IS NOT NULL;
Live ADB coordinates
Restaurant v.x (lng) v.y (lat) Viol.
⚠ Coordinate order swap
Oracle:  v.x = longitude, v.y = latitude
Leaflet: L.marker([latitude, longitude])
→ Always: L.marker([v.y, v.x])
GETVERTICES — the preferred extraction method

TABLE(SDO_UTIL.GETVERTICES(geom)) works identically for Points (1 row), LineStrings, and Polygons (N rows). The coordinate swap — [v.y, v.x] for Leaflet — is the single most common mistake when connecting Oracle Spatial to web maps.

ORDS REST — live Oracle ADB endpoint
-- ORDS is built into every ADB.
-- One SQL block exposes any query as REST:

-- AutoREST: same approach used by the CDC dashboard.
-- One call per object — no module/template/handler needed.
-- CORS is handled automatically by Oracle ADB.

-- Run once as ADMIN or the schema user:
BEGIN
  ORDS.ENABLE_OBJECT(
    p_enabled      => TRUE,
    p_schema       => 'SDO',
    p_object       => 'RESTAURANT_GEOJSON_V',
    p_object_type  => 'VIEW',
    p_object_alias => 'violations'
  );
  COMMIT;
END;

-- That's it! The URL is now live:
-- /ords/sdo/violations/
-- Returns: { "items": [...], "hasMore": false, "count": 20 }

-- JavaScript fetch — no mode, no proxy, no headers needed:
const res  = await fetch('https://...oraclecloudapps.com/ords/sdo/violations/');
const rows = (await res.json()).items;
Try it — fetch live from Oracle ADB

Click to make a real HTTP GET to your Oracle ADB instance in OCI Phoenix. Data comes directly from SDO.RESTAURANT_GEOJSON_V.

GET https://bk8uwrvkgqzvi2h-vbjson.adb.us-phoenix-1.oraclecloudapps.com/ords/sdo/violations/
Auto-REST · no manual module · same pattern as CDC project
ORDS — zero-infrastructure REST

No separate server, no config file, no deployment pipeline. ORDS.DEFINE_HANDLER() turns any SQL query into a REST endpoint. The response is standard JSON wrapped in {"items":[...]}. CORS is enabled with ORDS.SET_MODULE_ORIGINS_ALLOWED('heatmap','*') — already done in your ADB.

JavaScript — L.map() + L.tileLayer()
// Include in <head>:
<link rel="stylesheet" href="leaflet.css"/>
<script src="leaflet.js"></script>
<style> #map { height: 500px; } </style>
<div id="map"></div>

// Initialize map — [lat, lng] ← lat FIRST
const map = L.map('map')
  .setView([41.9003, -87.6534], 13);

// [41.9003, -87.6534] = [v.y, v.x] from Oracle

// Tile layer — free, no API key
L.tileLayer(
  'https://{s}.basemaps.cartocdn.com/light_all/{z}/{x}/{y}{r}.png',
  { attribution: '© CartoDB', maxZoom: 19 }
).addTo(map);

// {z}/{x}/{y} = zoom/col/row tiles
// Leaflet calculates these automatically
Live map — tiles loaded
L.map() — lat first, always

Leaflet takes [latitude, longitude] — opposite to Oracle's SDO_POINT_TYPE(longitude, latitude). So setView([v.y, v.x]). The map div must have an explicit CSS height — a block element defaults to height:0 and Leaflet renders nothing.

JavaScript — L.circleMarker()
// rows = (await fetch(ORDS_URL)).json().items
// Each row: { latitude, longitude, violation_count }

function color(v) {
  return v>=7 ? '#dc2626'  // red
       : v>=5 ? '#d97706'  // amber
       : v>=3 ? '#059669'  // green
       :         '#2563eb'; // blue
}

rows.forEach(r => {
  L.circleMarker(
    [r.latitude, r.longitude], // [v.y, v.x]!
    { radius:      4 + r.violation_count * 1.2,
      fillColor:   color(r.violation_count),
      color:       'white',
      weight:      1.5,
      fillOpacity: 0.85 }
  )
  .bindPopup(`<b>${r.restaurant_name}</b>
    <br/>${r.violation_count} violations`)
  .addTo(map);
});
Circle markers — click any point
circleMarker — pixel radius, constant at any zoom

L.circleMarker() radius is in pixels — stays constant as you zoom, ideal for data viz. L.circle() radius is in metres — scales with zoom, ideal for showing a spatial query radius like SDO_WITHIN_DISTANCE. Notice the ORDS response already has latitude and longitude as column names from the view's GETVERTICES extraction.

Oracle SQL — heat_intensity analytic
-- Oracle computes 0.0–1.0 intensity in the VIEW.
-- No JavaScript math needed.

-- Inside restaurant_geojson_v:
ROUND(
  r.violation_count /
  NULLIF(MAX(r.violation_count) OVER(), 0),
  4
) AS heat_intensity

-- OVER() = window = entire result set
-- MAX(violation_count) OVER() = 9

-- Results from your ADB:
-- Au Cheval       9  → 1.0000
-- Green Street    8  → 0.8889
-- Kumas Corner    7  → 0.7778
-- Blackbird       6  → 0.6667
-- ...
-- Alinea          0  → 0.0000

-- JavaScript just reads the column:
rows.map(r => [r.latitude, r.longitude, r.heat_intensity])
heat_intensity — all 20 rows
Restaurant Violations Intensity Bar
Compute in Oracle, not JavaScript

The analytic expression MAX(violation_count) OVER () runs in one SQL pass across the full result set. The normalised value arrives in every ORDS row — JavaScript reads r.heat_intensity directly with zero extra computation.

JavaScript — complete pipeline
const ORDS = 'https://bk8uwrvkgqzvi2h-vbjson.adb'+
             '.us-phoenix-1.oraclecloudapps.com'+
             '/ords/sdo/heatmap/violations';

async function buildMap() {

  // 1. Fetch live from Oracle ORDS
  const rows = (await (await fetch(ORDS)).json()).items;

  // 2. Leaflet map
  const map = L.map('map').setView([41.9,-87.65],13);
  L.tileLayer('https://{s}.basemaps.cartocdn.com/light_all/{z}/{x}/{y}{r}.png',
    {attribution:'© CartoDB'}).addTo(map);

  // 3. Heat layer — Oracle intensity direct
  L.heatLayer(
    rows.map(r=>[r.latitude, r.longitude, r.heat_intensity]),
    { radius:35, blur:20, max:1.0,
      gradient:{0:'#3b82f6',0.5:'#f59e0b',1:'#ef4444'} }
  ).addTo(map);

  // 4. Circle markers
  rows.forEach(r =>
    L.circleMarker([r.latitude, r.longitude],
      {radius:4+r.violation_count*1.1,
       fillColor:r.violation_count>6?'#dc2626':'#2563eb',
       color:'white',weight:1.5,fillOpacity:0.85})
    .bindPopup(`<b>${r.restaurant_name}</b><br/>${r.violation_count} violations`)
    .addTo(map)
  );
}
buildMap();
Complete heat map — click any marker
The whole pipeline in 20 lines

Oracle geocoded the addresses → stored SDO_GEOMETRY → GETVERTICES extracted coordinates → the view normalised intensity with MAX() OVER() → ORDS served JSON → JavaScript mapped [r.latitude, r.longitude, r.heat_intensity] into L.heatLayer(). Every hard part happened in Oracle SQL.

ORACLE SPATIAL March 2026 · Oracle ADB 23ai

From Address to Heat Map:
Oracle ADB Now Geocodes Inside the Database

Since 2023, Oracle Autonomous Database 23ai includes a built-in geocoder. One SQL call converts a plain text address into a precise geographic coordinate stored as MDSYS.SDO_GEOMETRY — no external API, no key management, no extra cost. This post shows the complete pipeline from that single SQL call to a live interactive heat map.

📍
The Geocoder — One SQL Call, Full Coordinates
SDO_GCDR.ELOC_GEOCODE_AS_GEOM() · Available on Oracle ADB 23ai · Powered by HERE Maps

Before 2023, geocoding in Oracle required a separately licensed on-premises geocoder or an external API call from your application. Since ADB 23ai, it is a single SQL function call that runs entirely inside the database. Oracle handles the call to HERE Maps internally — your code never touches an HTTP endpoint.

-- Convert any address to longitude + latitude in one statement SELECT v.x AS longitude, v.y AS latitude FROM ( SELECT SDO_GCDR.ELOC_GEOCODE_AS_GEOM( JSON_OBJECT('address' VALUE '1600 Amphitheatre Parkway, Mountain View, CA') ) AS geom FROM dual ) g, TABLE(SDO_UTIL.GETVERTICES(g.geom)) v; -- Result: LONGITUDE=-122.0839 LATITUDE=37.42305 -- Stored as: SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-122.0839, 37.42305, NULL))

The result is an SDO_GEOMETRY point with SRID 4326 (WGS84 — the same coordinate system used by GPS, Google Maps, and Leaflet). SDO_UTIL.GETVERTICES() is the lateral join that unpacks it: v.x = longitude, v.y = latitude. It works identically for Points, Lines, and Polygons — making it the preferred extraction method.

⚠ Important: SDO_GCDR is a PL/SQL package, not a REST API.

You call it in a SQL UPDATE or SELECT statement. There is no HTTP call from your application code, no API key to rotate, and no per-call billing beyond your ADB subscription.

🗺
From SDO_GEOMETRY to a Web Map
Oracle Spatial Studio · Leaflet.js · Any map provider

Once your addresses are geocoded into SDO_GEOMETRY, you have two good options for visualisation:

A
Oracle Spatial Studio — a no-code web tool built into ADB. Drag columns onto a map, run spatial analyses, publish results. No JavaScript needed. Deploy from the OCI Marketplace in minutes. Best for analysts and data exploration.
B
Any standard map library — Leaflet.js, Mapbox GL, OpenLayers, Google Maps. Use SDO_UTIL.TO_GEOJSON() to convert SDO_GEOMETRY to standard GeoJSON. Export via ORDS. Render in any web application. Full control over styling, interactivity, and layout.

This demo uses Leaflet.js with the leaflet-heat plugin — completely free, no API key, works offline. The heat map intensity is computed directly in Oracle SQL using an analytic window function (MAX(violation_count) OVER ()) so JavaScript receives pre-normalised 0.0–1.0 values and maps them directly to colours.

🔌
Live Data via ORDS — Built Into Every ADB
Auto-REST · No server · No config · CORS automatic

Oracle REST Data Services (ORDS) is built into every ADB instance. Five lines of SQL turns any table or view into a live REST endpoint that any browser can fetch — including full CORS support out of the box.

-- Enable Auto-REST on a view — creates GET /ords/sdo/violations/ BEGIN ORDS.ENABLE_OBJECT( p_enabled => TRUE, p_object => 'RESTAURANT_GEOJSON_V', p_object_type => 'VIEW', p_object_alias => 'violations', p_auto_rest_auth => FALSE -- TRUE + OAuth2 for production ); COMMIT; END; / -- Live URL (works in any browser, no CORS issues): -- GET https://YOUR-ADB.adb.us-phoenix-1.oraclecloudapps.com/ords/sdo/violations/ -- Returns: { "items": [ { "restaurant_name": ..., "latitude": ..., ... } ] }

The p_auto_rest_auth => FALSE flag makes the endpoint public — no login needed. The ORDS response wraps rows in {"items":[...]} and includes full pagination. The HTML file fetches it with a single await fetch(url) call.

🔒 Production: OAuth2 in two steps

Set p_auto_rest_auth => TRUE, then go to Database Actions → REST → Security → OAuth Clients. Create a client, issue a client_credentials grant, and pass the token as Authorization: Bearer <token> in your fetch call. No external identity provider required — Oracle ADB handles it natively.

🚀
Hosting — From Laptop to Production
python -m http.server · Oracle APEX · OCI Object Storage

The complete demo is a single self-contained HTML file. It embeds all the JavaScript, CSS, and Oracle data. It fetches live data from ORDS. It requires no build step, no npm, no framework. Here are three ways to serve it:

1
Development — Python HTTP server
python -m http.server 8502
Copy the HTML file to a directory, run this command, open http://YOUR_IP:8502/chicago_heatmap.html. No proxy needed — the HTML fetches ORDS directly. This is how this demo runs today.
2
Production — Oracle APEX (zero extra infrastructure)
APEX is built into every ADB. Upload the HTML file as a Static Application File (App Builder → Shared Components → Static Files). Create a blank APEX page and embed it via iframe or directly in an HTML region. Since APEX and ORDS run on the same ADB instance, there are no CORS issues at all — perfectly same-origin. Your entire spatial application lives inside Oracle with no external servers.
3
Scalable — OCI Object Storage static website
Upload to a public OCI Object Storage bucket with static website hosting enabled. Optionally add OCI CDN or Cloudflare in front. The HTML file is served as a static asset globally; all dynamic data still comes from ORDS on your ADB.
✨
The Complete Picture — What's in This Demo
A fully working spatial application — all Oracle, all free, all included with ADB

Everything in this demo is running live on Oracle ADB 23ai. The full stack, and the cost of each component:

📦 Database Layer
SDO_GCDR geocoder    → included
MDSYS.SDO_GEOMETRY  → included
SPATIAL_INDEX_V2   → included
SDO_UTIL.GETVERTICES→ included
SDO_UTIL.TO_GEOJSON →included
🌐 API Layer
ORDS Auto-REST     → included
CORS headers       → automatic
Pagination         → automatic
OAuth2 security    → included
JSON output        → automatic
🗺 Map Layer
Leaflet.js         → open source
leaflet-heat       → open source
CartoDB tiles      → free tier
No map API key     → needed
🖥 Hosting
python -m http.server → built-in
Oracle APEX        → included
OCI Object Storage→ pay per GB
No Node.js         → needed
No npm             → needed
📂 Everything is on GitHub

The complete setup SQL and this HTML file are available as a single GitHub repository. Clone, update the ADB hostname, run the SQL script, serve the HTML — fully working spatial heat map in under 15 minutes on any Oracle ADB 23ai instance.

oracle_spatial_heatmap_complete.sql  ← complete setup script
chicago_heatmap.html               ← self-contained heat map app
🤖
How AI Accelerates This Workflow
Claude AI + Oracle SQLcl MCP — built this entire demo in a single conversation

This entire demo — the database setup, the geocoding, the ORDS endpoint, the HTML heat map, all three tabs, the SQL reference script — was built through a conversation with Claude AI connected directly to the Oracle ADB via SQLcl MCP. AI ran every SQL statement live, read the results, corrected errors, and iterated until everything worked.

1
SQL generation — describe what you want in plain English, AI writes the spatial SQL, creates the index, sets up ORDS. No documentation lookup.
2
Live execution — AI runs the SQL on your actual database, reads the results, and fixes errors immediately. The geocoding, the view, the ORDS endpoint — all verified live.
3
HTML generation — AI wrote the complete Leaflet heat map application, all CSS, all JavaScript, the tab structure, the tutorial steps, and this blog post — all in one session.
4
Spatial analysis — ask AI to interpret the map. It identified the Fulton Market / West Loop cluster (Au Cheval, Green Street, Blackbird — within 300m, 23 combined violations) and explained the inspection risk. Analysis that takes hours, done in seconds.

The combination of Oracle ADB 23ai spatial capabilities + AI code generation + ORDS Auto-REST means a developer can go from a table of addresses to a fully deployed interactive heat map in a single working session — without reading a single page of documentation.