Paragon Corpoation PostGIS Spatial Database Engine OSGeo.org The Open Source Geospatial Foundation UMN Mapserver Boston Geographic Information Systems       PostGreSQL Object Relational Database Management System
Home   GIS Books   About Boston GIS   Consulting Services  Boston GIS Blog  Postgres OnLine Journal  Planet PostGIS

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.


PostGIS in Action
check out book and download the first chapter and SQL Primer for free.

Tips and Tricks for PostGIS

PostgreSQL: Up and Running
PostgreSQL: Up and RunningIt covers PostgreSQL 8.4-9.2 with special focus on features introduced in PostgreSQL 9.1-9.2

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

Loading


Tutorial and Tip Sites
Desktop GIS
External Data
GIS Events and Groups
GIS SDKs and Frameworks
External Resources
Glossary
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:
    CREATE EXTENSION hstore;
  3. Download Massachusetts osm file from CloudMade http://downloads.cloudmade.com/americas/northern_america/united_states/massachusetts. 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 http://wiki.openstreetmap.org/wiki/Osm2pgsql. There are compiled binaries available for many Linux variants, Windows, and Mac OSX.

    If you are on windows, go here http://wiki.openstreetmap.org/wiki/Osm2pgsql#Windows_XP. 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 default.style file in your package, download it from the above links. For the HOTOSM install, default.style is located in the Program Files/HOSTOSM/share folder. Copy the default.style 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: http://www.dbsgeo.com/.

  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 default.style --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 default.style --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                   | http://mass.gov/mgis/townhalls.htm
Clinic                      |
college                     |   
:
library                     | http://mass.gov/mgis/libraries.htm
:




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
pgRouting: Loading OpenStreetMap with Osm2Po and route querying more ...
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0) more ...
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 1.5) 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
 CommenterComment
11/1/2013 8:16:57 AMReginaBehzad,

Which OS are you running on. I haven't had the need to upload the full OSM database so can't say. I've heard good things about loading large osm sets with Imposm so you might want to check Imposm http://imposm.org/ out if you have issues with osm2pgsql. unfortunately I don't think there are any builds for windows for imposm and not sure how much trouble to build.

11/1/2013 8:10:23 AMReginaMike,

You need to have hstore installed first.

Should be able to do that with

CREATE EXTENSION hstore;

Sorry if that wasn't clear.
10/31/2013 4:28:22 AMBehzad GhadimiHi,
I have read your article and thank for that. It was useful for me.
But what if I want to import the whole 30GB map data?
Isn't there be memory full error or something like that?
8/27/2013 11:37:34 PMMike LashHi! Thank you for posting these sample queries. I'm having trouble running the ones that involve 'tags->'. Was wondering if you have any suggestions. We are running PostgreSQL version 9.1 and PostGIS version 2.0.1. I saw the additional information in another comment about the operators but wasn't sure if that was the issue. Thank you!
6/2/2013 5:28:17 PMReginaTony,

It's a bit dated but not that much. Last I tried was a couple of months ago pulling data from http://metro.teczno.com/ for Boston. It worked fine.

The error you are getting seems to be more an issue with your data file or your database connection.

What is the line you are using for osm2pgsql

It looks like its reading the file fine, but breaking apart when trying to connect to your database. What follows -d should be the name of your postgis enabled database.

-d yourdatabase

It is also possible the directory slashes in your path are confusing it. You can try D:/ instead or alternatively

D:
cd D:\data
osm2pgsql wales.osm.bz2 -d yourdatabase -U postgres -P 5432 -S default.style --hstore
5/31/2013 1:13:55 PMTony BurtonHi,

Is this information still current?
I followed the instructions, although instead of using HOTOSM to install osm2pgsql, I grabbed the latest version of the software and unzipped it as the HOTOSM.exe is 3 years old.

