Search Results reverse_geocode

Overview

MDSYS.SDO_GCDR is the Oracle Spatial GeoCoding and Reverse GeoCoding engine packaged as a PL/SQL API owned by the MDSYS schema. Although it is shipped as part of the Oracle Spatial and Graph option rather than as a native Oracle E-Business Suite component, it is commonly encountered in EBS 12.1.1 and 12.2.2 environments because it supplies the address-validation and coordinate-resolution logic on which geospatial extensions to EBS applications depend — customer and supplier address cleansing, site-level latitude/longitude assignment, territory and service-coverage mapping, and last-mile routing use cases built on top of the Shipping, Order Management, CRM, and Real Estate modules.

The package resolves a textual address into a normalized form and a geodetic point, and performs the inverse operation by returning the nearest matching address for a supplied coordinate. In EBS installations the geocoder is typically backed by an Oracle Spatial geocoding data set (for example NAVTEQ or Tele Atlas reference data) registered in the MDSYS schema, with a country profile that governs parsing rules such as street type positions, prefixes, suffixes, and second-unit separators.

Key Procedures and Functions

ETRM 12.2.2 documents 27 procedures and functions in this package. The validate* family — VALIDATECOUNTRYROW, VALIDATEINLINESTREETTYPEROW, VALIDATESTREETTYPEROW, VALIDATESECONDUNITROW, VALIDATESTREETPREFIXROW, VALIDATESTREETSUFFIXROW, VALIDATEREGIONROW, VALIDATECITYROW, VALIDATELOCALITYDICTROW, VALIDATESTREETDICTROW, VALIDATEPOBOXROW, and VALIDATEPLACENAMEROW — validates individual rows of country-profile and dictionary metadata, including postal box, place name, locality, and street dictionary entries. The source comments state these are used by country profile validation triggers and should not be documented; they are internal support routines.

The principal public entry points are BATCH_GEOCODE, GEOCODE, GEOCODE_AS_GEOMETRY, GEOCODE_ADDR, GEOCODE_ALL, GEOCODE_ADDR_ALL, and REVERSE_GEOCODE. BATCH_GEOCODE performs set-based geocoding over many input addresses in a single call, which is the routine most often referenced in batch address-cleansing requests. GEOCODE resolves an individual address; GEOCODE_ADDR geocodes an address structure; GEOCODE_ALL and GEOCODE_ADDR_ALL return all candidate matches ranked by confidence rather than the best match only. GEOCODE_AS_GEOMETRY returns the result directly as an SDO_GEOMETRY point. REVERSE_GEOCODE maps a supplied coordinate to its nearest street address. CREATE_PROFILE_TABLES provisions the country-profile and dictionary tables required by the geocoder.

Tables Accessed

The documented package references DBMS_ASSERT and PLITBLM through APPS synonyms. DBMS_ASSERT is used to sanitize and validate SQL identifiers and literals constructed dynamically, which is essential in a geocoder that must defend against SQL injection while assembling queries. PLITBLM provides PL/SQL interface support used internally by the package. The country-profile and dictionary tables created or validated by CREATE_PROFILE_TABLES and the validate* routines are not enumerated in the ETRM metadata excerpt; these reside in the MDSYS schema and hold reference geocoding data supplied by the data vendor.

Usage Notes

SDO_GCDR is not exposed through standard EBS forms; it is invoked from custom PL/SQL, concurrent programs, or database triggers written for geospatial extensions. A typical batch pattern calls BATCH_GEOCODE from a concurrent program over staged address rows, writing latitude and longitude back to the customer or site table. Interactive or single-address geocoding uses GEOCODE or GEOCODE_AS_GEOMETRY. Because the package runs AUTHID CURRENT_USER, execute privileges and synonym grants must be explicitly provided to the calling EBS schema.