Geo-Datentypen

Geographische Referenzsysteme (spatial reference systems)

• Sphärisch (SRID 4326) - gesamte Welt, GPS
• Planar (SRID 31468)- Deutschland

Point - U-Bahnhöfe Berlin

Linestring - Straßensegmente Schöneweide

Polygon - HTW Campus Wilhelminenhof

Kartesische und sphärische Koordinatensystsme

Planare Projektion

Geometry-Typ vs. Geography-Typ

Distanzberechnungen

SQL

select st_distance(
  -- Berlin
  st_point(13.4049, 52.5200, 4326),
  -- München
  st_point(11.5667, 48.1333, 4326)    
) as dst;

select st_distance(
  -- Berlin
  st_point(13.4049, 52.5200, 4326)::geography,
  -- München
  st_point(11.5667, 48.1333, 4326)::geography    
) as dst;

select st_distance(
  -- Berlin
  st_transform(
    st_point(13.4049, 52.5200, 4326), 
    31468
  ),
  -- München
  st_transform(
    st_point(11.5667, 48.1333, 4326), 
    31468
  )    
) as dst;

Formeln zur Distanzberechnung

Geo-Index: Strukturen

• Räumliche Abfragen sind komplex
• Welche Grundstücke schneiden diese Straße?
• Welche Cafés liegen innerhalb dieses Stadtteils?
• Welche Krankenhäuser sind im Umkreis von 5 km?
• Ohne Index müsste für jeden einzelnen Datensatz die komplexe geometrische Berechnung durchgeführt werden
• GiST-Index (Generalized Search Tree) basiert auf R-Tree
• Keine Speicherung der komplexe Geometrie (z.B. ein Polygon mit 1000 Punkten)
• Sondern kleinstmögliches Rechteck, das eine Geometrie vollständig umschließt
• Vergleiche zwischen Bounding Boxes (BB) sind schnell
• 
• Welche Objekte schneiden das gelbe Dreieck
  
• Filterschritt: Welche BB der Objekte schneiden die BB des gelben Dreiecks
• Das liefert (wenige) Kandidaten
  
• Verfeinerungsschritt: Exakte Suche nur in Kandidaten
  

Geo-Index: Performance

Anzahl Straßensegmente in Pankow (83846)

In der Abfrage wird ein Geo-Join durchgeführt

select count(*)
from bln_edges be 
     join bln_bezirk_museum bm on st_intersects(be.geometry, bm.shape)
where bm.name='Pankow'

Code zur Index-Erzeung

create index idx_bln_edges_geometry on ugeobln.bln_edges using gist (geometry)

Nur Bounding-Box-Suche

select count(*)
from bln_edges be 
     join bln_bezirk_museum bm on be.geometry && bm.shape
where bm.name='Pankow'

Dauert nur 0.055 Sekunden, liefert aber ungenaue Ergebnisse, da kein Verfeinerungsschritt

Geo-Funktionen

Zentroid eines Polygons

select name, geometry, st_centroid(geometry) as center
from gis_osm_pois_a_free_1 
where osm_id ='41361350';

Box

select ST_MakeEnvelope(13.5233,52.4572,13.5322,52.4602, 4326);

Intersect

select null, null, null, null,
  ST_MakeEnvelope(13.5233,52.4572,13.5322,52.4602, 4326)
union all
select *
from gis_osm_pois_a_free_1 
where st_intersects(ST_MakeEnvelope(13.5233,52.4572,13.5322,52.4602, 4326), geometry);

Bounding Box

select name, shape from bezirk
union all
select 'bb' as name, ST_Extent(shape) as shape from bezirk;

Distance

select * 
from gis_osm_buildings_a_free_1 b
order by b.geometry <-> st_point(13.5270, 52.4585, 4326)
limit 300;

Voronoi

select pos from haltestelle
union all
select ST_VoronoiPolygons(st_collect(pos)) as shape from haltestelle h;

Area

with 
  pi as (
    select * 
    from gis_osm_places_a_free_1 
    where name = 'Pfaueninsel'
  )
select b.geometry
from gis_osm_buildings_a_free_1 b join pi on st_contains(pi.geometry, b.geometry)
union all
select  pi.geometry from pi;

with 
  pi as (
    select geometry
    from gis_osm_places_a_free_1 
    where name = 'Pfaueninsel'
  ),
  d as (
    select
      round(min(st_area(pi.geometry::geography))) as flaeche_insel,
      round(sum(st_area(b.geometry::geography))) as flaeche_gebaeude
    from gis_osm_buildings_a_free_1 b join pi on st_contains(pi.geometry, b.geometry)
  )
select *, d.flaeche_insel-d.flaeche_gebaeude as diff from d;