gistfile1.txt

osmosis --rb austria-latest.osm.pbf --rb czech-republic-latest.osm.pbf --rb hungary-latest.osm.pbf --rb poland-latest.osm.pbf --rb slovakia-latest.osm.pbf --rb ukraine-latest.osm.pbf --merge --merge --merge merge --m --bp file="../slovakia-extrude.poly" clipIncompleteEntities=true --wb file="slovakia-extrude.osm.pbf" omitmetadata=true

osmosis --rb austria-latest.osm.pbf --rb czech-republic-latest.osm.pbf --rb hungary-latest.osm.pbf --rb poland-latest.osm.pbf --rb slovakia-latest.osm.pbf --rb ukraine-latest.osm.pbf --merge --merge --merge --merge --m --bb left=16.6346 bottom=47.5438 right=22.7748 top=49.7483 clipIncompleteEntities=true --wb file="slovakia-extrude.osm.pbf" omitmetadata=true

as pg root:
CREATE EXTENSION hstore;
CREATE EXTENSION postgis;

-- drop TABLE ways, way_nodes, relation_members, relations, nodes, users;

as martin:
\i /usr/share/doc/osmosis/examples/pgsnapshot_schema_0.6.sql

osmosis --read-pbf file=slovakia-extrude.osm.pbf --wp database=gis user=martin host=localhost password=b0n0

#osm2pgsql -c -G -U martin -d gis -S martin.style slovakia-latest.osm.pbf
osm2pgsql -c -s -G -U martin -d gis -S martin.style slovakia-extrude.osm.pbf

alter table planet_osm_line add column osmc_red text;
alter table planet_osm_line add column osmc_blue text;
alter table planet_osm_line add column osmc_green text;
alter table planet_osm_line add column osmc_yellow text;

