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');