Diskuse:155UZPR / Semestrální projekt
Přístup k databázi pgis_uzpd
- host: geo102.fsv.cvut.cz
- uživatelské jméno: uzpd_<skupina><rok>, např. uzpd_a12
Příklad pro skupinu A v roce 2012
psql pgis_uzpd -U uzpd_a12 -h geo102.fsv.cvut.cz -W
Vstupní data
Databáze obsahuje ve schématu osm data OpenStreetMap ČR.
SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_schema = 'osm';
f_table_name | f_geometry_column | srid | type ---------------+-------------------+--------+------------ czech_roads | geom | 900913 | LINESTRING czech_point | geom | 900913 | POINT czech_line | geom | 900913 | LINESTRING czech_polygon | geom | 900913 | POLYGON (4 rows)
SELECT f_table_name, f_geometry_column, srid, TYPE FROM geometry_columns WHERE f_table_schema = 'ruian';
f_table_name | f_geometry_column | srid | type --------------------+-------------------+------+-------------- castiobci | geom | 5514 | POINT obce | geom | 5514 | MULTIPOLYGON katastralniuzemi | geom | 5514 | MULTIPOLYGON kraje | geom | 5514 | MULTIPOLYGON okresy | geom | 5514 | MULTIPOLYGON momc | geom | 5514 | POINT regionysoudrznosti | geom | 5514 | MULTIPOLYGON mop | geom | 5514 | POINT vusc | geom | 5514 | MULTIPOLYGON orp | geom | 5514 | MULTIPOLYGON spravniobvody | geom | 5514 | POINT pou | geom | 5514 | MULTIPOLYGON staty | geom | 5514 | MULTIPOLYGON zsj | geom | 5514 | MULTIPOINT (14 rows)
SELECT f_table_name, f_geometry_column, srid, TYPE FROM geometry_columns WHERE f_table_schema = 'ruian_praha';
f_table_name | f_geometry_column | srid | type ------------------+-------------------+------+----------------- adresnimista | geom | 5514 | POINT castiobci | geom | 5514 | POINT stavebniobjekty | geom | 5514 | MULTIPOLYGON katastralniuzemi | geom | 5514 | MULTIPOLYGON momc | geom | 5514 | MULTIPOLYGON ulice | geom | 5514 | MULTILINESTRING mop | geom | 5514 | MULTIPOLYGON obce | geom | 5514 | MULTIPOLYGON zsj | geom | 5514 | MULTIPOLYGON parcely | geom | 5514 | POLYGON spravniobvody | geom | 5514 | MULTIPOLYGON (11 rows)
Nastavení cesty
Příklad pro skupinu a11
SET search_path to a11, public, osm;
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_roads auth_name | local auth_srid | 900913 proj4text | +proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs -[ RECORD 2 ]+----------------------------------------------------------------------------------- f_table_name | czech_point auth_name | local auth_srid | 900913 proj4text | +proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs -[ RECORD 3 ]+----------------------------------------------------------------------------------- f_table_name | czech_line auth_name | local auth_srid | 900913 proj4text | +proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs -[ RECORD 4 ]+----------------------------------------------------------------------------------- f_table_name | czech_polygon auth_name | local auth_srid | 900913 proj4text | +proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +ellps=WGS84 +datum=WGS84 +units=m +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 = 'gis1';
-[ RECORD 1 ]+-------------------------------------------------------------
f_table_name | kltm50
auth_name | EPSG
auth_srid | 2065
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 | EPSG
auth_srid | 2065
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 | EPSG
auth_srid | 2065
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 | EPSG
auth_srid | 2065
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 | EPSG
auth_srid | 2065
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 | EPSG
auth_srid | 2065
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
Při kombinaci dat ze schémat osm a gis1 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 gis1.obce AS obce ON
ST_Within(kino.geom, ST_Transform(obce.geom, 900913)) 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 gis1.obce;
-- pro verzi PostGIS 2 a vyšší
ALTER TABLE obce ADD COLUMN geom1 geometry(multipolygon, 900913);
-- jinak
SELECT AddGeometryColumn('obce', 'geom1', 900913, 'multipolygon', 2)
UPDATE obce SET geom = ST_Transform(geom, 900913);
SELECT nazev,st_astext(st_pointonsurface(geom)),st_astext(st_pointonsurface(geom1)) FROM obce LIMIT 1;
nazev | st_astext | st_astext -------------+--------------------------------------------+------------------------------------------ Janov | POINT(-609759.823365303 -1031626) | POINT(1809202.78794486 6472061.18305099) Zbinohy | POINT(-675667.786795435 -1119110.5) | POINT(1723590.72181339 6325386.91494681) Přerubenice | POINT(-782200.81284686 -1022891.5) | POINT(1540403.34786534 6451113.01200902) Budíškovice | POINT(-677092.051407079 -1164735.45932934) | POINT(1728957.60445694 6255645.00002806) Stranný | POINT(-742560.517232815 -1080292.5) | POINT(1613841.21781601 6371454.14432071) (5 rows)
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.geom) 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 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 vinice_geom ON vinice USING gist (geom);
Odstranění tématické vrstvy
-- ve verzi PostGIS 2 a vyšší
DROP TABLE a11.vinice;
-- jinak
SELECT dropgeometrytable('a11', 'vinice');