When I attempt step 6, the process starts and gets to:
Reading in file: D:\\data\\wales.osm.bz2
Processing: Nodes(1815k 139.7k/s) Way(190k 11.91k/s) Relation(1570 392.50/s) parse time: 33s

Reading in file: -d
error while opening file -d

I'm guessing the problem may be due to incompatibilities between the various software and guides I have used to get to here. Any information you may be able to provide would be gratefully received.
Thanks
12/7/2012 4:52:27 PMReginaMike,
Sorry when you said reverse geocoding I assumed you were talking about the reverse geocoder function/geocoder packaged with PostGIS. Now I realize you might be asking about a query against OSM data.

What query are you trying to run?
12/7/2012 4:48:02 PMReginaNot enough information to tell. Which version of tiger geocoder are you running? the 2.0 or the 2.1.0 in trunk.

Which point are you testing? Which state do you have loaded? Often time people reverse the order of the points. e.g. the point should be long lat and not lat long. That's the common mistake people make and in that case you'll get nothing back.

Other causes are failure in loading. You want to make sure that in tiger_data schema for state in question that you have faces,edges,place,featnames loaded.

For 2.1 you need to run the national load first that loads in state, county, zip data. Otherwise the reverse geocode will not return anything.




12/7/2012 1:02:06 PMMikejust curious why reverse geocoding doesnt pull any data for an imported state when i search the coordinates?

i followed the import and indexing steps
10/30/2012 4:03:04 PMReginaYou didn't do anything wrong. Try installing this patch.

http://trac.osgeo.org/postgis/ticket/1287

I think newer versions of osm2pgsql (none windows has the correct syntax that works for 1.5/2.0, windows new binary is not available yet to my knowledge)
10/26/2012 6:44:26 AMAnandThanks Regina,
Now I am getting a different error.
Writing rel(3k)
Committing transaction for planet_osm_point
Sorting data and creating indexes for planet_osm_point
CREATE INDEX planet_osm_point_index ON planet_osm_point USING GIST (way GIST_GEO
METRY_OPS);
failed: ERROR: operator class "gist_geometry_ops" does not exist for access me
thod "gist"
Error occurred, cleaning up
-------------------------
I am using postgresql9.2 with postgis 2.0.
Any idea what could be wrong?
10/20/2012 6:05:38 PMReginaI think that error happens if you already loaded the data or parts of it. you might want to try truncating your table or if loading multiple files, make sure areas don't overlap
10/18/2012 10:09:05 AMAdhisheshwhile appending the data i am getting this error

failed: ERROR: duplicate key value violates unique constraint "planet_osm_rels_pkey"
CONTEXT: COPY planet_osm_rels, line 559: "148838 2 235 {424317935,158368533,42394358,42394470,50752242,42394374,42394370,42394404,42394405,423..."
8/26/2012 7:57:19 PMReginaAnand,

Sorry for the delay in responding. This was a bug in 2.0 the 900913 got accidentally removed.
http://trac.osgeo.org/postgis/ticket/1805

This is fixed in 2.0.1, but spatial_re_sys aren't loaded as part of soft upgrade, so upgrading probably won't help.

You can manually insert it back with this:

---
--- Google Maps / Microsoft Maps
---
INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (900913,'spatialreferencing.org',900913,'PROJCS["Popular Visualisation CRS / Mercator (deprecated)",GEOGCS["Popular Visualisation CRS",DATUM["Popular_Visualisation_Datum",SPHEROID["Popular Visualisation Sphere",6378137,0,AUTHORITY["EPSG","7059"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6055"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4055"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Mercator_1SP"],PARAMETER["central_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PARAMETER["false_northing",0],AUTHORITY["EPSG","3785"],AXIS["X",EAST],AXIS["Y",NORTH]]','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs' );


It's deprecated as well but seems to have stuck in everyone's mind including mine because of the

