Hi, On 06.02.20 13:29, Jez Nicholson wrote: > I come from a database background, and when a question isn't easily > answered with Taginfo or Overpass Turbo I jump to my trusty local > postgres database of UK data. I have a script that downloads the British > Isles from Geofabrik, loads it with osm2pgsql, adds some useful indexes, > and then removes Eire. Thereafter I can run SQL queries across the whole > database to get 'UK-wide' result I would recommend using --hstore-all instead of just --hstore because this gives you *all* tags in the "tags" column and therefore makes some analyses easier (cf. some of the examples below).
It is certainly a good approach to answer complicated questions, and also an excellent training ground for people to hone their SQL skills. Some scribbles from a recent training: "what are the most frequently used key on a polygon": select count(*) as c, (each(tags)).key as k from planet_osm_polygon group by k order by c desc limit 10; or "what are the most frequently used key-value combos": select count(*) as c, each(tags) as k from planet_osm_polygon group by k order by c desc; or "which are the longest hiking routes": select osm_id, st_length(way::geography) as l, tags from planet_osm_line where tags->'route' = 'hiking' order by l desc; Having said that, for the easier questions there's also the per-region taginfo on Geofabrik (it's a bit beta still but good enough) - it doesn't actually feature the UK as an area but you can do England/Scotland/Wales separately: http://taginfo.geofabrik.de/europe/great-britain/england/ Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09" E008°23'33" _______________________________________________ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb