Deliverable 3 - Importing OSM Data into Postgres Database

In 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:

where gis is the name of the database to be created.

installs the language plpgsql.

Consecutively, adding postgis functionality to the database is important and is done as follows:

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

Step 2: Adding/Importing OSM data into the database created:

Change directory to where you unzipped osm2pgsql:

Import OSM data:

Osm2pgsql creates the following tables when the data is imported into the postgres database:

• planet_osm_line: holds all non-closed pieces of geometry (called ways) at a high resolution. They mostly represent actual roads and are used when looking at a small, zoomed-in detail of a map.
• planet_osm_nodes: an intermediate table that holds the raw point data (points in lat/long) with a corresponding "osm_id" to map them to other tables.
• planet_osm_point: holds all points-of-interest together with their OSM tags - tags that describe what they represent.
• planet_osm_polygon: holds all closed piece of geometry (also called ways) like buildings, parks, lakes, areas, etc.
• planet_osm_rels: an intermediate table that holds extra connecting information about polygons.
• planet_osm_roads: holds lower resolution, non-closed piece of geometry in contrast with "planet_osm_line". This data is used when looking at a greater distance, covering much area and thus not much detail about smaller, local roads.
• planet_osm_ways: an intermediate table which holds non-closed geometry in raw format.

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:

      SELECT
        ST_AsText (ST_Transform (ST_GeomFromText ('POINT(' | | '-1361466073' / 100 | | ' ' | | '451747960' / 100 | | ')', 900913), 94326));

This gives the latitude and longitude for the given positional point, taken from planet_osm_point, as shown below:

The 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:

      SELECT
        osm_id,
        highway,
        ref,
        name
      FROM planet_osm_line
      WHERE highway IS NOT NULL
      AND ref = 'I 5'
      AND ST_Intersects(way, ST_Transform(ST_MakeEnvelope((-122.302571660907), 
         (37.5601802504715), (-122.436330806712), (37.8074531813721), 94326), 900913));

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:

      INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
        VALUES (94326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs ', 
      'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],
      AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,
      AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]');