Neues Whitepaper zu Spatial and Graph Analytics mit Oracle DB 12c

Es gibt ein aktuelles Whitepaper zu Spatial und Graph Analytics bezogen auf die Oracle DB 12c. Autor ist Siva Ravada, Leiter des Entwicklungsteams bei Oracle für Spatial and Graph.
In den Ausführungen zum Graph-Teil geht es u.a. um Linked Open Data, RDF (Resource Description Framework), SPARQL oder die Property Graph Unterstützung in 12.2.
Zu den Erweiterung im Spatial Bereich seit Version 12.1 wurde hier im Blog schon Einiges geschrieben. Was in 12.2 kommt, ist u.a. die GeoJSON-Unterstützung. Das kann bereits über den Oracle Database Cloud Service getestet werden.

Neue Spalte(n) in den Geocoding-Tabellen für die Oracle DB 12c

Den Anstoß zu diesem Blog-Eintrag habe ich von Cord Artmeier vom Oracle Partner g.on experience erhalten. Cord, vielen Dank dafür.

Cord hatte ein Problem mit dem Reverse Geocoding in der Version 12c der Oracle Datenbank entdeckt. Sein Aufruf

select sdo_gcdr.reverse_geocoding (...) from dual;

gab NULL zurück. Nicht das erwartete Ergebnis im Vergleich zum gleichen Aufruf mit der Version 11.2. Und noch nicht einmal eine Fehlermeldung, an der er sich orientieren konnte.

Was war der Grund für dieses Verhalten?
Die dem Geocoding zugrunde liegenden Referenzdaten in den GC_xxx-Tabellem wurden zu Testzwecken aus einer 11.2 Datenbank direkt in die neue aufgesetzte 12.1 Datenbank übernommen.
Für die Version 12.1 der Geocoding Engine (als Teil der Option Oracle Spatial and Graph) wurden jedoch einigen GC-Tabellen neue Spalten hinzugefügt.
So die Spalte TRANSLITERATION_TYPE in den Tabellen GC_AREA_xxx und GC_ROAD_xxx. Diese fehlte für einen erfolgreichen Geocoding- bzw. Reverse Geocoding-Aufruf, ein Umstand, der schnell erkennbar wurde beim Prüfen des Tracefiles.

Diese Änderung ist bisher nur im Oracle Spatial and Graph Geocoder Developer’s Guide vermerkt, nicht jedoch im Oracle Spatial and Graph Developer’s Guide 12.1. Das sollte aber bald nachgebessert sein.

Und hier noch mal der Hinweis darauf, wie einfach Geocoding und Reverse Geocoding direkt in der Oracle Datenbank geht:

SQL> select sdo_gcdr.geocode(
  2           user,
  3           sdo_keywordarray('Schiffbauergasse 14','Potsdam'),
  4           'DE',
  5           'DEFAULT')
  6    from dual;

SDO_GCDR.GEOCODE(USER,SDO_KEYWORDARRAY('SCHIFFBAUERGASSE14','POTSDAM'),'DE','DEF
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'Schiffbauergasse', NULL, NULL, 'Potsd
am', 'Potsdam', 'BRANDENBURG', 'DE', '14467', NULL, '14467', NULL, '14', 'SCHIFF
BAUER', 'GASSE', 'F', 'F', NULL, NULL, 'R', .75, 748777426, '??X?#ENUT?B281CP?',
 1, 'DEFAULT', 13.07432, 52.40411, '??010101010??404?', 8307)

SQL> select sdo_gcdr.reverse_geocode(
  2           user,
  3           sdo_geometry(2001,8307,sdo_point_type(13.07432,52.40411,null),null,null),
  4           'DE')
  5    from dual;

