Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
>This will leave your site down between the delete and import of new data. >It's also going to be fragile, because using append mode with a file that >isn't a diff isn't supported, and if the area has a lot of footpaths, it'll be >slower, since >append has to do more work. >If you match the SQL delete and osmosis filtering carefully you shouldn't get >too many errors, but you've probably got some to do with updates and >changing >object types. >As long as you're aware of these problems and it works for your needs, I'd say >to go for it. Thanks. I have actually used append frequently over several years and never had a problem with it; main issue is that memory is expensive on servers so I have to do take various less-than-ideal steps to work round low memory. The intention is to bring the site down over the (say) 2-3 hours the update is taking place; this is what I used to do when I last performed weekly updates. This would be scheduled for (probably) Wednesday 0100-0400 UK time, a time that the server is likely to see little traffic as it's UK and Ireland oriented; at such time most of Europe will be asleep. Granted there might be a few out-of-Europe visitors at that time; but as my site is quite niche with limited visitors, I consider this acceptable downtime. Nick From: Paul Norman Sent: 13 November 2018 10:12:58 To: talk@openstreetmap.org Subject: Re: [OSM-talk] osm2pgsql diff application with filtered OSM data On 2018-11-11 7:53 AM, Nick Whitelegg wrote: After thinking about this, I realised that I don't really want to update _all_ the data that often. The only thing I need to update on a weekly basis is the footpaths (I'm not so bothered if say the roads, or the pubs are a year out of date - as long as newly mapped footpaths appear quickly). So what I'm now doing is just doing an osmosis extract of paths weekly, deleting all data in the DB which I class as a 'path' and repopulating in amend mode. This will leave your site down between the delete and import of new data. It's also going to be fragile, because using append mode with a file that isn't a diff isn't supported, and if the area has a lot of footpaths, it'll be slower, since append has to do more work. If you match the SQL delete and osmosis filtering carefully you shouldn't get too many errors, but you've probably got some to do with updates and changing object types. As long as you're aware of these problems and it works for your needs, I'd say to go for it. ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
On 2018-11-11 7:53 AM, Nick Whitelegg wrote: After thinking about this, I realised that I don't really want to update _all_ the data that often. The only thing I need to update on a weekly basis is the footpaths (I'm not so bothered if say the roads, or the pubs are a year out of date - as long as newly mapped footpaths appear quickly). So what I'm now doing is just doing an osmosis extract of paths weekly, deleting all data in the DB which I class as a 'path' and repopulating in amend mode. This will leave your site down between the delete and import of new data. It's also going to be fragile, because using append mode with a file that isn't a diff isn't supported, and if the area has a lot of footpaths, it'll be slower, since append has to do more work. If you match the SQL delete and osmosis filtering carefully you shouldn't get too many errors, but you've probably got some to do with updates and changing object types. As long as you're aware of these problems and it works for your needs, I'd say to go for it. ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
Thanks for all the replies. After thinking about this, I realised that I don't really want to update _all_ the data that often. The only thing I need to update on a weekly basis is the footpaths (I'm not so bothered if say the roads, or the pubs are a year out of date - as long as newly mapped footpaths appear quickly). So what I'm now doing is just doing an osmosis extract of paths weekly, deleting all data in the DB which I class as a 'path' and repopulating in amend mode. Thanks, Nick From: Paul Norman Sent: 08 November 2018 20:10:14 To: talk@openstreetmap.org Subject: Re: [OSM-talk] osm2pgsql diff application with filtered OSM data On 2018-11-08 6:34 AM, Nick Whitelegg wrote: At the moment I download full planet extracts about every 6 months. However, due to the limitations of my server, I filter out (with osmosis) a lot of stuff I don't need so that I am basically left with roads, footpaths, natural features, water features and selected POIs. I'd like to move towards a system which applies diffs from geofabrik instead, and applies them regularly (daily or weekly) with osm2pgsql. My question is this; given that not everything in the diff will be in my database (as I filter out what I don't need during the import process), will osm2pgsql apply the diff successfully or will it complain that not all features in the diff are in my database? I can think of four ways to do this, all which have a different balance of correctness, performance, and ease of use. There are two "right" ways to do this. The first one is to re-import every week. Because imports without slim tables (either --slim --drop or no --slim) are faster, this is a good option and needs less space than a database able to consume diffs. The second right way involves keeping two files, one with the current full data, and one with the filtered data. Call these "planet.pbf" and "planet-filtered.pbf". Then when updating create "planet-new.pbf", filter it to get "planet-filtered-new.pbf", create a diff for the differences between "planet-filtered-new.pbf" and "planet-filtered.pbf", and apply that diff to the database. Then replace the old files with the new ones. This will keep the database correct. A "wrong" way to do it is to import the filtered data, apply updates directly, and periodically delete data from the DB. The problem with this is that if someone adds one of the selected POI tags to a building that you have filtered out, osm2pgsql won't have the node data to create a geometry. This might be acceptable, depending on use case. A less wrong way would be to modify your filtering so no nodes are filtered out. There are still potential errors with relations, but these are less common. If you're doing the planet or a large extract and using flat nodes there's no storage penalty for having all the nodes. ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
... append mode! From: Nick Whitelegg Sent: 11 November 2018 15:53:18 To: talk@openstreetmap.org Subject: Re: [OSM-talk] osm2pgsql diff application with filtered OSM data Thanks for all the replies. After thinking about this, I realised that I don't really want to update _all_ the data that often. The only thing I need to update on a weekly basis is the footpaths (I'm not so bothered if say the roads, or the pubs are a year out of date - as long as newly mapped footpaths appear quickly). So what I'm now doing is just doing an osmosis extract of paths weekly, deleting all data in the DB which I class as a 'path' and repopulating in amend mode. Thanks, Nick From: Paul Norman Sent: 08 November 2018 20:10:14 To: talk@openstreetmap.org Subject: Re: [OSM-talk] osm2pgsql diff application with filtered OSM data On 2018-11-08 6:34 AM, Nick Whitelegg wrote: At the moment I download full planet extracts about every 6 months. However, due to the limitations of my server, I filter out (with osmosis) a lot of stuff I don't need so that I am basically left with roads, footpaths, natural features, water features and selected POIs. I'd like to move towards a system which applies diffs from geofabrik instead, and applies them regularly (daily or weekly) with osm2pgsql. My question is this; given that not everything in the diff will be in my database (as I filter out what I don't need during the import process), will osm2pgsql apply the diff successfully or will it complain that not all features in the diff are in my database? I can think of four ways to do this, all which have a different balance of correctness, performance, and ease of use. There are two "right" ways to do this. The first one is to re-import every week. Because imports without slim tables (either --slim --drop or no --slim) are faster, this is a good option and needs less space than a database able to consume diffs. The second right way involves keeping two files, one with the current full data, and one with the filtered data. Call these "planet.pbf" and "planet-filtered.pbf". Then when updating create "planet-new.pbf", filter it to get "planet-filtered-new.pbf", create a diff for the differences between "planet-filtered-new.pbf" and "planet-filtered.pbf", and apply that diff to the database. Then replace the old files with the new ones. This will keep the database correct. A "wrong" way to do it is to import the filtered data, apply updates directly, and periodically delete data from the DB. The problem with this is that if someone adds one of the selected POI tags to a building that you have filtered out, osm2pgsql won't have the node data to create a geometry. This might be acceptable, depending on use case. A less wrong way would be to modify your filtering so no nodes are filtered out. There are still potential errors with relations, but these are less common. If you're doing the planet or a large extract and using flat nodes there's no storage penalty for having all the nodes. ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
On 2018-11-08 6:34 AM, Nick Whitelegg wrote: At the moment I download full planet extracts about every 6 months. However, due to the limitations of my server, I filter out (with osmosis) a lot of stuff I don't need so that I am basically left with roads, footpaths, natural features, water features and selected POIs. I'd like to move towards a system which applies diffs from geofabrik instead, and applies them regularly (daily or weekly) with osm2pgsql. My question is this; given that not everything in the diff will be in my database (as I filter out what I don't need during the import process), will osm2pgsql apply the diff successfully or will it complain that not all features in the diff are in my database? I can think of four ways to do this, all which have a different balance of correctness, performance, and ease of use. There are two "right" ways to do this. The first one is to re-import every week. Because imports without slim tables (either --slim --drop or no --slim) are faster, this is a good option and needs less space than a database able to consume diffs. The second right way involves keeping two files, one with the current full data, and one with the filtered data. Call these "planet.pbf" and "planet-filtered.pbf". Then when updating create "planet-new.pbf", filter it to get "planet-filtered-new.pbf", create a diff for the differences between "planet-filtered-new.pbf" and "planet-filtered.pbf", and apply that diff to the database. Then replace the old files with the new ones. This will keep the database correct. A "wrong" way to do it is to import the filtered data, apply updates directly, and periodically delete data from the DB. The problem with this is that if someone adds one of the selected POI tags to a building that you have filtered out, osm2pgsql won't have the node data to create a geometry. This might be acceptable, depending on use case. A less wrong way would be to modify your filtering so no nodes are filtered out. There are still potential errors with relations, but these are less common. If you're doing the planet or a large extract and using flat nodes there's no storage penalty for having all the nodes. ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
On 08/11/2018 16:16, Darafei "Komяpa" Praliaskouski wrote: Usually people also clip minutely osc, as per day database grows by a small country otherwise. There's a worked example of that (in a slightly different context) at https://wiki.openstreetmap.org/wiki/User:SomeoneElse/Ubuntu_1804_tileserver_load#Updating_your_database_as_people_edit_OpenStreetMap . That uses https://github.com/zverik/regional - I'm guessing you'll be able to "borrow" everything you need from there. Best Regards, Andy ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
Hello Darafei and Frederik, OK - thanks for that. What I will do therefore, to avoid unwanted data, is to generate a filtered planet extract and import that into the DB, and then generate another filtered extract and find the diff between the two. Advantage of that is that I'll only need to do the first filtering run on the server, and subsequent runs on my local machine (assuming the resulting diff is small enough to upload from my local machine to the server). Thanks, Nick From: Darafei "Komяpa" Praliaskouski Sent: 08 November 2018 16:16:48 To: Nick Whitelegg Cc: osm-talk Subject: Re: [OSM-talk] osm2pgsql diff application with filtered OSM data Hi Nick, osm2pgsql is tolerant to features absent in database. You can in theory even start with empty set of tables and just insert new diff data. Usually people also clip minutely osc, as per day database grows by a small country otherwise. чт, 8 нояб. 2018 г. в 17:37, Nick Whitelegg mailto:nick.whitel...@solent.ac.uk>>: ... sorry, when I say "full planet extracts" I mean only England, Wales, Scotland, Ireland (all) and Greece - not the entire planet. Thanks, Nick From: Nick Whitelegg Sent: 08 November 2018 14:34:17 To: osm-talk Subject: osm2pgsql diff application with filtered OSM data Hi, Looking towards overhauling the import system I use for my Freemap site (free-map.org.uk<http://free-map.org.uk>) which is itself going to go through an overhaul in the near future by moving to Tangram and hopefully applying hill shading. At the moment I download full planet extracts about every 6 months. However, due to the limitations of my server, I filter out (with osmosis) a lot of stuff I don't need so that I am basically left with roads, footpaths, natural features, water features and selected POIs. I'd like to move towards a system which applies diffs from geofabrik instead, and applies them regularly (daily or weekly) with osm2pgsql. My question is this; given that not everything in the diff will be in my database (as I filter out what I don't need during the import process), will osm2pgsql apply the diff successfully or will it complain that not all features in the diff are in my database? Thanks, Nick ___ talk mailing list talk@openstreetmap.org<mailto:talk@openstreetmap.org> https://lists.openstreetmap.org/listinfo/talk -- Darafei Praliaskouski Support me: http://patreon.com/komzpa ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
Hi, On 08.11.2018 15:34, Nick Whitelegg wrote: > My question is this; given that not everything in the diff will be in my > database (as I filter out what I don't need during the import process), > will osm2pgsql apply the diff successfully or will it complain that not > all features in the diff are in my database? It will apply the diff as far as possible, but it will also add a lot of unwanted/unnecessary stuff from the diff to the database which you would then have to filter out again. There's also a danger that you will miss some things. For example, consider a rural garage (shop=car_repair) mapped as a way with four nodes; you filter that out because you're not interested. Half a year later the shop closes, and a tourism=hostel opens instead. Assuming you are interested in this kind of POI, the diff that you process contains the way with the new tags, but not the nodes since these have not changed. The hostel will not appear in your osm2pgsql database because the geometry cannot be built due to lack of nodes. One way to avoid this downloading the full files, filtering them, and then generating a diff by comparing the filtered file to the last filtered file, and importing *that* diff. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09" E008°23'33" ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
Hi Nick, osm2pgsql is tolerant to features absent in database. You can in theory even start with empty set of tables and just insert new diff data. Usually people also clip minutely osc, as per day database grows by a small country otherwise. чт, 8 нояб. 2018 г. в 17:37, Nick Whitelegg : > > ... sorry, when I say "full planet extracts" I mean only England, Wales, > Scotland, Ireland (all) and Greece - not the entire planet. > > > Thanks, > > Nick > > -- > *From:* Nick Whitelegg > *Sent:* 08 November 2018 14:34:17 > *To:* osm-talk > *Subject:* osm2pgsql diff application with filtered OSM data > > > > Hi, > > > Looking towards overhauling the import system I use for my Freemap site ( > free-map.org.uk) which is itself going to go through an overhaul in the > near future by moving to Tangram and hopefully applying hill shading. > > > At the moment I download full planet extracts about every 6 months. > However, due to the limitations of my server, I filter out (with osmosis) a > lot of stuff I don't need so that I am basically left with roads, > footpaths, natural features, water features and selected POIs. > > > I'd like to move towards a system which applies diffs from geofabrik > instead, and applies them regularly (daily or weekly) with osm2pgsql. > > > My question is this; given that not everything in the diff will be in my > database (as I filter out what I don't need during the import process), > will osm2pgsql apply the diff successfully or will it complain that not all > features in the diff are in my database? > > > Thanks, > > Nick > > ___ > talk mailing list > talk@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk > -- Darafei Praliaskouski Support me: http://patreon.com/komzpa ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] osm2pgsql diff application with filtered OSM data
... sorry, when I say "full planet extracts" I mean only England, Wales, Scotland, Ireland (all) and Greece - not the entire planet. Thanks, Nick From: Nick Whitelegg Sent: 08 November 2018 14:34:17 To: osm-talk Subject: osm2pgsql diff application with filtered OSM data Hi, Looking towards overhauling the import system I use for my Freemap site (free-map.org.uk) which is itself going to go through an overhaul in the near future by moving to Tangram and hopefully applying hill shading. At the moment I download full planet extracts about every 6 months. However, due to the limitations of my server, I filter out (with osmosis) a lot of stuff I don't need so that I am basically left with roads, footpaths, natural features, water features and selected POIs. I'd like to move towards a system which applies diffs from geofabrik instead, and applies them regularly (daily or weekly) with osm2pgsql. My question is this; given that not everything in the diff will be in my database (as I filter out what I don't need during the import process), will osm2pgsql apply the diff successfully or will it complain that not all features in the diff are in my database? Thanks, Nick ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
[OSM-talk] osm2pgsql diff application with filtered OSM data
Hi, Looking towards overhauling the import system I use for my Freemap site (free-map.org.uk) which is itself going to go through an overhaul in the near future by moving to Tangram and hopefully applying hill shading. At the moment I download full planet extracts about every 6 months. However, due to the limitations of my server, I filter out (with osmosis) a lot of stuff I don't need so that I am basically left with roads, footpaths, natural features, water features and selected POIs. I'd like to move towards a system which applies diffs from geofabrik instead, and applies them regularly (daily or weekly) with osm2pgsql. My question is this; given that not everything in the diff will be in my database (as I filter out what I don't need during the import process), will osm2pgsql apply the diff successfully or will it complain that not all features in the diff are in my database? Thanks, Nick ___ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
