- Yesterday, I got retention task of <Customizing OSM Map>to be in GD. So, I started to work on it.;)
I decided to modify osm database. What I have done with my map will be clear to you soon.:)
The following command will import the database.
osm2pgsql –slim -k -d gis -C 1500 –number-processes 4 /usr/local/share/maps/planet/tcc.pbf
-k or –hstore here means add tags without column to an additional hstore (key/value) column to PostgreSQL tables. In layman language, using this flag while importing data will add a new column <tags> into the tables. But I got the the following error.
Error:
osm2pgsql version 0.91.0-dev (64 bit id space)
Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Osm2pgsql failed due to ERROR: CREATE UNLOGGED TABLE planet_osm_point (osm_id int8,”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,”brand” text,”bridge” text,”boundary” text,”building” text,”capital” text,”construction” text,”covered” text,”culvert” text,”cutting” text,”denomination” text,”disused” text,”ele” text,”embankment” text,”foot” text,”generator:source” text,”harbour” text,”highway” text,”historic” text,”horse” text,”intermittent” text,”junction” text,”landuse” text,”layer” text,”leisure” text,”lock” text,”man_made” text,”military” text,”motorcar” text,”name” text,”natural” text,”office” text,”oneway” text,”operator” text,”place” text,”population” text,”power” text,”power_source” text,”public_transport” text,”railway” text,”ref” text,”religion” text,”route” text,”service” text,”shop” text,”sport” text,”surface” text,”toll” text,”tourism” text,”tower:type” text,”tunnel” text,”water” text,”waterway” text,”wetland” text,”width” text,”wood” text,”z_order” int4,”tags” hstore,way geometry(POINT,3857) ) WITH ( autovacuum_enabled = FALSE ) failed: ERROR: type “hstore” does not exist
LINE 1: …xt,”width” text,”wood” text,”z_order” int4,”tags” hstore,way…
Then, I got to know that I have not created the extension hstore.
Solution:
create extension hstore;
Note: Run above command only after connecting to database where tables are stored. In my case it was “gis” only.
Then I modified OSMBright.xml file. What I want is if there are tags available for building(polygon) then display it otherwise display name of the building. So, according to it the logic is –
SELECT COALESCE(landuse, leisure, “natural”, highway, amenity, tourism) AS type, CASE WHEN (tags->’opening_hours’) IS NOT NULL THEN (tags->’opening_hours’) ELSE name END AS name, way_area AS area, ST_PointOnSurface(way) AS way FROM planet_osm_polygon WHERE name IS NOT NULL AND ST_IsValid(way) ORDER BY area DESC;
Actually, I have shown here full command but important thing to note here is the CASE condition.
\d+ <tablename>;
It will describe structure of the table. Through this you can check whether new column named tags added or not.
The above image clearly shows that instead of displaying the name of the place i.e “Peer Khana” it is showing value of the attribute <tag> i.e. “24/7”.
Then I tried to modify the names of the buildings.
update planet_osm_polygon set name=’Amisha Colony’ where name=’Anmol Colony’;
It was very interesting task to me as I have modified osm database first time and no doubt learnt Postgresql also.
Bye Bye.
THANK YOU! I manually made the hstore extension by:
sudo su – postgres
psql
\c gis
CREATE EXTENSION hstore;
\q
I appreciate your helpful tip! Are you avaliable for OSM advice? Thanks!
LikeLike
Hie, pardon me for the late reply. Yes i will feel happy to help you in osm related doubts. Feel free to ask.
LikeLike