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

Přihlašovací dialog pgadmin3 pro databázi pgis_uzpd

Příklad dotazu v prostředí PgAdmin3

Vstupní data

Databáze obsahuje ve schématu osm data OpenStreetMap ČR ze dne 21.2.2012.

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)

Dále je v databázi najdete data z cvičné databáze (schéma gis1)

SELECT f_table_name, f_geometry_column, srid, type FROM geometry_columns WHERE f_table_schema = 'gis1';

   f_table_name    | f_geometry_column | srid |    type    
-------------------+-------------------+------+------------
 kltm50            | geom              | 2065 | POLYGON
 lesy              | geom              | 2065 | POLYGON
 obce              | geom              | 2065 | POLYGON
 obce_b            | geom              | 2065 | POINT
 zeleznice         | geom              | 2065 | LINESTRING
 zeleznice_stanice | geom              | 2065 | POINT
(6 rows)

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    | 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

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    | 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

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.way, 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;
UPDATE obce SET geom = ST_Transform(geom, 900913);
SELECT Populate_geometry_column(pgclass::'obce');

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

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';

Nastavení cesty

Příklad pro skupinu a11

SET search_path to a11, public, osm, gis1;

Vytvoření tématické vrstvy

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

CREATE TABLE vinice AS SELECT osm_id, way 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);

• Aktualizace metadatové tabulky geometry_columns

SELECT populate_geometry_columns('a11.vinice'::regclass);

Odstranění tématické vrstvy

SELECT dropgeometrytable('a11', 'vinice');