Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
Stéphane, Yes, you’re right in your analysis of how the co-ordinates are stored in the slim mode tables. This’ll help confirm it for you: Executing this query against your Postgres database: select lon, lat, ST_X(ST_Transform(ST_SetSRID(ST_Point(lon / 100, lat / 100),900913),4326)), ST_Y(ST_Transform(ST_SetSRID(ST_Point(lon / 100, lat / 100),900913),4326)) from planet_osm_nodes where id = 19; Yields: Lat;lon;st_x;st_y 679034492;-2304090;-0.206971841461138;51.9458701799331 Request the same node from the API: http://api.openstreetmap.org/api/0.6/node/19 http://api.openstreetmap.org/api/0.6/node/19 Yields: … node id=19 … lat=51.9458753 lon=-0.20698 … … Gregory From: Stéphane Henriod [mailto:s...@henriod.info] Sent: 06 September 2012 06:56 To: Ramas Cc: dev@openstreetmap.org Subject: Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc) Hi again everybody! I am trying to implement the post-processing solution: removing all records in planet_osm_ways that are completely outside my bounding box. For this I first need to find out all the nodes outside the bounding box... The problem is that the x and y coordinates of planet_osm_nodes seem to be plenty wrong. My first record, for instance, has lat = 450768421 lon = 796483777 I assume that these are in srid 900913 but, if they are, they are way outside the limits of the 900913 srs. Actually, I just realised that they are systematically wrong by a factor 100. The correct coordinates should be something like: lat = 4507684 lon = 7964837 Have I done something wrong? or is there a known (or unknown?) bug with osm2pgsql? The geometries of the useful tables (line, point, polygon, roads) seem correct, but the x/y columns of the temporary tableshave this problem... Cheers Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info http://www.henriod.info/ On Wed, Sep 5, 2012 at 5:25 PM, Ramas ies...@ramuno.lt wrote: You can check tool osmupdate which has option to clip data outside boundaries. http://wiki.openstreetmap.org/wiki/Osmupdate#Applying_Geographical_Borders On 5 September 2012 16:06, Stéphane Henriod s...@henriod.info wrote: thanks for your anwers! But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Except if anyone else has a magical solution... ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
hi, but is it a bug? or am I missing the good reason why there is this factor 100? I searched the source code to find the reason why there is a x100 on coordinate, but couldn't find where it's done. But my guess is that for performance reasons, the field to store x/y was chosen to be an integer instead of a float. And because rounding a spherical mercator to the unit would loose sub meter precision, it was stored as multiplied by 100 for ~cm accuracy -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
That would make sense indeed. But then if this is confirmed to be a wanted behavior, then we should document it explicitely somewhere Thanks to all for your help and advice! Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Thu, Sep 6, 2012 at 11:33 AM, sly (sylvain letuffe) li...@letuffe.orgwrote: hi, but is it a bug? or am I missing the good reason why there is this factor 100? I searched the source code to find the reason why there is a x100 on coordinate, but couldn't find where it's done. But my guess is that for performance reasons, the field to store x/y was chosen to be an integer instead of a float. And because rounding a spherical mercator to the unit would loose sub meter precision, it was stored as multiplied by 100 for ~cm accuracy -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
On 09/06/2012 11:39 AM, Stéphane Henriod wrote: But then if this is confirmed to be a wanted behavior, then we should document it explicitely somewhere This is wanted behaviour, the relevant defines/macros and an explanation are in node-ram-cache.h starting at line 16. You could probably remove the #define FIXED_POINT line to make it use floatingpoint, but as FIXED_POINT has been the standard for quite some time I wouldn't expect that everything works properly without it. Also, what would be the point, making it slower on purpose? I do not think this needs to be documented - these are essentially internal tables, and using fixed point arithmetic to speed things up and/or ease storage requirements is pretty much one of the first lessons in programming for beginners. What should probably be changed though is the define #define DOUBLE_TO_FIX(x) ((int)((x) * scale)) This will cause an unexpected loss of precission due to conversion errors from the integer cast, as I discovered when I wrote a little tool to convert the planet_osm_nodes table to the new flatnodes-file: DOUBLE_TO_FIX(FIX_TO_DOUBLE(x)) does _NOT_ always return x. This macro should probably use a proper round() instead of the integer cast. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
As usual i forgot to send it to the list. -- Forwarded message -- From: Jais Pedersen j...@pedersens.net Date: Wed, Sep 5, 2012 at 3:15 PM Subject: Re: [OSM-dev] Osmosis - Cutting out a bounding box from a diff file (.osc) To: Stéphane Henriod s...@henriod.info I setup a Vietnam tile server just for fun and I made a script that worked like this: 1. Make a copy of the Vietnam extract. 2. Apply the update to the copy 3. Clip the copy using the Vietnam clip bounds file (I think i made my own from the admin boundary, but it might have been the one from Geofabrik) 4. Make a diff between the original and the copy 5. Apply the diff to the PostGIS database 6 Finally make the copy the new original for next update Unfortunately the server died (it was a retired PC), so I no longer have the script and it never had more than a single digit number of users, so I don't know how it will perform under real load, but for a small (as in the amount of data) extract it seemed to work quite well - I never really checked how it worked with object that grew or was moved across the border, but i suspect that would not be handled so well. I'm planning to setup a new one after the license change, so I would love to hear if anybody has a better way to do it. /Jais On Wed, Sep 5, 2012 at 2:19 PM, Stéphane Henriod s...@henriod.info wrote: PS: it looks like only the tables - planet_osm_rels - planet_osm_ways Get data from outside my bounding box and thus grow extremely quickly. Don't really know how to interpret this behavior! -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 8:56 AM, Stéphane Henriod s...@henriod.info wrote: Dear all I now have a running process where I dowload the hourly diffs and those are applied to the PostGIS database with osm2pgsql. My problem is that, despite the *osm2pgsql -b* option, it seems that my bounding box is ignored and the full diff file is added to my database, As a consequence, the database is growing very quickly and includes lots of data outside of my area of interest. My question is thus: is it possible to cut out the bounding box from the diff file before calling osm2pgsql? If I understand correctly, the bounding-box task ( http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#--bounding-box_.28--bb.29) only works on planet files, not diff files. Correct? Is there a way to get the following process working: 1. Get the last hourly diff file 2. Extract from the diff file all the objects within my bounding box 3. Apply this to the PostGIS database with osm2pgsql Thanks in advance and best wishes Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
Thanks Jais I actually thought of such a solution as well, but it's not ideal, as you need to have the full data both in the PostGIS database and as an osm xml file. If there was a way to apply the bounding box directly to the osc (or to get osm2pgsql to actually use the bounding box when applying the diff file), that would be much more efficient! Cheers, Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 10:16 AM, Jais Pedersen j...@pedersens.net wrote: As usual i forgot to send it to the list. -- Forwarded message -- From: Jais Pedersen j...@pedersens.net Date: Wed, Sep 5, 2012 at 3:15 PM Subject: Re: [OSM-dev] Osmosis - Cutting out a bounding box from a diff file (.osc) To: Stéphane Henriod s...@henriod.info I setup a Vietnam tile server just for fun and I made a script that worked like this: 1. Make a copy of the Vietnam extract. 2. Apply the update to the copy 3. Clip the copy using the Vietnam clip bounds file (I think i made my own from the admin boundary, but it might have been the one from Geofabrik) 4. Make a diff between the original and the copy 5. Apply the diff to the PostGIS database 6 Finally make the copy the new original for next update Unfortunately the server died (it was a retired PC), so I no longer have the script and it never had more than a single digit number of users, so I don't know how it will perform under real load, but for a small (as in the amount of data) extract it seemed to work quite well - I never really checked how it worked with object that grew or was moved across the border, but i suspect that would not be handled so well. I'm planning to setup a new one after the license change, so I would love to hear if anybody has a better way to do it. /Jais On Wed, Sep 5, 2012 at 2:19 PM, Stéphane Henriod s...@henriod.info wrote: PS: it looks like only the tables - planet_osm_rels - planet_osm_ways Get data from outside my bounding box and thus grow extremely quickly. Don't really know how to interpret this behavior! -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 8:56 AM, Stéphane Henriod s...@henriod.info wrote: Dear all I now have a running process where I dowload the hourly diffs and those are applied to the PostGIS database with osm2pgsql. My problem is that, despite the *osm2pgsql -b* option, it seems that my bounding box is ignored and the full diff file is added to my database, As a consequence, the database is growing very quickly and includes lots of data outside of my area of interest. My question is thus: is it possible to cut out the bounding box from the diff file before calling osm2pgsql? If I understand correctly, the bounding-box task ( http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#--bounding-box_.28--bb.29) only works on planet files, not diff files. Correct? Is there a way to get the following process working: 1. Get the last hourly diff file 2. Extract from the diff file all the objects within my bounding box 3. Apply this to the PostGIS database with osm2pgsql Thanks in advance and best wishes Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
Hi again so I see that the tables nodes, ways and rels are temporary tables resulting from the --slim option ( http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema#Intermediate_tables) Does this mean that I can simply empty them regularly? It looks to me like Mapnik won't need them at all, but I might be wrong? Thanks in advance, cheers, Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 10:22 AM, Stéphane Henriod s...@henriod.info wrote: Thanks Jais I actually thought of such a solution as well, but it's not ideal, as you need to have the full data both in the PostGIS database and as an osm xml file. If there was a way to apply the bounding box directly to the osc (or to get osm2pgsql to actually use the bounding box when applying the diff file), that would be much more efficient! Cheers, Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 10:16 AM, Jais Pedersen j...@pedersens.net wrote: As usual i forgot to send it to the list. -- Forwarded message -- From: Jais Pedersen j...@pedersens.net Date: Wed, Sep 5, 2012 at 3:15 PM Subject: Re: [OSM-dev] Osmosis - Cutting out a bounding box from a diff file (.osc) To: Stéphane Henriod s...@henriod.info I setup a Vietnam tile server just for fun and I made a script that worked like this: 1. Make a copy of the Vietnam extract. 2. Apply the update to the copy 3. Clip the copy using the Vietnam clip bounds file (I think i made my own from the admin boundary, but it might have been the one from Geofabrik) 4. Make a diff between the original and the copy 5. Apply the diff to the PostGIS database 6 Finally make the copy the new original for next update Unfortunately the server died (it was a retired PC), so I no longer have the script and it never had more than a single digit number of users, so I don't know how it will perform under real load, but for a small (as in the amount of data) extract it seemed to work quite well - I never really checked how it worked with object that grew or was moved across the border, but i suspect that would not be handled so well. I'm planning to setup a new one after the license change, so I would love to hear if anybody has a better way to do it. /Jais On Wed, Sep 5, 2012 at 2:19 PM, Stéphane Henriod s...@henriod.info wrote: PS: it looks like only the tables - planet_osm_rels - planet_osm_ways Get data from outside my bounding box and thus grow extremely quickly. Don't really know how to interpret this behavior! -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 8:56 AM, Stéphane Henriod s...@henriod.info wrote: Dear all I now have a running process where I dowload the hourly diffs and those are applied to the PostGIS database with osm2pgsql. My problem is that, despite the *osm2pgsql -b* option, it seems that my bounding box is ignored and the full diff file is added to my database, As a consequence, the database is growing very quickly and includes lots of data outside of my area of interest. My question is thus: is it possible to cut out the bounding box from the diff file before calling osm2pgsql? If I understand correctly, the bounding-box task ( http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#--bounding-box_.28--bb.29) only works on planet files, not diff files. Correct? Is there a way to get the following process working: 1. Get the last hourly diff file 2. Extract from the diff file all the objects within my bounding box 3. Apply this to the PostGIS database with osm2pgsql Thanks in advance and best wishes Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
On mercredi 5 septembre 2012, Stéphane Henriod wrote: Hi again yo, again, Does this mean that I can simply empty them regularly? You can, but then applying diffs won't be possible at all. Those table are not only used to reduce memory usage of the first import, but also to hold all data to solve the problem I mentionned about diffs not having all informations of their constituent nodes in order to apply them. It looks to me like Mapnik won't need them at all, but I might be wrong? It depends on your mapnik style sheets, but my guess is that there are no reasons to use those table for rendering (They don't hold geometries) -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
Hi Sylvain thanks for your anwers! But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Except if anyone else has a magical solution... Cheers Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 2:29 PM, sly (sylvain letuffe) li...@letuffe.orgwrote: On mercredi 5 septembre 2012, Stéphane Henriod wrote: Hi again yo, again, Does this mean that I can simply empty them regularly? You can, but then applying diffs won't be possible at all. Those table are not only used to reduce memory usage of the first import, but also to hold all data to solve the problem I mentionned about diffs not having all informations of their constituent nodes in order to apply them. It looks to me like Mapnik won't need them at all, but I might be wrong? It depends on your mapnik style sheets, but my guess is that there are no reasons to use those table for rendering (They don't hold geometries) -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is to increase the storage capacity ;-) is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Doing that will erase data that you want in your bbox, suppose a way crossing the border of your bbox, it will be erased while it still had nodes in the bbox. A better but longer solution is to remove every ways with no nodes in the bbox. But that probably won't be as easy as it sounds. Better increase you storage capacity ;-) -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
I think I will go for your second option: remove every way with no node in the bbox. Increasing the storage capacity is unfortunately not possible for now (mutualized server) and, anyway, it wouldn't make so much sense: I want to have only data for one region in my database and, like this, I will end up with the full planet (as every single node in OSM might be modified one day or the other...) By the way, would this be a bug or a request of improvement for osm2pgsql or osmosis? - osm2pgsql: actually use the --bbox option when applying a diff file to the database - osmosis: allowing to extract part of a diff file based on a bbox Cheers Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 3:14 PM, sly (sylvain letuffe) li...@letuffe.orgwrote: But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is to increase the storage capacity ;-) is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Doing that will erase data that you want in your bbox, suppose a way crossing the border of your bbox, it will be erased while it still had nodes in the bbox. A better but longer solution is to remove every ways with no nodes in the bbox. But that probably won't be as easy as it sounds. Better increase you storage capacity ;-) -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
I will end up with the full planet (as every single node in OSM might be modified one day or the other...) Well, My guess is that it will take a lng time, so long before that you might need to re-import your extract for whatever fail delete, style change, software update, and so on. Thus returning your database storage back to your extract's size only. Note that a regular extract re-import is also a way to clean what is outside your zone of interest. By the way, would this be a bug or a request of improvement for osm2pgsql or osmosis? I think it is not possible to do it properly, a dirty hack loosing data from time to time could do but that will still require server ressources. As said earlier here : http://lists.openstreetmap.org/pipermail/dev/2012-August/025503.html A share zone for regional diffs exports could do, or support for the new augmented diffs in those tools. But both still have to be done. -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
On Wed, Sep 5, 2012 at 3:42 PM, sly (sylvain letuffe) li...@letuffe.orgwrote: I will end up with the full planet (as every single node in OSM might be modified one day or the other...) Well, My guess is that it will take a lng time, so long before that you might need to re-import your extract for whatever fail delete, style change, software update, and so on. Thus returning your database storage back to your extract's size only. 200% agree! But my server quota will be reached much earlier :-) I'll keep this one in mind if any other option fails! Note that a regular extract re-import is also a way to clean what is outside your zone of interest. Might consider that as well but sounds a bit heavy to do it every minute / hour By the way, would this be a bug or a request of improvement for osm2pgsql or osmosis? I think it is not possible to do it properly, a dirty hack loosing data from time to time could do but that will still require server ressources. As said earlier here : http://lists.openstreetmap.org/pipermail/dev/2012-August/025503.html A share zone for regional diffs exports could do, or support for the new augmented diffs in those tools. But both still have to be done. I just tried another hack, but feel it's going nowhere: 1. Export all from the database to an osm file = full.osm 2. Download the latest diff file = changes.osc 3. Apply changes.osc to full.osm = updated.osm 4. Extract data in the bbox from updated.osm = updated_bbox.osm 5. Derive the changes between full.osm and updated_bbox.osm = change_bbox.osc Then this change file (change_bbox.osc) contains all the changes, but only in the bbox and can be applied to the DB, using osm2pgsql. Although a bit heavy I thought it would work... But apparently, Osmosis cannot read the PostGIS database, if using the osm2pgsql schema, right? I've tried with --read-apidb, --read-pgsql and --read-pgsimp, and always getting errors. Can someone tell me if this approach is possible? Thanks again! Stéphane -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
I just tried another hack, but feel it's going nowhere: 1. Export all from the database to an osm file = full.osm That's not possible using an osm2pgsql schema with existing tools I know. Although a bit heavy I thought it would work... But apparently, Osmosis cannot read the PostGIS database, if using the osm2pgsql schema, right? correct. osmosis can read a... osmosis schema ;-) -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
You can check tool osmupdate which has option to clip data outside boundaries. http://wiki.openstreetmap.org/wiki/Osmupdate#Applying_Geographical_Borders On 5 September 2012 16:06, Stéphane Henriod s...@henriod.info wrote: thanks for your anwers! But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Except if anyone else has a magical solution... ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
Thanks Ramas! unfortunately, what I want to update is a PostGIS database, not an OSM file, so I believe that osmupdate is not the good tool for me! Cheers, Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 5:25 PM, Ramas ies...@ramuno.lt wrote: You can check tool osmupdate which has option to clip data outside boundaries. http://wiki.openstreetmap.org/wiki/Osmupdate#Applying_Geographical_Borders On 5 September 2012 16:06, Stéphane Henriod s...@henriod.info wrote: thanks for your anwers! But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Except if anyone else has a magical solution... ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Fwd: Osmosis - Cutting out a bounding box from a diff file (.osc)
Hi again everybody! I am trying to implement the post-processing solution: removing all records in *planet_osm_ways* that are completely outside my bounding box. For this I first need to find out all the nodes outside the bounding box... The problem is that the x and y coordinates of *planet_osm_nodes* seem to be plenty wrong. My first record, for instance, has lat = 450768421 lon = 796483777 I assume that these are in srid 900913 but, if they are, they are way outside the limits of the 900913 srs. Actually, I just realised that they are systematically wrong by a factor 100. The correct coordinates should be something like: lat = 4507684 lon = 7964837 Have I done something wrong? or is there a known (or unknown?) bug with osm2pgsql? The geometries of the useful tables (line, point, polygon, roads) seem correct, but the x/y columns of the temporary tableshave this problem... Cheers Stéphane -- Le mot progrès n'aura aucun sens tant qu'il y aura des enfants malheureux -- Albert Einstein A journey does not need reasons. Before long, it proves to be reason enough in itself. One thinks that one is going to make a journey, yet soon it is the journey that makes or unmakes you. -- Nicolas Bouvier Photos de voyages, photos de montagne: http://www.henriod.info On Wed, Sep 5, 2012 at 5:25 PM, Ramas ies...@ramuno.lt wrote: You can check tool osmupdate which has option to clip data outside boundaries. http://wiki.openstreetmap.org/wiki/Osmupdate#Applying_Geographical_Borders On 5 September 2012 16:06, Stéphane Henriod s...@henriod.info wrote: thanks for your anwers! But it looks like I have a problem now... 1. I need to keep the tables ways, rels and nodes because of the diff updates 2. Those 3 tables will continue growing up with each new diff, until I reach the storage capacity of the server The only solution I see is a post-processing that will erase from PostGIS all the nodes lying outside my bounding box + all the ways using one of thses nodes + all the relations using one of thses nodes or one of these ways. Except if anyone else has a magical solution... ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
