Paragon Corpoation PostGIS Spatial Database Engine The Open Source Geospatial Foundation UMN Mapserver Boston Geographic Information Systems   
FOSS4G International 2017, August 14th-18th 2017
   PostGreSQL Object Relational Database Management System
Home   About Boston GIS   Consulting Services  Boston GIS Blog  Postgres OnLine Journal  Planet PostGIS  PostGIS Funding

Purpose of BostonGIS

BostonGIS is a testbed for GIS and Web Mapping solutions utilizing open source, freely available and/or open gis technologies. We will be using mostly Boston, Massachusetts data to provide mapping and spatial database examples.

If you have some thoughts or comments on what you would like to see covered on this site, drop us a line on our Feed Back page.

GIS Tutorials on Opensource and OpenGIS technologies Tutorials
GIS Article comments Article and Tutorial Comments
Boston GIS BLog Rss FeedBoston GIS blog

PDF HTML All BostonGIS tutorials packaged together in an E-Book.

Boston GIS Store


Tutorial and Tip Sites
Desktop GIS
External Data
GIS Events and Groups
GIS SDKs and Frameworks
External Resources
GIS Blogs Around Boston
External GIS Blogs
External Papers Articles
GIS Quick Guides and References
OpenStreetMap and OpenLayers Tutorials
Part 3: Using your own custom built OSM tiles in OpenLayers more ...
Part 2: Building Tiles with PostGIS OpenStreetMap data and Mapnik: Your Own OpenStreetMap more ...
Part 1: Loading OpenStreetMap data into PostGIS: An Almost Idiot's Guide

Printer Friendly

For this exercise, we will download Massachusetts OSM data and then load it into our PostGIS spatially enabled PostgreSQL database. The OSM data contains roads, points of interests, building footprints, administrative boundaries, addresses, and too many other things to itemize. Note that much of the data provided in OSM for Massachusetts is provided by our very own Massachusetts Office of Geographic Information (MassGIS) as well as contributions from people like you. Now on with the show.

Loading the OSM Planet data

  1. These instructions assume you already have PostGIS 1.5+ installed and a spatially enabled database called osm. You can call the database anything you want or use an existing PostGIS spatial database. If you don't have one, create one using our Getting Started with PostGIS: An Almost Idiot's Guide
  2. Hstore is a key value tag column data type for PostgreSQL. It is sometimes referred to as a data type for supporting schema-less designs. It will require a bit more space to load but provides more flexibility on how you can query your OSM data and has additional information you will not find in any of the other columns. Installing hstore is optional but you will need it if you use the --hstore flag during load. Install Hstore in your PostgreSQL database. It is located in your PostgreSQL share/contrib/hstore.sql. If you are running PostgreSQL 9.1 or above, you can use the new extensions system to install by running the SQL statement:
  3. Download Massachusetts osm file from CloudMade You want to download the file called massachusetts.osm.bz2
  4. In order to load OpenStreetMap .OSM XML files, you will need osm2pgsql which you can find out more about at There are compiled binaries available for many Linux variants, Windows, and Mac OSX.

    If you are on windows, go here If you plan to build map tiles with the data later, we recommend the HOTOSM package which installs osm2pgsql as well as MapNik and OSMOSIS. These will be useful for generating tiles.

  5. If you don't see a file in your package, download it from the above links. For the HOTOSM install, is located in the Program Files/HOSTOSM/share folder. Copy the file into the same folder as your massachusetts.osm.bz2 file.

    Note: IF you plan to setup a mapping tile server with OSM data later, check out Dane Springmeyer's Mapnik tutorials:

  6. If you install the windows HOTOSM package make sure to reboot your pc as requested to get all the path variables in your system. Next at the command line cd into the folder containing your data and run below to load the data:
    osm2pgsql massachusetts.osm.bz2 -d osm -U postgres -P 5432 -S --hstore

    If you want to load additional states, use the --append option switch. So for example if I wanted to load neighboring states like New Hampshire, I would download New Hampshire and then follow with this command.

    osm2pgsql new_hampshire.osm.bz2 --append -d osm -U postgres -P 5432 -S --hstore
  7. If all goes well with your install, your screen should look something like:
    osm2pgsql SVN version 0.69-21289M
    Using projection SRS 900913 (Spherical Mercator)
    Setting up table: planet_osm_point
    NOTICE:  table "planet_osm_point" does not exist, skipping
    NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
    Setting up table: planet_osm_line
    NOTICE:  table "planet_osm_line" does not exist, skipping
    NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
    Setting up table: planet_osm_polygon
    NOTICE:  table "planet_osm_polygon" does not exist, skipping
    NOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping
    Setting up table: planet_osm_roads
    NOTICE:  table "planet_osm_roads" does not exist, skipping
    NOTICE:  table "planet_osm_roads_tmp" does not exist, skipping
    Mid: Ram, scale=100
    !! You are running this on 32bit system, so at most
    !! 3GB of RAM can be used. If you encounter unexpected
    !! exceptions during import, you should try running in slim
    !! mode using parameter -s.
    Reading in file: massachusetts.osm
    Processing: Node(10082k) Way(621k) Relation(2k)
    Node stats: total(10082538), max(1202366398)
    Way stats: total(621446), max(104206285)
    Relation stats: total(2846), max(1463423)
    Writing way(621k)
    Writing rel(2k)
    Committing transaction for planet_osm_point
    Sorting data and creating indexes for planet_osm_point
    Completed planet_osm_point
    Committing transaction for planet_osm_line
    Sorting data and creating indexes for planet_osm_line
    Completed planet_osm_line
    Committing transaction for planet_osm_polygon
    Sorting data and creating indexes for planet_osm_polygon
    Completed planet_osm_polygon
    Committing transaction for planet_osm_roads
    Sorting data and creating indexes for planet_osm_roads
    Completed planet_osm_roads