900913 = GOOGLE moniker
7/3/2012 6:46:15 AMAnandI have followed the steps upto Step 7.
Here I get the following output resulting in an error
***********
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
SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
failed: ERROR: AddGeometryColumn() - invalid SRID
CONTEXT: SQL statement "SELECT AddGeometryColumn('','',$1,$2,$3,$4,$5, $6)"
PL/pgSQL function "addgeometrycolumn" line 5 at SQL statement

Error occurred, cleaning up
**********************

I am using PostGIS 2.0 and PostgreSQL 9.1.
Any idea what could be wrong?

Thanks and regards,
Anand.
5/23/2012 2:34:24 AMReginaAnother additional note. If you are running PostGIS 2.0 and using osm2pgsql for windows, the new change in casting broke this. Dane has posted in this trackit ticket a work around.

http://trac.osgeo.org/postgis/ticket/1287

In addition to the legacy.sql, you'll need this legacy cast until we have a new osm2pgsql version ready for windows.

CREATE OPERATOR CLASS gist_geometry_ops
FOR TYPE geometry USING GIST AS
STORAGE box2df,
OPERATOR 1 << ,
OPERATOR 2 &< ,
OPERATOR 3 && ,
OPERATOR 4 &> ,
OPERATOR 5 >> ,
OPERATOR 6 ~= ,
OPERATOR 7 ~ ,
OPERATOR 8 @ ,
OPERATOR 9 &<| ,
OPERATOR 10 <<| ,
OPERATOR 11 |>> ,
OPERATOR 12 |&> ,

OPERATOR 13 <-> FOR ORDER BY pg_catalog.float_ops,
OPERATOR 14 <#> FOR ORDER BY pg_catalog.float_ops,
FUNCTION 8 geometry_gist_distance_2d (internal, geometry, int4),

FUNCTION 1 geometry_gist_consistent_2d (internal, geometry, int4),
FUNCTION 2 geometry_gist_union_2d (bytea, internal),
FUNCTION 3 geometry_gist_compress_2d (internal),
FUNCTION 4 geometry_gist_decompress_2d (internal),
FUNCTION 5 geometry_gist_penalty_2d (internal, internal, internal),
FUNCTION 6 geometry_gist_picksplit_2d (internal, internal),
FUNCTION 7 geometry_gist_same_2d (geom1 geometry, geom2 geometry, internal);
4/6/2012 12:33:20 PMChris PartridgeIf you're having trouble with osm2pgsql with postgis 2.0 - you must load the legacy.sql into your db from the postgis contrib directory. This will add the required functions for osm2pgsql to operate correctly.

Also don't forget to load 900913.sql into your database, this is in the osm2pgsql folder.
4/2/2012 10:02:23 AMhHi,

Thanks for a great tutorial, I got the following error while trying to load data into my postgresql/postgis (9.0/1.5) database. I would appreciate if you could provide some pointers on what might have happended?

Thx - H

Processing: Node(1403664k) Way(129971k) Relation(0k)COPY_END for planet_osm_roads failed: ERROR: could not load library "D:/Databases/PostgreSQL/9.0/lib/postgis-1.5.dll": Invalid access to memory location.

10/23/2011 10:11:12 PMEthyl Chiothis site is very informative for my thesis on Web GIS.i would like to explore more.Please allow me to. thank you very much.
9/7/2011 10:04:47 AMReginaIndeed you are right. The new index changes in later releases of PostGIS 2.0 seem to have broken osm2pgsql. I've put in tickets for this in
-- Index
http://trac.openstreetmap.org/ticket/3994
-- AsText
http://trac.openstreetmap.org/ticket/3995
8/21/2011 10:40:46 AMReginaKcs,

Not sure what you mean by fully-support. Are you getting errors when using PostGIS 2.0 and what subversion version of 2.0 are you running. 2.0 hasn't been released yet and is still undergoing many changes
8/10/2011 6:07:50 AMkcsIs it possible that osm2psql does not yet fully support PostGIS 2.0 ?
Locations of visitors to BostonGIS
Boston GIS      Copyright 2014      Paragon Corporation