Gebietsprüfung mit Koordinate

[StephanH]

Hallo wissendes Forum!

Mich beschäftigt derzeit die Frage, wie ich mittels einer Koordinate (x,y) prüfen kann, ob sie in einem bestimmten Polygon liegt.

Gegeben ist Tabelle1, in der z.B. Objektdaten liegen. Die Objektdaten sind mit x- und y-Koordinaten vom Typ float versehen.
In Tabelle 2 liegen die Gebiete. Die Polygondaten liegen in der Spalte Polygonpoints vom Typ SDO_Geometry.

Nun sollte es ja über das Kommando

SDO_RELATE(Feld a, Tabelle2.Polygonpoints,'mask=anyinteract')='TRUE'

irgendwie gehen.

Das Problem ist, ich müßte 2 Felder (x + y) der Tabelle 1 übergeben, aber ich finde nirgends, wie das geht :-(

Gäbe es in Tabelle1 ebenfalls ein Feld vom Typ SDO_GEOMETRY könnte ich das oben anstatt von Feld a angeben und ich würde hier nicht anfragen müssen...

Wie muß denn das korrekte Kommando heißen?

Grüße
Stephan

[defo]

Schau mal nach sdo_point_type.
Ich habe allerdings irgendwo gelesen, dass die sdo_relation ein Feld mit Spatial Index erwartet. Eine Konvertierung würde Dir da dann auch nicht helfen
Ansonsten kann man hier sicher alles nachlesen:
https://docs.oracle.com/cd/B19306_01...b14255/toc.htm
vielleicht hilft eine Funktion hiervon:
http://docs.oracle.com/cd/B19306_01/...5/sdo_util.htm

[StephanH]

Danke für die Tips, defo,

habe ein wenig herumexperimentiert, aber Oracle meldet: ORA-13226: Oberfläche ohne Spatial-Index nicht unterstützt...
insofern stimmt wohl deine Befürchtung mit dem Spatial-Index.
Scheint so, als müßte ich die Tabelle erweitern

[jum]

Hier mal ein kleines Beispiel zum Verständnis:

DROP   TABLE polygon;
 
CREATE TABLE polygon 
  (id NUMBER(5),
   geom SDO_GEOMETRY);
   
INSERT 
  INTO polygon (id, geom)
VALUES          (1, sdo_geometry(2003, NULL, NULL, sdo_elem_info_array (1,1003,1), sdo_ordinate_array(10,10, 20,10, 20,20, 10,20, 10,10)));


--spatiale Validierung
SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom,0.01) val 
  FROM polygon;

VAL
----
TRUE

--ohne INDEX möglich  (Funktion RELATE in SELECT-Liste)
WITH points (num, x,y) AS
 (SELECT 1,  5, 5 FROM dual UNION ALL
  SELECT 2, 10,10 FROM dual UNION ALL
  SELECT 3, 15,15 FROM dual UNION ALL 
  SELECT 4, 21,30 FROM dual)
SELECT id, num, x, y,
       sdo_geom.relate(geom,'DETERMINE',sdo_geometry(2001, NULL, sdo_point_type(x,y,0), NULL, NULL),0.1) det 
  FROM polygon, points;  

ID   NUM       X        Y       DET
---------------------------------------------
1	1	 5	 5	DISJOINT
1	2	10	10	TOUCH
1	3	15	15	CONTAINS
1	4	21	30	DISJOINT

-- Metadata  für Spatial/Locator
DELETE FROM user_sdo_geom_metadata
      WHERE table_name = 'POLYGON';

INSERT INTO user_sdo_geom_metadata(table_name, column_name, diminfo, srid)
       VALUES ('POLYGON', 'GEOM',
          mdsys.sdo_dim_array
           (mdsys.sdo_dim_element('X', 0, 100, 0.01),
            mdsys.sdo_dim_element('Y', 0, 100, 0.01)),
          null);

