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 http://www.openstreetmap.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 !!! http://www.openstreetmap.org/way/376880427#map=18/20.80528/-104.92827
This garden way needs smoothing 369775636 http://www.openstreetmap.org/way/369775636 Also this way 369775423
The find duplicate nodes it`s available here https://github.com/baditaflorin/osm-postgis-scripts/blob/master/find_duplicate_nodes.sql