|
|
Řádek 1: |
Řádek 1: |
| < [[153UZPD|Úvod do zpracování prostorových dat]] | | < [[153UZPD|Úvod do zpracování prostorových dat]] |
| __TOC__ | | __TOC__ |
| == Přístup k databázi pgis_uzpd ==
| |
|
| |
| === Přihlášení k databázi ===
| |
|
| |
| * host: <tt>geo102.fsv.cvut.cz</tt>
| |
| * uživatelské jméno: <tt>uzpd_<skupina><rok></tt>, např. <tt>uzpd_a11</tt>
| |
|
| |
| Příklad pro skupinu A v roce 2011
| |
|
| |
| psql pgis_uzpd -U uzpd_a11 -h geo102.fsv.cvut.cz -W
| |
|
| |
| [[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]]
| |
|
| |
| === Vstupní data ===
| |
|
| |
| Databáze obsahuje ve schématu '''osm''' data [[OpenStreetMap]] ČR ze dne 9.3.2011.
| |
|
| |
| <source lang=sql>
| |
| SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_schema = 'osm';
| |
| </source>
| |
|
| |
| <pre>
| |
| 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.
| |
|
| |
| <source lang=sql>
| |
| 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';
| |
| </source>
| |
|
| |
| <pre>
| |
| -[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------
| |
| f_table_name | czech_line
| |
| 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 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>
| |
|
| |
| <source lang=sql>
| |
| 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';
| |
| </source>
| |
|
| |
| <pre>
| |
| -[ RECORD 1 ]+-------------------------------------------------------------
| |
| f_table_name | kltm50
| |
| 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 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>
| |
|
| |
| 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.
| |
|
| |
| <source lang=sql>
| |
| SELECT count(*) from osm.czech_point AS kino JOIN gis1.obce AS obce ON
| |
| ST_Within(kino.way, ST_Transform(obce.geom, 900913)) AND
| |
| kino.amenity = 'cinema' AND obce.nazev = 'Praha';
| |
| </source>
| |
|
| |
| 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ř.
| |
|
| |
| <source lang=sql>
| |
| CREATE TABLE obce AS SELECT * FROM gis1.obce;
| |
| UPDATE obce SET geom = ST_Transform(geom, 900913);
| |
| SELECT Populate_geometry_column(pgclass::'obce');
| |
| </source>
| |
|
| |
| Korespondující dotaz dostane následující podobu
| |
|
| |
| <source lang=sql>
| |
| SELECT COUNT(*) FROM osm.czech_point AS kino JOIN obce AS obce ON
| |
| ST_Within(kino.way, obce.geom) AND
| |
| 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>
| |
|
| |
| === 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.
| |
|
| |
| <source lang="sql">
| |
| CREATE TABLE vinice AS SELECT osm_id, way AS geom FROM czech_polygon WHERE landuse = 'vineyard';
| |
| </source>
| |
|
| |
| * Definice primárního klíče
| |
|
| |
| <source lang="sql">
| |
| ALTER TABLE vinice ADD PRIMARY KEY(osm_id);
| |
| </source>
| |
|
| |
| Pokud přidání primárního klíče nad atributem <tt>osm_id</tt> zkolabuje, přidejte nový atribut <tt>gid</tt> a vytvořte primární klíč nad tímto atributem
| |
|
| |
| <source lang="sql">
| |
| ALTER TABLE vinice ADD COLUMN gid serial;
| |
| ALTER TABLE vinice ADD PRIMARY KEY(gid);
| |
| </source>
| |
|
| |
| * Vytvoření prostorového klíče
| |
|
| |
| <source lang="sql">
| |
| CREATE INDEX vinice_geom ON vinice USING gist (geom);
| |
| </source>
| |
|
| |
| * Aktualizace metadatové tabulky <tt>geometry_columns</tt>
| |
|
| |
| <source lang="sql">
| |
| SELECT populate_geometry_columns('a11.vinice'::regclass);
| |
| </source>
| |
|
| |
| === Odstranění tématické vrstvy ===
| |
|
| |
| <source lang="sql">
| |
| SELECT dropgeometrytable('a11', 'vinice');
| |
| </source>
| |
|
| |
| == Letní semestr 2010/2011 == | | == Letní semestr 2010/2011 == |
|
| |
|