SDO_GCDR.REVERSE_GEOCODE(USER,SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(13.07432,52.
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'Schiffbauergasse', NULL, NULL, 'Berli
ner Vorstadt', 'Potsdam', 'Brandenburg', 'DE', '14467', NULL, '14467', NULL, '14
', 'SCHIFFBAUER', 'GASSE', 'F', 'F', NULL, NULL, 'R', .750735339, 748777426, '',
 1, 'DEFAULT', 13.0743195, 52.404111, '???14141414??404?', 8307)

Beispielhaft den Nutzen von VPA testen - Ein kleines Skript für den Eigengebrauch

Im Anschluss an einen Workshop zu Oracle Spatial 12c hat einer der Teilnehmenden das Feature VPA (Vector Performance Accelerator) erfolgreich getestet.
Ich habe dazu eine kurze Nachricht in der Oracle Spatial and Graph Gruppe von LinkedIn geposted.

Für diejenigen, die VPA selbst mal ausprobieren wollen, habe ich hier ein kleines Skript. Dieses vergleicht die Ausführungszeiten einer SQL Query ohne VPA gegen die mit VPA. Es kann für eigene zu testende Abfragen entsprechend angepaßt werden.

Das Ergebnis für 3 beispielhafte Queries kann sich mehr als sehen lassen. Die Zusammenfassung findet Ihr am Ende.

Für die Tests habe ich Beispieldaten (Postleitzahlgebiete von DE) des Oracle Partners GfK GeoMarketing verwendet. (Den anzupassenden Teil habe ich rot gekennzeichnet.)

REM spatial_12c_vpa_perf_test.sql - collect performance information related to Oracle Spatial 12c
REM Run script as user SYSTEM
define SRDCNAME='SPATIAL_VPA_PERF_TRACE'
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
       to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on
REM
spool &&SRDCSPOOLNAME..txt
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp:       '||
          to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine:         '||host_name from v$instance
union all
select '| Version:         '||version from v$instance
union all
select '| DBName:          '||name from v$database
union all
select '| Instance:        '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/

set HEADING on
set echo on feedback on termout on pages 999 lines 132 long 300000000

DEFINE OBJ_OWNER = &1
DEFINE OBJ_PASSWD = &2

/*
 * Grant necessary privileges
 */

grant alter system to &&OBJ_OWNER;
grant alter session to &&OBJ_OWNER;

connect &&OBJ_OWNER/&&OBJ_PASSWD
alter system flush shared_pool;

alter session set tracefile_identifier='SRDC_Spatial_VPA_Perf_Test';
alter session set events '10046 trace name context forever, level 12';
-- alter session set sql_trace = TRUE;

alter session set spatial_vector_acceleration = FALSE;

set timing on
REM -- ------------------------------------
REM -- Add query to be traced and analyzed
REM -- Run without VPA first
REM -- ------------------------------------

REM -- Begin of query
with test as (
  select sdo_aggr_union(sdoaggrtype(g.geometry,0.05)) aggr_geom 
    from de_5digpc_2010 g
   where substr(g.id,1,1) in ('0','1','2'))
select count(*) 
  from test;
REM -- End of query

set timing off

alter system flush shared_pool;
alter session set spatial_vector_acceleration = TRUE;
 
set timing on
REM -- ------------------------------------
REM -- Add same query
REM -- Run with VPA now
REM -- ------------------------------------

REM -- Begin of query
with test as (
  select sdo_aggr_union(sdoaggrtype(g.geometry,0.05)) aggr_geom 
    from de_5digpc_2010 g
   where substr(g.id,1,1) in ('0','1','2'))
select count(*) 
  from test;
REM -- End of query
  
set timing off

spool off

Im Ergebnis erhalte ich Faktoren von 10 bis 253, was die Ausführungszeiten betrifft.
Fazit:
Je grösser die Anzahl der zu aggregierenden Polygone, umso höher ist der Performancegwinn.

Und hier sind die Ergbnisse im Detail:

Anzahl der aggr. Objekte	Geometrietyp	Elapsed time in Sek. ohne VPA	Elapsed time in Sek. mit VPA	Faktor	Abfrage
10	Polygon	18,33	1,78	10	with test as ( select sdo_aggr_union(sdoaggrtype(g.geometry,0.05)) aggr_geom from de_1digpc_2010 g) select count(*) from test;
95	Polygon	151,66	2,44	62	with test as ( select sdo_aggr_union(sdoaggrtype(g.geometry,0.05)) aggr_geom from de_2digpc_2010 g) select count(*) from test;
2168	Polygon	1338,65	5,29	253	with test as ( select sdo_aggr_union(sdoaggrtype(g.geometry,0.05)) aggr_geom from de_5digpc_2010 g where substr(g.id,1,1) in ('0','1','2')) select count(*) from test;

Kleines Anwendungsbeispiel für PointInPolygon

Letztens erhielt ich mal wieder eine kleine Fragestellung, dessen Lösungsansatz ich hier vorstellen möchte.

Frage:
Wie kann mittels SQL festgestellt werden, welche Linien-Geometrien ihren Startpunkt in einem definierten Gebiet (Polygon) haben? (Abflüge, Startpunkte für Wege zu Fuss oder mit einem Fahrzeug, ...)

Visuell kann das Ergebnis einer solchen Frage wie in dieser Abbildung dargestellt werden:

Es gibt sprichwörtlich viele Wege nach Rom.

Einer davon ist mein Lösungsansatz mittels des ab Oracle DB 12c verfügbaren neuen SDO_POINTINPOLYGON Operators. (Infos dazu gibt es auch in diesem Blog-Posting.)

Und das ist mein SQL-Statement:

WITH temp AS (
  SELECT r.id, first_vertex(r.geom) coord, r.geom geom 
    FROM t_routen r)                    -- T_ROUTE: Tabelle mit Liniengeometrien
SELECT * FROM (
  TABLE(
    SDO_POINTINPOLYGON(
      CURSOR(                           -- X,Y müssen die ersten beiden Parameter sein
        SELECT t.coord.sdo_point.x x, t.coord.sdo_point.y y, t.id  
          FROM temp t),
      (SELECT g.geom FROM t_gebiet g),  -- T_GEBIET: Tabelle mit Polygon für das Startgebiet
      0.05,
      'mask=inside'))) a;               -- Mask=INSIDE: Startpunkt soll im Polygon liegen

Wie zu sehen ist, verwende ich darin eine Funktion, first_vertex von mir benannt, die den 1. Stützpunkt einer (2D) Linien-Geometrie ermittelt. Und so sieht die Funktion aus:

CREATE OR REPLACE FUNCTION first_vertex(geom SDO_GEOMETRY)
RETURN SDO_GEOMETRY DETERMINISTIC
IS
  vertx MDSYS.VERTEX_SET_TYPE;
BEGIN
  vertx := SDO_UTIL.GETVERTICES(geom);
  RETURN SDO_GEOMETRY(2001, geom.sdo_srid, sdo_point_type(vertx(1).x, vertx(1).y, NULL), NULL, NULL);
END;
/

Warum verwende ich im Lösungsansatz nicht SDO_INSIDE(geometry1, geometry2);?

Der Grund ist recht einfach: Ich benötige auf geometry1 einen Spatial Index.
Den habe ich zwar auf der Tabelle T_ROUTEN selbst, jedoch nicht auf dem 1. Stützpunkt der Geometrien. Ich müsste also einen Function-Based Index anlegen, damit SDO_INSIDE funktioniert. Das spare ich mir, weil es ja SDO_POINTINPOLYGON gibt.

Die Fragestellung kann dann noch sehr leicht dahingehend erweitert werden, festzustellen, ob irgendwo auf meiner Route bestimmte andere Gebiete (Bundesländer, Umweltzonen, etc.) durchfahren, überflogen, etc. wurden.

Dazu vergleiche ich das Ergebnis der Anfrage oben in der WHERE-Klausel mit einer 2. Polygon-Geometrie (hier Tabelle T_ZONE).

WITH temp AS (
  SELECT r.id, first_vertex(r.geom) coord, r.geom geom 
    FROM t_routen r)                    -- T_ROUTE: Tabelle mit Liniengeometrien
SELECT * FROM (
  TABLE(
    SDO_POINTINPOLYGON(
      CURSOR(                           -- X,Y müssen die ersten beiden Parameter sein
        SELECT t.coord.sdo_point.x x, t.coord.sdo_point.y y, t.id  
          FROM temp t),
      (SELECT g.geom FROM t_gebiet g),  -- T_GEBIET: Tabelle mit Polygon für das Startgebiet
      0.05,
      'mask=inside'))) a                -- Mask=INSIDE: Startpunkt soll im Polygon liegen
WHERE a.id IN  (
  SELECT r.id
    FROM t_zone z, t_routen r
   WHERE a.id = r.id 
     AND SDO_ANYINTERACT (r.geom, z.geom) = 'TRUE');

Viel positives Feedback zu den Oracle Spatial Workshops mit Albert Godfrind

Im Januar und Februar haben wir in Frankfurt zwei jeweils 2-tägige Workshops zu Oracle Spatial 12c durchgeführt. Beide Termine waren sehr gut besucht. Und wir haben sehr viel positives Feedback erhalten, wie dieses hier (was ich aus dem Gedächtnis zitiere):

"Ich wusste nicht, dass ich mit Oracle auf einer solch detaillierten technischen und fachlichen Ebene sprechen kann, wenn es raumbezogene Daten geht ..".

Ja, Leute: Wir können auch technisch. Und nicht nur eine/r oder zwei von uns, wird sind mehr ;-).
Wir teilen unser Wissen gern, so gern, wie wir auch auf das Wissen von Anderen immer wieder zurückgreifen. Denn auch wir müssen das Rad ja nicht jedes Mal neu erfinden.