update planet_osm_line set osmc_red = (select string_agg(tags -> 'osmc:symbol',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('hiking', 'foot') and tags -> 'osmc:symbol' like 'red:%' and member_id = osm_id);

update planet_osm_line set osmc_blue = (select string_agg(tags -> 'osmc:symbol',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('hiking', 'foot') and tags -> 'osmc:symbol' like 'blue:%' and member_id = osm_id);

update planet_osm_line set osmc_green = (select string_agg(tags -> 'osmc:symbol',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('hiking', 'foot') and tags -> 'osmc:symbol' like 'green:%' and member_id = osm_id);

update planet_osm_line set osmc_yellow = (select string_agg(tags -> 'osmc:symbol',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('hiking', 'foot') and tags -> 'osmc:symbol' like 'yellow:%' and member_id = osm_id);

alter table planet_osm_line add column osmc_symbol text;

update planet_osm_line set osmc_symbol = (select tags -> 'osmc:symbol' from relations where id = -osm_id);

alter table planet_osm_line add column osmc_count smallint;

update planet_osm_line set osmc_count = (select count(distinct regexp_replace(rrr.tags -> 'osmc:symbol', '([^:]*).*', '\1')) from (relation_members inner join relations on member_type = 'W' and relation_id = id and relation_id = -osm_id) aaa inner join relation_members xxx on aaa.member_id = xxx.member_id inner join relations rrr on xxx.relation_id = rrr.id and rrr.tags -> 'osmc:symbol' ~ '^(red|blue|green|yellow):');

alter table planet_osm_point rename column "tower:type" to tower_type;
alter table planet_osm_polygon rename column "tower:type" to tower_type;

~~~~~~~~~ BIKE ~~~~~~~~
alter table planet_osm_line add column bike_red text;
alter table planet_osm_line add column bike_blue text;
alter table planet_osm_line add column bike_green text;
alter table planet_osm_line add column bike_yellow text;

update planet_osm_line set bike_red = (select string_agg(tags -> 'colour',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('bicycle', 'mtb') and tags -> 'colour' = 'red' and member_id = osm_id);

update planet_osm_line set bike_blue = (select string_agg(tags -> 'colour',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('bicycle', 'mtb') and tags -> 'colour' = 'blue' and member_id = osm_id);

update planet_osm_line set bike_green = (select string_agg(tags -> 'colour',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('hiking', 'foot') and tags -> 'colour' = 'green' and member_id = osm_id);

update planet_osm_line set bike_yellow = (select string_agg(tags -> 'colour',',') from relations left join relation_members on relation_id = id and member_type = 'W' where tags -> 'type' = 'route' and tags -> 'route' in ('bicycle', 'mtb') and tags -> 'colour' = 'yellow' and member_id = osm_id);

~~~~~~~~~~~~~~~~~~~~~~~~~~~







# select distinct xxx.relation_id from (relation_members inner join relations on member_type = 'W' and relation_id = id and relation_id = 1471863) aaa inner join relation_members xxx on aaa.member_id = xxx.member_id;

# select count(distinct regexp_replace(rrr.tags -> 'osmc:symbol', '([^:]*).*', '\1')) from (relation_members inner join relations on member_type = 'W' and relation_id = id and relation_id = 1471863) aaa inner join relation_members xxx on aaa.member_id = xxx.member_id inner join relations rrr on xxx.relation_id = rrr.id and rrr.tags ? 'osmc:symbol';

create index on nodes using HASH ((tags->'natural'));
create index on nodes using HASH ((tags->'man_made'));
create index on nodes using HASH ((tags->'amenity'));
create index on nodes using gin (tags);
create index on nodes using HASH ((tags->'name'));


#tracks-notunnel-nobridge {
  comp-op: src-over; // to force flattering
  [zoom >= 14] {
    line-width: 3;
    line-color: white;
    line-opacity: 0.4;
    line-join: round;
    line-cap: round;
    line-comp-op: dst-over;
    b/line-width: 1.5;
    b/line-color: #996600;
    b/line-dasharray: 5,4,2,4;
    b/line-cap: round;
    b/line-join: round;
    b/line-opacity: 0.8;
    [tracktype = 'grade1'] {
      b/line-dasharray: 100,0; /* i.e. none, see https://github.com/mapbox/carto/issues/214 */
    line-width: 4;
    }
    [tracktype = 'grade2'] {
      line-width:: 3.5;
      b/line-dasharray: 5,3;
    }
    [tracktype = 'grade3'] {
      b/line-dasharray: 4,4;
    }
    [tracktype = 'grade4'] {
      b/line-dasharray: 3,5;
    }
    [tracktype = 'grade5'] {
      b/line-dasharray: 2,6;
    }
  }
}



for i in *.hgt; do gdal_contour -a elevation -snodata 32767 -i 10 $i ${i%.hgt}; done

# shp2pgsql -I -c -g way N48E018/contour.shp contour > c.sql


SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "contour" (gid serial PRIMARY KEY,
"id" int4,
"elevation" float8);
SELECT AddGeometryColumn('','contour','way','-1','MULTILINESTRING',2);
COMMIT;



CREATE INDEX "contour_way_gist" ON "contour" using gist ("way" gist_geometry_ops);

for i in *; do shp2pgsql -I -a -g way $i/contour.shp contour >> c.sql; done

(select way, elevation, elevation::int4 % 50 as e50, elevation::int4 % 100 as e100 from contour) as contour


#gdaldem hillshade -co compress=lzw -compute_edges -s 111120 -z 2 -alt 66 N48E018.hgt N48E018.tiff

for i in *.hgt; do gdaldem hillshade -co compress=lzw -compute_edges -s 111120 -z 2 -alt 66 $i ${i%.hgt}.tiff; done



=========


http://a.freemap.sk/T/12/2280/1414.jpeg
http://mapy.hiking.sk/layers/tzt/12/000/002/280/000/002/681.png


http://mapy.hiking.sk/layers/tzt/12/000/002/280/000/002/682.png
http://a.freemap.sk/T/12/2280/1413.jpeg

x - 682 = 1413
x = 2095

===============


select id from ways left join relation_members on ways.id = relation_members.member_id and member_type = 'W' where relation_members.relation_id is null and tags - ARRAY['source', 'source:name'] = ''::hstore \g /home/martin/x

gis=# select unnest(akeys(tags)), count(unnest(akeys(tags))) from nodes group by unnest(akeys(tags)) order by count;

# remove nearby peak localities
select b.id from nodes a inner join nodes b on lower(a.tags->'name') = lower(b.tags->'name') and a.tags->'natural' = 'peak' and b.tags->'place' = 'locality' and ST_Distance_Sphere(a.geom, b.geom) < 1000

# untagged ways
select ways.id from ways left join relation_members on member_id = ways.id and member_type = 'W' where akeys((ways.tags - 'source'::text)) = ARRAY[]::text[]  and relation_members.relation_id is null

# names
update kataster set geom2 = st_transform(st_setsrid(geom, 99999), 4326);


CREATE EXTENSION unaccent;
create table exist_nazvy as select key, first(nazov) nazov, first(geom2) geom2 from kataster join (
select ways.tags->'name' as name, geom from ways join way_nodes on ways.id = way_nodes.way_id join nodes on nodes.id = way_nodes.node_id where ways.tags?'name' union
select ways.tags->'alt_name' as name, geom from ways join way_nodes on ways.id = way_nodes.way_id join nodes on nodes.id = way_nodes.node_id where ways.tags?'alt_name' union
select ways.tags->'old_name' as name, geom from ways join way_nodes on ways.id = way_nodes.way_id join nodes on nodes.id = way_nodes.node_id where ways.tags?'old_name' union
select ways.tags->'loc_name' as name, geom from ways join way_nodes on ways.id = way_nodes.way_id join nodes on nodes.id = way_nodes.node_id where ways.tags?'loc_name' union
select ways.tags->'name:sk' as name, geom from ways join way_nodes on ways.id = way_nodes.way_id join nodes on nodes.id = way_nodes.node_id where ways.tags?'name:sk' union
select nodes.tags->'name' as name, geom from nodes where nodes.tags?'name' union
select nodes.tags->'alt_name' as name, geom from nodes where nodes.tags?'alt_name' union
select nodes.tags->'old_name' as name, geom from nodes where nodes.tags?'old_name' union
select nodes.tags->'name:sk' as name, geom from nodes where nodes.tags?'name:sk' union
select nodes.tags->'name' as name, geom from nodes where nodes.tags?'name'
) existing on unaccent(lower(nazov)) = unaccent(lower(name)) and layer = 'popis_text' where nazov is not null and ST_Distance_Sphere(kataster.geom2, existing.geom) < 3000.0 group by key;

select count(*) from kataster left join exist_nazvy on layer = 'popis_text' and kataster.key = exist_nazvy.key where exist_nazvy.geom2 is null and  layer = 'popis_text';




# create shapefile with hiking trails
pgsql2shp -f hiking -g way gis "select way, relations.tags->'osmc:symbol' as symbol, relations.tags->'colour' as colour from planet_osm_line join relation_members on osm_id = member_id and member_type = 'W' join relations on id = relation_id where relations.tags->'type' = 'route' and (relations.tags->'route' = 'hiking' or relations.tags->'route' = 'foot')"

~~~~~~~~~~

create table data2 as select * from data2merged natural left join popisky2distinct;

~~~~~~~~~~~~~

select id from planet_osm_line join relation_members on osm_id = member_id and member_type = 'W' join relations on relation_id = id and tags->'type'='route' and tags->'route'='hiking' group by relations.id having 'MULTILINESTRING' = geometrytype(st_linemerge(st_union(way)));

~~~~~~~~~

create table data2merged without oids as select layer, key, st_union(geom) as geom, st_envelope(st_union(ex)) as ex from data2 group by layer, key;