-- Oracle 21c Geospatial Queries in SQL - Jewelry industry 8-domain (OA/ERP/CRM/HRM/MES/SCM/WMS/KMS)
-- Demonstrates geometry/geography types, distance, spatial relationships, spatial indexes.
-- Assumes Oracle Spatial is enabled (SPATIAL_OPTION=TRUE). Run as JEWELRY user on PDB technologygamepdb.
-- All object names start with a letter (Oracle unquoted identifiers cannot start with underscore).
-- Comments/PROMPT are ASCII; Chinese (if any) only inside string literals.
SET ECHO OFF FEED OFF PAGES 0 SERVEROUT OFF
WHENEVER SQLERROR CONTINUE
-- =====================================================================
-- 1. Idempotent cleanup: drop only GEO_ / GVAL_ objects (CASCADE CONSTRAINTS)
-- =====================================================================
BEGIN
FOR r IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'GEO_%' OR table_name LIKE 'GVAL_%') LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||r.table_name||' CASCADE CONSTRAINTS PURGE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
BEGIN
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name='GEO_SITE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END;
/
-- =====================================================================
-- 2. Dimension tables: 8 domains, 10 currencies, 12 regions (with tz + cur + hub coord)
-- =====================================================================
CREATE TABLE geo_domain (
domain_id NUMBER PRIMARY KEY,
code VARCHAR2(10) UNIQUE,
name VARCHAR2(40)
);
INSERT INTO geo_domain VALUES (1,'OA','Office Automation');
INSERT INTO geo_domain VALUES (2,'ERP','Enterprise Resource Planning');
INSERT INTO geo_domain VALUES (3,'CRM','Customer Relationship');
INSERT INTO geo_domain VALUES (4,'HRM','Human Resources');
INSERT INTO geo_domain VALUES (5,'MES','Manufacturing Execution');
INSERT INTO geo_domain VALUES (6,'SCM','Supply Chain');
INSERT INTO geo_domain VALUES (7,'WMS','Warehouse Management');
INSERT INTO geo_domain VALUES (8,'KMS','Knowledge Management');
CREATE TABLE geo_currency (
cur_code VARCHAR2(10) PRIMARY KEY,
name VARCHAR2(40),
rate_to_usd NUMBER(12,6)
);
INSERT INTO geo_currency VALUES ('USD','US Dollar',1.0);
INSERT INTO geo_currency VALUES ('CNY','Chinese Yuan',0.14);
INSERT INTO geo_currency VALUES ('JPY','Japanese Yen',0.0067);
INSERT INTO geo_currency VALUES ('SGD','Singapore Dollar',0.74);
INSERT INTO geo_currency VALUES ('INR','Indian Rupee',0.012);
INSERT INTO geo_currency VALUES ('GBP','British Pound',1.27);
INSERT INTO geo_currency VALUES ('EUR','Euro',1.08);
INSERT INTO geo_currency VALUES ('AED','UAE Dirham',0.27);
INSERT INTO geo_currency VALUES ('AUD','Australian Dollar',0.66);
INSERT INTO geo_currency VALUES ('BRL','Brazil Real',0.18);
CREATE TABLE geo_region (
region_id NUMBER PRIMARY KEY,
region_name VARCHAR2(40),
tz_name VARCHAR2(40),
cur_code VARCHAR2(10),
hub_lon NUMBER,
hub_lat NUMBER,
CONSTRAINT fk_geo_region_cur FOREIGN KEY(cur_code) REFERENCES geo_currency(cur_code)
);
INSERT INTO geo_region VALUES (1,'Greater China','Asia/Hong_Kong','CNY',114.17,22.32);
INSERT INTO geo_region VALUES (2,'China North','Asia/Shanghai','CNY',116.40,39.90);
INSERT INTO geo_region VALUES (3,'Japan','Asia/Tokyo','JPY',139.69,35.69);
INSERT INTO geo_region VALUES (4,'Southeast Asia','Asia/Singapore','SGD',103.80,1.35);
INSERT INTO geo_region VALUES (5,'India','Asia/Kolkata','INR',72.87,19.07);
INSERT INTO geo_region VALUES (6,'UK','Europe/London','GBP',-0.13,51.51);
INSERT INTO geo_region VALUES (7,'Eurozone','Europe/Paris','EUR',2.35,48.85);
INSERT INTO geo_region VALUES (8,'Middle East','Asia/Dubai','AED',55.27,25.20);
INSERT INTO geo_region VALUES (9,'North America E','America/New_York','USD',-74.00,40.71);
INSERT INTO geo_region VALUES (10,'North America W','America/Los_Angeles','USD',-118.24,34.05);
INSERT INTO geo_region VALUES (11,'Australia','Australia/Sydney','AUD',151.21,-33.87);
INSERT INTO geo_region VALUES (12,'South America','America/Sao_Paulo','BRL',-46.63,-23.55);
-- =====================================================================
-- 3. Main table geo_site: 6000 rows, SDO_GEOMETRY point (geography, SRID 4326)
-- + structured columns for high-concurrency filtering + tz + multi-currency
-- =====================================================================
CREATE TABLE geo_site (
site_id NUMBER PRIMARY KEY,
system_code VARCHAR2(10),
site_type VARCHAR2(20),
region_id NUMBER,
site_name VARCHAR2(60),
loc SDO_GEOMETRY,
cur_code VARCHAR2(10),
amount_orig NUMBER(12,2),
amount_usd NUMBER(12,2),
created_utc TIMESTAMP WITH TIME ZONE,
local_day DATE,
CONSTRAINT fk_geo_site_domain FOREIGN KEY(system_code) REFERENCES geo_domain(code),
CONSTRAINT fk_geo_site_region FOREIGN KEY(region_id) REFERENCES geo_region(region_id),
CONSTRAINT fk_geo_site_cur FOREIGN KEY(cur_code) REFERENCES geo_currency(cur_code)
);
INSERT INTO geo_site (site_id, system_code, site_type, region_id, site_name, loc, cur_code, amount_orig, amount_usd, created_utc, local_day)
SELECT
g.n,
d.code,
CASE MOD(g.n,4) WHEN 0 THEN 'STORE' WHEN 1 THEN 'WAREHOUSE' WHEN 2 THEN 'SUPPLIER' ELSE 'SERVICE' END,
r.region_id,
d.code||'_SITE_'||TO_CHAR(g.n),
SDO_GEOMETRY(2001, 4326,
SDO_POINT_TYPE(
-- unique per-region grid: k=FLOOR((n-1)/12) in [0, N/12-1]; 100 lon-cols x (N/12/100) lat-rows
r.hub_lon + (MOD(FLOOR((g.n-1)/12),100)*0.05 - 2.5),
r.hub_lat + (FLOOR(FLOOR((g.n-1)/12)/100)*0.5 - 1.0),
NULL), NULL, NULL),
c.cur_code,
ROUND(MOD(g.n,500)+10.5,2),
ROUND((MOD(g.n,500)+10.5) * c.rate_to_usd, 2),
FROM_TZ(CAST(TIMESTAMP '2025-01-01 00:00:00' + NUMTODSINTERVAL(MOD(g.n,1095),'DAY') + NUMTODSINTERVAL(MOD(g.n*13,86400),'SECOND') AS TIMESTAMP), 'UTC'),
TRUNC(CAST((FROM_TZ(CAST(TIMESTAMP '2025-01-01 00:00:00' + NUMTODSINTERVAL(MOD(g.n,1095),'DAY') + NUMTODSINTERVAL(MOD(g.n*13,86400),'SECOND') AS TIMESTAMP), 'UTC') AT TIME ZONE r.tz_name) AS DATE))
FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL<=6000) g
JOIN geo_domain d ON d.domain_id = MOD(g.n,8)+1
JOIN geo_region r ON r.region_id = MOD(g.n,12)+1
JOIN geo_currency c ON c.cur_code = r.cur_code;
-- =====================================================================
-- 3b. geo_zone: polygons (geometry, SRID 4326) for point-in-polygon (SDO_RELATE)
-- =====================================================================
CREATE TABLE geo_zone (
zone_id NUMBER PRIMARY KEY,
zone_name VARCHAR2(40),
geom SDO_GEOMETRY
);
INSERT INTO geo_zone VALUES (1,'Greater China',
SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(100,18, 125,18, 125,45, 100,45, 100,18)));
INSERT INTO geo_zone VALUES (2,'Europe',
SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(-12,35, 35,35, 35,60, -12,60, -12,35)));
INSERT INTO geo_zone VALUES (3,'North America',
SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(-130,25, -65,25, -65,50, -130,50, -130,25)));
INSERT INTO geo_zone VALUES (4,'Southeast Asia',
SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(95,-10, 120,-10, 120,10, 95,10, 95,-10)));
-- =====================================================================
-- 4. Register metadata, then create R-tree spatial index on geo_site.loc
-- =====================================================================
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name='GEO_SITE';
BEGIN
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name='GEO_SITE';
INSERT INTO USER_SDO_GEOM_METADATA (table_name, column_name, diminfo, srid)
VALUES ('GEO_SITE','LOC',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('LON',-180,180,0.005),
SDO_DIM_ELEMENT('LAT',-90,90,0.005)
), 4326);
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
-- Drop the spatial index if it somehow survived a prior run, then (re)create it.
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX geo_ix_loc';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE INDEX geo_ix_loc ON geo_site(loc) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- =====================================================================
-- 5. Validation log table + procedure
-- =====================================================================
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE gval_log PURGE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE gval_log (chk_id VARCHAR2(10), descr VARCHAR2(80), pass CHAR(1), detail VARCHAR2(120));
-- Helper functions: SQL cannot access SDO_GEOMETRY attributes via dot-notation in this
-- environment (ORA-00904), but PL/SQL attribute access works. Use these in SELECT lists.
CREATE OR REPLACE FUNCTION geo_srid(p SDO_GEOMETRY) RETURN NUMBER IS BEGIN RETURN p.SDO_SRID; END;
/
CREATE OR REPLACE FUNCTION geo_lon(p SDO_GEOMETRY) RETURN NUMBER IS BEGIN RETURN p.SDO_POINT.X; END;
/
CREATE OR REPLACE FUNCTION geo_lat(p SDO_GEOMETRY) RETURN NUMBER IS BEGIN RETURN p.SDO_POINT.Y; END;
/
CREATE OR REPLACE PROCEDURE geo_chk(p_id VARCHAR2, p_desc VARCHAR2, p_pass NUMBER, p_detail VARCHAR2) IS
BEGIN
INSERT INTO gval_log(chk_id, descr, pass, detail) VALUES (p_id, p_desc, CASE WHEN p_pass=1 THEN 'Y' ELSE 'N' END, p_detail);
END;
/
-- =====================================================================
-- 6. Demos: geospatial queries (geometry/geography, distance, relationships, index)
-- =====================================================================
PROMPT -- A. Sample sites with geography (SRID 4326) coords
SELECT site_id, system_code, site_type, region_id, site_name, geo_lon(loc) lon, geo_lat(loc) lat
FROM geo_site WHERE ROWNUM<=5;
PROMPT -- B. Distance (km) between two sites (SDO_GEOM.SDO_DISTANCE, geography geodesic)
SELECT ROUND(SDO_GEOM.SDO_DISTANCE(a.loc,b.loc,0.005,'unit=KM'),1) km
FROM geo_site a, geo_site b WHERE a.site_id=1 AND b.site_id=2;
PROMPT -- C. Sites within 5000 km of site 1 (SDO_WITHIN_DISTANCE, standalone)
SELECT b.site_id, b.site_name, ROUND(SDO_GEOM.SDO_DISTANCE(a.loc,b.loc,0.005,'unit=KM'),0) km
FROM geo_site a, geo_site b
WHERE a.site_id=1 AND SDO_WITHIN_DISTANCE(b.loc,a.loc,'distance=5000 unit=KM')='TRUE'
ORDER BY km
FETCH FIRST 10 ROWS ONLY;
PROMPT -- D. 3 nearest neighbors of site 1 (SDO_NN, standalone) with km
SELECT b.site_id, b.site_name, ROUND(SDO_GEOM.SDO_DISTANCE(b.loc,a.loc,0.005,'unit=KM'),0) km
FROM geo_site a, geo_site b
WHERE a.site_id=1 AND SDO_NN(b.loc,a.loc,'sdo_num_res=4',1)='TRUE' AND ROWNUM<=4
ORDER BY km;
PROMPT -- E. Point-in-polygon: sites inside Greater China zone (SDO_RELATE, standalone)
SELECT s.site_id, s.site_name, geo_lon(s.loc) lon, geo_lat(s.loc) lat
FROM geo_site s, geo_zone z
WHERE z.zone_name='Greater China'
AND SDO_RELATE(s.loc, z.geom, 'mask=INSIDE querytype=WINDOW')='TRUE'
FETCH FIRST 10 ROWS ONLY;
PROMPT -- F. Zone area (sq km) and polygon length (SDO_GEOM.SDO_AREA / SDO_LENGTH)
SELECT z.zone_name,
ROUND(SDO_GEOM.SDO_AREA(z.geom,0.005,'unit=SQ_KM'),0) area_sqkm,
ROUND(SDO_GEOM.SDO_LENGTH(z.geom,0.005,'unit=KM'),0) perim_km
FROM geo_zone z;
PROMPT -- G. Timezone: same UTC instant lands on different local_day per region
SELECT s.site_id, TO_CHAR(s.created_utc,'YYYY-MM-DD HH24:MI') utc,
r.region_name, TO_CHAR(s.local_day,'YYYY-MM-DD') local_day
FROM geo_site s JOIN geo_region r ON r.region_id=s.region_id
WHERE s.site_id IN (1,6000,30000) ORDER BY s.site_id;
PROMPT -- H. Multi-currency: only SUM(amount_usd) normalized (never cross-cur SUM amount_orig)
SELECT cur_code, SUM(amount_orig) orig_sum, SUM(amount_usd) usd_sum
FROM geo_site GROUP BY cur_code ORDER BY cur_code;
PROMPT -- I. High-concurrency: parallel + RESULT_CACHE aggregation
SELECT /*+ RESULT_CACHE PARALLEL(geo_site 4) */ system_code, COUNT(*) cnt
FROM geo_site GROUP BY system_code ORDER BY system_code;
PROMPT -- J. Combined: spatial within-distance + structured filter (STORE only)
SELECT COUNT(*) hits
FROM geo_site a, geo_site b
WHERE a.site_id=1 AND b.site_type='STORE'
AND SDO_WITHIN_DISTANCE(b.loc,a.loc,'distance=5000 unit=KM')='TRUE';
PROMPT -- K. Geography vs geometry: all site points carry SRID 4326 (geodesic)
SELECT COUNT(*) sites_with_srid4326 FROM geo_site WHERE geo_srid(loc)=4326;
PROMPT -- L. Domain distribution across 6000 sites
SELECT system_code, COUNT(*) cnt FROM geo_site GROUP BY system_code ORDER BY system_code;
-- =====================================================================
-- 7. Self-checks V1..V17
-- =====================================================================
DECLARE
v1 NUMBER; v2 NUMBER; v3 NUMBER; v4 NUMBER; v5 NUMBER; v5m NUMBER;
v6 NUMBER; v7 NUMBER; v8 NUMBER; v9 NUMBER; v10 NUMBER;
v11a NUMBER; v11b NUMBER; v11 NUMBER; v12 NUMBER; v13 NUMBER;
v14 NUMBER; v15 NUMBER; v16 NUMBER; v17 NUMBER;
BEGIN
-- V1 spatial index exists and is VALID
SELECT COUNT(*) INTO v1 FROM user_indexes WHERE index_name='GEO_IX_LOC' AND status='VALID' AND ityp_name='SPATIAL_INDEX';
geo_chk('V1','Spatial index GEO_IX_LOC VALID', CASE WHEN v1=1 THEN 1 ELSE 0 END, 'cnt='||v1);
-- V2 metadata registered
SELECT COUNT(*) INTO v2 FROM USER_SDO_GEOM_METADATA WHERE table_name='GEO_SITE' AND column_name='LOC';
geo_chk('V2','USER_SDO_GEOM_METADATA registered', CASE WHEN v2>=1 THEN 1 ELSE 0 END, 'cnt='||v2);
-- V3 row count
SELECT COUNT(*) INTO v3 FROM geo_site;
geo_chk('V3','geo_site row count = 6000', CASE WHEN v3=6000 THEN 1 ELSE 0 END, 'rows='||v3);
-- V4 8 domains
SELECT COUNT(DISTINCT system_code) INTO v4 FROM geo_site;
geo_chk('V4','8 domains present', CASE WHEN v4=8 THEN 1 ELSE 0 END, 'distinct='||v4);
-- V5 SDO_DISTANCE > 0 (km)
SELECT ROUND(SDO_GEOM.SDO_DISTANCE(a.loc,b.loc,0.005,'unit=KM'),1) INTO v5 FROM geo_site a, geo_site b WHERE a.site_id=1 AND b.site_id=2;
geo_chk('V5','SDO_DISTANCE km > 0', CASE WHEN v5>0 THEN 1 ELSE 0 END, 'km='||v5);
-- V6 SDO_WITHIN_DISTANCE hits > 0
SELECT COUNT(*) INTO v6 FROM geo_site a, geo_site b WHERE a.site_id=1 AND SDO_WITHIN_DISTANCE(b.loc,a.loc,'distance=5000 unit=KM')='TRUE';
geo_chk('V6','SDO_WITHIN_DISTANCE hits > 0', CASE WHEN v6>0 THEN 1 ELSE 0 END, 'hits='||v6);
-- V7 SDO_NN nearest distance (km via SDO_GEOM.SDO_DISTANCE, >=0)
SELECT ROUND(SDO_GEOM.SDO_DISTANCE(b.loc,a.loc,0.005,'unit=KM'),1) INTO v7 FROM geo_site a, geo_site b WHERE a.site_id=1 AND SDO_NN(b.loc,a.loc,'sdo_num_res=1',1)='TRUE' AND ROWNUM<=1;
geo_chk('V7','SDO_NN nearest km >= 0', CASE WHEN v7>=0 THEN 1 ELSE 0 END, 'nn_km='||v7);
-- V8 SDO_RELATE point-in-polygon hits > 0
SELECT COUNT(*) INTO v8 FROM geo_site s, geo_zone z WHERE z.zone_name='Greater China' AND SDO_RELATE(s.loc, z.geom, 'mask=INSIDE querytype=WINDOW')='TRUE';
geo_chk('V8','SDO_RELATE point-in-polygon hits > 0', CASE WHEN v8>0 THEN 1 ELSE 0 END, 'inside='||v8);
-- V9 SDO_GEOM.SDO_AREA sq km > 0
SELECT ROUND(SDO_GEOM.SDO_AREA(z.geom,0.005,'unit=SQ_KM'),0) INTO v9 FROM geo_zone z WHERE zone_name='Greater China';
geo_chk('V9','SDO_GEOM.SDO_AREA sq_km > 0', CASE WHEN v9>0 THEN 1 ELSE 0 END, 'sqkm='||v9);
-- V10 timezone: local_day differs from UTC date for some rows
SELECT COUNT(*) INTO v10 FROM geo_site WHERE TRUNC(CAST(created_utc AT TIME ZONE 'UTC' AS DATE)) <> local_day;
geo_chk('V10','tz shift makes local_day differ', CASE WHEN v10>0 THEN 1 ELSE 0 END, 'rows='||v10);
-- V11 multi-currency: SUM(amount_usd) normalized vs SUM(amount_orig) differ
SELECT SUM(amount_usd), SUM(amount_orig) INTO v11a, v11b FROM geo_site;
geo_chk('V11','multi-cur SUM(amount_usd) normalized', CASE WHEN v11a<>v11b THEN 1 ELSE 0 END, 'usd='||ROUND(v11a)||' orig='||ROUND(v11b));
-- V12 high-concurrency parallel+result_cache aggregation yields 8 groups
SELECT COUNT(*) INTO v12 FROM (
SELECT /*+ RESULT_CACHE PARALLEL(geo_site 4) */ system_code, COUNT(*) c FROM geo_site GROUP BY system_code
);
geo_chk('V12','parallel+result_cache agg groups=8', CASE WHEN v12=8 THEN 1 ELSE 0 END, 'groups='||v12);
-- V13 combined spatial + structured hits > 0
SELECT COUNT(*) INTO v13 FROM geo_site a, geo_site b WHERE a.site_id=1 AND b.site_type='STORE' AND SDO_WITHIN_DISTANCE(b.loc,a.loc,'distance=5000 unit=KM')='TRUE';
geo_chk('V13','combined spatial+structured hits > 0', CASE WHEN v13>0 THEN 1 ELSE 0 END, 'hits='||v13);
-- V14 geography SRID=4326 on all sites
SELECT COUNT(*) INTO v14 FROM geo_site WHERE geo_srid(loc)=4326;
geo_chk('V14','geography SRID=4326 on all sites', CASE WHEN v14=6000 THEN 1 ELSE 0 END, 'srid4326='||v14);
-- V15 distance unit consistency: km*1000 ~ m
SELECT ROUND(SDO_GEOM.SDO_DISTANCE(a.loc,b.loc,0.005,'unit=M'),0) INTO v5m FROM geo_site a, geo_site b WHERE a.site_id=1 AND b.site_id=2;
geo_chk('V15','distance unit KM vs M consistent', CASE WHEN ABS(v5m - v5*1000) < 100 THEN 1 ELSE 0 END, 'm='||v5m||' kmx1000='||ROUND(v5*1000));
-- V16 SDO_NN top-3 nearest
SELECT COUNT(*) INTO v16 FROM (
SELECT b.site_id FROM geo_site a, geo_site b WHERE a.site_id=1 AND SDO_NN(b.loc,a.loc,'sdo_num_res=3',1)='TRUE' AND ROWNUM<=3
);
geo_chk('V16','SDO_NN top-3 nearest', CASE WHEN v16=3 THEN 1 ELSE 0 END, 'n='||v16);
-- V17 idempotent: GEO_ tables = 5 (domain, currency, region, site, zone)
SELECT COUNT(*) INTO v17 FROM user_tables WHERE table_name LIKE 'GEO_%';
geo_chk('V17','idempotent GEO_ objects = 5', CASE WHEN v17=5 THEN 1 ELSE 0 END, 'tables='||v17);
END;
/
-- =====================================================================
-- Q. Self-check summary
-- =====================================================================
SELECT chk_id, descr, pass, detail FROM gval_log ORDER BY chk_id;
SELECT 'CHECK SUMMARY: '||SUM(CASE WHEN pass='Y' THEN 1 ELSE 0 END)||'/'||COUNT(*)||' PASS' AS result FROM gval_log;
-- =====================================================================
-- R. Conclusions (ASCII)
-- =====================================================================
PROMPT 1) Geography = SDO_GEOMETRY with SRID 4326 (lon/lat); distance is geodesic via SDO_GEOM.SDO_DISTANCE(unit=KM).
PROMPT 2) Geometry = SDO_GEOMETRY polygons/linestrings; measure area/length via SDO_GEOM.SDO_AREA / SDO_LENGTH (unit=SQ_KM / KM).
PROMPT 3) Spatial relationships: SDO_WITHIN_DISTANCE, SDO_NN (nearest), SDO_RELATE (mask=INSIDE etc.) are STANDALONE functions (public synonyms), NOT SDO_GEOM methods.
PROMPT 4) SDO_GEOM.SDO_DISTANCE is a package method; SDO_WITHIN_DISTANCE/SDO_RELATE/SDO_NN/SDO_NN_DISTANCE are standalone.
PROMPT 5) Spatial index: register USER_SDO_GEOM_METADATA first, then CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX (R-tree).
PROMPT 6) SDO_NN_DISTANCE(1) returns METERS and the 'unit=KM' 2nd arg is REJECTED in this env (PLS-306). To get km, run SDO_NN to pick neighbor rows, then SDO_GEOM.SDO_DISTANCE(unit=KM) on them. SDO_NN needs the spatial index to evaluate.
PROMPT 7) Point-in-polygon: SDO_RELATE(geom, polygon, 'mask=INSIDE querytype=WINDOW') = 'TRUE'.
PROMPT 8) This env Oracle Spatial is licensed (SPATIAL_OPTION=TRUE); SDO_GEOMETRY / SDO_GEOM fully available.
PROMPT 9) Timezone: store UTC instant (TIMESTAMP WITH TIME ZONE); derive local_day via AT TIME ZONE (DST-safe); index-friendly.
PROMPT 10) Multi-currency: only SUM(amount_usd) normalized; never cross-currency SUM(amount_orig).
PROMPT 11) Trillion/hi-conc: cluster coords near regional hubs; spatial index serves within-distance/NN; structured filters via B-tree aux indexes; parallel+RESULT_CACHE for aggregates.
PROMPT 12) Naming: Oracle unquoted identifiers must start with a LETTER; '_name' is invalid and raises ORA-00911. Use GEO_ prefix (starts with G).
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/geovindu/article/details/170742822
