GeoJSON und die Oracle Datenbank

Beda Hammerschmidt ist einer der Experten bei Oracle für JSON in der Datenbank, aber ein "Newbie", was die Geodatenunterstützung in der Datenbank betrifft.
Jetzt hat er sich Letzteres mal genauer angeschaut. Herausgekommen ist sein Video im Rahmen der Ask TOM Office Hours zu GeoJSON in der Oracle DB.

Viel Spaß damit.


Ein Nachtrag:

Für "Punkt-in-Polygon" Abfragen gibt es einen optimierten Spatial Operator mit Namen SDO_POINTINPOLYGON. Ein kleines Anwendungsbeispiel dafür hatte ich in diesem Post veröffentlicht.

Affine Transformationen direkt in der Datenbank - nutzbar bereits mit Oracle Locator

In meinem Posting vom 8. September 2014 hatte ich beschrieben, wie mittels einer eigenen PL/SQL-Funktion Offsets zu Koordinatenwerten hinzugefügt werden können.

Es gibt darüberhinaus noch (mindestens) einen weiteren Lösungsansatz, der eine bereits existente Funktion zum Einsatz bringt. Und damit meine ich die Funktion AFFINETRANSFORMS im PL/SQL Package SDO_UTIL. Die Anwendung von AFFINETRANSFORM für die konkrete Aufgabenstellung möchte ich nachfolgend kurz beschreiben.

Hinweis:
Das Package SDO_UTIL ist mit jeder Edition der Oracle Datenbank nutzbar, also Bestandteil von Oracle Locator. (Sehen Sie dazu auch noch mal Tabelle B-1 im Oracle Spatial Developer´s Guide.)

Was ist die Aufgabenstellung?
Für alle Geometrien einer Tabelle soll eine Translation von 100m nach Norden erfolgen.

Lösung mittels SDO_UTIL.AFFINETRANSFORM:

select
  sdo_util.affinetransforms(
  geometry => geometry,
  translation => 'TRUE'  -- Hier soll eine Translation stattfinden
  tx => 0.0,             -- keine Rechtswert-Verschiebung
  ty => 100.0,           -- Hochwert-Verschiebung um 100 m 
  tz => 0.0
  ) as geometry
from geom_tab;

Das ist erst mal ziemlich einfach. Die Tücke steckt wie immer im Detail.
Die Verschiebungswerte tx, ty, tz  sind natürlich im zusammenhang mit dem Koordinatensystem der Geometrien zu sehen. Eine Verschiebung ty = 100.0 für Geometrien in ETRS89 / UTM (hier ist die Einheit Meter) bedeutet natürlich etwas anderes als für WGS84. Für Letzteres sind die Koordinatenwerte in Dezimalgrad angegeben.

Wie kann man möglichst einfach damit umgehen?
Mein Vorschlag dazu ist, vorab in der Datenbank eine Transformation durchzuführen.
Mit SQL ausgedrückt, sieht das dann so aus:

select
  sdo_util.affinetransforms(
    geometry => geometry,
    translation => 'TRUE', -- Hier soll eine Translation stattfinden
    tx => 0.0,             -- keine Rechtswert-Verschiebung
    ty => 100.0,           -- Hochwert-Verschiebung um 100 m 
    tz => 0.0
  )
from (                     -- Subquery in FROM-Klausel für Transformation
  select
    sdo_cs.transform(geometry, 25833) geometry
  from
    geom_tab_wgs84);

Visuell stellt sich das Ergebnis bezogen auf eine einzelne Geometrie so dar:

[Abb. 1: Original-Geometrie gelb, verschobene Geometrie orange eingefärbt.]

SDO_UTIL.AFFINETRANSFORMATIONS kann übrigens nicht nur für Translationen verwendet werden. Möglich sind u.a. auch Skalierungen, Spiegelungen oder Rotationen.

Für Rotationen füge ich gleich auch noch ein Beipiel hinzu. Das basiert auf einer sehr einfachen Geometrie, einem Rechteck, welches im unteren linken Stützpunkt um 45 gedreht wird.
Hinweis:
Der Rotationswert (Parameter ANGLE) wird als Bogenmass angegeben. Der Einfachheit halber nutze ich die Funktion CONVERT_UNIT im Paclage SDO_UTIL, um mit Werten in Grad zu arbeiten.

