Umrechnung von Adressen in Koordinaten direkt in der Oracle Datenbank

Die Gocoding Engine in der Oracle Datenbank wird mittels PL/SQL über das Package SDO_GCDR, welches dem Nutzer MDSYS gehört, angesprochen.
Neben dem eigentlichen Umrechnen einer Adresszeile in eine Punktgeometrie (SDO_GEOMETRY mit dem Geometrietyp 2001) mittels GEOCODE_AS_GEOMETRY gibt es in diesem Package auch die Funktion GEOCODE.

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

Sie wird genutzt, um eine zweite wichtige Funktion des Geocoders (neben der Berechnung der Geokoordinate) anzusprechen, nämlich das Verfizieren, Ergänzen und ggf. Korrigieren der Adresse. Der Rückgabewert hier ist ein Objekt vom Typ SDO_GEO_ADDR. Bei einem Match mit dem Referenzdatenbestand erhält man Informationen zum eigentlichen Prozess des Geokodierens wie z.B. die Qualität des Matches sowie die vervollständigten Daten der Adresse aus dem Referenzdatenbestand.

describe SDO_GEO_ADDR

offenbart, welcher Informationsumfang zur Verfügung gestellt werden kann.

Im Falle des oben angeführten Aufrufes erscheint das folgende Ergebnis, welches sich doch ohne Blick in den Oracle Spatial Developer´s Guide nur in einzelnen Teilen verständlich lesen läßt.

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?')

Mehr Verständlichkeit bringt hier ein PL/SQL-Skript, welches die Array-Werte herauszieht, zuordnet und im Falle von ERRORMESSAGE, MATCH_VECTOR und MATCH_MODE interpretiert.

/* Autor: Albert Godfrind */
/* Quellen: Pro Oracle Spatial for Oracle Database 11g und Oracle Spatial Workshop für Partner */
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

Jetzt wird die Funktion GEOCODE noch mal aufgerufen, aber diesmal innerhalb der Formatierungsfunktion.

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

Das Ergebnis ist jetzt doch sehr viel besser zu lesen und zu verstehen.
Probiert´s einfach mal aus.

- 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

Hinweis: Als Referenzdatenbestand stand mir für diesen Test mit dem Oracle Geocoder  das Datenset von Nokia (NAVTEQ) EU Q312 zur Verfügung.