Re: [Talk-GB] "OSMUK-in-a-box"
>do we cover British Overseas Territories such as Gibraltar and the Falkland Islands? Not as OSM UK CIC. We ended up settling on the British Islands: https://en.wikipedia.org/wiki/British_Islands Thank you, *Rob* ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Indeed. I will be plundering the excellent repository from Derry Hamilton for the Docker setup soon (unless someone beats me to it). I'd like to make the dataset all of the areas covered by OSMUK...do we cover British Overseas Territories such as Gibraltar and the Falkland Islands? On Thu, 6 Feb 2020, 15:32 Brian Prangle, wrote: > Looks like you've got yourself a show and tell session at the OSMUK AGM > Jez! > > On Thu, 6 Feb 2020 at 12:31, 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' results. >> >> I think that this would be useful for people on hackdays and the like and >> would be a good service for OSMUK to provide, so have just added a new >> github repository https://github.com/osm-uk/osmuk2pgsql >> >> Friendly-worded Issues are welcome, as are code contributions. I'd like >> to put it on a Docker environment so that it works quickly-and-easily on >> Windows, Linux, Mac, whatever. >> >> Comments? Thoughts? >> >> Regards, >> Jez >> ___ >> Talk-GB mailing list >> Talk-GB@openstreetmap.org >> https://lists.openstreetmap.org/listinfo/talk-gb >> > ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Looks like you've got yourself a show and tell session at the OSMUK AGM Jez! On Thu, 6 Feb 2020 at 12:31, 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' results. > > I think that this would be useful for people on hackdays and the like and > would be a good service for OSMUK to provide, so have just added a new > github repository https://github.com/osm-uk/osmuk2pgsql > > Friendly-worded Issues are welcome, as are code contributions. I'd like to > put it on a Docker environment so that it works quickly-and-easily on > Windows, Linux, Mac, whatever. > > Comments? Thoughts? > > Regards, > Jez > ___ > Talk-GB mailing list > Talk-GB@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk-gb > ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Hey Jez, Awesome! I’ve used Docker a few times, so am fairly confident in it, so happy to help unless someone else gets there first. Best, Adam On 6 Feb 2020, 12:31 +, 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' > results. > > I think that this would be useful for people on hackdays and the like and > would be a good service for OSMUK to provide, so have just added a new github > repository https://github.com/osm-uk/osmuk2pgsql > > Friendly-worded Issues are welcome, as are code contributions. I'd like to > put it on a Docker environment so that it works quickly-and-easily on > Windows, Linux, Mac, whatever. > > Comments? Thoughts? > > Regards, > Jez > ___ > Talk-GB mailing list > Talk-GB@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk-gb ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Op do 6 feb. 2020 om 13:06 schreef 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/ Also there seems to be GB: http://taginfo.geofabrik.de/europe/great-britain/ ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
When you have the setup guide drafted, I'll have a go at following the instructions to see if they're correct. I've never set up anything OSM locally so I can be your fresh pair of eyes on it. Thanks. Regards, *Paul* On Thu, 6 Feb 2020 at 13:17, Derry Hamilton wrote: > Hi Tony, > I did something similar a while back at > https://github.com/rasilon/osm_database so that might help you get > started? > > Cheers, > Derry > > On Thu, 6 Feb 2020 at 13:10, Tony OSM wrote: > >> Absolutely Fabulous! >> >> Not done Docker but I'll start learning how to get it on those >> environments. >> >> I'll try to support by QA and writing instructions as to how to get it >> live. >> >> Cheers >> >> TonyS999 >> On 06/02/2020 12: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' results. >> >> I think that this would be useful for people on hackdays and the like and >> would be a good service for OSMUK to provide, so have just added a new >> github repository https://github.com/osm-uk/osmuk2pgsql >> >> Friendly-worded Issues are welcome, as are code contributions. I'd like >> to put it on a Docker environment so that it works quickly-and-easily on >> Windows, Linux, Mac, whatever. >> >> Comments? Thoughts? >> >> Regards, >> Jez >> >> ___ >> Talk-GB mailing >> listTalk-GB@openstreetmap.orghttps://lists.openstreetmap.org/listinfo/talk-gb >> >> ___ >> Talk-GB mailing list >> Talk-GB@openstreetmap.org >> https://lists.openstreetmap.org/listinfo/talk-gb >> > ___ > Talk-GB mailing list > Talk-GB@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk-gb > ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Excellent. Not a new idea then. https://alexurquhart.com/post/set-up-postgis-with-docker/ looks like a reasonable summary of what Docker is and why. It being the whole of the UK, the download and create takes a while. I will be giving the choice of a smaller area, e.g. Greater Manchester. I'd like to include some sample SQL queries to help people get started. Help here would be useful. On Thu, 6 Feb 2020, 13:17 Derry Hamilton, wrote: > Hi Tony, > I did something similar a while back at > https://github.com/rasilon/osm_database so that might help you get > started? > > Cheers, > Derry > > On Thu, 6 Feb 2020 at 13:10, Tony OSM wrote: > >> Absolutely Fabulous! >> >> Not done Docker but I'll start learning how to get it on those >> environments. >> >> I'll try to support by QA and writing instructions as to how to get it >> live. >> >> Cheers >> >> TonyS999 >> On 06/02/2020 12: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' results. >> >> I think that this would be useful for people on hackdays and the like and >> would be a good service for OSMUK to provide, so have just added a new >> github repository https://github.com/osm-uk/osmuk2pgsql >> >> Friendly-worded Issues are welcome, as are code contributions. I'd like >> to put it on a Docker environment so that it works quickly-and-easily on >> Windows, Linux, Mac, whatever. >> >> Comments? Thoughts? >> >> Regards, >> Jez >> >> ___ >> Talk-GB mailing >> listTalk-GB@openstreetmap.orghttps://lists.openstreetmap.org/listinfo/talk-gb >> >> ___ >> Talk-GB mailing list >> Talk-GB@openstreetmap.org >> https://lists.openstreetmap.org/listinfo/talk-gb >> > ___ > Talk-GB mailing list > Talk-GB@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk-gb > ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Hi Tony, I did something similar a while back at https://github.com/rasilon/osm_database so that might help you get started? Cheers, Derry On Thu, 6 Feb 2020 at 13:10, Tony OSM wrote: > Absolutely Fabulous! > > Not done Docker but I'll start learning how to get it on those > environments. > > I'll try to support by QA and writing instructions as to how to get it > live. > > Cheers > > TonyS999 > On 06/02/2020 12: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' results. > > I think that this would be useful for people on hackdays and the like and > would be a good service for OSMUK to provide, so have just added a new > github repository https://github.com/osm-uk/osmuk2pgsql > > Friendly-worded Issues are welcome, as are code contributions. I'd like to > put it on a Docker environment so that it works quickly-and-easily on > Windows, Linux, Mac, whatever. > > Comments? Thoughts? > > Regards, > Jez > > ___ > Talk-GB mailing > listTalk-GB@openstreetmap.orghttps://lists.openstreetmap.org/listinfo/talk-gb > > ___ > Talk-GB mailing list > Talk-GB@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk-gb > ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
Absolutely Fabulous! Not done Docker but I'll start learning how to get it on those environments. I'll try to support by QA and writing instructions as to how to get it live. Cheers TonyS999 On 06/02/2020 12: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' results. I think that this would be useful for people on hackdays and the like and would be a good service for OSMUK to provide, so have just added a new github repository https://github.com/osm-uk/osmuk2pgsql Friendly-worded Issues are welcome, as are code contributions. I'd like to put it on a Docker environment so that it works quickly-and-easily on Windows, Linux, Mac, whatever. Comments? Thoughts? Regards, Jez ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
Re: [Talk-GB] "OSMUK-in-a-box"
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
[Talk-GB] "OSMUK-in-a-box"
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' results. I think that this would be useful for people on hackdays and the like and would be a good service for OSMUK to provide, so have just added a new github repository https://github.com/osm-uk/osmuk2pgsql Friendly-worded Issues are welcome, as are code contributions. I'd like to put it on a Docker environment so that it works quickly-and-easily on Windows, Linux, Mac, whatever. Comments? Thoughts? Regards, Jez ___ Talk-GB mailing list Talk-GB@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk-gb
