Chris Pollett > Students > Pundi Muralidharan
[Bio] [Blog] [Deliverable 1 - WebGL Program: The Logic Behind] [Deliverable 2 - The Study of OSM Data and Vector Tiles] [Deliverable 3 - Importing OSM Data into Postgres Database] [Deliverable 4 - Study of Current Tile Generators in Trend] [CS 298 Report-Intermediate - PDF] [CS 298 - Summary of Intermediate Results - PDF] [CS 298 Project Source Code - ZIP] |
Deliverable 3 - Importing OSM Data into Postgres DatabaseIn this deliverable, I downloaded a small area of OSM data in XML format. I chose the state of CA and used osm2pgsql which is a command-line based program that imports OpenStreetMap data to postGIS-enabled PostgreSQL databases. Osm2pgsql runs on most of the operating systems, I have used a Mac OS X for this purpose. It can be installed either via macports or homebrew, for which I chose the latter. I installed postgres.app and pgAdminIII, which is a GUI development platform for PostgreSQL database. The following are the steps followed in importing OSM data into the postgres database: Step 1: Create and prepare the database using the following commands:
createuser U postgres <username>
where gis is the name of the database to be created.
createlang U postgres plpgsql gis
installs the language plpgsql. Consecutively, adding postgis functionality to the database is important and is done as follows:
psql -U postgres -d gis -f PATH_TO_POSTGRES/share/contrib/postgis-1.5/postgis.sql
Finally, adding a projection (specifically spherical Mercator) is done. This is represented by 900913 in PostgreSQL database. The file 900913.sql is downladed from the following github repo: https://github.com/openstreetmap/osm2pgsql
psql -U postgres -d gis -f PATH_TO_FILE/900913.sql
Step 2: Adding/Importing OSM data into the database created: Change directory to where you unzipped osm2pgsql:
cd PATH_TO_OSM2PGSQL
Import OSM data:
osm2pgsql -U postgres -s -S ./default.style PATH_TO_OSM_FILE/osm-california.osm
Osm2pgsql creates the following tables when the data is imported into the postgres database:
As a next step, I tried to write an SQL query, which expresses the following condition: 'Given a bounding box represented by latitude and longitude coordinates, check if the highway 'I 5' passes through the bounding box, or not'. As a solution, I retrieved the latitude and longitude for the desired bounding box using the following query:
This gives the latitude and longitude for the given positional point, taken from planet_osm_point, as shown below: Figure: Output of the above queryThe above coordinates correspond to the upper bounds of the bounding box. The same query is used to find out lower bounds of the bounding box. The, using the coordinates for the bounding box, we could check if the highway I 5 passes through the bounding box or not. The following query produces the desired result:
In the above query, 94326 is the SRID (spatial reference ID) for Web Mercator projection used and can be added to the spatial_ref_sys table. This table contains all of the spatial projections used. The following query helps adding web mercator projection:
|