Creating and maintaining the relevance of the local database OSM

From OpenStreetMap Wiki
Jump to: navigation, search
Available languages — S-s-s wiki/Creating and maintaining the relevance of the local database OSM
Afrikaans Alemannisch aragonés asturianu azərbaycanca Bahasa Indonesia Bahasa Melayu Bân-lâm-gú Basa Jawa Baso Minangkabau bosanski brezhoneg català čeština dansk Deutsch eesti English español Esperanto estremeñu euskara français Frysk Gaeilge Gàidhlig galego Hausa hrvatski Igbo interlingua Interlingue isiXhosa isiZulu íslenska italiano Kiswahili Kreyòl ayisyen kréyòl gwadloupéyen Kurdî latviešu Lëtzebuergesch lietuvių magyar Malagasy Malti Nederlands Nedersaksies norsk bokmål norsk nynorsk occitan Oromoo oʻzbekcha/ўзбекча Plattdüütsch polski português português do Brasil română shqip slovenčina slovenščina Soomaaliga suomi svenska Tiếng Việt Türkçe Vahcuengh vèneto Wolof Yorùbá Zazaki српски / srpski беларуская български қазақша македонски монгол русский тоҷикӣ українська Ελληνικά Հայերեն ქართული नेपाली मराठी हिन्दी অসমীয়া বাংলা ਪੰਜਾਬੀ ગુજરાતી ଓଡ଼ିଆ தமிழ் తెలుగు ಕನ್ನಡ മലയാളം සිංහල ไทย မြန်မာဘာသာ ລາວ ភាសាខ្មែរ ⵜⴰⵎⴰⵣⵉⵖⵜ አማርኛ 한국어 日本語 中文(简体)‎ 吴语 粵語 中文(繁體)‎ ייִדיש עברית اردو العربية پښتو سنڌي فارسی ދިވެހިބަސް

Description of steps to create a local database of OSM data and support for its relevance

Setting up FreeBSD

Tuning the kernel

options         SYSVSHM                 # совместно используемая память SysV
options         SHMMAXPGS=65536
options         SYSVSEM                 # семафоры SysV
options         SEMMNI=40               # максимальное количество наборов семафоров в системе
options         SEMUME=40
options         SEMMNS=240              # макс количество семафоров в системе
options         SEMMNU=120              # макс количество структур undo в системе
options         SYSVMSG                 # межпроцессорное взаимодействие SysV

Set in login.conf:

postgres:\
        :lang=en_US.UTF-8:\
        :setenv=LC_COLLATE=C:\
        :tc=default:
