PostGIS

GEOMETRY CONSTRUCTORS

  • ST_GeomFromText
  • ST_PointFromText
  • ST_PolygonFromText
  • ST_LineFromText

   vstup: (wkt, [srid])

A co je to ten text?

  • ST_GeomFromGeoJSON
  • ST_GeomFromGML
  • ST_GeomFromGeoHash
  • ...

DATA

OpenStreetMap - export z Geofabrik

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

import do PostGIS (minulé cvičení)

DÁME ZÁVOD?!

JAK VYBRAT DATA?

CREATE TABLE railways_jm AS 
  SELECT railways.* FROM railways
  JOIN regions ON ST_Intersects(railways.geom, regions.geom)
  WHERE regions."NAZEV" = 'Jihomoravský';
 Nested Loop  (cost=0.00..225402.75 rows=249 width=212) (actual time=18.886..9911.633 rows=74253 loops=1)
   Join Filter: ((roads.geom && regions.geom) AND _st_intersects(roads.geom, regions.geom))
   Rows Removed by Join Filter: 672386
   ->  Seq Scan on regions  (cost=0.00..12.62 rows=1 width=32) (actual time=0.016..0.018 rows=1 loops=1)
         Filter: (("NAZEV")::text = 'Jihomoravský'::text)
         Rows Removed by Filter: 13
   ->  Seq Scan on roads  (cost=0.00..29397.39 rows=746639 width=212) (actual time=16.453..1662.866 rows=746639 loops=1)
 Total runtime: 9988.853 ms
(8 rows)

// bez prostorového indexu

/*
EXPLAIN ANALYZE SELECT roads.* FROM roads
JOIN regions ON ST_Intersects(roads.geom, regions.geom)
WHERE regions."NAZEV" = 'Jihomoravský';
*/
 Nested Loop  (cost=4.85..324.26 rows=249 width=214) (actual time=45.102..5101.472 rows=74253 loops=1)
   ->  Seq Scan on regions  (cost=0.00..12.62 rows=1 width=32) (actual time=0.015..0.018 rows=1 loops=1)
         Filter: (("NAZEV")::text = 'Jihomoravský'::text)
         Rows Removed by Filter: 13
   ->  Bitmap Heap Scan on roads  (cost=4.85..311.38 rows=25 width=214) (actual time=45.079..4931.495 rows=74253 loops=1)
         Recheck Cond: (geom && regions.geom)
         Rows Removed by Index Recheck: 154841
         Filter: _st_intersects(geom, regions.geom)
         Rows Removed by Filter: 71212
         ->  Bitmap Index Scan on roads_idx  (cost=0.00..4.85 rows=75 width=0) (actual time=40.142..40.142 rows=145465 loops=1)
               Index Cond: (geom && regions.geom)
 Total runtime: 5181.459 ms
(12 rows)

// gist index

/*
EXPLAIN ANALYZE SELECT roads.* FROM roads
JOIN regions ON ST_Intersects(roads.geom, regions.geom)
WHERE regions."NAZEV" = 'Jihomoravský';
*/
 Nested Loop  (cost=0.29..21.19 rows=1 width=214) (actual time=3.041..3850.302 rows=74253 loops=1)
   ->  Seq Scan on regions  (cost=0.00..12.62 rows=1 width=32) (actual time=0.021..0.024 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=214) (actual time=2.938..3681.432 rows=74253 loops=1)
         Index Cond: ((geom && regions.geom) AND (geom && regions.geom))
         Filter: _st_intersects(geom, regions.geom)
         Rows Removed by Filter: 71212
 Total runtime: 3930.270 ms
(9 rows)

// gist index

/*
EXPLAIN ANALYZE SELECT roads.* FROM roads
JOIN regions ON roads.geom && regions.geom
WHERE regions."NAZEV" = 'Jihomoravský' AND ST_Intersects(roads.geom, regions.geom);
*/
CREATE INDEX tablename_idx ON tablename USING GIST(geometry_column);

ALTER TABLE tablename ADD PRIMARY KEY (column_name);

PRACTICE MAKES PERFECT

  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.
SELECT SUM(ST_Area(geom)) FROM buildings_jm;

SELECT SUM(ST_Length(geom)) FROM waterways_jm;

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

SELECT name, ST_Length(geom) / 1000 AS length 
    FROM waterways_jm 
    ORDER BY length DESC 
    LIMIT 10;

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

// nebo

SELECT b.* FROM buildings_jm b 
    JOIN buildings_jm 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_jm WHERE name = 'Litava';

WITH morava AS (
    SELECT name, ST_LineMerge(ST_Union(geom)) AS geom
    FROM waterways_jm
    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;
Made with Slides.com