CREATE  INDEX polygon_spix
  ON polygon (geom)
  INDEXTYPE IS mdsys.spatial_index
  PARAMETERS ( 'SDO_INDX_DIMS=2' ); 

  
--nur mit INDEX möglich (Operator SDO_RELATE in WHERE-Klausel)  
WITH points (num, x,y) AS
 (SELECT 1,  5, 5 FROM dual UNION ALL
  SELECT 2, 10,10 FROM dual UNION ALL
  SELECT 3, 15,15 FROM dual UNION ALL 
  SELECT 4, 21,30 FROM dual)
SELECT id, num, x, y
  FROM polygon, points
 WHERE SDO_RELATE(geom, sdo_geometry(2001, NULL, sdo_point_type(x,y,0), NULL, NULL),'mask=anyinteract')='TRUE';


ID  NUM   X    Y
--------------------------
1    2	  10   10
1    3	  15   15

--nur mit INDEX möglich (Operator SDO_CONTAINS)  
WITH points (num, x,y) AS
 (SELECT 1,  5, 5 FROM dual UNION ALL
  SELECT 2, 10,10 FROM dual UNION ALL
  SELECT 3, 15,15 FROM dual UNION ALL 
  SELECT 4, 21,30 FROM dual)
SELECT id, num, x, y
  FROM polygon, points
 WHERE SDO_CONTAINS(geom, sdo_geometry(2001, NULL, sdo_point_type(x,y,0), NULL, NULL))='TRUE';

ID  NUM   X    Y
--------------------------
1    3	  15   15

[StephanH]

Hallo zusammen,

sorry, aber mir war eine Platte abgeraucht und das recovern hat ein wenig gedauert.

@ jum: erstmal vielen Dank für das Beispiel. Davon ausgehend dachte ich, es wäre notwendig, in die Struktur der Datenbank einzugreifen.
@ defo: die Idee mit SDO_POINT_Type war gar nicht verkehrt.

Gelöst habe ich das erstmal so:

select count(*), t1.name
from tabelle1 t1,
	(select t2.x, t2.y, t2.id 
	from tabelle2 t2
	join tabelle3 t3 on t3.id=t2.id
	where t3.time between '01.01.15' and '01.01.16'
	and t2.x is not null
	and t2.y is not null) d
where sdo_relate(polygonpoints, MDSYS,SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(select trunc(t4.x) from tabelle3 t4 where t4.id=d.id),(select trunc(t4.y) from tabelle3 t4 where t4.id=d.id),NULL),NUL,NULL),'mask=anyinteract querytype=WINDOW')='TRUE'
and t1.nameareatype='Typefestlegung'
group by t1.name

Durch die fehlende Indizierung muß der Nachteil in Kauf genommen werden, daß das Script "ein wenig" länger läuft. Hängt von der Anzahl der Tabelleneinträge ab. Hier lief es 4 Stunden...

Diese, zugegeben sehr langsame, Lösung bedarf sicher noch der Optimierung. Vielleicht hat ja jemand noch eine Idee, wie es ohne Modifizierung der Datenbank schneller geht.

Vielen Dank auf jeden Fall für die Hinweise und Gedanken.

Grüße,
Stephan

[defo]

Gelöst habe ich das erstmal so:

Durch die fehlende Indizierung muß der Nachteil in Kauf genommen werden, daß das Script "ein wenig" länger läuft. Hängt von der Anzahl der Tabelleneinträge ab. Hier lief es 4 Stunden...

Diese, zugegeben sehr langsame, Lösung bedarf sicher noch der Optimierung.