-- Einfache Geometrie
select 
 sdo_geometry (
   2003,
   8307,
   null,
   sdo_elem_info_array (1,1003,1),
   sdo_ordinate_array (2,0,2,5,0,5,0,0,2,0))
from dual;

-- Einfache Geometrie gedreht um 45 Grad bezogen auf den 1. Stützpunkt
select sdo_util.affinetransforms(
  geometry => sdo_geometry (
                2003,
                8307,
                null,
                sdo_elem_info_array (1,1003,1),
                sdo_ordinate_array (2,0,2,5,0,5,0,0,2,0)),
  rotation => 'TRUE',
  p1 => sdo_geometry (2001,8307,sdo_point_type(2,0,null),null,null),
  line1 => NULL,
  angle => sdo_util.convert_unit(45, 'Degree', 'Radian'),  -- Bogenmass
  -- Bzw. für Drehung im Uhrzeigersinn:
  -- angle => sdo_util.convert_unit(-45, 'Degree', 'Radian')   
  dir => -1                                                -- Default
) from dual;

Visuelle Darstellung der Ergebnisse der SQL-Abfragen (Geometrien):

 [Abb. 2: Einfache Geometrie - Orignal]

 [Abb. 3: Einfache Geometrie - um 45 Grad gedreht]

Die gedrehte Geometrie als SDO_GEOMETRY-Objekt - mit Anfangs- und Endstützpunkt x=2, y=0:

MDSYS.SDO_GEOMETRY(
 2003,
  8307,
  NULL,
  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  MDSYS.SDO_ORDINATE_ARRAY(
    2,0,
    -1.53553390593274,3.53553390593274,
    -2.94974746830583,2.12132034355964,
    0.585786437626905,-1.41421356237309,
    2,0)) 

PolygonToLine: Geht das auch umgekehrt?

Im PL/SQL Package SDO_UTIL findet sich die Funktion POLYGONTOLINE, welche ein Polygon in eine Linie konvertiert.
Für den umgekehrten Vorgang, eine Linie in ein Polygon umzuwandeln, gibt es (noch) keine solche Funktion im Package. Allerdings kann ich die gerade ganz gut brauchen. Denn für ein Set an Isolinien (SDO_GEOMETRY mit GTYPE=2002) sollen die abgedeckten Flächen berechnet werden. Flächenberechnungen mit SDO_GEOM.AREA sind aber nur für Polygone (inklusive Polygone mit Löchern) erlaubt.
Also schreiben wir uns selbst eine Funktion. Das ist auch nicht weiter schwierig, wie Ihr weiter unten sehen könnt.
Der besseren Lesbarkeit halber habe ich die wichtigsten Erläuterungen als Kommentar in den Code eingefügt.

Viel Spass beim Ausprobieren !

create or replace function line_to_polygon (
  p_line_geom sdo_geometry)
return sdo_geometry deterministic
is 
  p_polygon_geom sdo_geometry;
  k number;
begin
  /* 
   * Geometrietyp prüfen: Muss gleich 2002 für einfache 2D Linien-Geometrie sein.
   * Doku: http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objrelschema.htm#g1013735 
   */
  if p_line_geom.sdo_gtype <> 2002 then
    raise_application_error (-20001, 'Geometrie ist keine einfache 2D Linie.  GTYPE <> 2002.');
  end if;

  p_polygon_geom := p_line_geom;

  -- GTYPE neu setzen für einfache 2D Polygon-Geometrie.
  p_polygon_geom.sdo_gtype := 2003;

  /* 
   * ELEMENT_INFO_ARRAY neu setzen.
   * Doku: http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objrelschema.htm#SPATL494 
   */
  
  p_polygon_geom.sdo_elem_info := sdo_elem_info_array (1, 1003, 1);

  -- Anzahl der Stützpunkte bestimmen
  k := p_polygon_geom.sdo_ordinates.count;

  /* 
   * Polygon muss geschlossen werden. 
   * Daher prüfen, ob letzter Stützpunkt der Linie gleich dem 1. Stützpunkt ist. 
   * Wenn nicht, neuen Stützpunkt einfügen (SDO_ORDINATE_ARRAY wird erweitert um 2 Werte). 
   */

  if (p_polygon_geom.sdo_ordinates(k-1) <> p_polygon_geom.sdo_ordinates(1)
    or p_polygon_geom.sdo_ordinates(k) <> p_polygon_geom.sdo_ordinates(2))
  then
    p_polygon_geom.sdo_ordinates.extend(2);
    p_polygon_geom.sdo_ordinates(k+1) := p_polygon_geom.sdo_ordinates(1);
    p_polygon_geom.sdo_ordinates(k+2) := p_polygon_geom.sdo_ordinates(2);
  end if;
  
  -- Jetzt nur noch die Polygon-Geometrie zurückgeben
  return p_polygon_geom;
end line_to_polygon;
/
sho err

Diejenigen unter Euch, welche Anfang des Jahres im Oracle Spatial Workshop mit Albert Godfrind waren, finden die Funktion auch im Labs/07-processing-Ordner.

Welche Geometrietypen sind in einer SDO_GEOMETRY-Tabelle zu finden?

Dieser Tipp fällt wieder einmal in die Kategorie: Lauter kleine Helferlein.

Fragen Sie sich auch manchmal, welche Geometrietypen eigentlich in einer Tabelle so alles zusammengefaßt sind?
Ohne groß weiter nachzudenken, ist ein Ansatz:

  select distinct g.geom.sdo_gtype
    from geom_tab g
order by 1;

Im Ergebnis erhalte ich die numerische Kodierung der verwendeten Geometrietypen.

Das Ganze könnte ich zum Beispiel mit DECODE nun noch so aufbereiten, dass auch nicht mit dieser Kodierung Vertraute den Inhalt verstehen.

Alternativ - und das ist an dieser Stelle meine Empfehlung - kann ich aber auch die Funktion MIX_INFO im SDO_TUNE-Package benutzen.

execute sdo_tune.mix_info('GEOM_TAB','GEOM');

Die gibt mir die Geometrietypen nicht nur in lesbarer Form zurück, sondern zusätzlich auch gleich noch:

• die Gesamtzahl der Geometrien,
• die Anzahl an Geometrien pro Geometrietyp sowie
• die prozentuale Verteilung pro Geometrietyp

Das sieht für meine Beispiel-Tabelle dann so aus:

Total number of geometries: 5274896
   Point geometries:        0  (0%)
   Curvestring geometries:   0  (0%)
   Polygon geometries:      5274896  (100%)
   Complex geometries:      0  (0%)

Wieviel Speicherplatz brauchen Ihre SDO_GEOMETRY-Tabellen?

In Ergänzung zu meinem ersten Blog-Posting heute, möchte ich noch kurz eine einfache Funktion zum Berechnen des allokierten Speichers hinzufügen. Diese berücksichtigt ausschließlich Tabellen-, Lob- und Index-Segmente (ist also nicht für partitionierte Tabellen anwendbar).

create or replace function alloc_space_in_mbytes(tablename in varchar2)
return number 
is
tablesize number;
begin
  select 
    sum(bytes) into tablesize
  from (
    select 
      segment_name, 
      bytes  
    from 
      user_segments                     -- Tabellensegmente             
    where  
      segment_name = tablename
    union all
    select 
      s.segment_name segment_name, 
      s.bytes bytes
    from 
      user_lobs l,                      -- Lobsegmente
      user_segments s
    where 
      l.table_name = tablename and
      s.segment_name = l.segment_name
    union all
    select 
      s.segment_name segment_name, 
      s.bytes bytes
    from 
      user_indexes i,                   -- Indexsegmente
      user_segments s
    where 
      i.table_name = tablename and
      s.segment_name = i.index_name);

  tablesize := tablesize/1024/1024;     -- Umrechnung in MB
  return tablesize;
end;
/

Der Aufruf der Funktion kann dann wie folgt aussehen:

select
  alloc_space_in_mbytes('GEOM_TABLE_UNTRIMMED') untrimmed, 
  alloc_space_in_mbytes('GEOM_TABLE_TRIMMED') trimmed
from dual
/

Für eine Testtabelle mit 218237 Polygonen, insgesamt 60754462 Stützpunkten und vorhandenem Spatial Index ergaben sich damit folgende Werte:

