Geodatabáze.jecool.net

Lecture 9

Z3104 GEODATABÁZE

Obsah

  • Opakovaní - indexy
  • Základy prostorových funkcí
  • DIY - Do It Yourself

GEOMETRY CONSTRUCTORS

  • ST_GeomFromText
  • ST_PointFromText
  • ST_PolygonFromText
  • ST_LineFromText
  • ST_GeomFromGeoJSON
  • ST_GeomFromGML
  • ST_GeomFromGeoHash
  • ...

A co je ten text?

(wkt, [srid]) 


SELECT ST_PointFromText('POINT(-71.064544 42.28787)', 4326);

DATA

OpenStreetMap - export z Geofabrik

silnice, železnice, vodní toky, budovy - poskytovna Z3104

import do PostGIS (pozor na kódování)

DÁME ZÁVOD?!

Jak vybrat data?


CREATE TABLE railways_jm AS
  SELECT railways.* FROM railways
  JOIN kraje ON ST_Intersects(railways.the_geom, kraje.the_geom)
  WHERE kraje."NAZEV" = 'Jihomoravský';

Vysledek - bez prostoroveho indexu


EXPLAIN ANALYZE
  SELECT roads.* FROM roads
  JOIN kraje ON ST_Intersects(roads.the_geom, kraje.the_geom)
  WHERE kraje."NAZEV" = 'Jihomoravský';
            

"Nested Loop  (cost=0.00..479488.80 rows=561 width=218) (actual time=3.225..3157.865 rows=83504 loops=1)"
"  Join Filter: ((roads.the_geom && kraje.the_geom) AND _st_intersects(roads.the_geom, kraje.the_geom))"
"  Rows Removed by Join Filter: 757822"
"  ->  Seq Scan on roads  (cost=0.00..33572.26 rows=841326 width=218) (actual time=0.012..112.022 rows=841326 loops=1)"
"  ->  Materialize  (cost=0.00..13.76 rows=2 width=32) (actual time=0.000..0.000 rows=1 loops=841326)"
"        ->  Seq Scan on kraje  (cost=0.00..13.75 rows=2 width=32) (actual time=0.011..0.014 rows=1 loops=1)"
"              Filter: (("NAZEV")::text = 'Jihomoravský'::text)"
"              Rows Removed by Filter: 13"
"Total runtime: 3162.955 ms"

Vysledek - gist index


EXPLAIN ANALYZE
  SELECT roads.* FROM roads
  JOIN kraje ON ST_Intersects(roads.the_geom, kraje.the_geom)
  WHERE kraje."NAZEV" = 'Jihomoravský';
            

"Nested Loop  (cost=4.92..349.87 rows=280 width=218) (actual time=26.372..1784.974 rows=83504 loops=1)"
"  ->  Seq Scan on kraje  (cost=0.00..1.18 rows=1 width=32) (actual time=0.010..0.014 rows=1 loops=1)"
"        Filter: (("NAZEV")::text = 'Jihomoravský'::text)"
"        Rows Removed by Filter: 13"
"  ->  Bitmap Heap Scan on roads  (cost=4.92..348.41 rows=28 width=218) (actual time=26.358..1772.682 rows=83504 loops=1)"
"        Recheck Cond: (the_geom && kraje.the_geom)"
"        Rows Removed by Index Recheck: 151437"
"        Filter: _st_intersects(the_geom, kraje.the_geom)"
"        Rows Removed by Filter: 79167"
"        ->  Bitmap Index Scan on roads_idx  (cost=0.00..4.92 rows=84 width=0) (actual time=23.804..23.804 rows=162671 loops=1)"
"              Index Cond: (the_geom && kraje.the_geom)"
"Total runtime: 1789.744 ms"

Vysledek - gist index + lepsi vyber


EXPLAIN ANALYZE
  SELECT roads.* FROM roads
  JOIN kraje ON roads.geom && regions.the_geom
  WHERE kraje."NAZEV" = 'Jihomoravský' AND ST_Intersects(roads.the_geom, regions.the_geom);
            

"Nested Loop  (cost=0.29..9.74 rows=1 width=218) (actual time=144.480..1354.557 rows=83504 loops=1)"
"  ->  Seq Scan on kraje  (cost=0.00..1.18 rows=1 width=32) (actual time=0.013..0.017 rows=1 loops=1)"
"        Filter: (("NAZEV")::text = 'Jihomoravský'::text)"
"        Rows Removed by Filter: 13"
"  ->  Index Scan using roads_idx on roads  (cost=0.29..8.55 rows=1 width=218) (actual time=144.400..1342.508 rows=83504 loops=1)"
"        Index Cond: ((the_geom && kraje.the_geom) AND (the_geom && kraje.the_geom))"
"        Filter: _st_intersects(the_geom, kraje.the_geom)"
"        Rows Removed by Filter: 79167"
"Total runtime: 1359.467 ms"

Vytváření indexů


CREATE INDEX tablename_idx ON tablename USING GIST(geometry_column);
            

ALTER TABLE tablename ADD PRIMARY KEY (column_name);
            

SELECT SUM (ST_Area(the_geom)) FROM kraje;

9.84435847853486


SELECT SUM(ST_Area(ST_Transform(the_geom,31467))) FROM kraje;

79352702971.7372


SELECT SUM (ST_Area(the_geom::geography)) FROM kraje;

78922129910.3022

DIY - Do It Yourself

Úkol 1

  1. Jakou plochu zabírají všechny budovy v Jihomoravském kraji?
  2. Jak dlouhé jsou všechny vodní toky v Jihomoravském kraji?
  3. Jaká zástavba se v Jihomoravském kraji objevuje nejvíce? Jakou zabírá plochu?
  4. Jaké vodní toky jsou v Jihomoravském kraji nejdelší?
  5. Vyberte domy, které jsou ve vzdálenosti 500 m od Semilassa. Zobrazte si je v QGISu.
  6. Spočítejte koeficient křivolakosti Litavy a Moravy.
    • příkazy ST_StartPoint a ST_EndPoint nevracejí správné výsledky
    • podívejte se na správné řešení a zkuste zjistit postup výpočtu koeficientu.

Řešení


SELECT SUM(ST_Area(geom)) FROM buildings;

SELECT SUM(ST_Length(geom)) FROM waterways;

SELECT type, SUM(ST_Area(geom)) / 1000000 AS area
    FROM buildings
    WHERE type IS NOT NULL
    GROUP BY type
    ORDER BY area DESC;

SELECT name, SUM(ST_Length(geom)) / 1000 AS length
    FROM waterways
    WHERE name IS NOT NULL
    GROUP BY name
    ORDER BY length DESC
    LIMIT 10;

SELECT b.* FROM buildings AS b
    JOIN buildings b2 ON ST_Distance(b.geom, b2.geom) <= 500
    WHERE b2.name = 'Semilasso';

// nebo

SELECT b.* FROM buildings AS b
    JOIN buildings b2 ON ST_DWithin(b.geom, b2.geom, 500)
    WHERE b2.name = 'Semilasso';

SELECT
    name, ST_Length(geom) AS length, ST_Length(ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom))) AS line,
    ST_Length(geom)/ST_Length(ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom))) AS idx
    FROM waterways WHERE name = 'Litava';

WITH morava AS (
    SELECT name, ST_LineMerge(ST_Union(geom)) AS geom
    FROM waterways
    WHERE name = 'Morava'
    GROUP BY name
)
SELECT
    name,
    ST_Length(geom) AS length,
    ST_Length(ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom))) AS line,
    ST_Length(geom) / ST_Length(ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom))) AS idx
    FROM morava;

Děkuji za pozornost