Diskuse:155UZPR / Semestrální projekt: Porovnání verzí

Z GeoWikiCZ
mBez shrnutí editace
(Není zobrazeno 45 mezilehlých verzí od stejného uživatele.)
Řádek 1: Řádek 1:
== Přístup k databázi pgis_uzpd ==
== Přístup k databázi uzpr_projekty ==


=== Přihlášení k databázi ===
* databáze: <tt>uzpr_projekty</tt>
* host: <tt>geo102.fsv.cvut.cz</tt>
* uživatelské jméno: <tt>uzpr<rok>_<skupina></tt>, např. <tt>uzpr21_a</tt>


* host: <tt>geo102.fsv.cvut.cz</tt>
Příklad pro skupinu A v akademickém roce 2020/2021
* uživatelské jméno: <tt>uzpd_<skupina><rok></tt>, např. <tt>uzpd_a11</tt>


Příklad pro skupinu A v roce 2011
psql uzpr_projekty -U uzpr21_a -h geo102.fsv.cvut.cz -W


psql pgis_uzpd -U uzpd_a11 -h geo102.fsv.cvut.cz -W
{{fig|qgis-uzpd-connect|Přihlašovací dialog v programu QGIS|size=300}}


<!--
[[Image:pgadmin3-uzpd.png|center|thumb|300px|Přihlašovací dialog pgadmin3 pro databázi pgis_uzpd]]
[[Image:pgadmin3-uzpd.png|center|thumb|300px|Přihlašovací dialog pgadmin3 pro databázi pgis_uzpd]]
[[Image:pgadmin3-uzpd1.png|center|thumb|640px|Příklad dotazu v prostředí PgAdmin3]]
[[Image:pgadmin3-uzpd1.png|center|thumb|640px|Příklad dotazu v prostředí PgAdmin3]]
-->


=== Vstupní data ===
== Vstupní data ==
 
Databáze obsahuje ve schématu '''osm''' data [[OpenStreetMap]] ČR ze dne 9.3.2011.


<source lang=sql>
<source lang=sql>
SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_schema = 'osm';
SELECT * FROM geometry_columns;
</source>
</source>


<pre>
== Transformace dat ==
f_table_name  | f_geometry_column |  srid  |    type   
---------------+-------------------+--------+------------
czech_line    | way              | 900913 | LINESTRING
czech_point  | way              | 900913 | POINT
czech_polygon | way              | 900913 | POLYGON
czech_roads  | way              | 900913 | LINESTRING
(4 rows)
</pre>
 
