<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif;" dir="ltr">
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<p style="margin-top:0;margin-bottom:0">Sorry - I've just re-read the log and indexes were added on planet_osm_line ! Not sure how I missed that !</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<p style="margin-top:0;margin-bottom:0">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?</p>
<p style="margin-top:0;margin-bottom:0"><br>
Thanks,</p>
<p style="margin-top:0;margin-bottom:0">Nick<br>
</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<div id="Signature">
<div id="divtagdefaultwrapper" dir="ltr" style="color: rgb(0, 0, 0); font-family: Calibri, Arial, Helvetica, sans-serif, "EmojiFont", "Apple Color Emoji", "Segoe UI Emoji", NotoColorEmoji, "Segoe UI Symbol", "Android Emoji", EmojiSymbols; font-size: 12pt;">
<p></p>
<br>
</div>
</div>
</div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Nick Whitelegg <nick.whitelegg@solent.ac.uk><br>
<b>Sent:</b> 19 March 2019 17:20:29<br>
<b>To:</b> dev@openstreetmap.org<br>
<b>Subject:</b> [OSM-dev] osm2pgsql - incomplete import (missing indexing), possible to add indexes manually?</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div id="x_divtagdefaultwrapper" dir="ltr" style="font-size:12pt; color:#000000; font-family:Calibri,Helvetica,sans-serif">
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">Hi,</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">I've attempted to import all highways and selected POIs in Europe into an osm2pgsql database.
<br>
</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">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):</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">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.</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">Extract from my log:</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0"></p>
<div>WARNING: there is no transaction in progress<br>
Sorting data and creating indexes for planet_osm_point<br>
Stopping table: planet_osm_nodes<br>
Stopped table: planet_osm_nodes in 0s<br>
Stopping table: planet_osm_ways<br>
Building index on table: planet_osm_ways<br>
Stopping table: planet_osm_rels<br>
Building index on table: planet_osm_rels<br>
Sorting data and creating indexes for planet_osm_line<br>
Sorting data and creating indexes for planet_osm_polygon<br>
Sorting data and creating indexes for planet_osm_roads<br>
Stopped table: planet_osm_rels in 0s<br>
node cache: stored: 263295984(54.84%), storage efficiency: 50.22% (dense blocks: 933, sparse nodes: 258322433), hit rate: 56.39%<br>
Osm2pgsql failed due to ERROR: CREATE TABLE planet_osm_point_tmp AS<br>
SELECT * FROM planet_osm_point<br>
WHERE ST_IsValid(way)<br>
ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10)<br>
COLLATE "C" failed: ERROR: could not write block 10861 of temporary file: No space left on device<br>
<br>
</div>
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).
<p></p>
<p style="margin-top:0; margin-bottom:0">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.<br>
</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">At this stage, I presume I can add an index manually, but what should I add the index on?</p>
<p style="margin-top:0; margin-bottom:0">Do I need the temp tables that I deleted?<br>
</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<p style="margin-top:0; margin-bottom:0">Thanks,</p>
<p style="margin-top:0; margin-bottom:0">Nick<br>
</p>
<p style="margin-top:0; margin-bottom:0"><br>
</p>
<div id="x_Signature">
<div id="x_divtagdefaultwrapper" dir="ltr" style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif,"EmojiFont","Apple Color Emoji","Segoe UI Emoji",NotoColorEmoji,"Segoe UI Symbol","Android Emoji",EmojiSymbols; font-size:12pt">
<p></p>
<br>
</div>
</div>
</div>
</div>
</body>
</html>