0xT9MdPZ

1#!/bin/bash
2# This is the osm2postgresql script, a GNU/Linux bash
3# script to load openstreetmap data into a postgresql
4# database (creating if necessary the database with
5# postgis and hstore extensions).
6# This program is distributed under the terms of the
7# GNU/General Public License version 2 or later (at your choice). See:
8# http://www.gnu.org/licenses/gpl-2.0.html
9# http://www.gnu.org/copyleft/gpl.html
10# There is no warranty for this free software. Use at your own risk!
11# Please read the full "NO WARRANTY" section of the GPL before using this program.
12# Copyright 2011-2012 Mayeul KAUFFMANN
13
14version="osm2postgresql version 0.5rc4 (28 January 2011)"
15scriptfile="./osm2postgresql_05rc4.sh"
16current_parameters=$@  
17
18 
19### Example usages:
20# ./osm2postgresql_05.sh --help
21### Other examples at http://wiki.openstreetmap.org/wiki/Osm2postgresql
22
23
24
25
26
27## Release notes:
28
29# IMPORTANT NOTE with respect to the install="yes" option:
30# The install="yes" option is ALPHA quality and tested on Kubuntu
31#  11.10 32 bits only. It may prevent the Novell evolution email client to run
32# on some platform; see:
33# http://forums.enterprisedb.com/posts/list/2616.page
34# In addition, PostgresPlus has been discontinued. PostgreSQL Standard Server is still mentioned here but download link is lacking there. Getting postgres installer with postgis requires to use ancillary link and software (postgres 8.4, postgis 1.4). It is recommended (and easier) to install more recent versions using your package manager.
35# As stated above, this part of osm2postgresql is alpha quality. You will                           be asked to type your root or sudo password and to type the postgres password ('postgres') as well.
36
37
38# Known issues
39# Spaces and dashes in options not handled correctly, use underscores in options instead 
40
41# Most of the remaining code is beta quality. The SQL part is currently
42# the most reliable (tested on large .osm files) and used in production.
43
44# The management of rights with postgresql is tricky.
45# The best is to let the current user connect to a local database (with same name
46# as the user name) through a socket (you may need to configure postgresql to trust this).
47# Figuring out what is the best universal solution for this script is still a work in progress...
48#  You might also want to allow your user to do
49#  (without password): sudo /usr/bin/psql
50## To do it, you should know how to use 'visudo'
51# The following assumes your default editor is vi. If this is not the case,
52##  you may want to change it typing:
53# sudo select-editor
54## You will be proposed a list of editors
55## Then type the following command on the shell (without the sharp '#'):
56# sudo visudo
57## Go to the end of the last line of the file with the keyboard then press
58# the 'i' key to be in insertion mode
59## Add the following line on a new line at the end of the file:
60# yourusername  ALL=(postgres) /usr/bin/psql *
61## To exit without saving, type the ESCAPE key, then type (without the sharp):
62# :q!
63# To exit after saving (w=write, q=quit), type the ESCAPE key, then type
64# (without the sharp):
65# :wq
66
67
68#####################
69# Code starts here
70
71
72f () {
73    errcode=$? # save the exit code
74    echo "Error $errcode. The command which triggered the error was:"
75    echo "$BASH_COMMAND"
76    echo "on line ${BASH_LINENO[0]} of the osm2postgresql script."
77    echo -e $how_to_remove 
78    exit $errcode
79}
80trap f ERR
81
82NOW=$(date +"%Y-%m-%d_%Hh%Mm%S") # so that it's accessible out of the next block too, for the log file name.
83
84
85installdir=~/PostgresMap
86install="no" # by default, assume the server is installed and running
87createdb="yes" # by default, assume the database is created (with postgis
88# and hstore support) and empty and owned by current user (trusted local
89# connection with socket without password when logged with a specific linux
90# user)
91
92dbname="gis_map"
93port=5432
94arch="64"
95username=postgres
96password=geodatabase # used only if create user or if --pgpass
97EPSG="do_not_reproject"
98rivers="cut" # where appropriate, cut rivers, streams... ovelaping riverbanks (nicer rendering)
99filetype="xml" # default .osm file instead of .pbf
100
101usage ()
102{
103echo "Usage: $scriptfile [OPTION]...  --osm filename.osm  [OPTION]...
104
105EXAMPLE USAGE (download and uncompress, then import into database):
106wget -O - http://download.geofabrik.de/osm/europe/monaco.osm.bz2 | bzcat > monaco.osm
107$scriptfile --osm monaco.osm
108More examples are shown at the end.
109
110MANDATORY PARAMETER: 
111-f, --file              name of .osm or .pbf file to import
112
113
114OPTIONAL PARAMETERS (NOT FOLLOWED BY A VARIABLE):
115--help                  display this help
116--version               display the version and date of this script
117-c, --createdb          create a database. If not, make sure your database has postgis,
118                          spatial_ref_sys and hstore.
119-r,--rivers_as_is          keep each river, stream... as is (default is to cut rivers to
120                          improve rendering of streams and rivers linked to rivers and lakes)
121--pbf                   import OSM binary file (.pbf, faster) instead of XML-type .osm
122--pgpass                add entry to pgpass (use with care)
123-i, --install           [NOT recommended]. Install a postgres server (using EntrepriseDB installer) and create a db user. Read release note in the script first!
124
125OPTIONAL PARAMETERS WHICH MUST BE FOLLOWED BY A VARIABLE:
126-d, --dbname            database name to create and/or connect to;
127                          with --install or --createdb, default is OSM$NOW
128                          otherwise default is `whoami`
129-U, --username          username (default: `whoami`). Role must already exist (except
130                          with --install). Mileage varies...
131--password              postgresql password for 'username', to be added to ~/.pgpass file
132                        (use with care)
133-h, --host              server host (default is to use a socket with user `whoami`
134                          except with --install option: default is 127.0.0.1
135-p, --port              server port (default when not using a socket is: 5432)
136-O, --options_postgres  any additional psql options to connect to postgres as postgres user
137-u, --options_user      any additional psql options to connect to postgres as current user
138-t, --tablespace        name of the tablespace to create (only if createdb="yes" and
139                          not install="yes" ; will be created in folder 'datadir')
140-D, --datadir           where to save the database (with option --install or --tablespace)
141                          You will be asked to type your root or sudo password, except if
142                          this folder already exists on disk (see next option too)
143--no_mkdir              does not try to create datadir nor chown it to postgres. Usefull if
144                         you use -D but do not want the script to ask for your (su)do password.
145                         In this case you must mkdir and chown yourself before.
146-m, --temporarydir      where to save the files created by osmosis
147-P, --postgis           folder where to find the sql commands to install postgis in database
148-H, --hstore            folder where to find the sql commands to install hstore in database
149-E, --EPSG              new SRID (EPSG) code (if you want to reproject the geometries)
150                          do not use to keep data in lat/long.
151-I, --installdir        where to install the postgres server (with --install option);
152                          default is: ~/PostgresPlus
153-x, --psql_folder       folder where psql executable is installed (e.g.
154                          /usr/lib/postgresql/9.0/bin/). By default, the script will search
155                          in the path.
156-a, --arch              architecture when installing the postgres server
157                          use "-a x64" for 64 bits; do not use that option for 32 bits
158-b, --bash_init_script  the name of the file (e.g. your bash init script) where to add
159                         a command that will add the path to 'psql' if using the 
160                         --installdir option. Default is ~/.bashrc
161
162The last two parameters are only relevant with the --install option.
163
164EXAMPLES:
165$scriptfile --osm monaco.osm
166The above command assumes the database is already created (with postgis
167and hstore support), has no conflicting tables and is owned by current user (trusted local
168connection with socket without password when logged with a specific linux
169user, which means running psql from the command line is enough to connect
170to the `whoami` database).
171
172If you want a database to be created, you need to provide the paths to postgis.sql
173and hstore.sql, for instance with:
174
175$scriptfile --postgis /usr/share/postgresql/8.4/contrib/postgis-1.5 \
176  --hstore /usr/share/postgresql/8.4/contrib \
177  --createdb   --osm monaco.osm
178
179You can install a server and populate a new database using the EntrepriseDB installer:
180$scriptfile --install --osm monaco.osm
181In this case, you will be asked your root or sudo password each time it is required.
182
183More detailed instructions and examples at:
184http://wiki.openstreetmap.org/wiki/Osm2postgresql
185
186"
187}
188
189
190## Read parameters from command line
191while [ "$1" != "" ]; do
192  case $1 in
193    --help )            usage
194                        exit ;;
195    -d | --dbname )     shift # database name to create and/or connect to?
196                        dbname=$1 ;;
197    -U | --username )   shift
198                        username=$1 ;;
199    --pgpass )          pgpass="yes" ;;
200    --password )        shift
201                        password=$1 ;;
202    -h | --host )       shift
203                        host=$1 ;;
204    -p | --port )       shift
205                        port=$1 ;;
206    -O | --options_postgres )  shift # set of other options to connect to postgres as postgres user
207                        pgoptions_postgres=$1 ;;
208    -u | --options_user )  shift # set of other options to connect to postgres as current user
209                        pgoptions_user=$1 ;;
210    -i | --install )    install="yes" ;; # install a postgres server?
211    -I | --installdir )  shift # where to install it
212                        installdir=$1 ;;
213    -a | --arch )  shift # use "-a x64" for 64 bits; do not use that option for 32 bits
214                        arch=$1 ;;
215    -c | --createdb )   createdb="yes" ;; # create a database? Default is "no"
216    -D | --datadir )    shift
217                        datadir=$1 ;; # where to save the db if you use the --install option
218    --no_mkdir ) no_mkdir="yes" ;;
219    -m | --temporarydir )    shift
220                        temporarydir=$1 ;; # where to save the files created by osmosis
221    -t | --tablespace ) shift
222                        tablespace=$1 ;; # name of the tablespace to create (only if createdb="yes" and not (install="yes") ; will be created in folder 'datadir')
223# If you have several hard disks, you can choose which one you want to use (the disk space you use is called a 'TABLESPACE' in postgresql).
224    -P | --postgis )     shift  # folder where to find the sql commands to install postgis
225                        postgisfolder=$1 ;;
226    -H | --hstore )     shift # folder where to find the sql commands to install hstore
227                        hstorefolder=$1 ;;
228    -E | --EPSG )     shift # new SRID (EPSG code) if you want to reproject the geometries
229                        EPSG=$1 ;;
230    -r | --rivers_as_is)    rivers="keep" ;; # keep rivers, streams... untouched
231    -f | --file ) shift  # OSM file to import (can be .osm or .pbf)
232                        osmfile=$1 ;;
233    --pbf )    # OSM binary file (.pbf) import instead of xml
234                        filetype=pbf ;;
235    -b | --bash_init_script ) shift
236                        bash_init_script=$1 ;;
237    -x | --psql_folder )       shift
238                        psql_folder=$1 ;;
239
240
241    --version  )        echo $version
242                        exit ;;
243      * )
244  esac
245  shift
246done
247
248if [ -z "$osmfile" ] ; then
249  usage
250  exit
251fi
252
253# logging commands and output thanks to:
254# http://www.unix.com/shell-programming-scripting/29949-capture-command-run-log-file.html
255# Do not log before, otherwise even the help goes to the log when the user uses --help
256{  
257#    set -x  
258
259NOW=$(date +"%Y-%m-%d_%H:%M:%S") # accessible within this block
260echo "Time: Started $NOW"
261
262if [ -z "$datadir" ] ; then
263  datadir="$installdir/data"
264fi
265
266if [ -z "$temporarydir" ] ; then
267  temporarydir="`pwd`/temp$dbname"
268fi
269
270if  [ ! -z "$host" ] ; then
271  pgconnect_postgres=" -h $host "
272else
273  if [ "$install" = "yes" ]; then
274  # the installer does not use a socket but a tcp connection...
275  host="127.0.0.1"
276  pgconnect_postgres=" -h 127.0.0.1 "
277  fi
278fi
279
280if  [ ! -z "$port" ] ; then
281  pgconnect_postgres=" $pgconnect_postgres -p $port "
282fi
283
284pgconnect_user=$pgconnect_postgres
285
286if  [ ! -z "$pgoptions_postgres" ] ; then
287  pgconnect_postgres=" $pgconnect_postgres $pgoptions_postgres "
288fi
289
290if  [ ! -z "$pgoptions_user" ] ; then
291  pgconnect_postgres=" $pgconnect_postgres $pgoptions_user "
292fi
293
294pgconnect_user=" -d $dbname -U $username $pgconnect_user "
295
296
297#  TODO: defines this HowTo before in case there is an error above
298how_to_remove=" ## To delete the imported or temporary data, you might need to \n
299## run something similar to this (use with care the lines with the '*'): \n
300# To remove osmosis temporary data: \n
301rm osmosis-0.*.tgz* \n
302rm -r osmosis-*/ \n
303rm -r $temporarydir \n
304# To delete the imported data, you may need to run: \n
305dropdb $pgconnect_postgres $pgoptions_user $dbname \n
306# If you created a tablespace: \n
307echo \"DROP TABLESPACE $tablespace;\" | ${psql_folder}psql $pgconnect_postgres $pgoptions_user  \n
308# If you created a tablespace or used the --install option: \n
309sudo rm -r $datadir \n
310"
311
312postgres_installer_name_start=postgresplus-8.4.1-2-linux
313postgres_installer_name_end=.bin
314postgres_installer_name=$postgres_installer_name_start$archfilestring$postgres_installer_name_end
315if [ "$install" = "yes" ]; then
316  mkdir -p -v $installdir
317  if [ "$arch" = "x64" ]; then
318    archfilestring="-64"
319  else
320    archfilestring=""
321  fi
322
323  
324
325  if [ ! -e "$postgres_installer_name" ]; then # installer not found in local folder
326    echo "One-click installer is about to be downloaded."
327    wget http://get.enterprisedb.com/ga/$postgres_installer_name
328  fi
329  chmod a+x $postgres_installer_name
330  echo currentdir:`pwd`
331
332
333  echo "Enter your sudo or root password to execute the following command:"
334  echo "$postgres_installer_name --prefix $installdir --datadir $datadir --serverport $port --mode unattended --unattendedmodeui minimal"
335  if [ -z `which sudo` ]; then
336    # 'sudo' does not exist. We are probably not on Ubuntu, we can su root
337    su - root -c "./$postgres_installer_name --prefix $installdir --datadir $datadir --serverport $port --mode unattended --unattendedmodeui      minimal"
338  else
339    # 'sudo' exists (we may be on Ubuntu)
340    sudo ./$postgres_installer_name --prefix $installdir --datadir $datadir --serverport $port --mode unattended --unattendedmodeui       minimal
341  fi
342
343# Add new psql to the path. Thanks tahongawaka for pointing the need for this.
344# See discussion at https://sourceforge.net/projects/osm2postgresql/forums/forum/1683571/topic/4507607
345if  [ -z "$bash_init_script" ] ; then
346    bash_init_script=~/.bashrc # use default value
347fi
348echo "Postgres server installed. Adding the path to '$installdir/bin/psql' in $bash_init_script"
349touch $bash_init_script # make sure the file exists
350echo "" >> $bash_init_script # add an empty line
351echo "PATH=$installdir/bin:\$PATH; export PATH" >> $bash_init_script
352echo "The file '$bash_init_script' now contains the following code related to the PATH:"
353cat  $bash_init_script | grep PATH
354
355# Should fix bug "--install option: fails to export path"
356# https://sourceforge.net/tracker/?func=detail&atid=2072919&aid=3480106&group_id=509895
357psql_folder=$installdir/bin/
358
359echo "Will create database $dbname now."
360  set PGPASSWORD=postgres
361echo " BEGIN; CREATE USER $username; ALTER USER $username SUPERUSER;
362ALTER USER $username WITH PASSWORD '`echo $password`' ; COMMIT;
363CREATE DATABASE \"$dbname\" WITH OWNER $username TEMPLATE template_postgis;
364  \\c $dbname
365  \\i $installdir/share/postgresql/contrib/hstore.sql
366  " | ${psql_folder}psql $pgconnect_postgres -d postgres postgres --echo-queries --echo-hidden
367fi
368
369if [[ ( "$pgpass" = "yes" ) ]]; then
370    echo "$host:$port:$dbname:$username:$password" >> ~/.pgpass
371    chmod 0600 ~/.pgpass
372fi
373
374
375if [[ ( "$install" = "no" ) && ( "$createdb" = "yes" ) ]]; then
376  echo "Will try to create the database '$dbname'"
377  echo $pgconnect_postgres
378  echo "CREATE DATABASE \"$dbname\" WITH OWNER = $username;" | ${psql_folder}psql $pgconnect_postgres --echo-queries
379
380  if [ ! -z "$tablespace" ] ; then # Create tablespace. Folder MUST be owned by postgres (this is a postgresql limitation)
381    if [ -z "$no_mkdir" ] ; then
382      mkdir -p ${datadir%/*} # Create parent directory (does not complain if already exists)
383      mkdir $datadir # Will exit with error if fails or already exists
384
385      echo "Please enter your sudo or root password to create tablespace:"
386      if [ -z `which sudo` ]; then
387        su - root -c "chown postgres:postgres $datadir"
388      else
389        sudo chown postgres:postgres $datadir
390      fi
391    fi
392
393    echo "CREATE TABLESPACE $tablespace OWNER $username LOCATION '$datadir'; \
394     ALTER DATABASE $dbname SET TABLESPACE $tablespace;" | ${psql_folder}psql $pgconnect_postgres
395  fi
396
397  # Test if plpgsql is installed (should be true in postgresql 9.0 and false in 8.4, by default)
398  plpgsql_installed=`echo "SELECT 'plpgsql_is_installed'::varchar FROM pg_language WHERE lanname='plpgsql';" \
399    | ${psql_folder}psql $pgconnect_user | grep plpgsql_is_installed | sed 's/ //g'`
400
401  if [ "$plpgsql_installed" == "plpgsql_is_installed" ]; then
402      echo "plpgsql is already installed"
403    else
404      createlang  $pgconnect_user plpgsql
405  fi
406
407  ${psql_folder}psql  $pgconnect_user --file $postgisfolder/postgis.sql
408  ${psql_folder}psql  $pgconnect_user --file $postgisfolder/spatial_ref_sys.sql
409  ${psql_folder}psql  $pgconnect_user --file $hstorefolder/hstore.sql
410
411fi
412
413
414if [[ ! -f osmosis-0.40.tgz ]]; then
415  wget http://dev.openstreetmap.org/~bretth/osmosis-build/osmosis-0.40.tgz
416fi
417tar --extract --gzip --keep-old-files --file osmosis-0.40.tgz # Keep old (user-customized) files
418osmosis_version=osmosis-0.40
419echo $osmosis_version
420
421
422
423${psql_folder}psql $pgconnect_user -f ./$osmosis_version/script/pgsnapshot_schema_0.6.sql  # -d $dbname
424${psql_folder}psql $pgconnect_user -f ./$osmosis_version/script/pgsnapshot_schema_0.6_bbox.sql  # -d $dbname
425${psql_folder}psql $pgconnect_user -f ./$osmosis_version/script/pgsnapshot_schema_0.6_linestring.sql  # -d $dbname
426
427execdir=`pwd`
428echo "Will create temporarydir $temporarydir"
429date +"%Y-%m-%d_%H:%M:%S"
430echo "Time: Starting import with Osmosis at `date +"%Y-%m-%d_%H:%M:%S"`"
431mkdir $temporarydir 
432
433file_descr=`du --bytes --time $osmfile`
434
435./$osmosis_version/bin/osmosis -v --read-$filetype file="$osmfile" --buffer --write-pgsql-dump  enableBboxBuilder=yes enableLinestringBuilder=yes  nodeLocationStoreType="TempFile" directory="$temporarydir" 
436
437
438
439# Adapted from osmosis 0.40.
440# See in the pgsnapshot_load_0.6.sql file:  "Comment these out if the COPY files include bbox or linestring column values."
441cd $temporarydir
442
443
444
445${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
446  -- Drop all primary keys and indexes to improve load speed.
447  ALTER TABLE nodes DROP CONSTRAINT pk_nodes;
448  ALTER TABLE ways DROP CONSTRAINT pk_ways;
449  ALTER TABLE way_nodes DROP CONSTRAINT pk_way_nodes;
450  ALTER TABLE relations DROP CONSTRAINT pk_relations;
451  ALTER TABLE relation_members DROP CONSTRAINT pk_relation_members;
452  DROP INDEX idx_nodes_geom;
453  DROP INDEX idx_way_nodes_node_id;
454  DROP INDEX idx_relation_members_member_id_and_type;
455  DROP INDEX idx_ways_bbox;
456  DROP INDEX idx_ways_linestring;
457
458  -- Import the table data from the data files using the fast COPY method.
459  \copy users FROM 'users.txt'
460  \copy nodes FROM 'nodes.txt'
461  \copy ways FROM 'ways.txt'
462  \copy way_nodes FROM 'way_nodes.txt'
463  \copy relations FROM 'relations.txt'
464  \copy relation_members FROM 'relation_members.txt'
465
466  -- Add the primary keys and indexes back again (except the way bbox index).
467  ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
468  ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
469  ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
470  ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
471  ALTER TABLE ONLY relation_members ADD CONSTRAINT pk_relation_members PRIMARY KEY (relation_id, sequence_id);
472  CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
473  CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
474  CREATE INDEX idx_relation_members_member_id_and_type ON relation_members USING btree (member_id, member_type);
475
476  -- Index the way bounding box column.
477  CREATE INDEX idx_ways_bbox ON ways USING gist (bbox);
478  CREATE INDEX idx_ways_linestring ON ways USING gist (linestring);
479
480  -- Update all clustered tables because it doesn't happen implicitly.
481  CLUSTER nodes USING idx_nodes_geom;
482  CLUSTER ways USING idx_ways_linestring;
483
484  -- Perform database maintenance due to large database changes.
485  VACUUM ANALYZE;
486_EOF_
487
488date +"%Y-%m-%d_%H:%M:%S"
489echo "Time: Finished import with Osmosis at `date +"%Y-%m-%d_%H:%M:%S"`"
490
491cd $execdir
492
493# For debugging, you can use this instead:
494# ${psql_folder}psql $pgconnect_user  -d $dbname --echo-queries  <<- _EOF_
495${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
496-- DROP TABLE nodes_with_tags
497CREATE TABLE nodes_with_tags AS (SELECT *, NULL::text as class FROM nodes WHERE cast (tags as text) !='');
498ALTER TABLE nodes_with_tags ADD COLUMN idint4 int4;
499UPDATE nodes_with_tags SET idint4 = id;
500CREATE UNIQUE INDEX idx_nodes_idint4 ON nodes_with_tags (idint4);
501ALTER TABLE nodes_with_tags RENAME COLUMN tags TO tagshstore;
502ALTER TABLE nodes_with_tags ADD COLUMN tags text;
503UPDATE nodes_with_tags SET tags = regexp_replace(tagshstore::text, '=>', '=', 'g');
504CREATE INDEX idx_nodes_with_tags_geom ON nodes_with_tags USING GIST (geom);
505
506INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column,
507  coord_dimension, srid, "type")
508SELECT '', 'public', 'nodes_with_tags', 'geom', ST_CoordDim(geom), ST_SRID(geom), GeometryType(geom)
509FROM public.nodes_with_tags LIMIT 1;
510-- http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column
511
512-- Delete nodes that have only no informative tags
513DELETE FROM nodes_with_tags 
514WHERE idint4 IN (
515  SELECT idint4 FROM nodes_with_tags
516  WHERE (tags like '%"source"=%'
517  or  tags like '%"created_by"=%'
518  or  tags like '%"converted_by"=%')
519  and array_upper(avals(tagshstore), 1) = 1
520 -- length of the hstore=1
521);
522
523DELETE FROM nodes_with_tags 
524WHERE idint4 IN (
525  SELECT idint4 FROM nodes_with_tags
526  WHERE ((tags like '%"source"=%' AND tags like '%"created_by"=%')
527OR (tags like '%"source"=%' AND tags like '%"converted_by"=%')
528OR (tags like '%"created_by"=%' AND tags like '%"converted_by"=%')
529)
530  and array_upper(avals(tagshstore), 1) = 2
531 -- length of the hstore=2
532);
533_EOF_
534
535## Reproject the geometries if asked by user
536if [ ! "$EPSG" = "do_not_reproject" ]; then
537echo "Time: Starting reprojection at `date +"%Y-%m-%d_%H:%M:%S"`"
538  ${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
539  UPDATE nodes_with_tags SET geom = ST_SetSRID(ST_Transform(geom, $EPSG), $EPSG);
540  ALTER TABLE ways drop constraint enforce_srid_bbox;
541  ALTER TABLE ways drop constraint enforce_srid_linestring;
542  UPDATE ways SET linestring = ST_SetSRID(ST_Transform(linestring, $EPSG), $EPSG);
543  UPDATE ways SET bbox = ST_SetSRID(ST_Transform(bbox, $EPSG), $EPSG);
544  SELECT Populate_Geometry_Columns(); -- update geometry_columns after all reprojections
545
546-- TODO (to check): According to QGIS:
547-- epsg:9OO913  +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +over +no_defs
548-- epsg:3785  +proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +a=6378137 +b=6378137 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs
549_EOF_
550fi
551echo "Time: Finished reprojection at `date +"%Y-%m-%d_%H:%M:%S"`"
552
553${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
554ALTER TABLE ways ADD COLUMN idint4 int4;
555UPDATE ways SET idint4 = id;
556CREATE UNIQUE INDEX idint4idx ON ways (idint4);
557ALTER TABLE ways RENAME COLUMN tags TO tagshstore;
558ALTER TABLE ways ADD COLUMN tags text;
559UPDATE ways SET tags = regexp_replace(tagshstore::text, '=>', '=', 'g');
560
561ALTER TABLE ways ADD COLUMN name text;
562ALTER TABLE nodes_with_tags ADD COLUMN name text;
563UPDATE nodes_with_tags SET name = tagshstore -> 'name' WHERE tagshstore ? 'name';
564UPDATE ways SET name = tagshstore -> 'name' WHERE tagshstore ? 'name';
565
566
567-- -- -- -- -- --
568--  POLYGONS - --
569-- -- -- -- -- --
570-- CREATE TABLE WITH POLYGONS MADE OF A SINGLE LINESTRING
571-- DROP TABLE simple_polys;
572CREATE TABLE simple_polys AS(
573  SELECT  idint4,
574    ST_MakePolygon(linestring) as polygon,
575    regexp_replace(tags::text, '=>', '=', 'g')  as tags,
576    tagshstore,
577    area(ST_MakePolygon(linestring)) as area -- TODO: convert into metric system (or better do it on projected, metric data, processing eveything after reprojection)
578  FROM ways
579  WHERE IsClosed(linestring)
580    and NPoints(linestring) > 3
581-- new in version 0.2
582--    and idint4 not in (SELECT member_id FROM relation_members WHERE member_role = 'outer')
583);
584ANALYZE VERBOSE simple_polys;
585CREATE UNIQUE INDEX idx_idint4_simple_polys ON simple_polys (idint4);
586
587
588-- ADD polygons to TABLE relations
589ALTER TABLE relations ADD COLUMN idint4 int4;
590UPDATE relations SET idint4 = id::int4;
591CREATE UNIQUE INDEX idx_idint4 ON relations (idint4);
592
593ALTER TABLE relations RENAME COLUMN tags TO tagshstore;
594ALTER TABLE relations ADD COLUMN tags text;
595UPDATE relations SET tags = regexp_replace(tagshstore::text, '=>', '=', 'g');
596
597ALTER TABLE relations ADD COLUMN outerring_linestring geometry;
598ALTER TABLE relations ADD COLUMN outerring_array int4[];
599ALTER TABLE relations ADD COLUMN outerring geometry;
600
601ALTER TABLE relations ADD COLUMN innerring_linestring geometry[];
602
603ALTER TABLE relations ADD COLUMN polygon geometry;
604
605-- Create an ARRAY of all outerrings
606UPDATE relations SET outerring_array = (
607  SELECT  array_agg( r1.member_id) as array1
608  FROM relation_members r1, ways
609  WHERE r1.member_role = 'outer'
610    and r1.relation_id = relations.id
611    and ways.idint4 = r1.member_id
612    and NPoints(ways.linestring) > 1 and IsValid(ways.linestring)
613  GROUP BY r1.relation_id
614);
615
616-- Create outerring linestring (not checked if valid yet)
617UPDATE relations SET outerring_linestring = (
618  SELECT   ST_LineMerge(ST_Collect(ways.linestring))
619  FROM relation_members r1, ways
620  WHERE r1.member_role = 'outer'
621  and r1.relation_id = relations.id
622  and 
623  NPoints(ways.linestring) > 1 and IsValid(ways.linestring) 
624  and ways.idint4 = r1.member_id
625  GROUP BY r1.relation_id
626);
627
628-- Create innerrings linestrings (not checked if valid yet)
629UPDATE relations SET innerring_linestring = (
630    ARRAY(
631	  SELECT ST_LineMerge(ST_Collect(linestring)) as inner_ring FROM ways WHERE ways.idint4  IN
632	    (SELECT member_id FROM relation_members r1
633		WHERE r1.relation_id = relations.idint4 and r1.member_role = 'inner'
634	    )
635	)
636  )
637  WHERE array_length(ARRAY(
638	  SELECT ST_LineMerge(ST_Collect(linestring)) as inner_ring FROM ways WHERE ways.idint4  IN
639	    (SELECT member_id FROM relation_members r1
640		WHERE r1.relation_id = relations.idint4 and r1.member_role = 'inner'
641	    )
642	),1) >0 --check that there is at least one inner line
643;
644
645ALTER TABLE relations ADD COLUMN poly_type text;
646
647UPDATE relations SET poly_type= 'unknown';
648
649-- A ring with only 3 points is flat: A-B-A (1st point = 3rd point), hence buggy
650UPDATE relations SET poly_type= 'no valid outerring' WHERE 
651NPoints(outerring_linestring) < 4 -- 5 relations are buggy in italy.osm
652or outerring_linestring IS NULL -- about 16000 (relations between simple nodes?)
653; 
654-- The above must be done before what follows, because if less than 3 points, test may crash
655UPDATE relations SET poly_type= 'no valid outerring' WHERE 
656poly_type = 'unknown'
657and NOT IsClosed( outerring_linestring); -- 136 are buggy in italy.osm
658
659UPDATE relations SET poly_type= 'no valid outerring' WHERE 
660poly_type = 'unknown'
661and NOT IsSimple( outerring_linestring); -- 102 more are buggy in italy.osm
662
663
664-- If (NOT poly_type= 'no valid outerring') after the above, it means there is a valid outerring. Now, let us see if there is a valid inerring (or several)
665
666-- If there is no inner line, there is no valid inerring
667UPDATE relations SET poly_type= 'no valid inerring'
668WHERE poly_type = 'unknown'
669and innerring_linestring IS NULL
670; -- 3015 more have no valid inerring
671
672-- Innering must be closed
673UPDATE relations SET poly_type= 'no valid inerring'
674WHERE poly_type = 'unknown'
675and (NOT ISClosed(ST_LineMerge(ST_Collect(innerring_linestring ))))
676; -- 44 more are buggy
677
678-- Innering must be big enough
679-- FIXME: this checks that all the innerings together have more than 3 points, not that each innering is valid
680UPDATE relations SET poly_type= 'no valid inerring'
681WHERE poly_type = 'unknown'
682and NPoints(ST_LineMerge(ST_Collect(innerring_linestring ))) <4
683; 
684
685
686
687-- Check further validity of innerring: closed and big enough
688-- However, if there are several holes and only one is too small, the test based on NPoints()
689-- will not be sensitive enough (such wrong relations are probably extremely rare; none found in italy.osm).
690-- FIXME: is this still necessary after the tests above?
691UPDATE relations SET poly_type= 'valid innerring'
692WHERE poly_type = 'unknown'
693and ( ISClosed(ST_LineMerge(ST_Collect(innerring_linestring ))))
694and NPoints(ST_LineMerge(ST_Collect(innerring_linestring ))) > 3
695; 
696
697-- SELECT id,poly_type FROM relations WHERE ISClosed(ST_LineMerge(ST_Collect(innerring_linestring ))) and NPoints(ST_LineMerge(ST_Collect(innerring_linestring )))=3; --should give no result if OSM data were perfect; not the case (2 results for italy.osm).
698
699-- TODO: try to collect the tags FROM the rings and assign them to the relation if relevant
700
701UPDATE relations SET polygon = 
702ST_MakePolygon(outerring_linestring, (innerring_linestring ))
703WHERE poly_type= 'valid innerring'
704and GeometryType(outerring_linestring) ='LINESTRING';
705
706-- The complex polygons that are valid no longer need to be represented with their outerring only
707-- and not deleting those simple_polys will prevent insertion in the final polygon UNION below
708DELETE FROM simple_polys WHERE simple_polys.idint4 IN (SELECT member_id FROM relation_members WHERE member_role='outer' and relation_id IN
709(SELECT id FROM relations WHERE poly_type='valid innerring' and ST_IsValid(polygon)
710)
711);
712
713DELETE FROM ways WHERE ways.idint4 IN (SELECT member_id FROM relation_members WHERE member_role='outer' and relation_id IN
714(SELECT id FROM relations WHERE poly_type='valid innerring' and ST_IsValid(polygon))
715);
716
717-- Also clean useless innerrings stored as simple_polys or ways
718DELETE FROM simple_polys WHERE simple_polys.idint4 IN (
719  SELECT member_id FROM relation_members WHERE member_role='inner' and relation_id IN
720    (SELECT id FROM relations WHERE poly_type='valid innerring' and ST_IsValid(polygon))
721  )
722and akeys(tagshstore)::text='{}'
723;
724
725DELETE FROM ways WHERE ways.idint4 IN (
726  SELECT member_id FROM relation_members WHERE member_role='inner' and relation_id IN
727    (SELECT id FROM relations WHERE poly_type='valid innerring' and ST_IsValid(polygon))
728  )
729and akeys(tagshstore)::text='{}'
730;
731
732
733-- Create simple polygon not having (valid) innering(s)
734UPDATE relations SET polygon = MakePolygon(outerring_linestring)
735WHERE (poly_type = 'no valid inerring'
736OR poly_type = 'unknown')
737--   and id=1309665
738and GeometryType((outerring_linestring)) ='LINESTRING'
739;
740
741
742-- Disaggregate multilines into linestrings, one per row
743DROP TABLE if exists dumped_multilinestring;
744CREATE TABLE dumped_multilinestring AS (
745SELECT relations.id as relation_id, tags, tagshstore,
746  generate_series(1,(Select ST_NumGeometries(outerring_linestring))) as lineseq,
747  ST_GeometryN(outerring_linestring, generate_series(1, ST_NumGeometries (outerring_linestring))) AS outerring_linestring
748 FROM relations
749  WHERE GeometryType(outerring_linestring) ='MULTILINESTRING' --anyway the query would not give any 'LINESTRING'
750);
751
752CREATE INDEX idx_dumped_multilinestring_relation_id ON dumped_multilinestring (relation_id);
753
754ALTER TABLE dumped_multilinestring ADD COLUMN outerring_polygon geometry;
755UPDATE dumped_multilinestring SET outerring_polygon = MakePolygon(outerring_linestring)
756  WHERE isclosed(outerring_linestring);
757ALTER TABLE dumped_multilinestring ADD COLUMN idint4 int4;
758UPDATE dumped_multilinestring SET idint4 = relation_id*1000 + lineseq; --*might* create duplicate id if more than 999 linestrings in a single multilinestring AND if bad luck
759
760
761INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") (SELECT  ''::text, 'public'::text, 'dumped_multilinestring'::text, 'outerring_polygon'::text, 2::integer, 4326::integer, 'GEOMETRY'::text FROM dumped_multilinestring limit 1);
762
763-- TODO: add other rows in geometry_columns
764
765-- Delete polygons from simple_polys that are also stored in dumped_multilinestring and have essentially similar or less informative tags
766DELETE FROM simple_polys WHERE simple_polys.idint4 IN (
767SELECT  s.idint4
768FROM simple_polys s, relation_members, dumped_multilinestring m
769WHERE  s.idint4 = relation_members.member_id AND
770  m.relation_id = relation_members.relation_id
771and  (array_length(akeys(s.tagshstore),1)  < array_length(akeys(m.tagshstore),1) OR akeys(s.tagshstore)::text='{}')
772and relation_members.member_role ='outer' -- keep a simple polygon if it is just an inner in multilinestring
773and (
774-- array_length(akeys(s.tagshstore),1) =0  is not true when tagshstore is empty
775akeys(s.tagshstore)::text='{}'
776OR s.tagshstore -> 'natural' = m.tagshstore -> 'natural' -- sometimes the tags for the simple_polys are quite different; this tries to exclude those by testing that the values is the same for at least one of the 3 follwing keys
777OR s.tagshstore -> 'landuse' = m.tagshstore -> 'building'
778OR s.tagshstore -> 'landuse' = m.tagshstore -> 'building')
779group by s.idint4 )
780;
781ANALYZE VERBOSE simple_polys;
782
783-- Put all polygons in a single table
784DROP TABLE IF EXISTS polygons ;
785CREATE TABLE polygons AS ( 
786  SELECT idint4 as parentid, tags, tagshstore, polygon, id as relation_id, NULL::text as class  FROM relations
787 UNION ALL
788  SELECT idint4 as parentid, tags, tagshstore, polygon, NULL::int4  as relation_id, NULL::text as class  FROM simple_polys
789 UNION ALL
790  SELECT idint4 as parentid, tags, tagshstore, outerring_polygon, relation_id, NULL::text as class  FROM dumped_multilinestring
791);
792
793-- The above does NOT guarantee uniqueness of idint4 (maybe is it necessary to check whether relation ids overlap with ways id?).
794-- For example, with above query, there are duplicates parentid, raising e.g. (asia.osm):
795-- -- ERROR:  could not create unique index "idx_polygons"
796--   Key (idint4)=(48582015) is duplicated.
797-- id        tag                                                                          relation_id;  class
798-- http://www.openstreetmap.org/browse/relation/48582
799-- 48582015; ""type"="multipolygon"";                                                     48582;        ""
800-- http://www.openstreetmap.org/browse/way/48582015
801-- 48582015; ""building"="yes", "addr:street"="Советская улица", "addr:housenumber"="79"";              ;"building"
802
803
804-- From http://blog.enricostahn.com/2010/06/11/postgresql-add-primary-key-to-an-existing-table.html
805ALTER TABLE polygons   ADD COLUMN idint4 INTEGER;
806CREATE SEQUENCE polygons_idint4_seq;
807UPDATE polygons SET idint4 = nextval('polygons_idint4_seq');
808ALTER TABLE polygons ALTER COLUMN idint4 SET DEFAULT nextval('polygons_idint4_seq');
809ALTER TABLE polygons ALTER COLUMN idint4 SET NOT NULL;
810ALTER TABLE polygons ADD UNIQUE (idint4);
811ALTER TABLE polygons DROP CONSTRAINT polygons_idint4_key RESTRICT;
812ALTER TABLE polygons ADD PRIMARY KEY (idint4);
813
814CREATE UNIQUE INDEX idx_polygons ON polygons (idint4);
815CREATE INDEX idx_polygons_geom ON polygons USING GIST (polygon);
816ALTER TABLE polygons CLUSTER ON idx_polygons_geom; -- Very fast (few ms for large dataset)
817-- TODO: is it usefull to do the same as what the load script does for ways? See below:
818--    CREATE INDEX idx_ways_bbox  ON ways USING gist (bbox);
819--    CREATE INDEX idx_ways_linestring ON ways USING gist (linestring);
820--    ALTER TABLE ways CLUSTER ON idx_ways_linestring;
821
822
823
824ALTER TABLE polygons ADD COLUMN name text;
825UPDATE polygons SET name= tagshstore -> 'name' WHERE exist(tagshstore,'name')
826;
827
828
829
830SELECT Populate_Geometry_Columns(); -- update geometry_columns
831
832
833ALTER TABLE polygons ADD COLUMN area float;
834UPDATE polygons SET area=ST_Area(polygon);
835
836-- To speedup reading tags (hstore has index support for @> and ? operators)
837CREATE INDEX ways_tagshstore_idx ON ways USING GIST(tagshstore);
838
839-- ALTER TABLE ways DROP COLUMN isarea;
840ALTER TABLE ways ADD COLUMN isarea boolean;
841ALTER TABLE ways ADD COLUMN isclosed boolean;
842UPDATE ways SET isclosed = true WHERE  IsClosed("linestring");
843
844UPDATE ways SET isarea = false
845WHERE isclosed=true and isarea IS NULL
846and NOT (tagshstore @> 'area=>yes')
847and (
848tagshstore @> 'area=>no'
849OR (tagshstore ? 'highway' and not(tagshstore @> 'highway=>services')) -- different from highway=>service WITHOUT 's'
850OR tagshstore @> 'junction=>roundabout'
851OR tagshstore ? 'barrier'
852);
853ANALYZE VERBOSE ways (isarea);
854
855UPDATE ways SET isarea = true
856WHERE isclosed=true and
857isarea IS NULL
858and NOT (tagshstore @> 'area=>no')
859and (
860-- tagshstore -> 'area' ='yes' -- might be slow. The following uses the index if exists
861tagshstore @> 'area=>yes'
862OR tagshstore @> 'amenity=>parking'
863OR tagshstore @> 'building=>yes'
864OR (tagshstore ? 'building' AND NOT(tagshstore @> 'building=>no'))
865OR tagshstore @> 'aeroway=>aerodrome'
866OR tagshstore @> 'waterway=>riverbank'
867or tagshstore @> 'highway=>services'
868OR tagshstore @> 'place=>suburb'
869OR tagshstore ? 'landuse'
870OR tagshstore ? 'wood'
871OR tagshstore ? 'place'
872OR tagshstore ? 'leisure' -- could this wrongly classify a closed 'leisure' track?
873OR tagshstore ? 'amenity'
874-- OR tagshstore ? ''
875OR tagshstore ? 'sport'  -- this might wrongly classify a closed sport track
876OR (tagshstore ? 'natural' AND NOT (tagshstore @> 'natural=>coastline'))
877OR tagshstore @> 'power=>station'
878 -- all closed natural ways are areas, with the (rare?) exception of circular cliff (which must have area=>no) or very small islands as single-way/closed coastline
879-- OR tagshstore @> '=>'
880)
881;
882
883-- Known closed ways that are not areas:
884--  "junction"="roundabout"
885DELETE FROM  ways WHERE isarea = true
886and idint4 in (SELECT idint4 FROM polygons);
887
888-- Extracting a small area from a large .osm file with osmosis 0.38 does cut some (all?) polygons and leave them without some of the children nodes. You will see it if you do the following SELECT at this point:
889-- SELECT id, tagshstore, nodes,NPOINTS(linestring), ST_asEWKT(linestring) from ways WHERE  isarea = true and NPOINTS(linestring)=3; -- note: complains if you do not filter  NPOINTS(linestring)=3, try this:
890-- SELECT id, tagshstore, nodes,NPOINTS(linestring) from ways WHERE  isarea = true
891-- here is how to remove those (destroyed) polygons
892DELETE FROM  ways WHERE isarea = true and NPOINTS(linestring)<4;
893-- Normally at this point, the following query should not return any row. Otherwise please add here or above the SQL code to manage those cases.
894-- SELECT * FROM  ways WHERE isarea = true ;
895-- those polygons might be there for the same reason:
896DELETE FROM  ways WHERE NPOINTS(linestring)<2;
897-- SELECT count(id) FROM  ways WHERE NPOINTS(linestring)=1 ;
898-- Note that with old postgis it is not possible to do:
899-- SELECT id FROM  ways WHERE not isvalid(linestring) ;
900-- because of bug: http://trac.osgeo.org/postgis/ticket/408
901
902
903
904-- Create a "class" field for easy rendering with GIS software (based on a single field)
905-- Note: QGIS's rule-based renderer can use complex queries at the style level with no need for this
906-- See http://www.qgis.org/wiki/Using_OpenStreetMap_data
907-- and http://trac.osgeo.org/qgis/ticket/3222
908-- This is provided as a convenience for the user. This is still beta quality but should be useful.
909-- Below, instead of:
910--    tags like '%"building"="no"%'
911--  this is used
912--     where tagshstore @> 'building=>no'::hstore
913--  After a first pass, hstore-based query are about twice faster than like-based queries.
914
915ALTER TABLE ways ADD COLUMN class text;
916 UPDATE ways  SET class='aerialway' WHERE class IS NULL AND tags like '%aerialway%' ;
917 UPDATE ways  SET class='coastline' WHERE class IS NULL AND tagshstore @> ('natural' => 'coastline')  ;
918 UPDATE ways  SET class='ferry' WHERE class IS NULL AND tags like '%ferry%' ;
919 UPDATE ways  SET class='footway' WHERE class IS NULL AND tagshstore @> ('highway' => 'footway')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
920 UPDATE ways  SET class='motorway' WHERE class IS NULL AND tagshstore @> ('highway' => 'motorway')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) AND NOT (tagshstore @> ('bridge' => 'yes') ) ;
921 UPDATE ways  SET class='motorway (bridge)' WHERE class IS NULL AND tagshstore @> ('highway' => 'motorway')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) AND (tagshstore @> ('bridge' => 'yes') ) ;
922 UPDATE ways  SET class='motorway link' WHERE class IS NULL AND tagshstore @> ('highway' => 'motorway_link')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) AND NOT (tagshstore @> ('bridge' => 'yes') ) ;
923 UPDATE ways  SET class='motorway link' WHERE class IS NULL AND tagshstore @> ('highway' => 'motorway_link')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) AND (tagshstore @> ('bridge' => 'yes') ) ;
924 UPDATE ways  SET class='national park' WHERE class IS NULL AND tagshstore @> ('boundary' => 'national_park')  ;
925 UPDATE ways  SET class='path' WHERE class IS NULL AND tagshstore @> ('highway' => 'path')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
926 UPDATE ways  SET class='pedestrian highway' WHERE class IS NULL AND tagshstore @> ('highway' => 'pedestrian')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
927 UPDATE ways  SET class='power line' WHERE class IS NULL AND tagshstore @> ('power' => '%line')  ;
928 UPDATE ways  SET class='primary highway' WHERE class IS NULL AND tagshstore @> ('highway' => 'primary')  AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
929 UPDATE ways  SET class='rail' WHERE class IS NULL AND tagshstore @> ('railway' => 'rail')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
930 UPDATE ways  SET class='rail' WHERE class IS NULL AND tagshstore @> ('railway' => 'rail')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
931 UPDATE ways  SET class='residential highway' WHERE class IS NULL AND tagshstore @> ('highway' => 'residential')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
932 UPDATE ways  SET class='road' WHERE class IS NULL AND tagshstore @> ('highway' => 'road')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
933 UPDATE ways  SET class='secondary highway' WHERE class IS NULL AND tagshstore @> ('highway' => 'secondary')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
934 UPDATE ways  SET class='service highway' WHERE class IS NULL AND tagshstore @> ('highway' => 'service')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
935 UPDATE ways  SET class='steps' WHERE class IS NULL AND tagshstore @> ('highway' => 'steps')  ;
936 UPDATE ways  SET class='tertiary highway' WHERE class IS NULL AND ((tagshstore @> ('highway' => 'tertiary') ) OR (tagshstore @> ('highway' => 'tertiary_link') )) AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
937 UPDATE ways  SET class='grade-5 track' WHERE class IS NULL AND tagshstore @> ('highway' => 'track')  AND tagshstore @> ('tracktype' => 'grade5')  ;
938 UPDATE ways  SET class='grade-4 track' WHERE class IS NULL AND tagshstore @> ('highway' => 'track')  AND tagshstore @> ('tracktype' => 'grade4')  ;
939 UPDATE ways  SET class='grade-3 track' WHERE class IS NULL AND tagshstore @> ('highway' => 'track')  AND tagshstore @> ('tracktype' => 'grade3')  ;
940 UPDATE ways  SET class='grade-2 track' WHERE class IS NULL AND tagshstore @> ('highway' => 'track')  AND tagshstore @> ('tracktype' => 'grade2')  ;
941 UPDATE ways  SET class='grade-1 track' WHERE class IS NULL AND tagshstore @> ('highway' => 'track')  AND tagshstore @> ('tracktype' => 'grade1')  ;
942 UPDATE ways  SET class='track' WHERE class IS NULL AND tagshstore @> ('highway' => 'track')  AND NOT (tagshstore @> ('tracktype' => 'grade') ) ;
943 UPDATE ways  SET class='unclassified highway' WHERE class IS NULL AND tagshstore @> ('highway' => 'unclassified')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
944 UPDATE ways  SET class='river' WHERE class IS NULL AND tagshstore @> ('waterway' => 'river')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
945 UPDATE ways  SET class='stream' WHERE class IS NULL AND tagshstore @> ('waterway' => 'stream')   AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
946 UPDATE ways  SET class='waterway' WHERE class IS NULL AND (tags like '%"waterway"%' OR (tags like '%"water"%')) AND NOT (tags like '%"stream"%') ;
947 UPDATE ways  SET class='tunnel' WHERE class IS NULL AND tagshstore @> ('tunnel' => 'yes')  ;
948 UPDATE ways  SET class='level 1 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '1') ) and not (tags like '%coastline%') ;
949 UPDATE ways  SET class='level 2 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '2') ) and not (tags like '%coastline%') ;
950 UPDATE ways  SET class='level 3 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '3') ) and not (tags like '%coastline%') ;
951 UPDATE ways  SET class='level 4 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '4') ) and not (tags like '%coastline%') ;
952 UPDATE ways  SET class='level 5 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '5') ) and not (tags like '%coastline%') ;
953 UPDATE ways  SET class='level 6 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '6') ) and not (tags like '%coastline%') ;
954 UPDATE ways  SET class='level 7 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '7') ) and not (tags like '%coastline%') ;
955 UPDATE ways  SET class='level 8 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '8') ) and not (tags like '%coastline%') ;
956 UPDATE ways  SET class='level 9 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '9') ) and not (tags like '%coastline%') ;
957 UPDATE ways  SET class='level 0 admin. boundary' WHERE class IS NULL AND tagshstore @> ('boundary' => 'administrative')  and (tagshstore @> ('admin_level' => '10') ) and not (tags like '%coastline%') ;
958 UPDATE ways  SET class='trunk' WHERE class IS NULL AND tagshstore @> ('highway' => 'trunk')  AND NOT (tagshstore @> ('tunnel' => 'yes') ) ;
959 UPDATE ways  SET class='cycleway' WHERE class IS NULL AND tagshstore @> ('highway' => 'cycleway')  ;
960 UPDATE ways  SET class='trunk link' WHERE class IS NULL AND tagshstore @> ('highway' => 'trunk_link')  ;
961 UPDATE ways  SET class='primary link' WHERE class IS NULL AND tagshstore @> ('highway' => 'primary_link')  ;
962 UPDATE ways  SET class='secondary link' WHERE class IS NULL AND tagshstore @> ('highway' => 'secondary_link')  ;
963 UPDATE ways  SET class='airplane taxiway' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'taxiway')  ;
964 UPDATE ways  SET class='runway' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'runway')  ;
965 UPDATE ways  SET class='living street' WHERE class IS NULL AND tagshstore @> ('highway' => 'living_street')  ;
966 UPDATE ways  SET class='motorway in construction' WHERE class IS NULL AND tags like '%"construction"%' and tags like '%"motorway"%' ;
967 UPDATE ways  SET class='highway in construction' WHERE class IS NULL AND tags like '%"construction"%' and NOT (tags like '%"motorway"%') ;
968 UPDATE ways  SET class='fence' WHERE class IS NULL AND tagshstore @> ('barrier' => 'fence')   ;
969 UPDATE ways  SET class='hedge' WHERE class IS NULL AND tagshstore @> ('barrier' => 'hedge')   ;
970 UPDATE ways  SET class='wall' WHERE class IS NULL AND tagshstore @> ('barrier' => '%wall')   ;
971 UPDATE ways  SET class='pier' WHERE class IS NULL AND tagshstore @> ('man_made' => 'pier')   ;
972 UPDATE ways  SET class='railway' WHERE class IS NULL AND tags like '%"railway"%'  AND NOT (tags like '%"rail"%')   AND NOT (tags like '%"platform"%')    ;
973 UPDATE ways  SET class='building' WHERE class IS NULL AND tags like '%"building"%' ;
974 UPDATE ways  SET class='residential' WHERE class IS NULL AND tagshstore @> ('landuse' => 'residential')  ;
975 UPDATE ways  SET class='parking' WHERE class IS NULL AND tags like '%"parking"%' ;
976 UPDATE ways  SET class='industrial' WHERE class IS NULL AND tagshstore @> ('landuse' => 'industrial')  ;
977 UPDATE ways  SET class='downhill piste' WHERE class IS NULL AND tagshstore @> ('piste:type' => 'downhill')  ;
978 UPDATE ways  SET class='cliff' WHERE class IS NULL AND tags like '%"cliff"%' ;
979 UPDATE nodes_with_tags SET class='cash dispenser' WHERE class IS NULL AND tags like '%"atm"%' ;
980 UPDATE nodes_with_tags SET class='bank' WHERE class IS NULL AND tags like '%"bank"%' ;
981 UPDATE nodes_with_tags SET class='bench' WHERE class IS NULL AND tags like '%"bench%' ;
982 UPDATE nodes_with_tags SET class='buoy' WHERE class IS NULL AND tags like '%buoy%' ;
983 UPDATE nodes_with_tags SET class='bus stop' WHERE class IS NULL AND tags like '%"bus_stop%' ;
984 UPDATE nodes_with_tags SET class='bus station' WHERE class IS NULL AND tags like '%bus_station%' ;
985 UPDATE nodes_with_tags SET class='bar' WHERE class IS NULL AND  tags like '%"bar"%' ;
986 UPDATE nodes_with_tags SET class='cafe' WHERE class IS NULL AND tags like '%cafe%'  ;
987 UPDATE nodes_with_tags SET class='drinking water' WHERE class IS NULL AND tags like '%drinking_water%' ;
988 UPDATE nodes_with_tags SET class='fast food' WHERE class IS NULL AND tags like '%fast_food%' ;
989 UPDATE nodes_with_tags SET class='fountain' WHERE class IS NULL AND tags like '%fountain%' ;
990 UPDATE nodes_with_tags SET class='lpg fuel' WHERE class IS NULL AND tagshstore @> ('amenity' => 'fuel')  and tagshstore @> ('fuel:lpg' => 'yes')  ;
991 UPDATE nodes_with_tags SET class='cemetery' WHERE class IS NULL AND tags like '%grave_yard%' OR tags like '%cemetery%' ;
992 UPDATE nodes_with_tags SET class='hospital (incl. emergency)' WHERE class IS NULL AND tags like '%hospital%' and tagshstore @> ('emergency' => 'yes')   ;
993 UPDATE nodes_with_tags SET class='hospital' WHERE class IS NULL AND tags like '%hospital%' ;
994 UPDATE nodes_with_tags SET class='ice cream shop' WHERE class IS NULL AND tags like '%ice_cream%' ;
995 UPDATE nodes_with_tags SET class='parking (with fee)' WHERE class IS NULL AND tagshstore @> ('amenity' => 'parking')  and tagshstore @> ('fee' => 'yes')   ;
996 UPDATE nodes_with_tags SET class='pharmacy' WHERE class IS NULL AND tags like '%pharmacy%' ;
997 UPDATE nodes_with_tags SET class='police' WHERE class IS NULL AND tags like '%"police"%' ;
998 UPDATE nodes_with_tags SET class='post box' WHERE class IS NULL AND tags like '%post_box%' ;
999 UPDATE nodes_with_tags SET class='post office' WHERE class IS NULL AND tags like '%post_office%' ;
1000 UPDATE nodes_with_tags SET class='recycling' WHERE class IS NULL AND tags like '%"recycling%' ;
1001 UPDATE nodes_with_tags SET class='restaurant' WHERE class IS NULL AND tags like '%restaurant%' ;
1002 UPDATE nodes_with_tags SET class='shelter (with fireplace)' WHERE class IS NULL AND  tagshstore @> ('amenity' => 'shelter')  and tagshstore @> ('fireplace' => 'yes')  ;
1003 UPDATE nodes_with_tags SET class='shelter' WHERE class IS NULL AND  tagshstore @> ('amenity' => 'shelter')  ;
1004 UPDATE nodes_with_tags SET class='taxi' WHERE class IS NULL AND tagshstore @> ('amenity' => 'taxi')  ;
1005 UPDATE nodes_with_tags SET class='telephone' WHERE class IS NULL AND tags like  '%"amenity"="telephone"%' ;
1006 UPDATE nodes_with_tags SET class='theatre' WHERE class IS NULL AND tags like '%"theatre"%' ;
1007 UPDATE nodes_with_tags SET class='toilets (accessible with wheelchair)' WHERE class IS NULL AND tags like '%"toilets%' AND tagshstore @> ('wheelchair' => 'yes')  ;
1008 UPDATE nodes_with_tags SET class='toilets' WHERE class IS NULL AND tags like '%"toilets"%' ;
1009 UPDATE nodes_with_tags SET class='townhall' WHERE class IS NULL AND tags like '%townhall%' ;
1010 UPDATE nodes_with_tags SET class='vending machine' WHERE class IS NULL AND tags like '%vending_machine%' ;
1011 UPDATE nodes_with_tags SET class='locality' WHERE class IS NULL AND tagshstore @> ('place' => 'locality')  ;
1012 UPDATE nodes_with_tags SET class='hamlet' WHERE class IS NULL AND tagshstore @> ('place' => 'hamlet')  ;
1013 UPDATE nodes_with_tags SET class='village' WHERE class IS NULL AND tagshstore @> ('place' => 'village')  ;
1014 UPDATE nodes_with_tags SET class='town' WHERE class IS NULL AND tagshstore @> ('place' => 'town')  ;
1015 UPDATE nodes_with_tags SET class='city' WHERE class IS NULL AND tagshstore @> ('place' => 'city')  ;
1016 UPDATE nodes_with_tags SET class='guidepost' WHERE class IS NULL AND tagshstore @> ('information' => 'guidepost')  ;
1017 UPDATE nodes_with_tags SET class='attraction' WHERE class IS NULL AND tagshstore @> ('tourism' => 'attraction')  ;
1018 UPDATE nodes_with_tags SET class='camp site' WHERE class IS NULL AND tagshstore @> ('tourism' => 'camp_site')  ;
1019 UPDATE nodes_with_tags SET class='guest house' WHERE class IS NULL AND tagshstore @> ('tourism' => 'guest_house')  OR tagshstore @> ('tourism' => 'bed_and_breakfast')   ;
1020 UPDATE nodes_with_tags SET class='motel' WHERE class IS NULL AND tagshstore @> ('tourism' => 'motel')  ;
1021 UPDATE nodes_with_tags SET class='hotel' WHERE class IS NULL AND tagshstore @> ('tourism' => 'hotel')  ;
1022 UPDATE nodes_with_tags SET class='information office' WHERE class IS NULL AND tagshstore @> ('information' => 'office')  ;
1023 UPDATE nodes_with_tags SET class='information board' WHERE class IS NULL AND tagshstore @> ('information' => 'board')  ;
1024 UPDATE nodes_with_tags SET class='map' WHERE class IS NULL AND  tags like '%"map"%' ;
1025 UPDATE nodes_with_tags SET class='picnic area' WHERE class IS NULL AND tags like '%picnic%' ;
1026 UPDATE nodes_with_tags SET class='viewpoint' WHERE class IS NULL AND tags like '%viewpoint%' ;
1027 UPDATE nodes_with_tags SET class='power-related' WHERE class IS NULL AND tags like '%"power"%' ;
1028 UPDATE nodes_with_tags SET class='peak (&lt;500 m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND (tagshstore @> ('ele' => '[1-9][0-9]m*')   OR tagshstore @> ('ele' => '[1-4][0-9][0-9]m*') ) ;
1029 UPDATE nodes_with_tags SET class='peak (500 - 1000 m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND tagshstore @> ('ele' => '[5-9][0-9][0-9]m*')  ;
1030 UPDATE nodes_with_tags SET class='peak (1000 - 1500 m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND tagshstore @> ('ele' => '1[0-4][0-9][0-9]m*')  ;
1031 UPDATE nodes_with_tags SET class='peak (1500 - 2000 m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND tagshstore @> ('ele' => '1[5-9][0-9][0-9]m*')  ;
1032 UPDATE nodes_with_tags SET class='peak (2000 - 3000 m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND tagshstore @> ('ele' => '2[0-9][0-9][0-9]m*')  ;
1033 UPDATE nodes_with_tags SET class='peak (3000 - 4000 m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND tagshstore @> ('ele' => '3[0-9][0-9][0-9]m*')  ;
1034 UPDATE nodes_with_tags SET class='peak (>4000m)' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')   AND tagshstore @> ('ele' => '[4-8][0-9][0-9][0-9]m*')  ;
1035 UPDATE nodes_with_tags SET class='peak' WHERE class IS NULL AND tagshstore @> ('natural' => 'peak')  ;
1036 UPDATE nodes_with_tags SET class='wayside cross' WHERE class IS NULL AND tagshstore @> ('historic' => 'wayside_cross')  ;
1037 UPDATE nodes_with_tags SET class='helipad' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'helipad')  ;
1038 UPDATE nodes_with_tags SET class='building' WHERE class IS NULL AND tags like '%building%' ;
1039 UPDATE nodes_with_tags SET class='archaeological site' WHERE class IS NULL AND tags like '%archaeological%' ;
1040 UPDATE nodes_with_tags SET class='communication tower' WHERE class IS NULL AND tagshstore @> ('tower:type' => 'communication')  ;
1041 UPDATE nodes_with_tags SET class='bunker' WHERE class IS NULL AND tags like '%"bunker%' ;
1042 UPDATE nodes_with_tags SET class='ruin' WHERE class IS NULL AND tags like '%"ruin%' ;
1043 UPDATE nodes_with_tags SET class='castle' WHERE class IS NULL AND tags like '%castle%'  ;
1044 UPDATE nodes_with_tags SET class='other' WHERE class IS NULL AND (tags like '%TEMPORARYTEST%') and  NOT (tags like '%bench%') AND NOT (tags like '%housenumber%') AND NOT (tagshstore @> ('natural' => 'peak') ) ;
1045 UPDATE nodes_with_tags SET class='alpine hut' WHERE class IS NULL AND tags like '%"alpine_hut%' ;
1046 UPDATE nodes_with_tags SET class='caravan site' WHERE class IS NULL AND tagshstore @> ('tourism' => 'caravan_site')   ;
1047 UPDATE nodes_with_tags SET class='chalet' WHERE class IS NULL AND tags like '%"chalet%'  ;
1048 UPDATE nodes_with_tags SET class='hostel' WHERE class IS NULL AND tags like '%"hostel"%'  ;
1049 UPDATE nodes_with_tags SET class='courthouse' WHERE class IS NULL AND tags like '%"courthouse"%' ;
1050 UPDATE nodes_with_tags SET class='fire station' WHERE class IS NULL AND tags like '%"fire_station%' ;
1051 UPDATE nodes_with_tags SET class='library' WHERE class IS NULL AND  tagshstore @> ('amenity' => 'library')  ;
1052 UPDATE nodes_with_tags SET class='playground' WHERE class IS NULL AND tagshstore @> ('leisure' => 'playground')  ;
1053 UPDATE nodes_with_tags SET class='prison' WHERE class IS NULL AND tagshstore @> ('amenity' => 'prison')  ;
1054 UPDATE nodes_with_tags SET class='survey point' WHERE class IS NULL AND  tags like '%"survey_point%' ;
1055 UPDATE nodes_with_tags SET class='waste' WHERE class IS NULL AND tags like  '%"amenity"="waste_%' ;
1056 UPDATE nodes_with_tags SET class='cycle barrier' WHERE class IS NULL AND  tagshstore @> ('barrier' => 'cycle_barrier')  OR ( tags like '%"barrier"%' AND  tagshstore @> ('foot' => 'yes')    AND  tagshstore @> ('bicycle' => 'no')   ) ;
1057 UPDATE nodes_with_tags SET class='block' WHERE class IS NULL AND tagshstore @> ('barrier' => 'block')  ;
1058 UPDATE nodes_with_tags SET class='bollard' WHERE class IS NULL AND tagshstore @> ('barrier' => 'bollard')  ;
1059 UPDATE nodes_with_tags SET class='cattle grid' WHERE class IS NULL AND tagshstore @> ('barrier' => 'cattle_grid')  ;
1060 UPDATE nodes_with_tags SET class='gate' WHERE class IS NULL AND tagshstore @> ('barrier' => 'gate')  ;
1061 UPDATE nodes_with_tags SET class='turnstile' WHERE class IS NULL AND tagshstore @> ('barrier' => 'kissing_gate')  OR tagshstore @> ('barrier' => 'turnstile')  ;
1062 UPDATE nodes_with_tags SET class='lift gate' WHERE class IS NULL AND tagshstore @> ('barrier' => 'lift_gate')  ;
1063 UPDATE nodes_with_tags SET class='stile' WHERE class IS NULL AND tagshstore @> ('barrier' => 'stile')  ;
1064 UPDATE nodes_with_tags SET class='entrance' WHERE class IS NULL AND tagshstore @> ('barrier' => 'entrance')  ;
1065 UPDATE nodes_with_tags SET class='toll booth' WHERE class IS NULL AND tagshstore @> ('barrier' => 'toll_booth')  ;
1066 UPDATE nodes_with_tags SET class='barrier' WHERE class IS NULL AND tags like '%"barrier"%' ;
1067 UPDATE nodes_with_tags SET class='college' WHERE class IS NULL AND tags like '%"college%' ;
1068 UPDATE nodes_with_tags SET class='kindergarten, nursery' WHERE class IS NULL AND  tags like '%kindergarten%' or tags like '%nursery%' ;
1069 UPDATE nodes_with_tags SET class='school' WHERE class IS NULL AND tagshstore @> ('amenity' => 'school')  ;
1070 UPDATE nodes_with_tags SET class='university' WHERE class IS NULL AND tags like '%"university"%' ;
1071 UPDATE nodes_with_tags SET class='pub' WHERE class IS NULL AND  tagshstore @> ('amenity' => 'pub')  ;
1072 UPDATE nodes_with_tags SET class='dentist' WHERE class IS NULL AND tags like '%"dentist"%'  ;
1073 UPDATE nodes_with_tags SET class='doctor' WHERE class IS NULL AND tags like '%doctor%'  ;
1074 UPDATE nodes_with_tags SET class='optician' WHERE class IS NULL AND tags like '%"optician%' ;
1075 UPDATE nodes_with_tags SET class='veterinary' WHERE class IS NULL AND tags like '%"veterinary"%'  ;
1076 UPDATE nodes_with_tags SET class='tree cluster' WHERE class IS NULL AND tagshstore @> ('natural' => 'tree')  and tags like '%"cluster"%'  ;
1077 UPDATE nodes_with_tags SET class='broad leafed tree' WHERE class IS NULL AND tagshstore @> ('natural' => 'tree')  and tags like '%"broad_leafed"%'  ;
1078 UPDATE nodes_with_tags SET class='conifer' WHERE class IS NULL AND tagshstore @> ('natural' => 'tree')  and tags like '%"conifer"%'  ;
1079 UPDATE nodes_with_tags SET class='tree' WHERE class IS NULL AND tagshstore @> ('natural' => 'tree')  ;
1080 UPDATE nodes_with_tags SET class='bureau de change' WHERE class IS NULL AND tags like '%"bureau_de_change"%' ;
1081 UPDATE nodes_with_tags SET class='christian place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'christian')  ;
1082 UPDATE nodes_with_tags SET class='jewish place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'jewish')  ;
1083 UPDATE nodes_with_tags SET class='muslim place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'muslim')  ;
1084 UPDATE nodes_with_tags SET class='bahai place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'bahai')  ;
1085 UPDATE nodes_with_tags SET class='buddhist place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'buddhist')  ;
1086 UPDATE nodes_with_tags SET class='hindu place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'hindu')  ;
1087 UPDATE nodes_with_tags SET class='jain place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'jain')  ;
1088 UPDATE nodes_with_tags SET class='shinto place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'shinto')  ;
1089 UPDATE nodes_with_tags SET class='sikh place of worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' and tagshstore @> ('religion' => 'sikh')  ;
1090 UPDATE nodes_with_tags SET class='place_of_worship' WHERE class IS NULL AND tags like '%"place_of_worship"%' ;
1091 UPDATE nodes_with_tags SET class='battlefield' WHERE class IS NULL AND tagshstore @> ('historic' => 'battlefield')   ;
1092 UPDATE nodes_with_tags SET class='art gallery' WHERE class IS NULL AND tagshstore @> ('shop' => 'art')   or  tags like '%"gallery"%'  ;
1093 UPDATE nodes_with_tags SET class='beach' WHERE class IS NULL AND tagshstore @> ('natural' => 'beach')   ;
1094 UPDATE nodes_with_tags SET class='casino' WHERE class IS NULL AND tagshstore @> ('amenity' => 'casino')  ;
1095 UPDATE nodes_with_tags SET class='cinema' WHERE class IS NULL AND tagshstore @> ('amenity' => 'cinema')  ;
1096 UPDATE nodes_with_tags SET class='clock' WHERE class IS NULL AND tagshstore @> ('amenity' => 'clock')  ;
1097 UPDATE nodes_with_tags SET class='memorial' WHERE class IS NULL AND tagshstore @> ('historic' => 'memorial')  ;
1098 UPDATE nodes_with_tags SET class='monument' WHERE class IS NULL AND tagshstore @> ('historic' => 'monument')  ;
1099 UPDATE nodes_with_tags SET class='steam railway' WHERE class IS NULL AND tags like '%steam%' and tags like '%"railway"%' ;
1100 UPDATE nodes_with_tags SET class='theme_park' WHERE class IS NULL AND tagshstore @> ('tourism' => 'theme_park')  ;
1101 UPDATE nodes_with_tags SET class='watermill' WHERE class IS NULL AND tagshstore @> ('man_made' => 'watermill')  ;
1102 UPDATE nodes_with_tags SET class='windmill' WHERE class IS NULL AND tagshstore @> ('man_made' => 'windmill')  ;
1103 UPDATE nodes_with_tags SET class='wreck' WHERE class IS NULL AND tagshstore @> ('historic' => 'wreck')  ;
1104 UPDATE nodes_with_tags SET class='zoo' WHERE class IS NULL AND tagshstore @> ('tourism' => 'zoo')  ;
1105 UPDATE nodes_with_tags SET class='cave_entrance' WHERE class IS NULL AND tagshstore @> ('natural' => 'cave_entrance')  ;
1106 UPDATE nodes_with_tags SET class='crane' WHERE class IS NULL AND tagshstore @> ('man_made' => 'crane')  ;
1107 UPDATE nodes_with_tags SET class='embassy' WHERE class IS NULL AND tags like '%"consulate"%' or tags like '%"embassy"%' ;
1108 UPDATE nodes_with_tags SET class='disused mining place' WHERE class IS NULL AND (tags like '%"mineshaft"%' OR tagshstore @> ('landuse' => 'surface_mining')  OR tags like '%"mine_entrance"%' OR tagshstore @> ('landuse' => 'quarry') ) and ( tagshstore @> ('disused' => 'yes')  OR tags like '%"historic"%') ;
1109 UPDATE nodes_with_tags SET class='mining' WHERE class IS NULL AND (tags like '%"mineshaft"%' OR tagshstore @> ('landuse' => 'surface_mining')  OR tags like '%"mine_entrance"%' OR tagshstore @> ('landuse' => 'quarry') ) and NOT (tagshstore @> ('disused' => 'yes') ) ;
1110 UPDATE nodes_with_tags SET class='water tower' WHERE class IS NULL AND tagshstore @> ('man_made' => 'water_tower')  ;
1111 UPDATE nodes_with_tags SET class='beverage shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'alcohol')  OR tagshstore @> ('shop' => 'beverages')  ;
1112 UPDATE nodes_with_tags SET class='bakery' WHERE class IS NULL AND tagshstore @> ('shop' => 'bakery')  ;
1113 UPDATE nodes_with_tags SET class='bicycle shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'bicycle')  ;
1114 UPDATE nodes_with_tags SET class='bookshop' WHERE class IS NULL AND tagshstore @> ('shop' => 'books')  ;
1115 UPDATE nodes_with_tags SET class='butcher' WHERE class IS NULL AND tagshstore @> ('shop' => 'butcher')  ;
1116 UPDATE nodes_with_tags SET class='car shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'car')  ;
1117 UPDATE nodes_with_tags SET class='car repair' WHERE class IS NULL AND tagshstore @> ('shop' => 'car_repair')  ;
1118 UPDATE nodes_with_tags SET class='clothes shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'clothes')  ;
1119 UPDATE nodes_with_tags SET class='computer shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'computer')  ;
1120 UPDATE nodes_with_tags SET class='confectionery shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'confectionery')  ;
1121 UPDATE nodes_with_tags SET class='convenience shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'convenience')  ;
1122 UPDATE nodes_with_tags SET class='copyshop' WHERE class IS NULL AND tagshstore @> ('shop' => 'copyshop')  ;
1123 UPDATE nodes_with_tags SET class='doityourself shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'doityourself')  ;
1124 UPDATE nodes_with_tags SET class='estate agent' WHERE class IS NULL AND tags like '%"estate_agent"%' ;
1125 UPDATE nodes_with_tags SET class='seafood shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'seafood')  ;
1126 UPDATE nodes_with_tags SET class='florist' WHERE class IS NULL AND tagshstore @> ('shop' => 'florist')  ;
1127 UPDATE nodes_with_tags SET class='garden centre' WHERE class IS NULL AND tagshstore @> ('shop' => 'garden_centre')  ;
1128 UPDATE nodes_with_tags SET class='gift shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'gift')  ;
1129 UPDATE nodes_with_tags SET class='greengrocer' WHERE class IS NULL AND tagshstore @> ('shop' => 'greengrocer')  ;
1130 UPDATE nodes_with_tags SET class='hairdresser' WHERE class IS NULL AND tagshstore @> ('shop' => 'hairdresser')  ;
1131 UPDATE nodes_with_tags SET class='hifi shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'hifi')  ;
1132 UPDATE nodes_with_tags SET class='jewelry' WHERE class IS NULL AND tagshstore @> ('shop' => 'jewelry')  ;
1133 UPDATE nodes_with_tags SET class='laundry' WHERE class IS NULL AND tagshstore @> ('shop' => 'laundry')  ;
1134 UPDATE nodes_with_tags SET class='mobile phone shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'mobile_phone')  ;
1135 UPDATE nodes_with_tags SET class='motorcycle shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'motorcycle')  ;
1136 UPDATE nodes_with_tags SET class='musical instrument shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'musical_instrument')  ;
1137 UPDATE nodes_with_tags SET class='pet shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'pet')  ;
1138 UPDATE nodes_with_tags SET class='camera shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'photo')  OR tags like '%"shop"="camera%' ;
1139 UPDATE nodes_with_tags SET class='supermarket' WHERE class IS NULL AND tagshstore @> ('shop' => 'supermarket')  ;
1140 UPDATE nodes_with_tags SET class='fishing' WHERE class IS NULL AND tags like '%tackle%' or tags like '%"fishing"%' ;
1141 UPDATE nodes_with_tags SET class='video shop' WHERE class IS NULL AND tagshstore @> ('shop' => 'video')  ;
1142 UPDATE nodes_with_tags SET class='archery' WHERE class IS NULL AND tagshstore @> ('sport' => 'archery')  ;
1143 UPDATE nodes_with_tags SET class='baseball' WHERE class IS NULL AND tagshstore @> ('sport' => 'baseball')  ;
1144 UPDATE nodes_with_tags SET class='cricket' WHERE class IS NULL AND tagshstore @> ('sport' => 'cricket')  ;
1145 UPDATE nodes_with_tags SET class='diving' WHERE class IS NULL AND tagshstore @> ('sport' => 'diving')  ;
1146 UPDATE nodes_with_tags SET class='golf' WHERE class IS NULL AND tagshstore @> ('sport' => 'golf')  ;
1147 UPDATE nodes_with_tags SET class='gym' WHERE class IS NULL AND tagshstore @> ('amenity' => 'gym')  or  tags like '%"fitness%'  ;
1148 UPDATE nodes_with_tags SET class='gymnastics' WHERE class IS NULL AND tagshstore @> ('sport' => 'gymnastics')  ;
1149 UPDATE nodes_with_tags SET class='horse racing' WHERE class IS NULL AND tagshstore @> ('sport' => 'horse_racing')  ;
1150 UPDATE nodes_with_tags SET class='skating' WHERE class IS NULL AND tagshstore @> ('sport' => 'skating')  ;
1151 UPDATE nodes_with_tags SET class='motor sport' WHERE class IS NULL AND tagshstore @> ('sport' => 'motor')  ;
1152 UPDATE nodes_with_tags SET class='playground' WHERE class IS NULL AND tagshstore @> ('leisure' => 'playground')  ;
1153 UPDATE nodes_with_tags SET class='sailing' WHERE class IS NULL AND tags like '%"sailing%' ;
1154 UPDATE nodes_with_tags SET class='nordic ski piste' WHERE class IS NULL AND tagshstore @> ('piste:type' => 'nordic')  ;
1155 UPDATE nodes_with_tags SET class='downhill ski piste' WHERE class IS NULL AND tagshstore @> ('piste:type' => 'downhill')  ;
1156 UPDATE nodes_with_tags SET class='snooker' WHERE class IS NULL AND tags like '%snooker%'  ;
1157 UPDATE nodes_with_tags SET class='soccer' WHERE class IS NULL AND tagshstore @> ('sport' => 'soccer')  ;
1158 UPDATE nodes_with_tags SET class='swimming' WHERE class IS NULL AND tags like '%"swimming"%' ;
1159 UPDATE nodes_with_tags SET class='tennis' WHERE class IS NULL AND tagshstore @> ('sport' => 'tennis')  ;
1160 UPDATE nodes_with_tags SET class='windsurf' WHERE class IS NULL AND tags like '%windsurf%' ;
1161 UPDATE nodes_with_tags SET class='sport' WHERE class IS NULL AND tags like '%"leisure"%' or tags like '%"sport"%' ;
1162 UPDATE nodes_with_tags SET class='aerodrome' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'aerodrome')  ;
1163 UPDATE nodes_with_tags SET class='airport' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'aerodrome')  and tags like '%"iata"%' ;
1164 UPDATE nodes_with_tags SET class='fuel' WHERE class IS NULL AND tagshstore @> ('amenity' => 'fuel')  ;
1165 UPDATE nodes_with_tags SET class='parking (with places for disabled)' WHERE class IS NULL AND tagshstore @> ('amenity' => 'parking')  and (tagshstore @> ('capacity:disabled' => 'yes')    ) ;
1166 UPDATE nodes_with_tags SET class='private parking' WHERE class IS NULL AND tagshstore @> ('amenity' => 'parking')  and ( tagshstore @> ('access' => 'private')  or tagshstore @> ('access' => 'no') ) ;
1167 UPDATE nodes_with_tags SET class='parking' WHERE class IS NULL AND tagshstore @> ('amenity' => 'parking')  ;
1168 UPDATE nodes_with_tags SET class='airport gate' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'gate')  ;
1169 UPDATE nodes_with_tags SET class='airport terminal' WHERE class IS NULL AND tagshstore @> ('aeroway' => 'terminal')  ;
1170 UPDATE nodes_with_tags SET class='car sharing' WHERE class IS NULL AND tagshstore @> ('amenity' => 'car_sharing')  ;
1171 UPDATE nodes_with_tags SET class='ford' WHERE class IS NULL AND tagshstore @> ('highway' => 'ford')  ;
1172 UPDATE nodes_with_tags SET class='lighthouse' WHERE class IS NULL AND tagshstore @> ('man_made' => 'lighthouse')  ;
1173 UPDATE nodes_with_tags SET class='marina' WHERE class IS NULL AND tagshstore @> ('leisure' => 'marina')  ;
1174 UPDATE nodes_with_tags SET class='bicycle parking' WHERE class IS NULL AND tagshstore @> ('amenity' => 'bicycle_parking')  ;
1175 UPDATE nodes_with_tags SET class='harbour' WHERE class IS NULL AND tagshstore @> ('harbour' => 'yes')  ;
1176 UPDATE nodes_with_tags SET class='bicycle rental' WHERE class IS NULL AND tagshstore @> ('amenity' => 'bicycle_rental')  ;
1177 UPDATE nodes_with_tags SET class='car rental' WHERE class IS NULL AND tagshstore @> ('amenity' => 'car_rental')  ;
1178 UPDATE nodes_with_tags SET class='roundabout' WHERE class IS NULL AND tags like '%roundabout"%' ;
1179 UPDATE nodes_with_tags SET class='roundabout' WHERE class IS NULL AND tags like '%roundabout"%' and tagshstore @> ('direction' => 'clockwise')  ;
1180 UPDATE nodes_with_tags SET class='traffic signals' WHERE class IS NULL AND tagshstore @> ('highway' => 'traffic_signals')  ;
1181 UPDATE nodes_with_tags SET class='railway halt' WHERE class IS NULL AND tagshstore @> ('railway' => 'halt')   ;
1182 UPDATE nodes_with_tags SET class='railway station' WHERE class IS NULL AND tagshstore @> ('railway' => 'station')   ;
1183 UPDATE nodes_with_tags SET class='subway entrance' WHERE class IS NULL AND tagshstore @> ('railway' => 'subway_entrance')  ;
1184 UPDATE nodes_with_tags SET class='tram stop' WHERE class IS NULL AND tagshstore @> ('railway' => 'tram_stop')  ;
1185 UPDATE nodes_with_tags SET class='turning circle' WHERE class IS NULL AND tagshstore @> ('highway' => 'turning_circle')  ;
1186 UPDATE nodes_with_tags SET class='weir' WHERE class IS NULL AND tagshstore @> ('waterway' => 'weir')  ;
1187 UPDATE nodes_with_tags SET class='aerialway station' WHERE class IS NULL AND tagshstore @> ('aerialway' => 'station')  ;
1188 UPDATE nodes_with_tags SET class='pylon' WHERE class IS NULL AND tags like '%"pylon"%' ;
1189 UPDATE nodes_with_tags SET class='crossing' WHERE class IS NULL AND tagshstore @> ('highway' => 'crossing')  OR tagshstore @> ('railway' => 'crossing')   ;
1190 UPDATE nodes_with_tags SET class='railway crossing' WHERE class IS NULL AND tagshstore @> ('railway' => 'crossing')   ;
1191 UPDATE nodes_with_tags SET class='level crossing' WHERE class IS NULL AND tagshstore @> ('railway' => 'level_crossing')  ;
1192 UPDATE nodes_with_tags SET class='motorway junction' WHERE class IS NULL AND tagshstore @> ('highway' => 'motorway_junction')  ;
1193 UPDATE nodes_with_tags SET class='noexit' WHERE class IS NULL AND tagshstore @> ('noexit' => 'yes')  ;
1194 UPDATE nodes_with_tags SET class='mountain pass' WHERE class IS NULL AND tagshstore @> ('mountain_pass' => 'yes')  ;
1195 UPDATE nodes_with_tags SET class='wayside shrine' WHERE class IS NULL AND tagshstore @> ('historic' => 'wayside_shrine')  ;
1196 UPDATE nodes_with_tags SET class='wayside cross' WHERE class IS NULL AND tagshstore @> ('historic' => 'wayside_cross')  ;
1197 UPDATE nodes_with_tags SET class='museum' WHERE class IS NULL AND tagshstore @> ('tourism' => 'museum')  ;
1198 UPDATE nodes_with_tags SET class='spring' WHERE class IS NULL AND tagshstore @> ('natural' => 'spring')  ;
1199 UPDATE nodes_with_tags SET class='tourist information' WHERE class IS NULL AND tagshstore @> ('tourism' => 'information')  ;
1200 UPDATE nodes_with_tags SET class='shop' WHERE class IS NULL AND tags like '%"shop"%' ;
1201 UPDATE nodes_with_tags SET class='tower' WHERE class IS NULL AND tagshstore @> ('man_made' => 'tower')  ;
1202 UPDATE nodes_with_tags SET class='waterfall' WHERE class IS NULL AND tags like '%"waterfall"%' ;
1203 UPDATE nodes_with_tags SET class='farmyard' WHERE class IS NULL AND tagshstore @> ('landuse' => 'farmyard')  or tagshstore @> ('place' => 'farm')  ;
1204 UPDATE nodes_with_tags SET class='city limit' WHERE class IS NULL AND tagshstore @> ('traffic_sign' => 'city_limit')  ;
1205 UPDATE nodes_with_tags SET class='stop' WHERE class IS NULL AND tagshstore @> ('highway' => 'stop')  ;
1206 UPDATE polygons SET class='pedestrian area' WHERE class IS NULL AND tagshstore @> ('highway' => 'pedestrian')  ;
1207 UPDATE polygons SET class='beach' WHERE class IS NULL AND tagshstore @> ('natural' => 'beach')  ;
1208 UPDATE polygons SET class='soccer' WHERE class IS NULL AND tagshstore @> ('sport' => 'soccer')  ;
1209 UPDATE polygons SET class='cemetery' WHERE class IS NULL AND tags like '%cemetery%' OR tags like '%grave_yard%' ;
1210 UPDATE polygons SET class='forest' WHERE class IS NULL AND tags like '%"forest"%'  OR tags like '%"wood"%' ;
1211 UPDATE polygons SET class='park, garden, playground' WHERE class IS NULL AND tags like '%"park"%' OR tags like '%"garden"%' OR tags like '%"playground"%' ;
1212 UPDATE polygons SET class='parking' WHERE class IS NULL AND tags like '%"parking"%' ;
1213 UPDATE polygons SET class='with fee' WHERE class IS NULL AND tagshstore @> ('fee' => 'yes')  ;
1214 UPDATE polygons SET class='leisure area' WHERE class IS NULL AND tags like '%"leisure"%' and not (tagshstore @> ('leisure' => 'golf_course') ) ;
1215 UPDATE polygons SET class='coastline' WHERE class IS NULL AND tags like '%"coastline"%' ;
1216 UPDATE polygons SET class='highway; area accessible to vehicles' WHERE class IS NULL AND tags like '%highway%' ;
1217 UPDATE polygons SET class='residential' WHERE class IS NULL AND tagshstore @> ('landuse' => 'residential')  ;
1218 UPDATE polygons SET class='building' WHERE class IS NULL AND tags like '%"building"%' ;
1219 UPDATE polygons SET class='lake, large river' WHERE class IS NULL AND tagshstore @> ('natural' => 'water')  OR tagshstore @> ('waterway' => 'riverbank')  ;
1220 UPDATE polygons SET class='camp site' WHERE class IS NULL AND tagshstore @> ('tourism' => 'camp_site')  ;
1221 UPDATE polygons SET class='farmyard' WHERE class IS NULL AND tagshstore @> ('landuse' => 'farmyard')  ;
1222 UPDATE polygons SET class='farm land' WHERE class IS NULL AND tagshstore @> ('landuse' => 'farm')  OR tagshstore @> ('landuse' => 'farmland')  ;
1223 UPDATE polygons SET class='grass' WHERE class IS NULL AND tagshstore @> ('landuse' => 'grass')  ;
1224 UPDATE polygons SET class='meadow' WHERE class IS NULL AND tagshstore @> ('landuse' => 'meadow')  ;
1225 UPDATE polygons SET class='scrub, heath' WHERE class IS NULL AND tagshstore @> ('natural' => 'scrub')  or tagshstore @> ('natural' => 'heath')  ;
1226 UPDATE polygons SET class='industrial' WHERE class IS NULL AND tagshstore @> ('landuse' => 'industrial')  ;
1227 UPDATE polygons SET class='golf course' WHERE class IS NULL AND tagshstore @> ('leisure' => 'golf_course')  ;
1228 UPDATE polygons SET class='quarry' WHERE class IS NULL AND tagshstore @> ('landuse' => 'quarry')  ;
1229 UPDATE polygons SET class='power facility' WHERE class IS NULL AND tags like '%"power"%' ;
1230 UPDATE polygons SET class='wastewater plant' WHERE class IS NULL AND tags like '%"wastewater_plant"%' ;
1231 UPDATE polygons SET class='hospital' WHERE class IS NULL AND tagshstore @> ('amenity' => 'hospital')  ;
1232ANALYZE VERBOSE ways (class);
1233ANALYZE VERBOSE nodes_with_tags (class); 
1234ANALYZE VERBOSE polygons (class);
1235
1236
1237
1238
1239_EOF_
1240
1241# Rivers, streams... are represented in ways that touch themself in the middle of lakes and
1242# large rivers. This generates ugly labelling of those ways. This is also a problem if your
1243# want a different color for rivers and lakes.
1244# The following cuts their parts that are in lakes or over larger rivers, when appropriate
1245if [ "$rivers" = "cut" ]; then
1246
1247${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
1248
1249UPDATE ways SET linestring=newriver FROM (SELECT  * FROM (SELECT ways.id, ST_Difference(linestring,
1250(SELECT ST_Union(polygons.polygon) FROM polygons
1251WHERE ST_Intersects(polygons.polygon, ways.linestring)
1252and (polygons.tagshstore ? 'waterway' OR polygons.tagshstore @> 'natural=>water' )
1253-- Large rivers are represented both as polygons and ways: polygons are used at large scales and
1254-- ways at small scales, so keep both when polygons.name != ways.name 
1255and (polygons.name != ways.name OR polygons.name IS NULL)
1256)  ) as newriver FROM ways
1257WHERE (ways.tagshstore @> 'waterway=>stream' OR
1258ways.tagshstore @> 'waterway=>river' OR
1259ways.tagshstore @> 'waterway=>riverbank' OR
1260ways.tagshstore @> 'waterway=>canal' OR
1261ways.tagshstore @> 'waterway=>derelict_canal' OR
1262ways.tagshstore @> 'waterway=>ditch' OR
1263ways.tagshstore @> 'waterway=>drain' OR
1264-- do not modify weirs, dams, etc. that are not piece of water but are stopping water
1265ways.tagshstore @> 'natural=>water'   )
1266-- a canal can be on a bridge or on a tunnel (?) in this case it should be displayed
1267AND NOT (ways.tagshstore ? 'bridge' OR ways.tagshstore ? 'tunnel' OR ways.tagshstore @> 'lock=>yes')
1268) as newrivers
1269WHERE
1270--  cleaning is necessary (or it fails)
1271-- E.g. the two following objects (one line and one polygon) represent exactly the same object
1272-- (the first is included in the second, the result is a [valid?!] geometry with zero points)
1273-- http://www.openstreetmap.org/browse/way/64796488  and http://www.openstreetmap.org/browse/way/64796484
1274ST_IsValid(newriver)
1275AND GeometryType(newriver) = 'LINESTRING'
1276AND ST_NPoints(newriver) >1) as newrivers2
1277WHERE ways.id=newrivers2.id ;
1278_EOF_
1279fi
1280
1281
1282${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
1283ANALYZE VERBOSE ways;
1284_EOF_
1285
1286
1287ENDOFIMPORT=$(date +"%Y-%m-%d_%H:%M:%S")
1288
1289${psql_folder}psql $pgconnect_user  -d $dbname  <<- _EOF_
1290 COMMENT ON DATABASE "$dbname" IS '
1291 Created with $version, powered by Osmosis $osmosis_version,
1292  using the following parameters:
1293  "$current_parameters"
1294 OSM file size (bytes) / date / name:
1295  $file_descr
1296 Start of import: $NOW
1297 End of import:   $ENDOFIMPORT';
1298_EOF_
1299
1300echo "Time: Finished importation at `date +"%Y-%m-%d_%H:%M:%S"`"
1301echo -e "Importation completed correctly!
1302$how_to_remove
1303"
1304
1305#    set +x
1306} > logs_osm2postgresql_$NOW.txt 2>&1
1307
1308
1309exit