[OSM-dev] osm2pgsql commit point and index recovery

[Paul Norman]

You should be fine. Once pending ways are completed, everything else is
postgresql indexing and clustering. The following commands are what I run to
manually cluster and index. Note that this uses a st_geohash clustering
which is faster. I use psql commands run in parallel rather than do
everything in one connection.

 

It also does not create indexes for the slim tables, but does analyze them.

 

psql -d gis -c 'CREATE INDEX ON planet_osm_roads (
ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);

CLUSTER planet_osm_roads USING "planet_osm_roads_st_geohash_idx"; drop index
planet_osm_roads_st_geohash_idx;' &

psql -d gis -c 'CREATE INDEX ON planet_osm_point (
ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);

CLUSTER planet_osm_point USING "planet_osm_point_st_geohash_idx"; drop index
planet_osm_point_st_geohash_idx;' &

psql -d gis -c 'CREATE INDEX ON planet_osm_line (
ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);

CLUSTER planet_osm_line USING "planet_osm_line_st_geohash_idx"; drop index
planet_osm_line_st_geohash_idx;' &

psql -d gis -c 'CREATE INDEX ON planet_osm_polygon (
ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);

CLUSTER planet_osm_polygon USING "planet_osm_polygon_st_geohash_idx"; drop
index planet_osm_polygon_st_geohash_idx;' &

wait

psql -d gis -c 'CREATE INDEX ON planet_osm_roads USING gist (way);' &

psql -d gis -c 'CREATE INDEX ON planet_osm_point USING gist (way);' &

psql -d gis -c 'CREATE INDEX ON planet_osm_line USING gist (way);' &

psql -d gis -c 'CREATE INDEX ON planet_osm_polygon USING gist (way);' &

wait

psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
planet_osm_roads;' &

psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
planet_osm_point;' &

psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
planet_osm_line;' &

psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
planet_osm_polygon;' &

psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
planet_osm_ways;' &

psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
planet_osm_rels;' &

wait

psql -d gis -c 'CREATE INDEX ON planet_osm_polygon USING gist (way) WHERE
building IS NOT NULL;'

 

 

From: Aiden Bell [mailto:aiden449 at gmail.com] 
Sent: Sunday, March 23, 2014 1:20 PM
To: dev at openstreetmap.org
Subject: [OSM-dev] osm2pgsql commit point and index recovery

 

Hi all,

I recently imported the osm planet file using osm2pgsql. The process crashed
(hung my whole box) when creating the geometry indices and other indices on
the tables. Most of the indexing completed, but the box hung on one of the
last ones. Scouring through the source code, it looks fairly trivial to add
the indices by hand, however I was wondering if anybody knew if the database
is consistent at this point (ie, all row data comitted).

Don't want to have to start over with the whole import really.

Many thanks,

Aiden

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20140323/b9574849/attachment.html>