In diesem Sinne also vielen Dank an alle, die aktiv durch Teilnahme, Diskussion und / oder Fragen zum Gelingen dieser Workshops beigetragen haben.

Besonderer Dank geht an:

• Albert Godfrind für die inhaltlichen Vorbereitungen und Gestaltung
• Hans Viehmann als Oracle Spatial Technologies Produktmanager u.a. für das Sponsoring

Übrigens: Wiederholungen sind nicht ausgeschlossen.
Lasst Carsten und mich wissen, wenn Ihr daran Interesse habt.

Vergleichender Test von Oracle DB 12 mit eingeschaltetem Vector Performance Accelerator (VPA)

Simon Greener hat die Auswirkungen des Oracle Spatial 12c Features "Vector Performance Acceelerator" auf die Performance von räumlichen Abfragen untersucht. Darüberhinaus hat er die Ergebnisse mit anderen Hersteller verglichen.
Vorgehensweise und Ergebnisse sind hier veröffentlicht. Die Ergebnisse gibt es auf Nachfrage per Formular oder als Zusammenfassung in diesem Foliensatz von der OOW 2014 (Folien 11 bis 13).

Die Nutzung von VPA ist sehr einfach, indem der entsprechende Datenbank-Parameter auf TRUE gesetzt wird. Damit l&aum;sst sich sehr schnell in der eigenen Umgebung evaluieren, welchen Nutzen das Feature haben kann.

ALTER SYSTEM SET SPATIAL_VECTOR_ACCELERATION = TRUE;

Es ist zu beachten, dass Simon bei diesen Tests keine weiteren performance-beeinflussenden Techniken wie z.B. Parallelisierung betrachtet hat.

Wenn jemand eigene Tests mit VPA und Parallelisierung aufsetzen möchte, kann sie oder er sich gern an uns wenden.

Nächstes Webinar von Directions Magazine: Hochperformante Abfragen auf großen Vektordatenbeständen. Anwendung des Vector Performance Accelerator als Teil von Oracle Spatial and Graph 12c

Das nächste Webinar von Directions Magazine mit Oracle-Beteiligung trägt den Titel :

"Learn How Customers Are Experiencing 300x Performance Gains with Oracle Spatial and Graph"

Hauptthema ist die immense Beschleunigung, welche bei der Prozessierung und Abfrage von großen Datenmengen (Vektordaten) erzielt werden kann durch ein einfaches Umlegen eines "Schalters" in der Oracle Datenbank. Dieser Schalter ist der Datenbank-Parameter SPATIAL_VECTOR_ACCLERATION.

Wer also um diese Zeit gerade nichts anderes vor hat, kann sich am

• 6. Mai 2014 
• um 20 Uhr 

online dazuschalten.

Die Registrierung erfolgt über diesen Link: http://www.directionsmag.com/webinars/register/learn-how-customers-are-experiencing-300x-performance-gains-with-oracl/390239?DM_webinars_section&utm_medium=web&utm_campaign=390239

Ich empfehle,  sich zu registrieren, auch wenn die Live-Zuschaltung zeitlich nicht möglich ist. Die Webinare werden aufgezeichnet. Der Link zur Aufzeichnung wird hinterher an alle Registrierten verschickt.