[highlight=sql]
select count(*), t1.name
from tabelle1 t1,
(select t2.x, t2.y, t2.id
from tabelle2 t2
join tabelle3 t3
on t3.id = t2.id
where t3.time between '01.01.15' and '01.01.16'
and t2.x is not null
and t2.y is not null) d
where sdo_relate(polygonpoints, MDSYS,
SDO_GEOMETRY(2001, NULL,
MDSYS.SDO_POINT_TYPE (select trunc(t4.x)
from tabelle3 t4
where t4.id = d.id),
(select trunc(t4.y)
from tabelle3 t4
where t4.id = d.id),
NULL),
NUL,
NULL), 'mask=anyinteract querytype=WINDOW') = 'TRUE' and t1.nameareatype = 'Typefestlegung'
group by t1.name
[/highlight]
Das sieht nicht ganz lauffähig aus: NUL, Klammersetzung?
Die Subselects sind mit sehr großer Wahrscheinlichkeit suboptimal. Kannst Du mal ein korrektes SQL für weitere Optimierungsvorschläge liefern?

[StephanH]

Die Subselects sind mit sehr großer Wahrscheinlichkeit suboptimal.

Keine Frage. Es ging mir nicht um DIE optimale Lösung, sondern eher um einen gangbaren Weg, der der Optimierung bedarf.
Du hast völlig Recht, wenn du die Subselects als mit hoher Wahrscheinlichkeit suboptimal bezeichnest - sie sind suboptimal, aber wie sonst?

JUM zeigte ja einen anderen Weg, über Strukturveränderung, auf. Den wollte ich vermeiden, weil ich sonst einige Tabellen verändern muß. Wie bereits im 1. Post erwähnt, möchte ich mit einer X/Y-Koordinate bestimmen, welchem Polygon sie zuzuordnen ist, mit der Einschränkung, mich auf bestimmte Polygontypen zu beziehen (AND t1.nameareatype='Typefestlegung').

Das sieht nicht ganz lauffähig aus: NUL, Klammersetzung?

Stimmt, Übertragungsfehler meinerseits! Es folgt die korrigierte Fassung:

SELECT COUNT(*), t1.name
  FROM tabelle1 t1,
       (SELECT t2.x, t2.y, t2.id
           FROM tabelle2 t2
           JOIN tabelle3 t3
             ON t3.id = t2.id
          WHERE t3.TIME BETWEEN '01.01.15' AND '01.01.16'
            AND t2.x IS NOT NULL
            AND t2.y IS NOT NULL) d
 WHERE sdo_relate(polygonpoints, MDSYS, 
                  SDO_GEOMETRY(2001, NULL, 
                    MDSYS.SDO_POINT_TYPE ((SELECT trunc(t4.x)
                                            FROM tabelle3 t4
                                           WHERE t4.id = d.id),
                                         (SELECT trunc(t4.y)
                                            FROM tabelle3 t4
                                           WHERE t4.id = d.id),
                               NULL),
                  NULL,
                  NULL), 'mask=anyinteract querytype=WINDOW') = 'TRUE' 
AND t1.nameareatype = 'Typefestlegung'
GROUP BY t1.name

[defo]

..aber wie sonst?
..
JUM zeigte ja einen anderen Weg, über Strukturveränderung, auf. Den wollte ich vermeiden, weil ich sonst einige Tabellen verändern muß.
..
korrigierte Fassung:
..

Wie das sonst geht, ist ohne Datenmodel nur anhand des gegebenen SQL Statementes nicht so gut zu beantworten.
Im Prinzip halt alle Subselect ins Hauptselect verschieben. Ich hab mir das mal angesehen, bin mir aber nicht mal sicher, ob jetzt tabelle1-3 mit den aliasen t1-t4 und Mehrfachverwendung der Aliase nun wirklich separate Tabellen sind. Ich geh mal davon aus, das Statement ist etwas anonymisiert.
Dann noch das Übliche, Indizierung der Join Felder prüfen und das time Feld natürlich.

Was das Beispiel von JUM angeht bzw. die Vorgaben von Oracle bezüglich Indexierung, hast Du ja nun schon Nährungswerte, wie schlecht es laufen kann. Da Du offenbar ja noch tief in der Implementierungsphase bist, solltest Du vielleicht auch überlegen, Dir nicht doch die Mühe zu machen, die Struktur richtig zu stellen.