Creating and maintaining the relevance of the local database OSM

From OpenStreetMap Wiki
Jump to navigation Jump to search

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