NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

My osm2pgsql planet import chugged along for weeks and weeks and then the PostgreSQL backend crashed sometime after the creation of planet_osm_point_temp and planet_osm_roads_temp and during the creation of planet_osm_polygon_tmp as shown below.

It seems I was ever so close to completing the process, and hoping I can pick up where it left off, but I cannot find the sql statements except that it seems they are generated in code.

I am wondering if there is a documented set of SQL statements that are carried out during the process. It looks like middle-pgsql.cpp and table.cpp, but I'm hoping there might exist a set of the statements. Thanks.

STATEMENT:  CREATE TABLE planet_osm_polygon_tmp  AS
          SELECT * FROM planet_osm_polygon
            WHERE ST_IsValid(way)
            ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10)
            COLLATE "C"

2018-09-23 10:42:48.391 PDT [15816] @osm FATAL:  connection to client lost

asked 24 Sep '18, 16:35

middleforkgis's gravatar image

middleforkgis
41113
accept rate: 0%


Thank you SimonPoole,

Memory was all swapped out so I won't bore you with the performance issues.

Of course the answer to my original question - without regards to the wisdom or lack thereof - was to capture statements in the pg logfiles, after setting 'log_statement' in postgresql.conf to 'all' and running a new osm2pgsql build on a tiny data extract.

This gives the following (excerpt only - I won't post the whole thing but here it is in a gist

CREATE TABLE planet_osm_roads_tmp  AS; SELECT * FROM planet_osm_roads;  CREATE TABLE planet_osm_line_tmp  AS; SELECT * FROM planet_osm_line;  CREATE TABLE planet_osm_point_tmp  AS; SELECT
* FROM planet_osm_point;  CREATE TABLE planet_osm_polygon_tmp  AS; SELECT * FROM planet_osm_polygon;  DROP TABLE planet_osm_line;  DROP TABLE planet_osm_roads;  DROP TABLE planet_osm_polygon;  DROP TABLE planet_osm_point;  ALTER TABLE planet_osm_line_tmp RENAME TO planet_osm_line;  ALTER TABLE planet_osm_roads_tmp RENAME TO planet_osm_roads;  CREATE INDEX ON planet_osm_line USING GIST (way) WITH (FILLFACTOR=100);  CREATE INDEX ON planet_osm_roads USING GIST (way) WITH (FILLFACTOR=100);  ANALYZE planet_osm_roads;  ANALYZE planet_osm_line;  ALTER TABLE planet_osm_polygon_tmp RENAME TO planet_osm_polygon;  CREATE INDEX ON planet_osm_polygon USING GIST (way) WITH (FILLFACTOR=100);  ANALYZE planet_osm_polygon;  ALTER TABLE planet_osm_point_tmp RENAME TO planet_osm_point;  CREATE INDEX ON planet_osm_point USING GIST (way) WITH (FILLFACTOR=100);  ANALYZE planet_osm_point;
permanent link

answered 24 Sep '18, 21:31

middleforkgis's gravatar image

middleforkgis
41113
accept rate: 0%

Given half adequate hardware a full planet import should complete in max 2 days (if you are talking about import in to the rendering schema).

Trying to fiddle around with SQL statements and rerunning parts of the import is likely going to take far longer than simply fixing the underlying issues (not to mention that a system that takes weeks to import will not be updatable without falling more and more behind).

permanent link

answered 24 Sep '18, 19:56

SimonPoole's gravatar image

SimonPoole ♦
44.7k13326701
accept rate: 18%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×263
×18

question asked: 24 Sep '18, 16:35

question was seen: 1,848 times

last updated: 24 Sep '18, 21:31

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum