Tuesday Apr 01, 2014

Tips for using the Oracle Spatial and Graph geocoding engine to convert addresses directly in the Oracle database

By Karin Patenge, Principal Sales Consultant, Oracle Germany

Many OLTP systems and data warehouse implementations would benefit from having an address cleansing process included which at the same time associates geographic coordinates with each address for spatial analytics. Together with reference datasets from HERE/Nokia or TomTom the Oracle Geocoder can provide this functionality out-of-the-box.

The Geocoding engine in Oracle Spatial and Graph can be accessed using PL/SQL, via the SDO_GCDR package, which belongs to the user MDSYS. In addition to the the actual conversion of an address line into a point geometry (SDO_GEOMETRY with the geometry type 2001) using GEOCODE_AS_GEOMETRY, this package includes the GEOCODE function:

select SDO_GCDR.GEOCODE(user, SDO_KEYWORDARRAY('Schiffbauergasse 14', '14467 Potsdam'),'DE', 'DEFAULT') from dual;

This function provides the second important capability of the geocoder (in addition to the calculation of the geo-coordinates), namely to perform address verification. The return value is an object of type SDO_GEO_ADDR. Based on the address match against the reference dataset, this function returns information such as the quality of the match as well as the completed or corrected address from the reference dataset.

The command describe SDO_GEO_ADDR displays the structure of the object. The result for the query above can be found below, but without looking in the Oracle Spatial Developer's Guide only some parts are meaningful.

MDSYS.SDO_GEO_ADDR(MDSYS.SDO_KEYWORDARRAY(),
 NULL,NULL,NULL,NULL,NULL,
 'Potsdam','BRANDENBURG','DE','14467',
 NULL,NULL,NULL,NULL,NULL,NULL,'F','F',
 NULL,NULL,'L',0,64959047,'??????????B281CP?',4,'DEFAULT',
 13.04793,52.39935,'???11111110??400?')

To understand the output more easily, here is a PL/SQL script that extracts the array values and interprets the ERRORMESSAGE, MATCHVECTOR and MATCHMODE strings.

create or replace procedure format_geo_addr (
  address SDO_GEO_ADDR
)
AS
  type strings is table of varchar2(30);
  match_names strings := strings (
    '?            ',
    '0 (MATCHED)  ',
    '1 (ABSENT)   ',
    '2 (CORRECTED)',
    '3 (IGNORED)  ',
    '4 (SUPPLIED) '
  );
  address_elements strings := strings (
    null,
    null,
    'X Address Point',
    'O POI Name',
    '# House or building number',
    'E Street prefix',
    'N Street base name',
    'U Street suffix',
    'T Street type',
    'S Secondary unit',
    'B Built-up area or city',
    null,
    null,
    '1 Region',
    'C Country',
    'P Postal code',
    'P Postal add-on code'
  );
  element_match varchar2(128);
  element_match_code char(1);

BEGIN
  if address is not null then
    dbms_output.put_line ('- ID                  ' || address.ID);
    dbms_output.put_line ('- ADDRESSLINES');
    if address.addresslines is not null then
      for i in 1..address.addresslines.count() loop
        dbms_output.put_line ('- ADDRESSLINES['||i||']           ' || address.ADDRESSLINES(i));
      end loop;
    end if;
    dbms_output.put_line ('- PLACENAME           ' || address.PLACENAME);
    dbms_output.put_line ('- STREETNAME          ' || address.STREETNAME);
    dbms_output.put_line ('- INTERSECTSTREET     ' || address.INTERSECTSTREET);
    dbms_output.put_line ('- SECUNIT             ' || address.SECUNIT);
    dbms_output.put_line ('- SETTLEMENT          ' || address.SETTLEMENT);
    dbms_output.put_line ('- MUNICIPALITY        ' || address.MUNICIPALITY);
    dbms_output.put_line ('- REGION              ' || address.REGION);
    dbms_output.put_line ('- COUNTRY             ' || address.COUNTRY);
    dbms_output.put_line ('- POSTALCODE          ' || address.POSTALCODE);
    dbms_output.put_line ('- POSTALADDONCODE     ' || address.POSTALADDONCODE);
    dbms_output.put_line ('- FULLPOSTALCODE      ' || address.FULLPOSTALCODE);
    dbms_output.put_line ('- POBOX               ' || address.POBOX);
    dbms_output.put_line ('- HOUSENUMBER         ' || address.HOUSENUMBER);
    dbms_output.put_line ('- BASENAME            ' || address.BASENAME);
    dbms_output.put_line ('- STREETTYPE          ' || address.STREETTYPE);
    dbms_output.put_line ('- STREETTYPEBEFORE    ' || address.STREETTYPEBEFORE);
    dbms_output.put_line ('- STREETTYPEATTACHED  ' || address.STREETTYPEATTACHED);
    dbms_output.put_line ('- STREETPREFIX        ' || address.STREETPREFIX);
    dbms_output.put_line ('- STREETSUFFIX        ' || address.STREETSUFFIX);
    dbms_output.put_line ('- SIDE                ' || address.SIDE);
    dbms_output.put_line ('- PERCENT             ' || address.PERCENT);
    dbms_output.put_line ('- EDGEID              ' || address.EDGEID);
    dbms_output.put_line ('- ERRORMESSAGE        ' || address.ERRORMESSAGE);
    if address.MATCHVECTOR is not null then
      dbms_output.put_line ('- MATCHVECTOR         ' || address.MATCHVECTOR);
      for i in 1..length(address.MATCHVECTOR) loop
        if address_elements(i) is not null then
          if substr (address.matchvector,i,1) = '?' then
            element_match_code := 0;
          else
            element_match_code := substr(address.matchvector,i,1) + 1;
          end if;
          dbms_output.put_line ('-   '|| substr(address.errormessage,i,1)  || ' ' ||
            match_names (element_match_code + 1) || ' ' ||
            address_elements (i)
          );
        end if;
      end loop;
    end if;
    if address.MATCHVECTOR is not null then
      dbms_output.put_line ('- MATCHCODE           ' || address.MATCHCODE || ' = ' ||
        case address.MATCHCODE
          when  0 then 'Ambiguous'
          when  1 then 'Exact match'
          when  2 then 'Street type not matched'
          when  3 then 'House number not matched'
          when  4 then 'Street name not matched'
          when 10 then 'Postal code not matched'
          when 11 then 'City not matched'
        end
      );
    end if;
    dbms_output.put_line ('- MATCHMODE           ' || address.MATCHMODE);
    dbms_output.put_line ('- LONGITUDE           ' || address.LONGITUDE);
    dbms_output.put_line ('- LATITUDE            ' || address.LATITUDE);
  else
    dbms_output.put_line ('**** NO MATCH ****');
  end if;