run `cap_mkdb login.conf'

Set in rc.conf

postgresql_enable="YES"
postgresql_data="path_to/pgsql/data"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"


Work with Postgres

IInitializing the postgres database

#postgresql initdb

Set in postgresql.conf

shared_buffers = 128MB
checkpoint_segments = 20
maintenance_work_mem = 256MB
autovacuum = off

Run postgres

# sudo -u pgsql postgresql start

Create a postgres's user

# sudo -u pgsql createuser usr-local
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Create a postgres's database

#sudo -u pgsql createdb -E UTF8 -O usr-local db-local
#sudo -u pgsql createlang plpgsql db-local

Setting up access to postgres in pg_hba.conf.

# "local" is for Unix domain socket connections only
local   usr-local         db-local                               trust
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host   usr-local         db-local       192.168.0.0/24          trust
host    all         all         192.168.0.0/24          trust

Create a database structure

#sudo -u pgsql psql db-local < /usr/local/share/postgresql/contrib/hstore.sql
#sudo -u pgsql  psql db-local < /usr/local/share/postgresql/contrib/_int.sql

Note: _int.sql not needed when using osm2pgsql version v0.80 and above
add expansion postgis-1.5

#sudo -u pgsql  psql db-local < /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql
#sudo -u pgsql  psql db-local < /usr/local/share/postgis/contrib/postgis-1.5/spatial_ref_sys.sql

add pgsnapshot sheme

#sudo -u pgsql  psql db-local < /usr/local/share/osmosis/script/pgsnapshot_schema_0.6.sql
#sudo -u pgsql  psql db-local < /usr/local/share/osmosis/script/pgsnapshot_schema_0.6_action.sql
#sudo -u pgsql  psql db-local < /usr/local/share/osmosis/script/pgsnapshot_schema_0.6_bbox.sql
#sudo -u pgsql  psql db-local < /usr/local/share/osmosis/script/pgsnapshot_schema_0.6_linestring.sql

Specify the user of tables

#echo 'ALTER TABLE geometry_columns OWNER TO usr-local; ALTER TABLE spatial_ref_sys OWNER TO usr-local;'\
 | sudo -u pgsql psql db-local

Retrieve the initial data from the api-base

with osmosis

#osmosis --read-apidb database="db-api" user="usr-api" validateSchemaVersion=no outPipe.0="mypipe"\
 --write-xml file="first-dump_api.osm" inPipe.0="mypipe"

with a script osm2mp from a set of tools osm2mp

#perl osmget.pl long_min,lat_min,long_max,lat_max >> fisrt-dump_api.osm

with wget

#wget -O first-dump_api.osm "http://адрес_api-базы/map?bbox=long_min,lat_min,long_max,lat_max" 

Write the data in the local database

#osm2pgsql --style /usr/local/share/osm2pgsql/default.style --create --database db-local\
 --username usr-local --prefix planet --slim --cache 2048 --hstore first-dump_api.osm
--------------------------------------
osm2pgsql SVN version 0.70.5

Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_point
NOTICE:  table "planet_point" does not exist, skipping
NOTICE:  table "planet_point_tmp" does not exist, skipping
Setting up table: planet_line
NOTICE:  table "planet_line" does not exist, skipping
NOTICE:  table "planet_line_tmp" does not exist, skipping
Setting up table: planet_polygon
NOTICE:  table "planet_polygon" does not exist, skipping
NOTICE:  table "planet_polygon_tmp" does not exist, skipping
Setting up table: planet_roads
NOTICE:  table "planet_roads" does not exist, skipping
NOTICE:  table "planet_roads_tmp" does not exist, skipping
Mid: pgsql, scale=100, cache=2048MB, maxblocks=262145*8192
Setting up table: planet_nodes
NOTICE:  table "planet_nodes" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_nodes_pkey" for table "planet_nodes"
Setting up table: planet_ways
NOTICE:  table "planet_ways" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_ways_pkey" for table "planet_ways"
Setting up table: planet_rels
NOTICE:  table "planet_rels" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_rels_pkey" for table "planet_rels"

Reading in file: first-dump_api.osm
Processing: Node(48945k) Way(4309k) Relation(70702)  parse time: 7624s

Node stats: total(48945527), max(1334083869)
Way stats: total(4309318), max(118664108)
Relation stats: total(70702), max(1634550)

Going over pending ways
processing way (2732k)

Going over pending relations

node cache: stored: 47105737(96.24%), storage efficiency: 17.55%, hit rate: 95.42%
Committing transaction for planet_roads
Sorting data and creating indexes for planet_roads
Committing transaction for planet_line
Committing transaction for planet_polygon
Sorting data and creating indexes for planet_line
Sorting data and creating indexes for planet_polygon
Committing transaction for planet_point
Sorting data and creating indexes for planet_point
Stopping table: planet_nodes
Stopping table: planet_ways
Stopping table: planet_rels
Building index on table: planet_ways
Building index on table: planet_rels
Stopped table: planet_nodes
Stopped table: planet_rels
Completed planet_roads
Completed planet_point
Completed planet_line
Completed planet_polygon
Stopped table: planet_ways
------------------------------------

Preview and test the database

Connect to the database

 #psql -U usr-local db-local
 psql (8.4.8)
 Type "help" for help.

Call the list of tables

#db-local=> \d
             List of relations
 Schema |       Name        | Type  | Owner
--------+-------------------+-------+-------
 public | actions           | table | pgsql
 public | geography_columns | view  | pgsql
 public | geometry_columns  | table | usr-local
 public | node_tags         | table | pgsql
 public | nodes             | table | pgsql
 public | planet_line       | table | usr-local
 public | planet_nodes      | table | usr-local
 public | planet_point      | table | usr-local
 public | planet_polygon    | table | usr-local
 public | planet_rels       | table | usr-local
 public | planet_roads      | table | usr-local
 public | planet_ways       | table | usr-local
 public | relation_members  | table | pgsql
 public | relation_tags     | table | pgsql
 public | relations         | table | pgsql
 public | schema_info       | table | pgsql
 public | spatial_ref_sys   | table | usr-local
 public | users             | table | pgsql
 public | way_nodes         | table | pgsql
 public | way_tags          | table | pgsql
 public | ways              | table | pgsql
(21 rows)

Call the table planet_point

#db-local=> \d planet_point
Table "public.planet_point"
       Column       |   Type   | Modifiers
--------------------+----------+-----------
 osm_id             | integer  |
 access             | text     |
 addr:housename     | text     |
 addr:housenumber   | text     |
 addr:interpolation | text     |
 admin_level        | text     |
 aerialway          | text     |
 aeroway            | text     |
 amenity            | text     |
 area               | text     |
 barrier            | text     |
 bicycle            | text     |
...
 wood               | text     |
 z_order            | integer  |
 tags               | hstore   |
 way                | geometry |
Indexes:
    "planet_point_index" gist (way)
    "planet_point_pkey" btree (osm_id)
    "planet_point_tags" gin (tags)

Make a request by filling out and look osm_id

#db-local=> SELECT osm_id, tags FROM planet_point LIMIT 5;
     osm_id   |                     tags
------------+-----------------------------------------------
  329526473 | "alt_name"=>"РљСѓР±РёРЅРєР° 1", "esr:user"=>"182603"
 1305674782 |
   98820001 |
  634991837 |
  994660217 |
(5 rows)

Support for the relevance of local database

Analysis of changes:

Create a file changes by analyzing the data in the database and the file last upload

#osmosis --read-apidb database="db-api" user="usr-api" validateSchemaVersion=no --read-xml first-dump_api.osm\
 --derive-change --write-xml-change diff_api_changes.osc

Create a file changes by analyzing the old and new file upload

New upload data of api-base to a file
#osmosis --read-apidb database="db-api" user="usr-api" validateSchemaVersion=no outPipe.0="mypipe"\
 --write-xml new-dump_api.osm inPipe.0="mypipe"
Detection of changes in the old and new files upload and write them to a file
#osmosis --read-xml first-dump_api.osm --read-xml new-dump_api.osm --derive-change\
 --write-xml-change diff_api_changes.osc

Record the changes in the local database

Add a file with changes

#osm2pgsql --append --style /usr/local/share/osm2pgsql/default.style --database db-local --username usr-local\
 --prefix planet --slim --cache 2048 --hstore diff_api_changes.osc

Full replacement of data in a local database to new data of api-base

#osm2pgsql --style /usr/local/share/osm2pgsql/default.style --database db-local --username usr-local\
 --prefix planet --slim --cache 2048 --hstore new-dump_api.osm

Sources of information

Read more