User:S-s-s wiki/Creating and maintaining the relevance of the local database OSM
< User:S-s-s wiki(Redirected from User:S-s-s 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
- Category:Mapnik
- DE:HowTo Mapnik & Tirex
- HowTo mod_tile
- DE:HowtoMinutelyHstore
- User:Ajoessen/Osmosis
- User:BigPeteB/Setting up a local database copy
- Osmosis/Examples
- Osmosis/Detailed_Usage_0.39
- Osm2mp