end;
/
show errors

create or replace procedure format_addr_array (
  address_list SDO_ADDR_ARRAY
)

as
begin
  if address_list is not null and address_list.count() > 0 then
    for i in 1..address_list.count() loop
      dbms_output.put_line ('ADDRESS['||i||']');
      format_geo_addr (address_list(i));
    end loop;
  else
    dbms_output.put_line ('**** NO MATCH ****');
  end if;
end;
/
show errors

Now the GEOCODE function can be called again, but this time in conjunction with the format function:

exec format_geo_addr (SDO_GCDR.GEOCODE(user, SDO_KEYWORDARRAY('Schiffbauergasse 14', '14467 Potsdam'), 'DE', 'DEFAULT'));

And the resulting output is now formatted in a manner that is much easier to understand.


- ID                  0
- ADDRESSLINES
- PLACENAME          
- STREETNAME          Schiffbauergasse
- INTERSECTSTREET    
- SECUNIT            
- SETTLEMENT         
- MUNICIPALITY        Potsdam
- REGION              BRANDENBURG
- COUNTRY             DE
- POSTALCODE          14467
- POSTALADDONCODE    
- FULLPOSTALCODE     
- POBOX              
- HOUSENUMBER         14
- BASENAME            SCHIFFBAUER
- STREETTYPE          GASSE
- STREETTYPEBEFORE    F
- STREETTYPEATTACHED  F
- STREETPREFIX       
- STREETSUFFIX       
- SIDE                R
- PERCENT             .75
- EDGEID              748777426
- ERRORMESSAGE        ????#ENUT?B281CP?
- MATCHVECTOR         ???10101010??400?
-   ? ?             X Address Point
-   ? 1 (ABSENT)    O POI Name
-   # 0 (MATCHED)   # House or building number
-   E 1 (ABSENT)    E Street prefix
-   N 0 (MATCHED)   N Street base name
-   U 1 (ABSENT)    U Street suffix
-   T 0 (MATCHED)   T Street type
-   ? 1 (ABSENT)    S Secondary unit
-   B 0 (MATCHED)   B Built-up area or city
-   1 4 (SUPPLIED)  1 Region
-   C 0 (MATCHED)   C Country
-   P 0 (MATCHED)   P Postal code
-   ? ?             P Postal add-on code
- MATCHCODE           1 = Exact match
- MATCHMODE           DEFAULT
- LONGITUDE           13.0745378367008
- LATITUDE            52.4041174822031

Note: The reference dataset used for this test with the Oracle Spatial and Graph geocoder was provided by HERE/Nokia.

About

The official blog for the spatial features of Oracle Spatial and Graph, an option of Oracle Database - brought to you by the product managers and developers. Get technical tips, product information, and the latest news here. Visit our official product website at http://www.oracle.com/technetwork/database/options/spatialandgraph/overview/spatialfeatures-1902020.html

Search

Categories
Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today