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

Koordinatenwerte in SDO_ORDINATE_ARRAY auf sinnvolles Mass an Nachkommastellen trimmen

Kürzlich hatte ich Gelegenheit, Blick auf SDO-GEOMETRY-Daten zu werfen, die Gaus-Krüger 3 als Bezugssystem hatten. Was mir dabei auffiel, war die immens hohe Anzahl an Nachkommastellen bei den Koordinatenpaaren (Rechts- und Hochwert). Teilweise 30 Stellen und mehr.

Aus fachlicher Sicht macht das natürlich keinen Sinn. 2 oder 3 (cm, mm) Nachkommastellen sind üblich.
Auch technisch gesehen bedingen mehr Nachkommastellen sowohl mehr Speicherbedarf als auch möglicherweise negative Einflüsse auf die Performance bei Pflege und Abfragen.

Wie kann man aber nun schnell und ohne großen Aufwand, die Nachkommastellen trimmen?

Eine entsprechende Funktion in PL/SQL Package SDO_UTIL gibt es leider (noch) nicht. Das macht aber nichts. Denn der PL/SQL-Code dafür ist schnell geschrieben:

create or replace procedure trim_ordinates(
  p_geom in out nocopy sdo_geometry,
  p_prec in number default 5                -- Anzahl der Nachkommastellen
) is
begin
  for i in 1..p_geom.sdo_ordinates.count loop
    p_geom.sdo_ordinates(i) := round(p_geom.sdo_ordinates(i), p_prec);
  end loop;
end trim_ordinates;
/

create or replace function func_trim_ordinates(
  p_geom in sdo_geometry,
  p_prec in number default 2
) return sdo_geometry is
  l_geom sdo_geometry := p_geom ;
begin
  trim_ordinates(l_geom, p_prec);
  return l_geom;
end func_trim_ordinates;
/

sho err

-- Ergebnis der Funktion testen
select func_trim_ordinates(geom,3) from geom_tab;
/

-- Update durchführen
update geom_tab set geom=func_trim_ordinates(geom,3)
/
commit
/

Was spare ich ein an Speicherplatz?
Um diese Frage zu beantworten, überprüfe ich, wieviel Bytes für einen Rechts- bzw. Hochwert mit 30 Nachkommastellen benötigt werden versus einem mit 3.

select 
  vsize(5397831.164000000816421415421630849375) "Bytes_30_Nachkommastellen", 
  vsize(5397831.164) "Bytes_3_Nachkommastellen" 
from dual
/

20 Bytes werden für 30 Nachkommastellen benötigt, aber nur 7 Bytes bei 3 Nachkommastellen.
Bei einem Koordinatenpaar sind das 14 Bytes pro Stützpunkt.
Es ist also durchaus sinnvoll, sich über die notwendige Genauigkeit für die Speicherung von Geodaten Gedanken zu machen.

Kleines Experiment mit POINTONSURFACE

Im Package SDO_GEOM gibt es die Funktion SDO_POINTONSURFACE. Diese ermittelt einen Punkt, der räumlich auf der Oberfläche eines Polygons liegen soll.
Aber wie wird dieser Punkt ermittelt? Welches Ergebnis ist zu erwarten?

Um diese Fragen zu beantworten, habe ich mir eine Prozedur geschrieben, welche diese Funktion mehrfach aufruft und die zurückgegebenen Punktgeometrien in eine Tabelle schreibt.

drop table points purge;

create table points (
  id number,
  geom sdo_geometry,
  constraint points_pk primary key (id) enable);

declare
  type t_points is table of points%ROWTYPE;
  l_tab t_points := t_points();
  l_size number := 10;
  l_lastid number;
  l_pointonsurface sdo_geometry;
  l_surface sdo_geometry;

begin
  -- Fetch last id from points table
  select nvl(max(id),1) + 1 into l_lastid from points;
  select s.geom into l_surface from us_state s where s.name = 'California';

  -- Populate collection
  for i in 1 .. l_size LOOP
    -- Call POINTONSURFACE
    select sdo_geom.sdo_pointonsurface(l_surface, 0.05) into l_pointonsurface from dual;
    l_tab.extend;
    l_tab(l_tab.last).id := l_lastid;
    l_tab(l_tab.last).geom := l_pointonsurface;
    l_lastid := l_lastid + 1;

  end loop;
 
 -- Ingest table with points
  forall i in l_tab.first .. l_tab.last
    insert /*+ APPEND_VALUES */ into points values l_tab(i);
  commit; 
end;
/

Schaue ich mir die 10 Sätze in der Tabelle POINTS an, finde ich immer die gleichen Punktgeometrien: Lon=-123.00111 und Lat=37.772053.

select p.geom.sdo_point.x lon, p.geom.sdo_point.y lat from points p;

Wer also die Idee hat, diese Funktion zu verwenden, um eine Tabelle mit zufälligen Punktgeometrien zu befüllen, muss einen anderen Lösungsansatz wählen. (Einen solchen Lösungsansatz habe ich in diesem Blog-Posting vorgestellt.)

Aber was ist denn jetzt das "Geheimnis" hinter SDO_POINTONSURFACE?
Die Antwort ist (einfach):
Es wird der 1. Stützpunkt der Flächengeometrie zurückgegeben, die als Eingabeparameter übergeben wird. (Das ist in meinem Beispiel der Bundesstaat Kalifornien.)

select t.x, t.y, t.id
from us_state s, table(sdo_util.getvertices(s.geom)) t
where s.name = 'California' and t.id = 1;

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.

Performance bei Koordinaten-Transformationen: TRANSFORM vs. TRANSFORM_LAYER

Wenn es darum geht, die Koordinaten in einer SDO_GEOMETRY-Spalte in ein anderes
Koordinatensystem umzurechnen, nutzt man SDO_CS.TRANSFORM - ein Beispiel:

create table m_admin_area4_t31467 as
select
  feature_id,
  feature_name,
  area_id,
  name_langcode,
  feature_type,
  country_code_3,
  sdo_cs.transform(geometry, 31467) as geometry
from m_admin_area4;

Tabelle wurde erstellt.

Abgelaufen: 00:06:14.40

Die Tabelle enthält knapp 14.000 Polygone - die Umrechnung dauerte etwas mehr als
6 Minuten. So weit, so gut. Die Frage wäre allerdings, ob es nicht noch schnellere
Varianten gibt - denn stellt man sich eine Tabelle mit 100.000 Polygonen vor, wären
wir schon bei einer Stunde - und so weiter und so fort.
Es ist nicht überall bekannt, dass es neben SDO_CS.TRANSFORM noch eine weitere
Funktion SDO_CS.TRANSFORM_LAYER gibt, welche eine ganze Tabelle auf einmal
umrechnet. TRANSFORM_LAYER soll lt. Dokumentation dabei effizienter arbeiten als TRANSFORM - dafür
ist der Umgang damit etwas aufwändiger. Testen wir das einmal: Die Tabelle, die
umrechnet werden soll, muss einen Eintrag in der View USER_SDO_GEOM_METADATA
haben - ein Spatial-Index dagegen kann, muss aber nicht vorhanden sein. Zuerst sollte
man dies überprüfen.

select table_name, column_name, srid 
from user_sdo_geom_metadata 
where table_name like 'M_ADMIN%'

TABLE_NAME                       COLUMN_NAME                SRID
-------------------------------- -------------------- ----------
M_ADMIN_AREA0                    GEOMETRY                   8307
M_ADMIN_AREA1                    GEOMETRY                   8307
M_ADMIN_AREA2                    GEOMETRY                   8307
M_ADMIN_AREA3                    GEOMETRY                   8307
M_ADMIN_AREA4                    GEOMETRY                   8307
:                                :                          :

Danach kann SDO_CS.TRANSFORM_LAYER ausgeführt werden.

begin
  sdo_cs.transform_layer('M_ADMIN_AREA4', 'GEOMETRY', 'M_ADMIN_AREA4_DST', 31467);
end;
/

Abgelaufen: 00:01:38.95

Das ging schon wesentlich schneller - aber wenn wir uns die Ergebnistabelle ansehen,
dann bemerken wir schnell, dass wir noch nicht ganz fertig sind.

SQL> desc M_ADMIN_AREA4_DST
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 SDO_ROWID                                          ROWID
 GEOMETRY                                           MDSYS.SDO_GEOMETRY

Denn diese Tabelle enthält die umgerechneten Geometrie-Objekte nebst einer ROWID aus
der Quelltabelle. Wenn man nun also die "finale" Tabelle erzeugen möchte, die
aus genau den gleichen Spalten wie die Quelltabelle besteht, so braucht es einen
weiteren Schritt.

create table m_admin_area4_t31467 as
select  
  q.feature_id,
  q.feature_name,
  q.area_id,
  q.name_langcode,
  q.feature_type,
  q.country_code_3,
  i.geometry
from 
  m_admin_area4 q join m_admin_area4_dst i on (i.sdo_rowid = q.rowid)
/

Tabelle wurde erstellt.

Abgelaufen: 00:00:20.68

Diese 20 Sekunden müssen noch hinzugerechnet werden - denn erst nun ist die
gewünschte Tabelle erstellt. Es ergeben sich also etwa 2 Minuten. Verglichen mit
den 6 Minuten und 14 Sekunden ist das doch eine erhebliche Verbesserung! Der Grund dafür
sind Initialisierungen, die bei SDO_CS.TRANSFORM immer wieder und bei TRANSFORM_LAYER
nur einmal aufgerufen werden. Allerdings ist
dieser Ansatz nicht für on-the-fly Transformationen geeignet, da man stets die
"Zwischentabelle" generieren muss. Aber im Rahmen von Ladeprozessen kann man hiermit
durchaus eine Menge Zeit sparen ...