Dále je v databázi najdete data z [[Cvičná databáze PostGIS#gis1|cvičné databáze]] (schéma '''gis1''')
 
<source lang=sql>
SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_schema = 'gis1';
</source>
 
<pre>
  f_table_name    | f_geometry_column |  srid  |      type     
-------------------+-------------------+--------+-----------------
kltm50            | geom              | 102067 | MULTIPOLYGON
lesy              | geom              | 102067 | MULTIPOLYGON
obce              | geom              | 102067 | MULTIPOLYGON
obce_b            | geom              | 102067 | POINT
zeleznice        | geom              | 102067 | MULTILINESTRING
zeleznice_stanice | geom              | 102067 | POINT
(6 rows)
</pre>
 
==== Transformace dat ====


Data ze schémat <tt>osm</tt> a <tt>gis1</tt> jsou lokalizována v různých souřadnicových systémech.
Data ze schémat <tt>osm</tt> a <tt>gis1</tt> jsou lokalizována v různých souřadnicových systémech.
Řádek 59: Řádek 32:


<pre>
<pre>
-[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------
-[ RECORD 1 ]+--------------------------------------------------------------------------------------------------------------------------
f_table_name | czech_line
f_table_name | czech_line
auth_name    | spatialreferencing.org
auth_name    | EPSG
auth_srid    | 900913
auth_srid    | 3857
proj4text    | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs
proj4text    | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext  +no_defs
-[ RECORD 2 ]+-----------------------------------------------------------------------------------------------------------------
...
f_table_name | czech_point
auth_name    | spatialreferencing.org
auth_srid    | 900913
proj4text    | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs
-[ RECORD 3 ]+-----------------------------------------------------------------------------------------------------------------
f_table_name | czech_polygon
auth_name    | spatialreferencing.org
auth_srid    | 900913
proj4text    | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs
-[ RECORD 4 ]+-----------------------------------------------------------------------------------------------------------------
f_table_name | czech_roads
auth_name    | spatialreferencing.org
auth_srid    | 900913
proj4text    | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs
</pre>
</pre>


<source lang=sql>
<source lang=sql>
SELECT g.f_table_name, s.auth_name, s.auth_srid, s.proj4text FROM geometry_columns AS g JOIN
SELECT g.f_table_name, s.auth_name, s.auth_srid, s.proj4text FROM geometry_columns AS g JOIN
  spatial_ref_sys AS s ON g.srid = s.srid AND g.f_table_schema = 'gis1';
  spatial_ref_sys AS s ON g.srid = s.srid AND g.f_table_schema = 'ruian';
</source>
</source>


<pre>
<pre>
-[ RECORD 1 ]+-------------------------------------------------------------
-[ RECORD 1 ]+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f_table_name | kltm50
f_table_name | castiobci
auth_name    | esri
auth_name    | EPSG
auth_srid    | 102067
auth_srid    | 5514
proj4text    | +proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
proj4text    | +proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +units=m +no_defs  
            : +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1
...
-[ RECORD 2 ]+-------------------------------------------------------------
f_table_name | lesy
auth_name    | esri
auth_srid    | 102067
proj4text    | +proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
            : +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1
-[ RECORD 3 ]+-------------------------------------------------------------
f_table_name | obce
auth_name    | esri
auth_srid    | 102067
proj4text    | +proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
            : +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1
-[ RECORD 4 ]+-------------------------------------------------------------
f_table_name | obce_b
auth_name    | esri
auth_srid    | 102067
proj4text    | +proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
            : +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1
-[ RECORD 5 ]+-------------------------------------------------------------
f_table_name | zeleznice
auth_name    | esri
auth_srid    | 102067
proj4text    | +proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
            : +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1
-[ RECORD 6 ]+-------------------------------------------------------------
f_table_name | zeleznice_stanice
auth_name    | esri
auth_srid    | 102067
proj4text    | +proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
            : +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1
</pre>
</pre>


Při kombinaci dat ze schémat <tt>osm</tt> a <tt>gis1</tt> je nutné provést transformaci ([http://postgis.org/documentation/manual-1.5/ST_Transform.html ST_Transform]) dat do společného souřadnicové systému.
Při kombinaci dat ze schémat <tt>osm</tt> a <tt>ruian</tt> je nutné provést transformaci ([http://postgis.net/docs/manual-2.1/ST_Transform.html ST_Transform]) dat do společného souřadnicové systému.


<source lang=sql>
<source lang=sql>
SELECT count(*) from osm.czech_point AS kino JOIN gis1.obce AS obce ON
SELECT count(*) from osm.czech_point AS kino JOIN ruian.obce AS obce ON
  ST_Within(kino.way, ST_Transform(obce.geom, 900913)) AND
  ST_Within(kino.geom, ST_Transform(obce.geom, 3857)) AND
  kino.amenity = 'cinema' AND obce.nazev = 'Praha';
  kino.amenity = 'cinema' AND obce.nazev = 'Praha';
</source>
</source>
Řádek 136: Řádek 65:


<source lang=sql>
<source lang=sql>
CREATE TABLE obce AS SELECT * FROM gis1.obce;
CREATE TABLE obce AS SELECT * FROM ruian.obce;
UPDATE obce SET geom = ST_Transform(geom, 900913);
 
SELECT Populate_geometry_column(pgclass::'obce');
ALTER TABLE obce ADD COLUMN geom1 geometry(multipolygon, 3857);
 
UPDATE obce SET geom1 = ST_Transform(geom, 3857);
</source>
</source>
<source lang=sql>
SELECT nazev,st_astext(st_pointonsurface(geom)) as jtsk,st_astext(st_pointonsurface(geom1)) as pmercator FROM obce LIMIT 1;
</source>
<pre>
-[ RECORD 1 ]---------------------------------------
nazev    | Tvorovice
jtsk      | POINT(-551931.998228383 -1145211.62)
pmercator | POINT(1916798.04173232 6339076.60345749)
</pre>


Korespondující dotaz dostane následující podobu
Korespondující dotaz dostane následující podobu
Řádek 145: Řádek 86:
<source lang=sql>
<source lang=sql>
SELECT COUNT(*) FROM osm.czech_point AS kino JOIN obce AS obce ON
SELECT COUNT(*) FROM osm.czech_point AS kino JOIN obce AS obce ON
  ST_Within(kino.way, obce.geom) AND
  ST_Within(kino.geom, obce.geom1) AND
  kino.amenity = 'cinema' AND obce.nazev = 'Praha';
  kino.amenity = 'cinema' AND obce.nazev = 'Praha';
</source>
=== Nastavení cesty ===
Příklad pro skupinu <tt>a11</tt>
<source lang="sql">
SET search_path to a11, public, osm;
</source>
</source>


== Vytvoření tématické vrstvy ==
== Vytvoření tématické vrstvy ==


Příklad pro 'vinice' (polygonová data) - viz [http://wiki.openstreetmap.org/wiki/Cz:Map_Features Map Features] na wiki OSM.
Příklad pro 'vinice' (polygonová data) - viz [http://wiki.openstreetmap.org/wiki/Cs:Map_Features Map Features] na wiki OSM.


<source lang="sql">
<source lang="sql">
CREATE TABLE vinice AS SELECT osm_id, way AS geom FROM czech_polygon WHERE landuse = 'vineyard';
CREATE TABLE vinice AS SELECT osm_id, geom AS geom FROM osm.czech_polygon WHERE landuse = 'vineyard';
</source>
</source>


Řádek 181: Řádek 114:


<source lang="sql">
<source lang="sql">
CREATE INDEX vinice_geom ON vinice USING gist (geom);
CREATE INDEX ON vinice USING gist (geom);
</source>
 
* Aktualizace metadatové tabulky <tt>geometry_columns</tt>
 
<source lang="sql">
SELECT populate_geometry_columns('a11.vinice'::regclass);
</source>
</source>


Řádek 193: Řádek 120:


<source lang="sql">
<source lang="sql">
SELECT dropgeometrytable('a11', 'vinice');
DROP TABLE vinice;
</source>
</source>

Verze z 18. 11. 2020, 07:51

Přístup k databázi uzpr_projekty

  • databáze: uzpr_projekty
  • host: geo102.fsv.cvut.cz
  • uživatelské jméno: uzpr<rok>_<skupina>, např. uzpr21_a

Příklad pro skupinu A v akademickém roce 2020/2021

psql uzpr_projekty -U uzpr21_a -h geo102.fsv.cvut.cz -W
Přihlašovací dialog v programu QGIS


Vstupní data

SELECT * FROM geometry_columns;

Transformace dat

Data ze schémat osm a gis1 jsou lokalizována v různých souřadnicových systémech.

SELECT g.f_table_name, s.auth_name, s.auth_srid, s.proj4text FROM geometry_columns AS g JOIN
 spatial_ref_sys AS s ON g.srid = s.srid AND g.f_table_schema = 'osm';
-[ RECORD 1 ]+--------------------------------------------------------------------------------------------------------------------------
f_table_name | czech_line
auth_name    | EPSG
auth_srid    | 3857
proj4text    | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext  +no_defs
...
SELECT g.f_table_name, s.auth_name, s.auth_srid, s.proj4text FROM geometry_columns AS g JOIN
 spatial_ref_sys AS s ON g.srid = s.srid AND g.f_table_schema = 'ruian';
-[ RECORD 1 ]+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f_table_name | castiobci
auth_name    | EPSG
auth_srid    | 5514
proj4text    | +proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +units=m +no_defs 
...

Při kombinaci dat ze schémat osm a ruian je nutné provést transformaci (ST_Transform) dat do společného souřadnicové systému.

SELECT count(*) from osm.czech_point AS kino JOIN ruian.obce AS obce ON
 ST_Within(kino.geom, ST_Transform(obce.geom, 3857)) AND
 kino.amenity = 'cinema' AND obce.nazev = 'Praha';

Při opakované transformaci je výhodné vytvořit ve vlastním schématu kopii vektorové vrstvy ve zvoleném souřadnicovém systému, např.

CREATE TABLE obce AS SELECT * FROM ruian.obce;

ALTER TABLE obce ADD COLUMN geom1 geometry(multipolygon, 3857);

UPDATE obce SET geom1 = ST_Transform(geom, 3857);
SELECT nazev,st_astext(st_pointonsurface(geom)) as jtsk,st_astext(st_pointonsurface(geom1)) as pmercator FROM obce LIMIT 1;
-[ RECORD 1 ]---------------------------------------
nazev     | Tvorovice
jtsk      | POINT(-551931.998228383 -1145211.62)
pmercator | POINT(1916798.04173232 6339076.60345749)

Korespondující dotaz dostane následující podobu

SELECT COUNT(*) FROM osm.czech_point AS kino JOIN obce AS obce ON
 ST_Within(kino.geom, obce.geom1) AND
 kino.amenity = 'cinema' AND obce.nazev = 'Praha';

Vytvoření tématické vrstvy

Příklad pro 'vinice' (polygonová data) - viz Map Features na wiki OSM.

CREATE TABLE vinice AS SELECT osm_id, geom AS geom FROM osm.czech_polygon WHERE landuse = 'vineyard';
  • Definice primárního klíče
ALTER TABLE vinice ADD PRIMARY KEY(osm_id);

Pokud přidání primárního klíče nad atributem osm_id zkolabuje, přidejte nový atribut gid a vytvořte primární klíč nad tímto atributem

ALTER TABLE vinice ADD COLUMN gid serial;
ALTER TABLE vinice ADD PRIMARY KEY(gid);
  • Vytvoření prostorového klíče
CREATE INDEX ON vinice USING gist (geom);

Odstranění tématické vrstvy

DROP TABLE vinice;