Addresses Will Drop The Settlement If We Code In The ZIP (Doc ID 1357783.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms


Addresses will Drop the Settlement if we provide a zip code

Some addresses will drop the settlement if we code in a zip (it is a valid zip). If we send the same address without the zip the settlement stays and the proper zip gets added. Below are a few examples, There are many addresses returning this way.

Using Navteq geocoding data from the 2011Q1 ODF delivery. Examples below:

SQL> -- Example 1
SQL> -- This will return the address properly and adds the zip
SQL>
SQL> SELECT SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',
2 SDO_KEYWORDARRAY('1001 River Rd', 'King City, CA'),
3 'US', 'DEFAULT') FROM DUAL;

SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',SDO_KEYWORDARRAY('1001RIVERRD','KINGCITY,CA')
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'River Dr', NULL, NULL, 'King City', N
ULL, 'CA', 'US', '93930', NULL, '93930', NULL, '199', 'RIVER', 'DR', 'F', 'F', N
ULL, NULL, 'R', 0, 761523042, '?????ENU??B281CP?', 3, 'DEFAULT', -121.13352, 36.
20173, '???12101210??004?')


SQL>
SQL> -- This drops the settlement (when we provide the zip code)
SQL>
SQL> SELECT SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',
2 SDO_KEYWORDARRAY('1001 River Rd', 'King City, CA 93930'),
3 'US', 'DEFAULT') FROM DUAL;

SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',SDO_KEYWORDARRAY('1001RIVERRD','KINGCITY,CA93
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'River Rd', NULL, NULL, NULL, 'MONTERE
Y', 'CA', 'US', '93908', NULL, '93908', NULL, '1001', 'RIVER', 'RD', 'F', 'F', N
ULL, NULL, 'R', 1, 777967791, '????#ENUT??281C??', 11, 'DEFAULT', -121.55002, 36
.5563, '???10101012??002?')




SQL> -- Example 2:
SQL>
SQL> -- This will return the address properly and adds the zip
SQL>
SQL> SELECT SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',
2 SDO_KEYWORDARRAY('405 Utah St', 'Medicine Bow, WY'),
3 'US', 'DEFAULT') FROM DUAL;

SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',SDO_KEYWORDARRAY('405UTAHST','MEDICINEBOW,WY'
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'Utah St', NULL, NULL, 'Medicine Bow',
NULL, 'WY', 'US', '82329', NULL, '82329', NULL, NULL, 'UTAH', 'ST', 'F', 'F', N
ULL, NULL, ' ', 0, 126123243, '?????ENUT?B281CP?', 3, 'DEFAULT', -106.20475, 41.
89576, '???13101010??004?')


SQL>
SQL> -- This drops the settlement
SQL>
SQL> SELECT SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',
2 SDO_KEYWORDARRAY('405 Utah St', 'Medicine Bow, WY 82329'),
3 'US', 'DEFAULT') FROM DUAL;

SDO_GCDR.GEOCODE('GEOCODE_2011_Q1',SDO_KEYWORDARRAY('405UTAHST','MEDICINEBOW,WY8
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'Utah St', NULL, NULL, NULL, 'CARBON',
'WY', 'US', '82301', NULL, '82301', NULL, '405', 'UTAH', 'ST', 'F', 'F', NULL,
NULL, 'R', .040816327, 84769002, '????#ENUT??281C??', 11, 'DEFAULT', -107.23244,
41.7913988, '???10101012??002?')


We verified that this also happens when the Settlement names are all in UPPERCASE:

Reran the tests against Navteq 2011 Q1 data.

SQL> select settlement_name from gc_postal_code_nvt where rownum <10;

SETTLEMENT_NAME
--------------------------------------------------------------------------------
ORLEANS
GLOUCESTER
OTTAWA
ROCKCLIFFE PARK
ROCKCLIFFE PARK
OTTAWA
GLOUCESTER
OTTAWA
VANIER

9 rows selected.

Same results




Changes

Navteq Dataset

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms