Sorry - I've just re-read the log and indexes were added on planet_osm_line ! Not sure how I missed that !
However I'm guessing it might be that as the import didn't complete fully, the database might have been left in a sub-optimal state, is this the case or can I safely use it without further optimisation? Thanks, Nick ________________________________ From: Nick Whitelegg <[email protected]> Sent: 19 March 2019 17:20:29 To: [email protected] Subject: [OSM-dev] osm2pgsql - incomplete import (missing indexing), possible to add indexes manually? Hi, I've attempted to import all highways and selected POIs in Europe into an osm2pgsql database. It has mostly worked (in the sense that the data I want is in the DB), but failed towards the end due to insufficient disc space during the process of indexing (see below): The planet_osm_line and planet_osm_point have been populated successfully, however it appears that indexes were never added on planet_osm_line (they were added on planet_osm_point). Consequently line queries are slow and point queries faster. Extract from my log: WARNING: there is no transaction in progress Sorting data and creating indexes for planet_osm_point 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_rels Building index on table: planet_osm_rels Sorting data and creating indexes for planet_osm_line Sorting data and creating indexes for planet_osm_polygon Sorting data and creating indexes for planet_osm_roads Stopped table: planet_osm_rels in 0s node cache: stored: 263295984(54.84%), storage efficiency: 50.22% (dense blocks: 933, sparse nodes: 258322433), hit rate: 56.39% Osm2pgsql failed due to ERROR: CREATE TABLE planet_osm_point_tmp AS SELECT * FROM planet_osm_point WHERE ST_IsValid(way) ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C" failed: ERROR: could not write block 10861 of temporary file: No space left on device After this I deleted all the tables I don't need e.g. planet_osm_roads and planet_osm_ways, meaning I now have 52 GB free (out of 80). I don't want to use a bigger server if I can possibly help it as I only need the disc space for the import process, not for its day-to-day running. At this stage, I presume I can add an index manually, but what should I add the index on? Do I need the temp tables that I deleted? Thanks, Nick
_______________________________________________ dev mailing list [email protected] https://lists.openstreetmap.org/listinfo/dev
