Database format created by osm2pgsql

OpenStreetmap wiki page

The usual database generated by the osm2pgsql default style / configuration file is described here:

OpenStreetmap Wiki page on the default database schema

From the README file

The following is mostly taken from a README file that comes with (or used to come with) osm2pgsql.

Database Access Examples

If you wish to access the data from the database then the queries below should give you some hints. Note that these examples all use the 'latlong' projection which is not the default.

psql gis
gis=> \d

gis=> \d planet_osm_line

Each of the tables contains a subset of the planet.osm file representing a particular geometry type that is also explained here:

• Point contains nodes which have interesting tags, e.g. place=city, name=London
• Line contains ways with interesting tags, e.g. highway=motorway, ref=M25
• Polygon contains ways which form an enclosed area, e.g. landuse=reservoir

The DB columns are used as follows:

• osm_id = the planet.osm ID of the node(point) or way(line,polygon)
• name, place, landuse, ... = the value of the given key, if present on the node/way. If the tag is not present, the value is NULL. Only a subset of all possible tags are stored in the DB. Only ones rendered in the osm.xml are actually interesting to mapnik.
• way = PostGIS geometry describing the physical layout of the object.

Querying specific data requires knowlege of SQL and the OSM key/value system, e.g.

gis=> select osm_id,ST_AsText(way),name from planet_osm_point where amenity='cinema' limit 5;

(5 rows)

Mapnik renders the data in each table by applying the rules in the osm.xml file.

How could I get e.g. all highways in a given bounding box?

The 'way' column contains the geo info and is the one which you need to use in your WHERE clause. e.g.

gis=> select osm_id,highway,name from planet_osm_line where highway is not null and way && ST_GeomFromText('POLYGON((-1 52, 0.1 52, 0.1 52.1, 0 52.1, 0 52))',4326);

If you use the Pseudo-Mercator projection in your database (which is the osm2pgsql default) but still want to give the bounding box in Latitude and Longitude coordinates, you have to transform the bounding box:

gis=> select osm_id,highway,name from planet_osm_line where highway is not null and way && ST_Transform(ST_GeomFromText('POLYGON((-1 52, 0.1 52, 0.1 52.1, 0 52.1, 0 52))', 4326), 3857);

If osm2pgsql was run with a --prefix option, the names of the database columns may also be different.

See the Postgis docs for descriptions of all functions and operators, e.g. http://postgis.net/docs/manual-2.0/reference.html for version 2.0.

More database query examples

The examples above from the README were not quite consistent with the database generated by a default run of osm2pgsql, and make other special assumptions. First off, when running psql, you usually have to specify the database user name (unless it is the same as your system user name). For a standard database setup as explained here, the command line is:

psql -U mapper gis

Otherwise you will receive the error message "FATAL: role "..." does not exist", where ... is the wrong database user name that you have given or psql has assumed.

Now we can run a few more practically relevant queries on the database created with osm2pgsql defaults. We will see that this database setup has its limits, see below. Let's look for the closest cash dispensers to some location:

select name, ST_AsText(ST_Transform(way,4326)) from planet_osm_point
    where amenity = 'atm'
    order by way <-> ST_Transform(ST_SetSRID(ST_Point(1.234,5.678), 4326), 3857)
    limit 5;

This is an example of an SQL select statement which in our case has the following parts:

select <what to output> from <table> where <filter condition> order by <sorting criterion> limit <maximum result count>;

So in the select statement above we filter out OSM nodes (which osm2pgsql has entered into the table planet_osm_point) that have the OSM tag amenity set to atm. We want at most 5 results and sort them by proximity to the location 1.234,5.678 (substitute somewhere within the region your database covers when trying this). For the first 5 results, we print the name and the location (geocoordinates).

The proximity criterion and the functions acting on geocoordinates are features of PostGIS, the geospatial extention of PostgreSQL. The operator <-> computes the distance between two objects (using Euclidean geometry, which is OK at small distances). The function ST_Transform performs a coordinate transformation, and the function ST_SetSRID assigns a coordinate system to a point to make it a meaningful geographical location. The coordinate systems are denoted by their EPSG index, so 4326 means longitude/latitude, and 3857 means Pseudo-Mercator. The transformations are necessary because the database (with osm2pgsql's -m option or by default) is in Pseudo-Mercator coordinates but we want our location and result output to be in longitude/latitude.

We can also output the distance in an additional column, and use a further SQL feature to save some typing:

select name, ST_AsText(ST_Transform(way,4326)), way <-> ST_Transform(ST_SetSRID(ST_Point(1.234,5.678), 4326), 3857) as dist
    from planet_osm_point where amenity = 'atm' order by dist limit 5;

The as dist after the distance expression makes dist an alias of that expression, both in the output table header and in the remainder of the statement. This is very helpful to avoid duplicating complex expressions.

Unfortunately, what we cannot do with a database created with osm2pgsql defaults is output the address of the ATMs we have found. The standard import settings of osm2pgsql are intended for map rendering, and the only component of the address that is rendered on a house is the house number (or possibly house name). The street is of course rendered next to it, but its name comes from its own entry in the database, not from a house (or ATM) on that street. Therefore osm2pgsql does not enter the full addresses of objects into the database. To change that, you would have to modify its "style" (configuration) file under /usr/share/osm2pgsql/default.style or create a different configuration and use the -S option.

For another example, we search for an extended object such as a campsite. They are (usually) represented by an area, i.e. a polygon in the table planet_osm_polygon. The query works quite similarly to searching for points, only the output is a bit more involved because we do not want to print all vertices of the result polygons &emdash; we print the point closest to our reference location instead.

select name, ST_AsText(ST_ClosestPoint(ST_Transform(way,4326), ST_SetSRID(ST_Point(1.234,5.678), 4326)))
    from planet_osm_polygon
    where tourism = 'camp_site'
    order by way <-> ST_Transform(ST_SetSRID(ST_Point(1.234,5.678), 4326), 3857)
    limit 5;

Licensed under the Creative Commons Attribution-Share Alike 3.0 Germany License