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
| 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; |
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, ...)
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; /
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.
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');
"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: