Query regarding OSM file structure

If we click on the blue Export button, we'll receive an error in our browser. This error happens when we are trying to download an area that contains too many nodes (> 50000). By running CURL in a terminal to reproduce the error, we'll see the following message appear:

curl -I "http://www.openstreetmap.org/api/0.6/map?bbox=-122.3819%2C47.5763%2C-122.2677%2C47.6267"  
...
Error: You requested too many nodes (limit is 50000). Either request a smaller area, or use planet.osm  
...

Once osm2pgsql has been installed, we need to create a database. You can create a database via the terminal after logging into your Compose PostgreSQL deployment, or from the Compose Browser. We'll use the terminal which requires only the command line string provided in your deployment's Connection info panel. Once we're logged in, create a database called osm by typing:

CREATE DATABASE osm;

Ater that, connect to the database using \c osm;, and then install the PostGIS and HStore extensions using:

CREATE EXTENSION postgis;  
CREATE EXTENSION hstore;

Once the database and extensions have been set up, we can use osm2pgsql in our terminal like:

osm2pgsql -U admin -W -d osm -H aws-us-west-1-portal.3.dblayer.com -P 17124 --hstore --hstore-add-index map.osm

Now, let's look at some of the tables that have been created. When listing the tables using the \d command, we should see a list of tables like:

List of relations
 Schema |        Name        | Type  | Owner 
--------+--------------------+-------+-------
 public | geography_columns  | view  | admin
 public | geometry_columns   | view  | admin
 public | planet_osm_line    | table | admin
 public | planet_osm_point   | table | admin
 public | planet_osm_polygon | table | admin
 public | planet_osm_roads   | table | admin
 public | raster_columns     | view  | admin
 public | raster_overviews   | view  | admin
 public | spatial_ref_sys    | table | admin

Now we’ll take a look how to load data from OSM. The Overpass API uses a custom query language to define the queries. It takes some time getting used to, but luckily there is Overpass Turbo by Martin Raifer which comes in handy to interactively evaluate our queries directly in the browser. Let’s say you want to query nodes for cafes, then your query looks like this

node["amenity"="cafe"]({{bbox}}); out;

Another filter is the bounding box filter where {{bbox}} corresponds to the bounding box in which we want to search and works only in Overpass Turbo. Otherwise you can specify a bounding box by (south, west, north, east) in latitude and longitude which can look like

node["amenity"="pub"]  (53.2987342,-6.3870259,53.4105416,-6.1148829); out;

which you can try in Overpass Turbo. As we saw before in the OSM data model, there are also ways and relations which might also hold the same attribute. We can get those as well by using a union block statement, which collects all outputs from the sequence of statements inside a pair of parentheses as in

( node["amenity"="cafe"]({{bbox}});  way["amenity"="cafe"]({{bbox}});  relation["amenity"="cafe"]({{bbox}}););out;

Another way to filter queries is by area which can be specified like area["ISO3166-1"="GB"][admin_level=2]; which gives us the area for Great Britain. We can use this now as a filter for the query by adding (area) to our statement as in

area["ISO3166-1"="GB"][admin_level=2];node["place"="city"](area);out;

This query returns all cities in Great Britain. It is also possible to use a relation or a way as an area. In this case area ids need to be derived from an existing OSM way by adding 2400000000 to its OSM id or in case of a relation by adding 3600000000. Note that not all ways/relations have an area counterpart (i.e. those that are tagged with area=no, and most multipolygons and that don’t have a defined name=* will not be part of areas). If we apply the relation of Great Britain to the previous example we’ll then get

area(3600062149);node["place"="city"](area);out;