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.
