Im heutigen Blogpost möchte ich kurz anhand eines Beispiels aufzeigen, wie Sie die Entfernung von SDO_GEOMETRY Punkten berechnen können. Hierbei gehe ich auch konkret auf das Problem des Verbindens von zwei Datenmengen anhand einer definierten Entfernung ein.
Info:
Dieser Post dient auch der eigenen Dokumentation und referenziert Beispiele anderer Entwickler.
Zum besseren Verständnis ein kleines Beispiel mit dessen Hilfe ich das Select-Statement darstellen möchte:
Mit dem folgenden Statement werden die beiden Tabellen verglichen und nur die Datensätze zurückgegeben bei denen die Entfernung kleiner gleich 200 Kilometer ist.
Die Funktion SDO_JOIN verbindet die Datensätze deren Entfernung in die vorgegebene Distanz passt. Voraussetzung für die Nutzung ist ein Index auf die SDO-Spalte.
Die Funktion SDO_GEOM.SDO_DISTANCE berechnet die Entfernung von zwei Punkten.
Info:
Das originale Beispiel stammt von Paul Dziemiela aus dem Oracle Forum.
Ps.: Die genannten Funktionen sollten auch mit der Oracle XE funktionieren, da diese Bestandteil des "Oracle Locator" sind und nicht Teil der kostenpflichtigen Erweiterung "Oracle Spatial"
Ein Beispiel zur Entfernungsberechnung ganz ohne SDO_GEMETRY (Quelle: Oracle Forum):
Info:
Dieser Post dient auch der eigenen Dokumentation und referenziert Beispiele anderer Entwickler.
Zum besseren Verständnis ein kleines Beispiel mit dessen Hilfe ich das Select-Statement darstellen möchte:
set define off;
/* table erstellung - ddl */
CREATE TABLE "GEO_FUSSBALLSTADION1"
(
"PLATZ" NUMBER(7,0),
"NAME" VARCHAR2(22),
"EHEMALIGER_NAME" VARCHAR2(128),
"STADT" VARCHAR2(13),
"EROEFFNUNG" NUMBER(4,0),
"NEU_UMBAU" NUMBER(4,0),
"KOSTEN" NUMBER(5,0),
"LAT" VARCHAR2(20),
"LON" VARCHAR2(20),
"GEO_WGS84" "SDO_GEOMETRY"
) ;
CREATE TABLE "GEO_FUSSBALLSTADION2"
(
"PLATZ" NUMBER(7,0),
"NAME" VARCHAR2(22),
"EHEMALIGER_NAME" VARCHAR2(128),
"STADT" VARCHAR2(13),
"EROEFFNUNG" NUMBER(4,0),
"NEU_UMBAU" NUMBER(4,0),
"KOSTEN" NUMBER(5,0),
"LAT" VARCHAR2(20),
"LON" VARCHAR2(20),
"GEO_WGS84" "SDO_GEOMETRY"
) ;
/* beispiel daten - dml */
Insert into GEO_FUSSBALLSTADION1 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('74649','Olympiastadion Berlin',null,'Berlin','1936','2004','242','52.51470','13.23949',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(13.23949,52.51470,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION1 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('67812','Allianz Arena',null,'München','2005',null,'340','48.21880','11.62470',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(11.6247,48.2188,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION1 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('65718','Signal Iduna Park','Westfalenstadion','Dortmund','1974','2003','32','51.49257','7.45184',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.45184,51.49257,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION1 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('54903','Mercedes-Benz Arena','Gottlieb-Daimler-Stadion, Neckarstadion','Stuttgart','1933','2011','60','48.79226','9.23208',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(9.23208,48.79226,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION2 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('63666','Olympiastadion München',null,'München','1972','2002',null,'48.17312','11.54662',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(11.54662,48.17312,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION2 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('54740','Veltins-Arena','Arena Auf Schalke','Gelsenkirchen','2001',null,'191','51.55462','7.06752',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.06752,51.55462,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION2 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('51750','Volksparkstadion','Imtech Arena, HSH Nordbank Arena, AOL-Arena, Volksparkstadion (1953¿2001, seit 1. Juli 2015)','Hamburg','1925','1999','64','53.58712','9.89860',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(9.8986,53.58712,NULL),NULL,NULL));
Insert into GEO_FUSSBALLSTADION2 (PLATZ,NAME,EHEMALIGER_NAME,STADT,EROEFFNUNG,NEU_UMBAU,KOSTEN,LAT,LON,GEO_WGS84) values ('27190','Stadion Dresden','Glücksgas-Stadion, Rudolf-Harbig-Stadion, Dynamo-Stadion','Dresden','1923','2009','46','51.04078','13.74793',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(13.74793,51.04078,NULL),NULL,NULL));
/* Eintrag in USER_SDO_GEOM_METADATA */
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'GEO_FUSSBALLSTADION1',
'GEO_WGS84',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5)
),
8307
);
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'GEO_FUSSBALLSTADION2',
'GEO_WGS84',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5)
),
8307
);
/* Index Erstellung */
CREATE INDEX "GEO_FUSSBALLSTADION1_IDX" ON "GEO_FUSSBALLSTADION1" ("GEO_WGS84")
INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;
CREATE INDEX "GEO_FUSSBALLSTADION2_IDX" ON "GEO_FUSSBALLSTADION2" ("GEO_WGS84")
INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;
Das Beispiel erstellt zwei Tabellen mit Daten von Fußballstadien. Außerdem wird ein Eintrag in der USER_SDO_GEOM_METADATA gemacht und ein Index auf die SDO_GEOMETRY Spalte GEO_WGS84 erstellt.Mit dem folgenden Statement werden die beiden Tabellen verglichen und nur die Datensätze zurückgegeben bei denen die Entfernung kleiner gleich 200 Kilometer ist.
select
gs1.name as name1,
gs2.name as name2,
case
when gs1.platz > gs2.platz then 'FUSSBALLSTADION1 bietet mehr Zuschauerplätze'
else 'FUSSBALLSTADION2 bietet mehr Zuschauerplätze'
end as zuschauerplatz_vergleich,
round(sdo_geom.sdo_distance(
geom1 => gs1.geo_wgs84,
geom2 => gs2.geo_wgs84,
tol => 5,
unit => 'unit=KM'
),3) entfernung
from
geo_fussballstadion1 gs1,
geo_fussballstadion2 gs2,
table(
sdo_join(
'GEO_FUSSBALLSTADION1','GEO_WGS84',
'GEO_FUSSBALLSTADION2','GEO_WGS84',
'mask=ANYINTERACT distance=200 unit=KILOMETER'
)
) geo
where
geo.rowid1 = gs1.rowid and
geo.rowid2 = gs2.rowid
order by 1
Ergebnis:
Die Funktion SDO_GEOM.SDO_DISTANCE berechnet die Entfernung von zwei Punkten.
Info:
Das originale Beispiel stammt von Paul Dziemiela aus dem Oracle Forum.
Ps.: Die genannten Funktionen sollten auch mit der Oracle XE funktionieren, da diese Bestandteil des "Oracle Locator" sind und nicht Teil der kostenpflichtigen Erweiterung "Oracle Spatial"
Ein Beispiel zur Entfernungsberechnung ganz ohne SDO_GEMETRY (Quelle: Oracle Forum):
function get_distance (a_lat number,a_lon number,b_lat number, b_lon number)
return number is
/* Source: https://community.oracle.com/message/1703382 */
circum number := 40000; -- kilometers
pai number := acos(-1);
a_nx number;
a_ny number;
a_nz number;
b_nx number;
b_ny number;
b_nz number;
inner_product number;
begin
-- 'Geo Koordinaten Check'
if (a_lat=b_lat) and (a_lon=b_lon) then
return 0;
else
a_nx := cos(a_lat*pai/180) * cos(a_lon*pai/180);
a_ny := cos(a_lat*pai/180) * sin(a_lon*pai/180);
a_nz := sin(a_lat*pai/180);
b_nx := cos(b_lat*pai/180) * cos(b_lon*pai/180);
b_ny := cos(b_lat*pai/180) * sin(b_lon*pai/180);
b_nz := sin(b_lat*pai/180);
inner_product := a_nx*b_nx + a_ny*b_ny + a_nz*b_nz;
if inner_product > 1 then
return 0;
else
return (circum*acos(inner_product))/(2*pai);
end if;
end if;
exception
when others then
rollback; raise_application_error(-20001, 'Interner Fehler aufgetreten. Abarbeitung wurde abgebrochen. Error:' || sqlerrm);
end;