Spot checking the tables

If your data loaded, you should see three new tables all with a column called way that holds the PostGIS geometry and another column called tags which holds the hstore key value pairs.

The way column holds the PostGIS geometry in spherical web mercator projection or if you used the reproject switch, a different projection. NOTE that while spherical mercator is good for web mapping display, it sucks for measuring distances, area or anything that has to do with measurement. We'll talk about that later. So in your database you should see these 3 tables:

  • planet_osm_point: which contains points of interest such as restaurants, hospitals, schools, supermarkets and addresses
  • planet_osm_lines: contains roads and streets
  • planet_osm_polygons: contains lakes, building footprints, administrative boundaries such as towns and cities

Index your hstore column

There is some data available in Hstore that is just not available in any of the columns. Some of the more commonly used tags, you will find as columns in the data. With that said, we will index our hstore columns with these SQL commands.

CREATE INDEX idx_planet_osm_point_tags ON planet_osm_point USING gist(tags);
CREATE INDEX idx_planet_osm_polygon_tags ON planet_osm_polygon USING gist(tags);
CREATE INDEX idx_planet_osm_line_tags ON planet_osm_line USING gist(tags);

Query the data

Now for a simple query to pull all sushi places. Sadly it seems the sushi offering is not very complete:

SELECT name, ST_AsText(ST_Transform(way,4326)) AS pt_lonlattext -- tags 
FROM  planet_osm_point
WHERE tags @> 'cuisine=>sushi'::hstore;

-- Result --
    name     |         pt_lonlattext
 Moshi Moshi | POINT(-72.6285103 42.3202165)
 Mr Sushi    | POINT(-71.1553199 42.4162195)
Pull all the kinds of amenities and their sources:

This you can write one of two ways. Using the hstore tag (second query) is faster since its indexed.

SELECT DISTINCT amenity, tags->'source_url' As source
FROM planet_osm_point
WHERE amenity > ''
ORDER BY amenity;
-- about twice as fast for my MA dataset -- The ? 'amenity' is an indexable hstore operation that asks if the hstore tags has a key called 'amenity'
SELECT DISTINCT tags->'amenity' As amenity, tags->'source_url' As source
FROM planet_osm_point
WHERE tags ? 'amenity'
ORDER BY tags->'amenity';

          amenity           |                               source
bus_station                 |
cafe                        |
campsite                    |
:                           |
cinema                      |
City Hall                   |
Clinic                      |
college                     |   
library                     |

Post Comments About Part 1: Loading OpenStreetMap data into PostGIS: An Almost Idiot's Guide
Using OpenLayers: Part 2 more ...
Using OpenLayers: Part 1 more ... download
PostGIS, pgRouting, and PostgreSQL Tutorials
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.2) more ...
pgRouting: Loading OpenStreetMap with Osm2Po and route querying more ...
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0) more ...
OSCON 2009: Tips and Tricks for Writing PostGIS Spatial Queries more ...
PGCon2009: PostGIS 1.4, PostgreSQL 8.4 Spatial Analysis Queries, Building Geometries, Open Jump more ...
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL more ...
PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution more ...
Solving the Nearest Neighbor Problem in PostGIS more ...
PLR Part 2: PL/R and PostGIS more ...
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide more ...
Part 2 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Displaying the Maps more ... download
Part 1 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Compiling SharpMap with PostGIS more ...
Part 3: PostGIS Loading Data from Non-Spatial Sources more ...
Part 2: Introduction to Spatial Queries and SFSQL with PostGIS more ...
Miscellaneous Tutorials/Cheatsheets/Examples
SpatiaLite Tutorials
Boston External Map Examples
SQL Server 2008 Tutorials
UMN Mapserver Tutorials
General Commentary
Locations of visitors to BostonGIS
Boston GIS      Copyright 2017      Paragon Corporation