1 Reply Latest reply: Jun 23, 2014 9:36 AM by 8abf5195-50ac-42af-9bf7-c431ade76bfa RSS

    Oracle Geocoding Problems?

    8abf5195-50ac-42af-9bf7-c431ade76bfa

      Anyone could identify why Oracle geocoding make some failures in the following cases as found?

       

       

      1. In the admin area table: GC_AREA_XX, Oracle geocoding API will be failed to present the results for road level from GC_ROAD_XX table,when AREA_NAME column has the data containing the number like the pattern below. If translating those number to the letters by using TRANSLATE(area_name, '0123456789', 'ABCDEFGHIJ') , Oracle geocoding works.

       

      ABERDEEN  3

      ALEXANDRIA  1A

      GREENVIEW NO 16

       

      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('UNIVERSITY RD','GREENVIEW NO 16,AB'), 'CA','DEFAULT')x FROM dual

       

      2. Why does Oracle geocoding API:  SDO_GCDR.GEOCODE_ALL do a search from GC_POI_XX (Point Of Interest) table sometimes? It doesn't go to GC_ROAD_XX table to find exactly matching one for the road name directly. Instead, it brings more results from POI table. Sometimes, this API gets results both from road table and POI table. Actually, as found, the best API for POI table query is: SDO_GCDR.GEOCODE_ADDR_ALL  or  SDO_GCDR.GEOCODE_ADDR.

       

      3. The API SDO_GCDR.GEOCODE_ALL or SDO_GCDR.GEOCODE doesn't like the street name with the number before or after it, so it could not find it from the road table. However, if adding the house number before it, it can find it. For instance:

       

      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('7 SCHOOL RD','MINTO,ON'), 'CA','DEFAULT')x FROM dual  ---- only bring the result for the area

       

      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('20 7 SCHOOL RD','MINTO,ON'), 'CA','DEFAULT')x FROM dual  ---- good, bring the result for the street

       

      4.  Oracle geocoding API:  SDO_GCDR.GEOCODE_ALL doesn't like RUE, CH to be included in the street name. CH needs to be replaced by CHMIN. RUE needs to be deleted, or to add a number before it for that API, if no any keyword appears in the street name. It looks some keywords in GC_PARSER_PROFILES_XX have some influence to the road search for that API, we think that may not happen.

       

      For instance, the street name with CH: CH A CORNU  doesn't work in API, but CHMIN A CORNU works.

       

      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('CH A CORNU','ARGYLE,NS'), 'CA','DEFAULT')x FROM dual;

       

      For instance, the street name with RUE:

       

      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('RUE D''OTTAWA','COATICOOK,QC'), 'CA','DEFAULT')x FROM dual--not good result
      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('D''OTTAWA','COATICOOK,QC'), 'CA','DEFAULT')x FROM dual--good result

      SELECT SDO_GCDR.GEOCODE_ALL('TEST', SDO_KEYWORDARRAY('100 D''OTTAWA','COATICOOK,QC'), 'CA','DEFAULT')x FROM dual --good result

       

      For instance, the street name with keyword: ST, that influences the query. But if adding any letter around the keyword ST like ST%, the query works. Why?

       

      SELECT SDO_GCDR.GEOCODE_ALL('DMTI_2014', SDO_KEYWORDARRAY('RUE ST VICTOR','QUÉBEC,QC'), 'CA','DEFAULT')x FROM dual --not good result

      SELECT SDO_GCDR.GEOCODE_ALL('DMTI_2014', SDO_KEYWORDARRAY('RUE ST% VICTOR','QUÉBEC,QC'), 'CA','DEFAULT')x FROM dual --good result

        • 1. Re: Oracle Geocoding Problems?
          8abf5195-50ac-42af-9bf7-c431ade76bfa

          Why do the keywords in the column: STREET_TYPE_KEYWORD of table: GC_PARSER_PROFILES_CA make the Oracle geocoding failure (Oracle geocoding only gets the result for admin level, not for the street or road)?

           

          After removing EAST, NORTH, from keywords, the following geocoding queries do not get their roads. Does Oracle reserves EAST and NORTH as keywords?

           

          SELECT SDO_GCDR.GEOCODE_ALL('DMTI_2014', SDO_KEYWORDARRAY('EAST RIVER RD','HINTON,AB'), 'CA','DEFAULT')x FROM dual

           

          SELECT SDO_GCDR.GEOCODE_ALL('DMTI_2014', SDO_KEYWORDARRAY('EAST WELLINGTON RD','NANAIMO,BC'), 'CA','DEFAULT')x FROM dual


          SELECT SDO_GCDR.GEOCODE_ALL('DMTI_2014', SDO_KEYWORDARRAY('NORTH RIVER RD','OTTAWA,ON'), 'CA','DEFAULT')x FROM dual

           

           

          However, why does the following similar one gets its good result?

           

          SELECT SDO_GCDR.GEOCODE_ALL('DMTI_2014', SDO_KEYWORDARRAY('CAPE CHIN NORTH SHORE RD','NORTHERN BRUCE PENINSULA,ON'), 'CA','DEFAULT')x FROM dual