• SDO_ORDINATE_ARRAY-Werte mit überwiegend 12 bis 13 Nachkommastellen: 1643.5 MB
• SDO_ORDINATE_ARRAY-Werte gekürzt auf 5 Nachkommastellen: 1033.5 MB

NURBS-Kurven in Oracle12c - So geht's

Das heutige Blog-Posting widmet sich einem neuen Spatial-Feature in Oracle12c: den NURBS-Kurven;
diese werden ab Oracle 12.1 als Geometrietyp unterstützt. Die Grundlagen zu NURBS
(Non-Uniform Rational B-Spline) möchte ich im Rahmen dieses Blogs nicht erkläutern; dazu
gibt es eine Reihe guter Literatur und auch online sind eine Menge Informationen verfügbar - als Beispiel
sei der deutschsprachige Wikipedia-Artikel genannt.

Der wesentliche Vorteil einer NURBS-Kurve ist der, dass ein Kurvenverlauf mit wenig
Parametern sehr genau beschrieben werden kann. Dagegen verbraucht eine "Approximation" mit
Stützpunkten und geraden Linien als Verbindung wesentlich mehr Parameter für eine
wesentlich ungenauere Beschreibung der Kurve. Anwendung finden NURBS-Kurven vor allem
im CAD Bereich, aber auch Straßenkurven können mit NURBS-Kurven beschrieben werden.

Das folgende Syntaxbeispiel zeigt, wie eine NURBS-Kurve als SDO_GEOMETRY aufgebaut und
in eine Tabelle gespeichert wird.

insert into nurbs_test values (1, SDO_GEOMETRY(
  2002,                 -- Zweidimensionaler Linienzug
  31468,                -- Koordinatensystem
  NULL,                
  SDO_ELEM_INFO_ARRAY(  
    1, 2, 3             -- 1,2,3 = NURBS-Kurve
  ), 
  SDO_ORDINATE_ARRAY (
    3,                  -- Grad der Kurve (3=Kubisch) "d"
    7,                  -- Es gibt 7 Kontrollpunkte   "m"
    0,   0,   1,        -- 1. Kontrollpunkt
  -50, 100,   1,        -- :
   20, 200,   1, 
   50, 350,   1, 
   80, 200,   1, 
   90, 100,   1, 
   30,   0,   1,        -- 7. Kontrollpunkt
   11,                  -- Der Knotenvektor hat 11 Elemente = d + m + 1
   0,    0,   0,    0,  -- Normalisierter Knotenvektor
   0.25, 0.5, 0.75,     -- Start bei 0 - Ende bei 1
   1,    1,   1,    1   -- Ansteigend 
  )
)
)
/

Zunächst ist eine NURBS-Kurve ein Linienzug, der zwei- oder dreidimensional sein kann;
daher wird der SDO_GTYPE in diesem Fall, wie bei einer geraden Linie, auf 2002 gesetzt.
Der zweite Parameter ist, wie bei anderen Geometrien auch, das Koordinatensystem. Wie bei
den bisher auch schon unterstützten einfachen Kurven (Arc Line Strings) sind geodätische
Koordinatensysteme für NURBS-Kurven nicht zugelassen.

Für eine NURBS-Kurve wird das SDO_ELEM_INFO_ARRAY stets mit 1,2,3 belegt. Das
SDO_ORDINATE_ARRAY besteht aus mehreren Abschnitten:

• Der erste Wert ist der Grad der Kurve, welcher die verwendete Basisfunktion festlegt. Die Basisfunktion
  beschreibt den Verlauf der NURBS-Kurve.
  
  • "1" bedeutet, dass eine lineare Funktion verwendet wird - es ergibt sich eine einfache, zusammengesetzte Linie durch die Kontrollpunkte. Dazu braucht man dann eigentlich keine NURBS-Kurve, daher wird eine "1" wohl nur sehr selten verwendet
  • "2" ist eine quadratische Basisfunktion (die NURBS-Kurve entspricht dann einer Parabel)
  • "3" ist eine kubische Basisfunktion - die Kurve wird dann einen S-Verlauf bekommen
  • "5" ergibt eine mehrfach geschwungene Linie
