[Talk-GB] "OSMUK-in-a-box"

[Frederik Ramm]

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 frederik at remote.org  ##  N49°00'09" E008°23'33"