Created
September 11, 2018 11:08
-
-
Save grischard/b08714f83952053078d6362f3c776563 to your computer and use it in GitHub Desktop.
Import and maintain openstreetmap into postgis for Luxembourg
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- The following lines must be added to /usr/local/Cellar/osm2pgsql/HEAD/share/osm2pgsql/default.style | |
| -- # Extras for create_osm_street_list.sql | |
| -- way postal_code text linear | |
| -- way alt_name text linear | |
| -- way name:lb text linear | |
| -- way alt_name:lb text linear | |
| -- way is_in:city text linear | |
| DROP TABLE IF EXISTS road_names_osm; | |
| CREATE Table road_names_osm ( | |
| commune TEXT, | |
| rue TEXT, | |
| localite TEXT, | |
| postal_code TEXT, | |
| alt_name TEXT, | |
| namelb TEXT, | |
| alt_namelb TEXT | |
| ); | |
| -- rues | |
| insert INTO road_names_osm (commune, rue, postal_code, alt_name, namelb, alt_namelb) | |
| select g.name as commune, l.name as rue, l.postal_code, l.alt_name, l."name:lb" as namelb, l."alt_name:lb" as alt_namelb from planet_osm_line l, planet_osm_polygon g | |
| where l.highway is not null | |
| AND l.name is not null | |
| AND ST_Intersects(l.way,g.way) | |
| AND g.admin_level = '8' | |
| GROUP BY commune,rue,l.postal_code, l.alt_name, namelb, alt_namelb | |
| ORDER BY commune, rue; | |
| -- places (areas) | |
| insert INTO road_names_osm (commune, rue, postal_code, alt_name, namelb, alt_namelb) | |
| select g.name as commune, l.name as rue, l.postal_code, l.alt_name, l."name:lb" as namelb, l."alt_name:lb" as alt_namelb from planet_osm_polygon l, planet_osm_polygon g | |
| where l.highway is not null | |
| AND l.name is not null | |
| AND ST_Intersects(l.way,g.way) | |
| AND g.admin_level = '8' | |
| GROUP BY commune,rue,l.postal_code, l.alt_name, namelb, alt_namelb | |
| ORDER BY commune, rue; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # This is the default osm2pgsql .style file that comes with osm2pgsql. | |
| # | |
| # A .style file has 4 columns that define how OSM objects end up in tables in | |
| # the database and what columns are created. It interacts with the command-line | |
| # hstore options. | |
| # | |
| # Columns | |
| # ======= | |
| # | |
| # OsmType: This is either "node", "way" or "node,way" and indicates if this tag | |
| # applies to nodes, ways, or both. | |
| # | |
| # Tag: The tag | |
| # | |
| # DataType: The type of the column to be created. Normally "text" | |
| # | |
| # Flags: Flags that indicate what table the OSM object is moved into. | |
| # | |
| # There are 5 possible flags. These flags are used both to indicate if a column | |
| # should be created, and if ways with the tag are assumed to be areas. The area | |
| # assumptions can be overridden with an area=yes/no tag | |
| # | |
| # polygon - Create a column for this tag, and objects the tag with are areas | |
| # | |
| # linear - Create a column for this tag | |
| # | |
| # phstore - Don't create a column for this tag, but objects with the tag are areas | |
| # | |
| # delete - Drop this tag completely and don't create a column for it. This also | |
| # prevents the tag from being added to hstore columns | |
| # | |
| # nocache - Deprecated and does nothing | |
| # | |
| # If an object has a tag that indicates it is an area or has area=yes/1, | |
| # osm2pgsql will try to turn it into an area. If it succeeds, it places it in | |
| # the polygon table. If it fails (e.g. not a closed way) it places it in the | |
| # line table. | |
| # | |
| # Nodes are never placed into the polygon or line table and are always placed in | |
| # the point table. | |
| # | |
| # Hstore | |
| # ====== | |
| # | |
| # The options --hstore, --hstore-match-only, and --hstore-all interact with | |
| # the .style file. | |
| # | |
| # With --hstore any tags without a column will be added to the hstore column. | |
| # This will also cause all objects to be kept. | |
| # | |
| # With --hstore-match-only the behavior for tags is the same, but objects are | |
| # only kept if they have a non-NULL value in one of the columns. | |
| # | |
| # With --hstore-all all tags are added to the hstore column unless they appear | |
| # in the style file with a delete flag, causing duplication between the normal | |
| # columns and the hstore column. | |
| # | |
| # Special database columns | |
| # ======================== | |
| # | |
| # There are some special database columns that if present in the .style file | |
| # will be populated by osm2pgsql. | |
| # | |
| # These are | |
| # | |
| # z_order - datatype int4 | |
| # | |
| # way_area - datatype real. The area of the way, in the units of the projection | |
| # (e.g. square mercator meters). Only applies to areas | |
| # | |
| # osm_user, osm_uid, osm_version, osm_timestamp - datatype text. Used with the | |
| # --extra-attributes option to include metadata in the database. If importing | |
| # with both --hstore and --extra-attributes the meta-data will end up in the | |
| # tags hstore column regardless of the style file. | |
| # OsmType Tag DataType Flags | |
| node,way access text linear | |
| node,way addr:housename text linear | |
| node,way addr:housenumber text linear | |
| node,way addr:interpolation text linear | |
| node,way admin_level text linear | |
| node,way aerialway text linear | |
| node,way aeroway text polygon | |
| node,way amenity text polygon | |
| node,way area text # hard coded support for area=1/yes => polygon is in osm2pgsql | |
| node,way barrier text linear | |
| node,way bicycle text | |
| node,way brand text linear | |
| node,way bridge text linear | |
| node,way boundary text linear | |
| node,way building text polygon | |
| node capital text linear | |
| node,way construction text linear | |
| node,way covered text linear | |
| node,way culvert text linear | |
| node,way cutting text linear | |
| node,way denomination text linear | |
| node,way disused text linear | |
| node ele text linear | |
| node,way embankment text linear | |
| node,way foot text linear | |
| node,way generator:source text linear | |
| node,way harbour text polygon | |
| node,way highway text linear | |
| node,way historic text polygon | |
| node,way horse text linear | |
| node,way intermittent text linear | |
| node,way junction text linear | |
| node,way landuse text polygon | |
| node,way layer text linear | |
| node,way leisure text polygon | |
| node,way lock text linear | |
| node,way man_made text polygon | |
| node,way military text polygon | |
| node,way motorcar text linear | |
| node,way name text linear | |
| node,way natural text polygon # natural=coastline tags are discarded by a hard coded rule in osm2pgsql | |
| node,way office text polygon | |
| node,way oneway text linear | |
| node,way oneway:bicycle text linear | |
| node,way operator text linear | |
| node,way place text polygon | |
| node poi text | |
| node,way population text linear | |
| node,way power text polygon | |
| node,way power_source text linear | |
| node,way public_transport text polygon | |
| node,way railway text linear | |
| node,way ref text linear | |
| node,way religion text nocache | |
| node,way route text linear | |
| node,way service text linear | |
| node,way shop text polygon | |
| node,way sport text polygon | |
| node,way surface text linear | |
| node,way toll text linear | |
| node,way tourism text polygon | |
| node,way tower:type text linear | |
| way tracktype text linear | |
| node,way tunnel text linear | |
| node,way water text polygon | |
| node,way waterway text polygon | |
| node,way wetland text polygon | |
| node,way width text linear | |
| node,way wood text linear | |
| node,way z_order int4 linear # This is calculated during import | |
| way way_area real # This is calculated during import | |
| # Area tags | |
| # We don't make columns for these tags, but objects with them are areas. | |
| # Mainly for use with hstore | |
| way abandoned:aeroway text phstore | |
| way abandoned:amenity text phstore | |
| way abandoned:building text phstore | |
| way abandoned:landuse text phstore | |
| way abandoned:power text phstore | |
| way area:highway text phstore | |
| # Deleted tags | |
| # These are tags that are generally regarded as useless for most rendering. | |
| # Most of them are from imports or intended as internal information for mappers | |
| # Some of them are automatically deleted by editors. | |
| # If you want some of them, perhaps for a debugging layer, just delete the lines. | |
| # These tags are used by mappers to keep track of data. | |
| # They aren't very useful for rendering. | |
| node,way note text delete | |
| node,way note:* text delete | |
| node,way source text delete | |
| node,way source_ref text delete | |
| node,way source:* text delete | |
| node,way attribution text delete | |
| node,way comment text delete | |
| node,way fixme text delete | |
| # Tags generally dropped by editors, not otherwise covered | |
| node,way created_by text delete | |
| node,way odbl text delete | |
| node,way odbl:note text delete | |
| node,way SK53_bulk:load text delete | |
| # Lots of import tags | |
| # TIGER (US) | |
| node,way tiger:* text delete | |
| # NHD (US) | |
| # NHD has been converted every way imaginable | |
| node,way NHD:* text delete | |
| node,way nhd:* text delete | |
| # GNIS (US) | |
| node,way gnis:* text delete | |
| # Geobase (CA) | |
| node,way geobase:* text delete | |
| # NHN (CA) | |
| node,way accuracy:meters text delete | |
| node,way sub_sea:type text delete | |
| node,way waterway:type text delete | |
| # KSJ2 (JA) | |
| # See also note:ja and source_ref above | |
| node,way KSJ2:* text delete | |
| # Yahoo/ALPS (JA) | |
| node,way yh:* text delete | |
| # osak (DK) | |
| node,way osak:* text delete | |
| # kms (DK) | |
| node,way kms:* text delete | |
| # ngbe (ES) | |
| # See also note:es and source:file above | |
| node,way ngbe:* text delete | |
| # naptan (UK) | |
| node,way naptan:* text delete | |
| # Corine (CLC) (Europe) | |
| node,way CLC:* text delete | |
| # misc | |
| node,way 3dshapes:ggmodelk text delete | |
| node,way AND_nosr_r text delete | |
| node,way import text delete | |
| node,way it:fvg:* text delete | |
| # Extras for create_osm_street_list.sql | |
| way postal_code text linear | |
| way alt_name text linear | |
| way name:lb text linear | |
| way alt_name:lb text linear | |
| way is_in:city text linear | |
| # Extras for address checks | |
| node,way addr:street text linear | |
| node,way addr:postcode text linear | |
| node,way addr:city text linear | |
| node,way addr:country text linear | |
| node,way addr:place text linear | |
| node,way ref:caclr text linear | |
| # extras for bicycle network | |
| way network text linear |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP TABLE IF EXISTS road_names_cad; | |
| CREATE Table road_names_cad ( | |
| district VARCHAR(32), | |
| canton VARCHAR(32), | |
| commune VARCHAR(32), | |
| village VARCHAR(32), | |
| rue VARCHAR(64), | |
| code_postal NUMERIC(4) | |
| ); | |
| -- latest output of ./csventrifuge.py luxembourg-caclr-dicacolo dicacolo.csv | |
| --\copy cadastre from /home/openstreetmap/CACLR/latest/TR.DICACOLO.RUCP -- symlink to the real file | |
| \copy road_names_cad from /Users/stereo/osm/csventrifuge/luxembourg-streets.csv WITH CSV HEADER DELIMITER AS ',' | |
| -- csventrifuge does this for us | |
| -- UPDATE road_names_cad SET commune = replace(commune, 'Redange/Attert', 'Redange-sur-Attert'); | |
| -- UPDATE road_names_cad SET village = replace(village, 'Redange/Attert', 'Redange-sur-Attert'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/sh | |
| # clean up | |
| dropdb gis || exit 1 | |
| cd ~/osm/ || exit 2 | |
| # make a mess | |
| wget -4 -N http://osm.kewl.lu/luxembourg.osm/luxembourg.osm.bz2 | |
| # eat it all | |
| # createdb -E UTF8 gis | |
| createdb -E UTF8 -T template0 --locale=fr_FR.UTF-8 gis | |
| echo "CREATE EXTENSION postgis" | psql -d gis | |
| echo "CREATE EXTENSION fuzzystrmatch" | psql -d gis # for levenshtein | |
| # echo "CREATE EXTENSION hstore" | psql gis # maproulette script needs this for no good reason | |
| pbzcat -p7 luxembourg.osm.bz2 | osm2pgsql --database gis --proj 2169 --number-processes 8 -H /tmp -s -r xml -S /Users/stereo/osm/default.style /dev/stdin | |
| psql -d gis -f /Users/stereo/osm/create_osm_street_list.sql | |
| psql -d gis -f /Users/stereo/osm/import_cadastre.sql |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/sh | |
| # clean up | |
| # dropdb gis || exit 1 | |
| for truncate_table in planet_osm_line planet_osm_point planet_osm_rels planet_osm_ways planet_osm_nodes planet_osm_polygon planet_osm_roads; do | |
| echo "truncate $truncate_table;" | psql -d gis | |
| done | |
| cd ~/osm/ || exit 2 | |
| # make a mess | |
| # wget -N http://download.geofabrik.de/osm/europe/luxembourg.osm.bz2 | |
| ## UNCOMMENT ME | |
| wget -4 -N http://osm.kewl.lu/luxembourg.osm/luxembourg.osm.bz2 | |
| # eat it all | |
| # createdb -E UTF8 gis | |
| # psql -d gis -f /usr/local/share/postgis/postgis.sql | |
| # psql -d gis -f /usr/local/share/postgis/spatial_ref_sys.sql | |
| # psql -d gis -f /usr/local/share/postgis/legacy.sql | |
| # psql -d gis -f /usr/local/Cellar/postgresql/9.1.3/share/postgresql/extension/fuzzystrmatch--1.0.sql | |
| # psql -d gis -f /Users/stereo/osm/900913.sql | |
| pbzcat -p7 luxembourg.osm.bz2 | osm2pgsql --proj 2169 --database gis --number-processes 8 -H /tmp -s -r xml -S /Users/stereo/osm/default.style /dev/stdin | |
| psql -d gis -f /Users/stereo/osm/create_osm_street_list.sql | |
| psql -d gis -f /Users/stereo/osm/import_cadastre.sql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment