[SOLVED] Imported 1 region,next append import with slim failed-HowFix?

General talk
1

Hi.

My computer have only 12GB of memory so I was planning to import region by region in append mode, and not import Antarctica.

So I first used this to import Central Amerika region:

osm2pgsql -U postgres -l -m -d osm -p planet_osm -E 3857 -S “N:\osm2pgsql\default.style” T:\planet-osm-2018-07-28\central-america-latest.osm.pbf

the import finished in 1020s.

Then I tried to import South America using this:
now using append mode.

N:\osm2pgsql>osm2pgsql -a -s -U postgres -l -m -d osm -p planet_osm -E 3857 -S “N:\osm2pgsql\default.style” T:\planet-osm-2018-07-28\south-america-latest.osm.pbf

which failed after a few seconds reporting this:


osm2pgsql version 0.96.0 (64 bit id space)

Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, cache=800
Setting up table: planet_osm_nodes
node cache: stored: 0(-nan(ind)%), storage efficiency: -nan(ind)% (dense blocks: 0, sparse nodes: 0), hit rate: -nan(ind
)%
Osm2pgsql failed due to ERROR: PREPARE insert_node (int8, int4, int4) AS INSERT INTO planet_osm_nodes VALUES ($1,$2,$3);

PREPARE get_node_list(int8[]) AS SELECT id, lat, lon FROM planet_osm_nodes WHERE id = ANY($1::int8[]);
PREPARE delete_node (int8) AS DELETE FROM planet_osm_nodes WHERE id = $1;
 failed: ERROR:  relation "planet_osm_nodes" does not exist
LINE 1: ...ARE insert_node (int8, int4, int4) AS INSERT INTO planet_osm...

Could someone please explain what I have to do to make the import of South America work, and without having to delete the previously imported region Central America.

2

It doesn’t work because if you use slim, you have to use it for all the imports.

Osm2pgsql in slim mode and asked to append, will search for this node table.

PS: Single letter options are less legible, I didn’t encounter people using them. I had to check the documentation to see what they mean.

3

Do I just have to skip using --append, and use --slim, on first import (Central America), and then continue using --append with --slim on the regions I want to add?

Or do I have to create the tables which is needed by --slim mode manually before I do the first import?

4

Given that your goal (as I understand it) is to generate tiles for everywhere, why do you need to have all continents in the database at once? Can you import continent A, generate tiles for A, throw A away and repeat for B etc.?

I’d expect that appending continent-sized pieces of data would be extremely slow, and if you’re short of memory you might hit problems there too.

5

Depending on what you are trying to achieve (and a completely functional w/w import is probably not feasible) you could import each area into a separate group of tables: so instead of planet_osm_line you could have sam_osm_line, cam_osm_line, nam_osm_line etc. You could create a union view for rendering purposes (create view planet_osm_line as select * from … union all …), or even more creatively try and use the separate tables as partitons of a partition table planet_osm_line. Only think about doing this is if you have a decent level of familiarity with PostgreSQL and relational dbs in general.

There are multiple downsides: complexity, untested, and not supportable by the community. Also some features will end up in more than one set of tables, although this may not unduly interfere with rendering.

I have followed this approach as a means of extracting specific types of data from regional pbf files (for instance retail locations, residential roads etc). My motivations were two fold: smaller files to download & only 8 Gbytes memory on my laptop. I have imported the European road network successfully, but I dont know if such an import would be viable with the full range of obejcts in europe.osm.pbf.

6

Or a separate database per continent?

7

Sure, that’s another option, but then you cant just render one set of tiles (which may be desired).

8

My goal is to render one set of tiles for the area(s) imported, but to split up the generation process to cover smaller map parts than the whole imported map. But I did not know how to get the whole planet, or most of it in the DB if not importing all at once - with my current computer setup - limited RAM, currently 12GB, one SSD for the DB and 3.2 GHz 4 core processor - and Windows 7. I was just trying to find a way to start, and hopefully not hitting any errors on the import.

Using --slim mode on the first import (that was valuable information from RicoElectrico), will hopefulle make it so I can add other parts of the world laiter.

I’m currently importing Europe. First import in --slim mode. If this goes well (probably many days before import finish), then I plan to add other regions to the DB laiter on. Will backup the database before trying to import other regions.

At current time it is processing Way at 6.22k/s - and will probably finish this part of the import process in approximately 6 hours.

Regardless if this import work or not I will get some more memory. Memory usage is close to 100% while CPU is around 20%. Maybe I could speed up the import process by putting DB tables on different disks. So another SSD is also on the list. The europe-latest.osm.pbf is on a HDD 7500 RPM. I do not know if this is an issue, and if import time could be improved by putting the import file on an SSD (another one than the DB).

Thanks for feedback and suggestions. I’ll come back laiter and tell if this is working or not.

9

You need to be careful how you measure memory use. Both Linux and Windows NT (through Windows 10) try and use nearly all the memory. However this does not indicate that they have a memory crisis, as most of it may be cached data that can be re-fetched from disk, or may turn out not to be needed again.

In the case of Linux, there is a command that calculates the actual memory used:

         total       used       free     shared    buffers     cached

Mem: 8139540 3837612 4301928 214740 254304 1338348
-/+ buffers/cache: 2244960 5894580

In this case, I haven’t maxed out the system with cached pages, so there is a lot of totally unused memory, but the corrected usage (second line) can be much more different from the first line, and used can be close to 100% without their being a real problem.

The real warning sign is when you are showing a high page swap rate. Any use of the page file can be a warning.

10

I might hit a problem with the current import of europe-latest.osm.pbf.
I might run out of space for the DB.
This add-on question is about if I can delete the tables nodes, ways and rels before the import has finished completely.
I do not need those tables since I will not be doing any updates of the data in the database.
I now realise that the --slim mode creates the before mentioned 3 tables, and that they take up a lot of space (relatively).

I have the database on an 500GB SSD.
The import of Europe is still continuing, and have reached the point where it says:


...
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways
....
Stopping table: planet_osm_reels
Building index on table: planet_osm_reels
Stopped table: planet_osm_reels in 295s
...
All indexes on planet_osm_line created in 9563s
Completed planet_osm_lines

I have now only 86GB of free space on the SSD disk with the database, and it’s free space is quite rapidly filled up.

From the output above, it looks to me that the index building for table planet_osm_reels are done, and the table are stopped. So can I delete that one, and wait for similar output for the two other tables before I delete them?

Just in the time I wrote this comment, another 3GB of data where used on the SSD.
Where is the panic button?

11

Osm2pgsql finished with only 51GB left on the SSD, in 155324s.
Not bad with only 12GB of RAM.
Will now get some more ram, add another SSD, and move some tables to the second SSD.

Interesting information about an import done on a Windows server, in january 2017 (also importing europe-latest…pbf, which took approximately 15 days, but using HDD:
https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks#Europe_import_on_Windows_Server_2012_HDD

Using an SSD is a huge timesaver. Note that I also tuned the postgresql config before the import started.

Most important HW and software now:
ASUS P6X58D Premium, Socket-1366
Intel® Core i7-960 Processor (Quad Core, 3,2 Ghz)
Corsair Dominator DHX DDR3 1600MHz 12GB (CL-9)
Samsung 860 EVO 500GB SSD
Seagate Barracuda 1TB 3.5" HDD (7200 RPM SATA 6GB)
Microsoft Windows 7 Pro

with europe-latest.osm.pbf on the HDD, and PostGIS and DB-tables on the SSD.

Can I continue importing other regions using --slim mode, if I now delete the node, rels and ways tables (slim tables)?
I’m not going to do any updates on the data laiter on anyway, and would like to get the extra space on the SSD.

Here is the output from osm2pgsql:


N:\osm2pgsql>osm2pgsql -s -U postgres -C 8000 --number-processes 4 -l -m -d osm -p planet_osm -E 3857 -S "N:\osm2pgsql\d
efault.style"  T:\planet-osm-2018-07-28\europe-latest.osm.pbf
osm2pgsql version 0.96.0 (64 bit id space)

Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads
Allocating memory for sparse node cache
Node-cache: cache=8000MB, maxblocks=128000*65536, allocation method=9
Mid: pgsql, cache=8000
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels

Reading in file: T:\planet-osm-2018-07-28\europe-latest.osm.pbf
Using PBF parser.
Processing: Node(2118681k 234.0k/s) Way(257597k 7.15k/s) Relation(4049210 129.55/s)  parse time: 76343s
Node stats: total(2118681939), max(5790611300) in 9056s
Way stats: total(257597642), max(611664115) in 36030s
Relation stats: total(4049276), max(8481686) in 31257s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline

Going over pending ways...
        190530225 ways are pending

Using 4 helper-processes
Finished processing 190530225 ways in 40476 s

190530225 Pending ways took 40476s at a rate of 4707.24/s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Going over pending relations...
        0 relations are pending

Using 4 helper-processes
Finished processing 0 relations in 0 s

Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
node cache: stored: 524288001(24.75%), storage efficiency: 50.00% (dense blocks: 0, 
sparse nodes: 524288001), hit rate: -69.67%
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_point
NOTICE:  Self-intersection at or near point 587312.94433636917 6271092.4624617016
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Creating osm_id index on planet_osm_roads
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 1281s
Completed planet_osm_roads
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways
NOTICE:  Self-intersection at or near point 1659220.8248663542 7993093.1167870089
Creating osm_id index on planet_osm_point
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 4398s
Completed planet_osm_point
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels
Stopped table: planet_osm_rels in 295s
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
NOTICE:  Self-intersection at or near point -506086.5913292435 5280209.9148441646
Creating osm_id index on planet_osm_line
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 9563s
Completed planet_osm_line
NOTICE:  Self-intersection at or near point 530333.30102692614 5918886.5543764178
NOTICE:  Self-intersection at or near point 587312.91705696541 6271092.4234429011
Stopped table: planet_osm_ways in 30651s

Osm2pgsql took 155324s overall

N:\osm2pgsql>
12

Great to hear you have made progress!