0 Replies Latest reply: Jan 30, 2013 7:28 AM by 987910 RSS

    SDO_GCDR.GEOCODE and SDO_GCDR.GEOCODE_AS_GEOMETRY result in null and zeros

    987910
      Hi,
      I am very new to Oracle and Oracle Spatial and need some help (I apologize in advance if I am not using the correct terminology). I am using Oracle 11g R2 and am trying to use the sdo_gcdr.geocode and sdo_gcdr.geocode_as_geometry functions.

      I created the GC_ROAD_US AND GC_ROAD_SEGMENT_US tables and populated them using data from a table in our system (making sure to include all columns required for these tables as seen in the following white paper: http://download.oracle.com/otndocs/products/spatial/pdf/spatial11gr2_geocoder_twp.pdf). Our data did not have postal code data so that column in the GC_ROAD_SEGMENT_US is not populated.
      I also used script to create the GC_PARSER_PROFILEAFS and GC_PARSER_PROFILE tables and scripts to create the indexes for the data tables.

      The script did not create the GC_COUNTRY_PROFILE table so I had to create it. I did not include all columns listed (in the above mentioned white paper) but just the ones required and populated one row for the US as seen below (using the above mentioned white paper to populate the row...not sure if I did it correctly).

      COUNTRY_NAME: UNITED STATES OF AMERICA
      COUNTRY_CODE3: USA
      COUNTRY_CODE2: US
      LANG_CODE_1: ENG
      NUMBER_ADMIN_LEVELS: 4
      SETTLEMENT_LEVEL: 4
      MUNICIPALITY_LEVEL: 3
      REGION_LEVEL: 2
      SETTLEMENT_IS_OPTIONAL: T
      MUNICIPALITY_IS_OPTIONAL: T
      REGION_IS_OPTIONAL: T
      POSTCODE_IN_SETTLEMENT: F
      CACHED_ADMIN_AREA_LEVEL: null
      GC_TABLE_SUFFIX: US
      VERSION: 1.0


      The table which has the data that I want to geocode is called ADDRESSES and is in my KITE schema. The SDO packages are within the MDSYS schema.

      I entered this code into SQL Developer:
      select sdo_gcdr.geocode(‘KITE’, sdo_keywordarray('960 Locust St', 'OH'), 'US', 'DEFAULT') from dual;

      This is my result (when I click on the [MDSYS.SDO_GEO_ADDR] ): MDSYS.SDO_GEO_ADDR(0MDSYS.SDO_KEYWORDARRAY(),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,NULL,0,0,NULL)

      When keep everything the same in the above but change the table from dual to ADDRESSES, I get multiple rows all with the same result.

      select sdo_gcdr.geocode(‘KITE’, sdo_keywordarray('960 Locust St', 'OH'), 'US', 'DEFAULT') from ADDRESSES;
      This is my result (when I click on the [MDSYS.SDO_GEO_ADDR] ):
      Row 1: MDSYS.SDO_GEO_ADDR(0MDSYS.SDO_KEYWORDARRAY(),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,NULL,0,0,NULL)
      Row 2: MDSYS.SDO_GEO_ADDR(0MDSYS.SDO_KEYWORDARRAY(),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,NULL,0,0,NULL)
      Etc….

      I am unsure why I am getting NULL and zero values. I would think that at least the street name, region, country, house number, base name, and street type would be populated since I specified them in my query.

      When I enter the following into SQL Developer:
      select sdo_gcdr.geocode_as_geometry(‘kite’, sdo_keywordarray ('960 locust st', 'oh'), 'us') geom from dual;

      This is my result (when I click on the [MDSYS.SDO_GEOMETRY] ): MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)

      When keep everything the same in the above but change the table from dual to ADDRESSES, I get multiple rows all with the same result.

      This is my result (when I click on the [MDSYS.SDO_GEOMETRY] ):
      Row 1: MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)
      Row 2: MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)
      Etc….

      I am unsure why my result has a zero for both the longitude and latitude values.
      I would appreciate any help. Thanks!