08. Úvod do prostorového SQL - prostorové predikáty a funkce

Přednáška

Prostorové predikáty a funkce

Prostorové predikáty

-- relate

select st_geomfromtext('LINESTRING(-1 1, 0 0, 1 0, 3 1)') as geom
union
select st_geomfromtext('LINESTRING(-1 -1, 0 0, 1 0, 3 -1)') as geom;

select st_relate(
st_geomfromtext('LINESTRING(-1 1, 0 0, 1 0, 3 1)'),
st_geomfromtext('LINESTRING(-1 -1, 0 0, 1 0, 3 -1)'),
'1*1***1**'
);

select st_geomfromtext('POLYGON((0 0, 1 0, 2 1, 3 2, 3 3, 0 3, 0 0))') as geom
union
select st_geomfromtext('LINESTRING(1 0, 2 1, 1.5 2)') as geom;

select st_relate(
st_geomfromtext('POLYGON((0 0, 1 0, 2 1, 3 2, 3 3, 0 3, 0 0))'),
--st_geomfromtext('LINESTRING(1 1, 0.5 2)'),
st_geomfromtext('LINESTRING(1 0, 2 1, 1.5 2)'),
--st_geomfromtext('LINESTRING(2 1, 1.5 2)'),
--st_geomfromtext('LINESTRING(2.5 0, 2 2)'),
'1**10*F**'
);

-- equals

select st_geomfromtext('LINESTRING(0 0, 1 1)') as geom
union
select st_geomfromtext('LINESTRING(0 0, 1 1)') as geom;

select st_relate(
st_geomfromtext('LINESTRING(0 0, 1 1)'),
st_geomfromtext('LINESTRING(0 0, 1 1)'),
'TFFFTFFFT'
);

-- disjoint

select st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as geom
union
select st_geomfromtext('POLYGON((2 0, 3 0, 3 1, 2 1, 2 0))') as geom;

select st_relate(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('POLYGON((2 0, 3 0, 3 1, 2 1, 2 0))'),
'FF*FF****'
);

-- intersects

select st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as geom
union
select st_geomfromtext('POLYGON((0.5 0, 1.5 0, 1.5 1, 1.5 1, 0.5 0))') as geom;

select st_relate(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('POLYGON((0.5 0, 1.5 0, 1.5 1, 1.5 1, 0.5 0))'),
'TT*TT****'
);

-- touches

select st_geomfromtext('LINESTRING(0 0, 2 0)') as geom
union
select st_geomfromtext('LINESTRING(1 0, 1 1)') as geom;

select st_relate(
st_geomfromtext('LINESTRING(0 0, 2 0)'),
st_geomfromtext('LINESTRING(1 0, 1 1)'),
'FT*******'
);

select st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as geom
union
select st_geomfromtext('POLYGON((1 0, 2 0, 2 1, 2 1, 1 0))') as geom;

select st_relate(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('POLYGON((1 0, 2 0, 2 1, 2 1, 1 0))'),
'F***T****'
);

-- crosses

select st_geomfromtext('LINESTRING(0 0, 2 2)') as geom
union
select st_geomfromtext('LINESTRING(2 0, 0 2)') as geom;

select st_relate(
st_geomfromtext('LINESTRING(0 0, 2 2)'),
st_geomfromtext('LINESTRING(2 0, 0 2)'),
'0********'
);

select st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as geom
union
select st_geomfromtext('LINESTRING(0.5 0.5, 2 2)') as geom;

select st_relate(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('LINESTRING(0.5 0.5, 2 2)'),
'T*T******'
);

-- within

select st_geomfromtext('LINESTRING(0.5 0.5, 1 1)') as geom
union
select st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as geom;

select st_relate(
st_geomfromtext('LINESTRING(0.5 0.5, 1 1)'),
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
'T*F**F***'
);

-- contains

select st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as geom
union
select st_geomfromtext('LINESTRING(0.5 0.5, 0.9 0.9)') as geom;

select st_relate(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('LINESTRING(0.5 0.5, 0.9 0.9)'),
'T*T**T***'
);

Cvičení

Opakování úloh z předmětu GIS1 2. cvičení a 3. cvičení.

Otázky jsou postaveny nad datasetem ArcČR v3.3 (S:\K155\Public\data\GIS\ArcCR500 3.3).

Note

Data nejprve uložíme do prostorové databáze SpatiaLite. Ta nám v porovnání s virtuálními vrstvami FileGDB přinese lepší výkon. V datovém prohližeči zvolíme vrstvu ve formátu FileGDB a v kontextovém menu Export Layer > To File.

Seznam vrstev:

Letiste
Lesy
KladyZakladnichMap
Silnice_2015
BazinyARaseliniste
ChranenaUzemi
VyskoveKoty
VodniPlochy
ZeleznicniStanice
OkresyPolygony
KrajePolygony
ObcePolygony
StatPolygon

Prostorové SQL - predikáty (první část)
Prostorové SQL - funkce (první část)

Praktická úloha

Stáhněte si dataset Data50 a do nové prostorové databáze SpatiaLite nahrajte následující vrstvy:

ChraneneUzemi
SilniceDalnice

Určete, jak je dlouhý úsek v km dálnice D8, který prochází chráněným krajinným územím.

SELECT nazev, sum(st_length(geometry))/1000 AS delka_km
FROM (
 SELECT s.nazev,st_intersection(s.geometry, chu.geometry) AS geometry
 FROM silnicedalnice AS s
 JOIN chraneneuzemi AS chu
 ON st_intersects(s.geometry, chu.geometry)) as s
GROUP BY nazev
HAVING nazev = 'D8';

Úkol

Porovnejte výsledek u dálnice D8 s datasetem ArcČR500.

SELECT cislo_silnice, sum(st_length(geometry))/1000 AS delka_km
FROM (
 SELECT cislo_silnice,st_intersection(s.geometry, chu.geometry) AS geometry
 FROM silnice_2015 AS s
 JOIN chranenauzemi AS chu
 ON st_intersects(s.geometry, chu.geometry)) as s
GROUP BY cislo_silnice
HAVING cislo_silnice = 'D8';

Úkol

Proveďte analýzu pro všechny silnice a seřaďte je podle celkové délky:

SELECT nazev, s.typsil,sum(st_length(geometry))/1000 AS delka_km
FROM (
 SELECT s.nazev,s.typsil,st_intersection(s.geometry, chu.geometry) AS geometry
 FROM silnicedalnice AS s
 JOIN chraneneuzemi AS chu
 ON st_intersects(s.geometry, chu.geometry)) as s
GROUP BY nazev
ORDER BY delka_km desc;