osm2pgsql diff application with filtered OSM data

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

osm2pgsql diff application with filtered OSM data

Nick Whitelegg-2


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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Nick Whitelegg-2


... 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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Darafei "Komяpa" Praliaskouski
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 <[hidden email]>:


... 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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa

_______________________________________________
talk mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Frederik Ramm
In reply to this post by Nick Whitelegg-2
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 [hidden email]  ##  N49°00'09" E008°23'33"

_______________________________________________
talk mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Nick Whitelegg-2
In reply to this post by Darafei "Komяpa" Praliaskouski

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 <[hidden email]>
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 <[hidden email]>:


... 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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa

_______________________________________________
talk mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Andy Townsend
In reply to this post by Darafei "Komяpa" Praliaskouski
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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Paul Norman
In reply to this post by Nick Whitelegg-2
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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Nick Whitelegg-2


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 <[hidden email]>
Sent: 08 November 2018 20:10:14
To: [hidden email]
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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Nick Whitelegg-2

... append mode!






From: Nick Whitelegg
Sent: 11 November 2018 15:53:18
To: [hidden email]
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 <[hidden email]>
Sent: 08 November 2018 20:10:14
To: [hidden email]
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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Paul Norman
In reply to this post by Nick Whitelegg-2
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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk
Reply | Threaded
Open this post in threaded view
|

Re: osm2pgsql diff application with filtered OSM data

Nick Whitelegg-2

>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 <[hidden email]>
Sent: 13 November 2018 10:12:58
To: [hidden email]
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
[hidden email]
https://lists.openstreetmap.org/listinfo/talk