Obviously but very importantly:

• Before copying anything into OSM the license needs to be properly vetted.
• If you are planning to perform any kind of bulk update, the full extent of the Import Guidelines applies.

After comparing Tiger 2017 for 16 US states until now, using shp2cygnus, a tool that I written at Telenav to be able to compare big datasets in a automatic way, to identify roads that are present in the dataset, but not in OSM

After posting on the talk-us, some people told that it would be more useful to run this tool on other datasets, from state or local authorities.

In this article, I want to teach you how to create your own translation file, so that if you have a shp file, you can use Cygnus to compare this with OSM, using this link http://cygnus.improve-osm.org If you have a shp were the size is bigger then 50*50 km, you can send me a email and I can process it for you.

But you need to learn how to create your own translation file.

So, first step.

1. Find your data

… See full entry

Yesterday we had our anniversary OSM Time meetup, where we celebrated 13th years of OSM, and we also had a workshop for the participants, to learn how they can extract information’s from OSM, using overpass API and overpass-turbo.

… See full entry

1. The most simple way is to count just the ways with the highway tag that are at version 1

But …

This fails when we approach the fact that when you split a way, the longer segment will remain with the WAY id, and the smaller way will get a new ID, meaning that it will be version 1.

2. We can use the nodes of the ways

Using the nodes as a identifier, because even if the way is new, all of the nodes are still from the old way, meaning that the timestamp will be different, the user that created the node will be a different user then the user of the way.

3. But how can we find out this in OverPass ?

… See full entry

If i try to use the copy and paste function from the keyboard, more times than not it will not work.

Only when using the menu Edit -> copy it will copy and paste in the first go.

Are you having the same problem or there is something local with me and my colleagues from the map analyst team that we are having this problem ?

I made a ticket https://josm.openstreetmap.de/ticket/13620

And for simplicity, also, a video showing the error.

https://youtu.be/56ZLaZ_BM64

How to find out the total length of highway per type of road / per Country in PostGIS

using the OSM postgis Script Repository This is a part of the final result.

• Step 1. - Download the North-America osm.pbf file from Geofabrik __(7.4 Gb)__

The link is here You can use this command line to download the file in linux.

wget http://download.geofabrik.de/north-america-latest.osm.pbf

• Step 2. - Load the data into PostGIS using the scope.sh utility tool

You can find the script here https://github.com/baditaflorin/osm-postgis-scripts/ The aim of this tool is to simplify the process of importing a osm.pbf file into PostGIS. Now the procedure is complicated and you first have to create the database, then to enable postGIS and Hstore on that database, etc

… See full entry

Hello dear OSM community.

It was been a while since i posted in the diary.

I had made a new script that i had put in the OSM postGIS scripts repo https://github.com/baditaflorin/osm-postgis-scripts

You can find it in the statistics folder, under the name highway_length_per_type_different_attr.sql

How can you do the same for your country or region ?

First, download and run the scope.sh (linux) or the scope.bat (win) utility that you will use to load the file into postGIS.

After you load the file in postGIS, you just have to connect to the database either in PgAdmin or via Qgis Dbmanager and copy and paste the code.

It took 20 seconds to generate this result for the map of Romania (~150 MB pbf file)

Please help me with ideas of what other scripts would there be interesting for you. Thanks and see you at SOTM in 3 weeks

I define a grid city as a city formed from straight roads.

You can read more and see a video where i try to explain here https://josm.openstreetmap.de/ticket/13224

Now, if we have a matrix of 7*7 grid, we have to click almost 50 times to add the city.

With this solution, we should only add the vertical and horizontal lines, clicking 14 times, and then using the Validation, it will see that we have 49 Crossing ways, and would offer the possibility to fix, by merging the overlapping ways together

A B C D E F G

1 1 2 2 3 3 4 4 5 5 6 6 7 7

A B C D E F G 

https://josm.openstreetmap.de/ticket/13224

I will not explain here all the process, but if people are interested, i can do a short video explaining all the process.

I have used the OSM Postgis Script to load the data in PostGIS. This was made for Stereo70 projection, but can be adapted for wsg84

