Tags:

Oracle Spatial (Teil 3) - Entfernung von Punkten berechnen

Von Tobias Arnhold 1.27.2016
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:
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_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):
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;

Post Tags:

No Comment to " Oracle Spatial (Teil 3) - Entfernung von Punkten berechnen "