How to use osm2pgsql for loading raw OSM data into a Postgresql database in Windows

In this post we will look at how to use osm2pgsql, a command-line program for loading raw OSM data into a PostGIS database. In order to  get OpenStreetMap data into a database, you could create a Postgresql PostGIS enable database in which you can store geographic data. 

You can find the step to set up Postgresql with PostGIS in Windows in this blog post.

http://effective-gis.blogspot.com/2015/02/how-to-download-and-install-postgresql.html

Follow below steps to set up osm2pgsql on Windows , we assume that you have set up PostGIS database(s) correctly and later will give you some tips on that.

Get OSM2PGSQL

Step 1

osm2pgsql can be downloaded  from here and if you want, there are many builds available in internet.
https://github.com/openstreetmap/osm2pgsql

Step 2

Download the .zip  file and unzip it in a location where you will  not change later .To import data from OSM you have to run a program called osm2pgsql.exe in the extracted  folder. Extracted folder location will add to system path later.
Make sure that custom style file is in the extracted folder . This custom style file define which tags are included in the database during import

Set System Path 

Step 1

Go to Settings-> Settings->PC info-> Advanced system settings.

Click  "OK"  several times to  save new settings

Step 2

You can check the functioning of osm2pgsql using Windows Command Prompt

If you are getting something similar to this , then osm2pgsql  works fine

Getting Raw OSM Data

Before starting we need to  have some raw OSM data to import into  a database. This can be downloaded from OSM or  https://mapzen.com/data/metro-extracts/  which  host many OSM extracts all over the world.

Import Raw OSM data 

Step 1

Before importing data open PgAdmin III and create a new database named gis with superuser ownership.Then run osm2pgsql program via the command line .

We need to provide at least 

• The location of the OSM Data File
• Name of the database and username
• Location of the style file

Step 2

Run following command replacing location of the OSM Data File  and location of the style file.

osm2pgsql -c -d gis -U postgres -H localhost -S C:\default.style C:\osm2pgsql\TryOSM.osm

Step 3

It takes time to load all data into the database. If everything works fine then you will get  something similar to  this.

Testing Data

We can view the data in our database using QGIS if the import was successful .

Follow below steps to  view the data in QGIS

• Open QGIS and click on "Add postGIS Layer" In the connection bar click "New"

• Enter username as "postgres" and your password

• Click "OK" to save the connection settings and then click "Connect " to  connect  to  your PostgreSQL server.

• Click next to  see "public" and expand it to see all the layers (tables) in your database .

• There you can see a separate table for each object types-points, lines and polygons.

• Select layers and click "Add". Choose WGS84  as the CSR.

• If everything works fine, you will see the layers you selected displayed in QGIS.

GeoServer

GeoServer is a Java-based software server that allows users to view and edit geospatial data.  User must ensure JAVA is running on the computer before installing Geo Server and Apache Tomcat must be installed. 

Step 1,

Download Geo server (Stable version) from following link

http://geoserver.org/download/

Step 2,

Make sure Java is running on the computer or else install Java using JAVA SDK. 

Step 3,

We must create runtime environment for Java,

Go to My computer properties-  Advance system settings - Environment variables - New user variable 

Variable Name - JRE_HOME

Variable Value - C:\Program Files\Java\jre1.8.0_60

Step 3,

Copy Apache Tom Cat in to "C" drive

Step 4,

Copy and paste geoserver.war file into tomcat webapps. If the .war file is not available in the geoserver folder download it in the following link.
http://geoserver.org/release/stable/

Step 5,

Run startup.bat file in the Apache bin using command prompt.

Step 6,

Do not close the Tomcat and type following link in your web browser.

localhost:8080/geoserver/web

user name - Admin

password - Geoserver

Spatial Temporal analysis in QGIS

When we need to analyse spatial data which are changing it's locations with the time we have to use temporal analysis tools. Also this refers as tracking analysis. QGIS plugin "Time Manager" allows users to analyze  spatial data with the time. 


Step 1,

Open QGIS - Plugins - search "Time Manager" - Install the plugin

Step 2,

Add vector data layer which contain time/date as attribute

Step 3,

Open Time Manager plugin

Step 4,

Click on the "Settings' button in the Time manager and add data layer. then select the field where time values are stored

Step 5,

Change the time duration as required (Min, hours, day etc...)

 

Step 6,

Now we can run the analysis, just click on the play button

Step 7,

Click export button to export the analysis as video file

Temporal Analysis in ArcGIS

When we need to analyse spatial data which are changing it's locations with the time we have to use temporal analysis tools. In ArcGIS we can use Time slider to perform temporal analysis.


Step 1,

To do the temporal analysis we required spatial data which is having time or a date as attribute.

Step 2,

In ArcGIS shape file we cannot store time though we can store dates. hence we can create personal Geodatabase and import our shapefile. Then open shapefile in Access database which allows users to insert time together with the date in a same filed. 

Step 3,

We are using ArcGIS Time Slider for this practical, we can go to tool bar in the ArcGIS and access to time slider, if you currently not added any layer which is not having time attributes this tool remain inactive. 

 

Step 4,

We have to enable the time for the layer. Right click on the layer- Properties - Time. 

Step 5,

Now time slider is active and we can start temporal analysis. 

Step 6,

Click on the Time Slider to open the tool,

Step 7,

Users can select time field, field format, time step intervals (Minutes, Hours, Days etc...)and have to calculate layer time extent, and possible to select required time zone by clicking on the "options " button in the Time Slider

Step 8,

when all the adjustments been done we can run the Time slider which will display the locations depending on the time/Date,

Step 9,

To keep remain the displayed locations also we have to tick on "Display Data Cumulatively" option in the layer properties. 

Step 10, 

We can export temporal analysis as video. click on the video button the time slider and export the video. 

How to Display Elevation Profile in QGIS

QGIS provide easy way to display elevation of DEM and also possible to copy the values with the coordinates. In QGIS plugin called "Profile Tool" has to be installed to the QGIS

Step 1,

Go to plugins - Manage and Install Plugins

Step 2,

Search for "Profile Tool" - Install Plugin

Step 3,

After installation of the Profile plugin users can directly use the Profile tool 

Step 4,

Draw line over the DEM and double click to finish drawing. Tool will generate graph of the elevation. 

How to download locations from Google Map

When we need to download required places like hotels, schools or any location best method is to download from the Google maps. 

1. Step

Use following link for Google map

https://www.google.lk/maps

2. Step

Select "Menu" of the Google map

3. Step

Select "My Map" option in drop down list

4. Step

Once you click on "My Maps" you can see your maps if you have created before, to create new map select "Create Map",

5. Step 

Once you created new maps now you have to add required data to the maps, here I wanted to download hotels locations in Kandy district of Sri Lanka. You can select any places according to your requirement. 

6. Step 

You can see available location on the map with names,

7. Step

Then you have to click on the place makes and  select "Add to Map" option to add those locations to your map.

8. Step

  Once you add locations to map those  added Placemarks change in to red color.

9. Step

Once you complete adding data in to your map you can change the name of you map, click on the "Layer Option" in your map and select rename.

10. Step

once you complete adding data to your map you can print this map or you can export map as KML layer which can be converted to ESRI shapefile if you required to use in ArcGIS or even in GRASS or QGIS. otherwise you can display those KML on Google earth..

11. Step

You can display your KML on Google earth