• Der zweite Wert im SDO_ORDINATE_ARRAY gibt an, wieviele Kontrollpunkte vorliegen. Die Kontrollpunkte
  kann man sich so vorstellen, dass sie gewissermaßen "an der Kurve" ziehen.
  Die NURBS-Kurve wird (außer am Anfang und am Ende) nicht durch die Kontrollpunkte laufen. Jeder Kontrollpunkt
  ist gewichtet, mit der Gewichtung (zwischen 0 und 1) kann man festlegen, wie stark der
  Kontrollpunkt an der Kurve ziehen soll. Gibt man eine 0 an, so wird der Punkt quasi nicht berücksichtigt.



• Der darauf folgende Wert gibt die Anzahl der Elemente im dann folgenden Knotenvektor - hier
  muss immer der Wert (d + m + 1) stehen - also der Grad der Kurve plus die Anzahl der Kontrollpunkte
  plus eins. Für eine Kurve dritten Grades mit 7 Kontrollpunkten steht dort also eine 11.



• Der dann folgende Knotenvektor legt den Kurvenverlauf nicht direkt fest (das tun ja schon die Kontrollpunkte),
  sie haben nur indirekt Einfluß. Die absoluten Werte sind ohne Bedeutung, interessant ist allein das
  Verhältnis der Differenzen zueinander. Die Knotenvektoren 0,0,1,2,3,4,4 bedeuten also das
  gleiche wie 1,1,2,3,4,5,5 oder 0,0,0.25,0.50.0.75,1,1. Ein Knotenvektor muss steigend sein.
  Oracle erwartet normalisierte Werte (also zwischen 0 und 1). Ferner müssen die ersten "d+1" Werte gleich 0 und die letzten "d+1" Werte gleich 1 sein (siehe obiges Beispiel).

Für den Umgang mit der NURBS-Kurve gelten die gleichen Regeln für für SDO_GEOMETRY im allgemeinen; der Spatial Index wird erstellt wie immer (der Index löst die NURBS-Kurve übrigens intern in einen "normalen" Linestring auf). Die Validierung einer NURBS-Kurve erfolgt ebenfalls (wie immer) mit SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT. Würde man in obigem Beispiel also einen Knotenvektor mit 12 Elementen verwenden, so sähe die Validierung der Nurbs-Kurve wie folgt aus:

SQL> select gid, sdo_geom.validate_geometry_with_context(geom, 1) as valid 
  2  from nurbs_test where gid=1

       GID VALID
---------- --------------------
         1 13072 [Element <1>]

1 Zeile wurde ausgewählt.

SQL> exit
 
$ oerr ora 13072
13072, 00000, "incorrect number of knots for SDO_GEOMETRY object"
//     *Cause: An incorrect number of knot values was passed for a Non-Uniform
//             Rational B-Spline curve.
//     *Action: Correct the number of knot values passed for the Non-Uniform
//              Rational B-Spline curve.

Der Oracle MapViewer und Oracle Maps können NURBS-Kurven per Juli 2013 noch nicht darstellen; hier kann die SQL-Funktion SDO_GEOM.GETNURBSAPPROX weiterhelfen - die konvertiert die NURBS-Kurve explizit in einen "klassischen" zusammengesetzten Linienzug. Die folgende Abbildung zeigt die Appriximation der NURBS-Kurve aus dem Beispiel oben - die Knotenpunkte sind ebenfalls hinzugefügt.

SQL> select SDO_UTIL.GetNurbsApprox(geom, 0.05) as geom from nurbs_test where gid = 1;

GEOM
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 31468, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY
(0, 0, -2,9128395, 5,96995228, -5,6243745, 11,8211319, -8,1393559, 17,5559751, -
10,462535, 23,1769184, -12,598662, 28,6863981, -14,552488, 34,0868505, -16,32876
4, 39,380712, -17,932241, 44,5704191, -19,36767, 49,6584079, -20,639802, 54,6471
15, -21,753387, 59,5389767, -22,713177, 64,3364292, -23,523922, 69,0419091, -24,
190374, 73,6578527, -24,717284, 78,1866962, -25,109401, 82,6308762, -25,371477,
:

Viel Spaß beim Ausprobieren.