The result it`s a completely 3D map that you can navigate and see each all of the edits made in one year or for each month (not shown in the video or photos)

In this image you can see the ways that have been created or last modified in 2016 (top layer), then 2015, then 2014, then 2013, then 2012, then 2011, 2010,2009,etc

… See full entry

This is a smart and simple tool that i just not able to use because of some bugs that i was receiving.

https://github.com/planemad/osm-history-processor

fixed, now it works, in Ubuntu you need to remove the ./ in line 72

This script works in 2 parts.

1. First it will generate from the osm.pbf file the list with all the admin_level=4 (al_4)

Then it will remove the ones that are not closed. Then it will make a polygon from each al_4, using the ST_BuildArea

After it will have a inner join that will search for all the nodes and ways that are a restaurant.

2. Then, using st_contains we are searching to see in what polygon each restaurant fits.

Took 4 seconds to do it for Romania

You can find the query here https://github.com/baditaflorin/osm-postgis-scripts/blob/master/statistics/count_number_of_restaurants_inside_each_al_4.sql

… See full entry

I have added a new category , Statistics to the OSM PostGIS Script Repository

For example, using this quick query you can get a year by year statistic about the last time when a building was edited. You can see the result for Romania here :

https://github.com/baditaflorin/osm-postgis-scripts/tree/master/statistics

… See full entry

The total lenght of the pedestrian roads in the world is 2.919.220 kilometers

For the tunnels and bridges, i will also present to you the global figure here 114.949 pedestrian tunnels are in the osm planet map 351.099 pedestrian bridges are in the osm planet map

Crossings Name 48872 Paris 27647 Madrid 26310 Milan 18959 Yokohama 16748 Helsinki 15445 Kawasaki 15418 Tokio 13542 Moskau 13117 Portland 13081 Phoenix

Top Tunnels

Count tunnels Name city 4304 MĂĽnchen 2552 Moskau 2255 Berlin 1948 Essen 1792 Paris 1417 Hamburg 1366 Milan 1366 Vienna 1268 Helsinki 1205 London 1091 Zurich 1082 Prag 1021 Stuttgart

Top Bridges

Count Name 3911 Hongkong 3844 Kawasaki 3648 Shenzhen 3544 Kowloon 3535 Tokio 3327 Yokohama 3070 Rotterdam 2330 London 2256 Amsterdam

I made a windows version of the scope.sh script. The script helps you load a osm.pbf file into PostGIS, without needing to know osmosis or other low level tools. The fact is that you want to load and use the data, not think what command line should i use in postgres or osmosis for doing this.

Osm Diary Entry about Osm PostGIS Script Repo

## Github code ## YouTube Tutorial

The BBOX is generated based on the osm.pbf file that you provide.

Download Print A2 https://www.dropbox.com/s/6qppgjggop14szu/osm%20grid%20Romania%20and%20Moldova%20A2.png?dl=0

Osm Diary Entry about Osm PostGIS Script Repo

## Github code - The Grid is in the test fork ## YouTube Tutorial

Objective

… See full entry

New Osm Postgis Script – Find duplicated nodes.

The idea of using a single repository where everybody cand find and fork the same Postgis OpenStreetMap schema scripts, is allowing anybody in the world to run a query on every part of the OSM database.

Read more on the project here osm.org/user/baditaflorin/diary/37758 Github code here https://github.com/baditaflorin/osm-postgis-scripts/tree/master

Even if you want to load a city and run a analysis on that town, or you want to find out where are all the duplicated nodes in Mexico, you can reuse the code that other people had created. Or you can add your own script that will be compatible with anybody that will load the osm.pbf with Osmosis.

To make the loading of a osm.pbf more simple, i made the SCOPE loader script for linux users, that allow everybody to create a postgis database without needing to know how to use Osmosis and other command line tools. Youtube tutorial here https://www.youtube.com/watch?v=vhJQbKey9EI

Mexico Example :

In Pgadmin i have run the command to find duplicated nodes on all the nodes that exist in Mexico on a intel octacore, 16 GB RAM Machine

Mexico have 13.000.000 nodes, and the query took 550 seconds to complete. It found 43.000 rows, meaning around 10.000 instances of possible duplicated nodes, ways. For example this building appers on the same location 23 times !!! osm.org/way/376880427#map=18/20.80528/-104.92827

… See full entry

Openstreetmap Postgis Script Repository For Osmosis Pgsnapshot Schema - QGIS compatibility scripts

Github code here https://github.com/baditaflorin/osm-postgis-scripts/tree/master

Youtube tutorial here ( 4 minutes ) https://www.youtube.com/watch?v=vhJQbKey9EI

The licence is AGPLV3+

History

Here at Telenav , in the Map Analyst Team we use Postgis a lot.

In the real world, Postgis and OpenStreetMap seems to be used more only in Academics or by people that know a medium or high degree of programming.

I think that we should change that and make more simple the availability of Postgis as a tool that can help us visualize information’s from the OSM database much faster.

3 Ways of making Postgis more easy to use with OSM data

# I. - SCOPE

For this i will release SCOPE ( databaSe Creator Osmosis Postgis loadEr ), a command line tool for easy loading of a osm.pbf file into Postgis, via osmosis

• You will find scope in the repo

II. A central place for osm-postgis-scripts

… See full entry

The Long name is :

Postgis Openstreetmap Script Repository for Osmosis Pgsnapshot Schema

History

Here at Telenav , in the Map Analyst Team we use Postgis a lot.

In the real world, Postgis and OpenStreetMap seems to be used more only in Academics or by people that know a medium or high degree of programming.

I think that we should change that and make more simple the availability of Postgis as a tool that can help us visualize information’s from the OSM database much faster.

3 Ways of making Postgis more easy to use with OSM data

# I. - SCOPE

For this i will release SCOPE ( databaSe Creator Osmosis Postgis loadEr ), a command line tool for easy loading of a osm.pbf file into Postgis, via osmosis

• You can try scope in the repo
• Also, you will find there a Postgis example that i posted there.

II. A central place for Postgis OSM Scripts

… See full entry

I will not elaborate now on the difficulties that i had getting ( either osmconvert or osmfilter removes the timestamp, user_id, leaving me with a extracted file that is 50 MB osm.pbf but cannot do nothing more then do some static statistics.

I cannot see how many where imports, how is evolving in time, nothing.

I could try to get the information via Overpass API that this would mean to query the database fro 500.000 relations

Until i figure out what i can do, i was able only do do reverse geocoding using POSTGIS, tried with the http://www.gadm.org/ dataset in QGIS to count the points in each polygon, but it got stuck after 2 hours at 94 %

I will try to load the GADM dataset in postgis and do reverse geocoding from POSTGIS, so i can have a better lever or details about the spread of the turn restriction in each country

… See full entry