***Workshop FOSSGIS 2014 Berlin: OSM mit OGR
Referenten: Harald Schwenk, Olaf Kotzte

**PostgreSQL/PostGIS

-start Terminal
psql oder psql -U user postgres 
CREATE DATABASE osm;
\c osm
SELECT postgis_full_version();
CREATE EXTENSION postgis;


\encoding utf8 < nur Windows


**OGR

sudo find / -name *osmconf.ini* < nur um osmconf.ini zu finden
medit /usr/share/gdal/1.10/osmconf.ini < Start Editor mit osmconf.ini
-Editieren der osmconf.ini
-LINES add railway,
-MULTIPOLYGONS add addr:country,addr:city,addr:postcode,addr:street,addr:housenumber
-Speichern der osmconf.ini < Ort: /home/user/Desktop/...

-SET OSM_CONFIG_FILE=...\osmconf.ini < nur Windows

ogrinfo --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini  /home/user/Desktop/bremen-latest.osm.pbf

ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f PostgreSQL "PG:dbname=osm host=localhost" /home/user/Desktop/bremen-latest.osm.pbf -lco GEOMETRY_NAME=the_geom
-evtl. mit overwrite
ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f PostgreSQL "PG:dbname=osm host=localhost" -overwrite /home/user/Desktop/bremen-latest.osm.pbf -lco GEOMETRY_NAME=the_geom

ogrinfo -ro PG:"host=localhost user=xxx password=xxx dbname=osm"

**SQLite Erstellen
ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f SQLite -overwrite /home/user/Desktop/bremen-latest_points_.osm.sqlite /home/user/Desktop/bremen-latest.osm.pbf  -sql "SELECT * FROM points" -dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes
ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f SQLite -overwrite /home/user/Desktop/bremen-latest_lines_.osm.sqlite /home/user/Desktop/bremen-latest.osm.pbf  -sql "SELECT * FROM lines" -dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes
ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f SQLite -overwrite /home/user/Desktop/bremen-latest_multilinestrings_.osm.sqlite /home/user/Desktop/bremen-latest.osm.pbf  -sql "SELECT * FROM multilinestrings" -dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes
ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f SQLite -overwrite /home/user/Desktop/bremen-latest_multipolygons_.osm.sqlite /home/user/Desktop/bremen-latest.osm.pbf  -sql "SELECT * FROM multipolygons" -dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes
ogr2ogr --config OSM_CONFIG_FILE /home/user/Desktop/osmconf.ini -f SQLite -overwrite /home/user/Desktop/bremen-latest_other_relations_.osm.sqlite /home/user/Desktop/bremen-latest.osm.pbf  -sql "SELECT * FROM other_relations" -dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes


**wechsel zum PostgreSQL/PostGIS Datenbankmonitor
SELECT count(*) FROM points;
SELECT count(*) FROM lines;
SELECT count(*) FROM multilinestrings;
SELECT count(*) FROM multipolygons;
SELECT count(*) FROM other_relations;


SELECT count(*), highway FROM lines GROUP BY highway ORDER BY count;
SELECT count(*), railway FROM lines GROUP BY railway ORDER BY count;
SELECT count(*), boundary FROM multipolygons GROUP BY boundary;
SELECT DISTINCT admin_level FROM multipolygons;
SELECT DISTINCT admin_level::int FROM multipolygons ORDER BY admin_level;
\d lines


**länge Tramtrasse
SELECT the_geom FROM lines WHERE railway = 'tram' OFFSET 100 LIMIT 3;
SELECT ST_AsText(the_geom) FROM lines WHERE railway = 'tram' OFFSET 100 LIMIT 3;
SELECT ST_AsText(ST_Transform(the_geom,25832)) FROM lines WHERE railway = 'tram' OFFSET 100 LIMIT 3;
SELECT ST_Union(the_geom) FROM lines WHERE railway = 'tram';
SELECT ST_NumGeometries(ST_Union(the_geom)) FROM lines WHERE railway = 'tram';

(WITH vereinigung AS
    (SELECT ST_Union(the_geom) AS vereinigte_geomerie FROM lines WHERE railway = 'tram')
SELECT COUNT(*) FROM vereinigung)
;
SELECT ST_Transform(ST_Union(the_geom),25832) FROM lines WHERE railway = 'tram';
SELECT ST_Length(ST_Transform(ST_Union(the_geom),25832)) / 1000 FROM lines WHERE railway = 'tram';

SELECT ((ST_Length(ST_Union(ST_Transform(the_geom,25832))) / 1000.0)::numeric(8,3) || ' km')::text AS laenge_strabatrasse FROM lines WHERE railway = 'tram';

(WITH tram AS
    (SELECT ST_Intersection(a.the_geom, b.the_geom) AS the_geom FROM multipolygons a, lines b
        WHERE a.admin_level = '6'
            AND a.name = 'Bremen' --'D'|| chr(252) ||'sseldorf' 
            AND b.railway = 'tram')
SELECT ((ST_Length(ST_Union(ST_Transform(the_geom,25832))) / 1000.0)::numeric(8,3) || ' km')::text AS laenge_strabatrasse_in_bremen
    FROM tram)
;


**PLZ
\d multipolygons

SELECT addr_city, addr_postcode, addr_street, addr_housenumber FROM multipolygons
    WHERE addr_postcode IS NOT NULL AND addr_street IS NOT NULL AND addr_housenumber IS NOT NULL
    OFFSET 999 LIMIT 10
;

SELECT other_tags FROM multipolygons WHERE boundary = 'postal_code' OFFSET 35 LIMIT 10;

SELECT POSITION('postal_code' IN other_tags) FROM multipolygons
    WHERE boundary = 'postal_code'
;
SELECT (SUBSTRING(other_tags FROM (POSITION('postal_code' IN other_tags) + 13) FOR 5)) FROM multipolygons
    WHERE boundary = 'postal_code' OFFSET 35 LIMIT 10
;

SELECT a.addr_city, a.addr_postcode, a.addr_street, a.addr_housenumber
    FROM multipolygons a, multipolygons b
    WHERE ST_Within(a.the_geom, b.the_geom) IS TRUE
        AND a.building IS NOT NULL
        AND a.addr_postcode IS NOT NULL
        AND a.addr_street IS NOT NULL
        AND a.addr_housenumber IS NOT NULL
        AND b.boundary = 'postal_code'
        AND a.addr_postcode <> (SUBSTRING(b.other_tags FROM (POSITION('postal_code' IN b.other_tags) + 13) FOR 5))
;


**CREATE-VIEW für dxf-Export
DROP VIEW ogr_dxf;
CREATE OR REPLACE VIEW ogr_dxf AS
SELECT ogc_fid AS gid, 
            'Hausnummern'::text AS layer,
            'LABEL(f:"ogr_dxf",t:"' || addr_housenumber || '",s:2.5g,a:0,p:4,b:#cccccc,o:#141414)'::text AS ogr_style,
            ST_Transform(ST_Centroid((ST_Dump(the_geom)).geom),25832) AS the_geom --ST_Centroid((ST_Dump(the_geom)).geom)
    FROM multipolygons
        WHERE the_geom IS NOT NULL
            AND ST_IsValid(the_geom) IS TRUE
            AND building IS NOT NULL
            AND addr_housenumber IS NOT NULL
UNION
SELECT ogc_fid AS gid,
            'Building_Schraffur'::text AS layer,
            'BRUSH(fc:#c9c9c9,id:ogr-brush-4)'::text AS ogr_style,
            ST_Transform(the_geom,25832) AS the_geom
    FROM multipolygons
        WHERE the_geom IS NOT NULL
            AND ST_IsValid(the_geom) IS TRUE
            AND building IS NOT NULL
UNION
SELECT ogc_fid AS gid, 
            'Building'::text AS layer,
            'PEN(c:#141414,w:0.25g)'::text AS ogr_style,
            ST_Transform(ST_ExteriorRing((ST_Dump(the_geom)).geom),25832) AS the_geom
    FROM multipolygons
        WHERE the_geom IS NOT NULL
            AND ST_IsValid(the_geom) IS TRUE
            AND building IS NOT NULL
UNION
SELECT ogc_fid AS gid,
            'tram'::text AS layer,
            'PEN(c:#0000FF,w:2g)'::text AS ogr_style,
            ST_Transform(the_geom,25832) AS the_geom
    FROM lines
        WHERE the_geom IS NOT NULL
            AND ST_GeometryType(the_geom) = 'ST_LineString'
            AND railway = 'tram'
;

SELECT DISTINCT layer, ogr_style FROM ogr_dxf ORDER BY layer LIMIT 25;


medit /rofs/usr/share/gdal/1.10/header.dxf

SET CPL_DEBUG=on < nur Windows
SET CPL_LOG=C:\...\...\osm_dxf.log < nur Windows

ogrinfo -so -sql "(SELECT * FROM ogr_dxf)" "PG:dbname=osm host=localhost"

ogr2ogr --config CPL_DEBUG on --config CPL_LOG /home/user/Desktop/osm_dxf.log -sql "(SELECT * FROM ogr_dxf)" -f "dxf" /home/user/Desktop/osm_dxf.dxf "PG:dbname=osm host=localhost tables=ogr_dxf" -dsco header=/home/user/Desktop/header.dxf -clipsrc 460000 5870000 510000 5900000

ogrinfo -so -al /home/user/Desktop/osm_dxf.dxf

http://www.gdal.org/ogr/ogr_feature_style.html

***QGIS
Koordinatensystem einstellen: Enable on the fly > yes, Filter: ETRS89, Auswahl: ETRS89 / UTM Zone 32N EPSG:25832
Layer > ADD Postgis Layer
new Connection: Name irgendwas, Host Localhost, Database osm
Layer > ADD SpatiaLite Layer
new Connection: SpatiaLite im Filesystem ansteuern (Desktop)