Download all coffe-shop locations in Italy from OSM. Stuck with Qgis, R and geofabrik

Question

I wanted to download all café locations from OSM in Italy. I tried various ways and one of them was in qgis. I used the awesome quickosm-plugin and did the following:

But it always fails with a timout error asking me to try it later or do a smaller query.

I also tried to download the entire pbf-file from geofabrik here:

https://download.geofabrik.de/europe/italy.html

load this into qgis and then somehow select the right rows. This however also did not work due to its size.

I then tried to use the osmdata and the osmextract packages in R. For both I don't get any results. With the osmdata-package I ran the following:

cafes = opq("Italy") %>% 
  add_osm_feature(key = "amenity", value = "cafe") %>% 
  osmdata_sf()

Which results in this

> cafes
Object of class 'osmdata' with:
                 $bbox : 32.1634093,-96.9079386,32.2066985,-96.8616477
        $overpass_call : The call submitted to the overpass API
                 $meta : metadata including timestamp and version numbers
           $osm_points : 'sf' Simple Features Collection with 0 points
            $osm_lines : NULL
         $osm_polygons : 'sf' Simple Features Collection with 0 polygons
       $osm_multilines : NULL
    $osm_multipolygons : NULL

And for the osmextract I used the following:

cafes = oe_get(
  "Italy",
  quiet = FALSE,
  query = "Select * from points where amenity  = 'cafe'"
)

Besides some potential errors in my understanding of the query (and in general of osm keys and features) I have no Idea on how to get to the cafe-data.

One last idea would be to download the pbf again and put it into postgis. This however scares me a little bit as the setup of my postgis is not really working.

Answer 1

One option is to convert the PBF to Spatialite using OGR2OGR:

ogr2ogr -f "SQLite" -dsco SPATIALITE=YES italy.db italy.osm.pbf

Then you can connect to the Spatialite database from within QGIS.

1. Layer->Add Layer->Add Spatialite Layer

2. Add a New connection (to italy.db)

3. Select the Points layer

4. Set Filter as per the following:

   "other_tags" LIKE '%"amenity"=>"cafe"%'

This should get around the need to load the entire file into QGIS.

I also tried using QuickOSM. I could download cafes for Rome, but Italy appeared to be too much data.