This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

osm2pgsql: Are the SQL statements documented, available?

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

osm2pgsql sql

asked 24 Sep '18, 16:35

middleforkgis
41●1●1●3
accept rate: 0%

2 Answers:

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;

answered 24 Sep '18, 21:31

middleforkgis
41●1●1●3
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).

answered 24 Sep '18, 19:56

SimonPoole ♦
44.7k●13●326●701
accept rate: 18%