]> git.openstreetmap.org Git - nominatim.git/blob - utils/setup.php
caaf0f9126141dba717782a8804cdfa03becc712
[nominatim.git] / utils / setup.php
1 #!/usr/bin/php -Cq
2 <?php
3
4         require_once(dirname(dirname(__FILE__)).'/settings/settings.php');
5         require_once(CONST_BasePath.'/lib/init-cmd.php');
6         ini_set('memory_limit', '800M');
7
8         $aCMDOptions = array(
9                 "Create and setup nominatim search system",
10                 array('help', 'h', 0, 1, 0, 0, false, 'Show Help'),
11                 array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
12                 array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
13
14                 array('osm-file', '', 0, 1, 1, 1, 'realpath', 'File to import'),
15                 array('threads', '', 0, 1, 1, 1, 'int', 'Number of threads (where possible)'),
16
17                 array('all', '', 0, 1, 0, 0, 'bool', 'Do the complete process'),
18
19                 array('create-db', '', 0, 1, 0, 0, 'bool', 'Create nominatim db'),
20                 array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'),
21                 array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'),
22                 array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'),
23                 array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
24                 array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
25                 array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
26                 array('ignore-errors', '', 0, 1, 0, 0, 'bool', 'Continue import even when errors in SQL are present (EXPERT)'),
27                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
28                 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
29                 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
30                 array('no-partitions', '', 0, 1, 0, 0, 'bool', "Do not partition search indices (speeds up import of single country extracts)"),
31                 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
32                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
33                 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
34                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
35                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
36                 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
37                 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
38                 array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
39                 array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
40                 array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'),
41                 array('drop', '', 0, 1, 0, 0, 'bool', 'Drop tables needed for updates, making the database readonly (EXPERIMENTAL)'),
42         );
43         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
44
45         $bDidSomething = false;
46
47         // Check if osm-file is set and points to a valid file if --all or --import-data is given
48         if ($aCMDResult['import-data'] || $aCMDResult['all'])
49         {
50                 if (!isset($aCMDResult['osm-file']))
51                 {
52                         fail('missing --osm-file for data import');
53                 }
54
55                 if (!file_exists($aCMDResult['osm-file']))
56                 {
57                         fail('the path supplied to --osm-file does not exist');
58                 }
59
60                 if (!is_readable($aCMDResult['osm-file']))
61                 {
62                         fail('osm-file "'.$aCMDResult['osm-file'].'" not readable');
63                 }
64         }
65
66
67         // This is a pretty hard core default - the number of processors in the box - 1
68         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
69         if ($iInstances < 1)
70         {
71                 $iInstances = 1;
72                 echo "WARNING: resetting threads to $iInstances\n";
73         }
74         if ($iInstances > getProcessorCount())
75         {
76                 $iInstances = getProcessorCount();
77                 echo "WARNING: resetting threads to $iInstances\n";
78         }
79
80         // Assume we can steal all the cache memory in the box (unless told otherwise)
81         if (isset($aCMDResult['osm2pgsql-cache']))
82         {
83                 $iCacheMemory = $aCMDResult['osm2pgsql-cache'];
84         }
85         else
86         {
87                 $iCacheMemory = getCacheMemoryMB();
88         }
89
90         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
91         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
92
93         if ($aCMDResult['create-db'] || $aCMDResult['all'])
94         {
95                 echo "Create DB\n";
96                 $bDidSomething = true;
97                 $oDB =& DB::connect(CONST_Database_DSN, false);
98                 if (!PEAR::isError($oDB))
99                 {
100                         fail('database already exists ('.CONST_Database_DSN.')');
101                 }
102                 passthruCheckReturn('createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
103         }
104
105         if ($aCMDResult['setup-db'] || $aCMDResult['all'])
106         {
107                 echo "Setup DB\n";
108                 $bDidSomething = true;
109                 // TODO: path detection, detection memory, etc.
110
111                 $oDB =& getDB();
112
113                 $fPostgresVersion = getPostgresVersion($oDB);
114                 echo 'Postgres version found: '.$fPostgresVersion."\n";
115
116                 if ($fPostgresVersion < 9.1)
117                 {
118                         fail("Minimum supported version of Postgresql is 9.1.");
119                 }
120
121                 pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS hstore');
122                 pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis');
123
124                 // For extratags and namedetails the hstore_to_json converter is
125                 // needed which is only available from Postgresql 9.3+. For older
126                 // versions add a dummy function that returns nothing.
127                 $iNumFunc = $oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'");
128                 if (PEAR::isError($iNumFunc))
129                 {
130                         fail("Cannot query stored procedures.", $iNumFunc);
131                 }
132                 if ($iNumFunc == 0)
133                 {
134                         pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable");
135                         echo "WARNING: Postgresql is too old. extratags and namedetails API not available.";
136                 }
137
138                 $fPostgisVersion = getPostgisVersion($oDB);
139                 echo 'Postgis version found: '.$fPostgisVersion."\n";
140
141                 if ($fPostgisVersion < 2.1)
142                 {
143                         // Function was renamed in 2.1 and throws an annoying deprecation warning
144                         pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint');
145                 }
146
147                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
148                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
149                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
150                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_table.sql');
151                 if (file_exists(CONST_BasePath.'/data/gb_postcode_data.sql.gz'))
152                 {
153                         pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_data.sql.gz');
154                 }
155                 else
156                 {
157                         echo "WARNING: external UK postcode table not found.\n";
158                 }
159                 if (CONST_Use_Extra_US_Postcodes)
160                 {
161                         pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
162                 }
163
164                 if ($aCMDResult['no-partitions'])
165                 {
166                         pgsqlRunScript('update country_name set partition = 0');
167                 }
168
169                 // the following will be needed by create_functions later but
170                 // is only defined in the subsequently called create_tables.
171                 // Create dummies here that will be overwritten by the proper
172                 // versions in create-tables.
173                 pgsqlRunScript('CREATE TABLE place_boundingbox ()');
174                 pgsqlRunScript('create type wikipedia_article_match as ()');
175         }
176
177         if ($aCMDResult['import-data'] || $aCMDResult['all'])
178         {
179                 echo "Import\n";
180                 $bDidSomething = true;
181
182                 $osm2pgsql = CONST_Osm2pgsql_Binary;
183                 if (!file_exists($osm2pgsql))
184                 {
185                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
186                         fail("osm2pgsql not found in '$osm2pgsql'");
187                 }
188
189                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
190                 {
191                         $osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
192                 }
193                 if (CONST_Tablespace_Osm2pgsql_Data)
194                         $osm2pgsql .= ' --tablespace-slim-data '.CONST_Tablespace_Osm2pgsql_Data;
195                 if (CONST_Tablespace_Osm2pgsql_Index)
196                         $osm2pgsql .= ' --tablespace-slim-index '.CONST_Tablespace_Osm2pgsql_Index;
197                 if (CONST_Tablespace_Place_Data)
198                         $osm2pgsql .= ' --tablespace-main-data '.CONST_Tablespace_Place_Data;
199                 if (CONST_Tablespace_Place_Index)
200                         $osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
201                 $osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
202                 $osm2pgsql .= ' -C '.$iCacheMemory;
203                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
204                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
205                 passthruCheckReturn($osm2pgsql);
206
207                 $oDB =& getDB();
208                 $x = $oDB->getRow('select * from place limit 1');
209                 if (PEAR::isError($x)) {
210                         fail($x->getMessage());
211                 }
212                 if (!$x) fail('No Data');
213         }
214
215         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
216         {
217                 echo "Functions\n";
218                 $bDidSomething = true;
219                 if (!file_exists(CONST_InstallPath.'/module/nominatim.so')) fail("nominatim module not built");
220                 create_sql_functions($aCMDResult);
221         }
222
223         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
224         {
225                 $bDidSomething = true;
226
227                 echo "Tables\n";
228                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
229                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
230                 $sTemplate = replace_tablespace('{ts:address-data}',
231                                                 CONST_Tablespace_Address_Data, $sTemplate);
232                 $sTemplate = replace_tablespace('{ts:address-index}',
233                                                 CONST_Tablespace_Address_Index, $sTemplate);
234                 $sTemplate = replace_tablespace('{ts:search-data}',
235                                                 CONST_Tablespace_Search_Data, $sTemplate);
236                 $sTemplate = replace_tablespace('{ts:search-index}',
237                                                 CONST_Tablespace_Search_Index, $sTemplate);
238                 $sTemplate = replace_tablespace('{ts:aux-data}',
239                                                 CONST_Tablespace_Aux_Data, $sTemplate);
240                 $sTemplate = replace_tablespace('{ts:aux-index}',
241                                                 CONST_Tablespace_Aux_Index, $sTemplate);
242                 pgsqlRunScript($sTemplate, false);
243
244                 // re-run the functions
245                 echo "Functions\n";
246                 create_sql_functions($aCMDResult);
247         }
248
249         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
250         {
251                 echo "Partition Tables\n";
252                 $bDidSomething = true;
253                 $oDB =& getDB();
254                 $sSQL = 'select distinct partition from country_name';
255                 $aPartitions = $oDB->getCol($sSQL);
256                 if (PEAR::isError($aPartitions))
257                 {
258                         fail($aPartitions->getMessage());
259                 }
260                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
261
262                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
263                 $sTemplate = replace_tablespace('{ts:address-data}',
264                                                 CONST_Tablespace_Address_Data, $sTemplate);
265                 $sTemplate = replace_tablespace('{ts:address-index}',
266                                                 CONST_Tablespace_Address_Index, $sTemplate);
267                 $sTemplate = replace_tablespace('{ts:search-data}',
268                                                 CONST_Tablespace_Search_Data, $sTemplate);
269                 $sTemplate = replace_tablespace('{ts:search-index}',
270                                                 CONST_Tablespace_Search_Index, $sTemplate);
271                 $sTemplate = replace_tablespace('{ts:aux-data}',
272                                                 CONST_Tablespace_Aux_Data, $sTemplate);
273                 $sTemplate = replace_tablespace('{ts:aux-index}',
274                                                 CONST_Tablespace_Aux_Index, $sTemplate);
275                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
276                 foreach($aMatches as $aMatch)
277                 {
278                         $sResult = '';
279                         foreach($aPartitions as $sPartitionName)
280                         {
281                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
282                         }
283                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
284                 }
285
286                 pgsqlRunScript($sTemplate);
287         }
288
289
290         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
291         {
292                 echo "Partition Functions\n";
293                 $bDidSomething = true;
294                 $oDB =& getDB();
295                 $sSQL = 'select distinct partition from country_name';
296                 $aPartitions = $oDB->getCol($sSQL);
297                 if (PEAR::isError($aPartitions))
298                 {
299                         fail($aPartitions->getMessage());
300                 }
301                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
302
303                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
304                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
305                 foreach($aMatches as $aMatch)
306                 {
307                         $sResult = '';
308                         foreach($aPartitions as $sPartitionName)
309                         {
310                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
311                         }
312                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
313                 }
314
315                 pgsqlRunScript($sTemplate);
316         }
317
318         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
319         {
320                 $bDidSomething = true;
321                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
322                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
323                 if (file_exists($sWikiArticlesFile))
324                 {
325                         echo "Importing wikipedia articles...";
326                         pgsqlRunDropAndRestore($sWikiArticlesFile);
327                         echo "...done\n";
328                 }
329                 else
330                 {
331                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
332                 }
333                 if (file_exists($sWikiRedirectsFile))
334                 {
335                         echo "Importing wikipedia redirects...";
336                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
337                         echo "...done\n";
338                 }
339                 else
340                 {
341                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
342                 }
343         }
344
345
346         if ($aCMDResult['load-data'] || $aCMDResult['all'])
347         {
348                 echo "Drop old Data\n";
349                 $bDidSomething = true;
350
351                 $oDB =& getDB();
352                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
353                 echo '.';
354                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
355                 echo '.';
356                 if (!pg_query($oDB->connection, 'TRUNCATE location_property_osmline')) fail(pg_last_error($oDB->connection));
357                 echo '.';
358                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
359                 echo '.';
360                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
361                 echo '.';
362                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
363                 echo '.';
364                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
365                 echo '.';
366                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
367                 echo '.';
368                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
369                 echo '.';
370                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
371                 echo '.';
372
373                 $sSQL = 'select distinct partition from country_name';
374                 $aPartitions = $oDB->getCol($sSQL);
375                 if (PEAR::isError($aPartitions))
376                 {
377                         fail($aPartitions->getMessage());
378                 }
379                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
380                 foreach($aPartitions as $sPartition)
381                 {
382                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
383                         echo '.';
384                 }
385
386                 // used by getorcreate_word_id to ignore frequent partial words
387                 if (!pg_query($oDB->connection, 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS $$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE')) fail(pg_last_error($oDB->connection));
388                 echo ".\n";
389
390                 // pre-create the word list
391                 if (!$aCMDResult['disable-token-precalc'])
392                 {
393                         echo "Loading word list\n";
394                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
395                 }
396
397                 echo "Load Data\n";
398                 $aDBInstances = array();
399         
400         $aQueriesPlacex = array();
401         $aQueriesOsmline = array();
402         // the query is divided into parcels, so that the work between the processes, i.e. the DBInstances, will be evenly distributed
403         $iNumberOfParcels = 100;
404         for($i = 0; $i < $iNumberOfParcels; $i++)
405         {
406                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
407                         $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
408                         $sSQL .= 'geometry) select * from place where osm_id % '.$iNumberOfParcels.' = '.$i.' and not ';
409                         $sSQL .= '(class=\'place\' and type=\'houses\' and osm_type=\'W\' and ST_GeometryType(geometry) = \'ST_LineString\');';
410                         array_push($aQueriesPlacex, $sSQL);
411                         $sSQL = 'select insert_osmline (osm_id, housenumber, street, addr_place, postcode, country_code, ';
412                         $sSQL .= 'geometry) from place where osm_id % '.$iNumberOfParcels.' = '.$i.' and ';
413                         $sSQL .= 'class=\'place\' and type=\'houses\' and osm_type=\'W\' and ST_GeometryType(geometry) = \'ST_LineString\'';
414                         array_push($aQueriesOsmline, $sSQL);
415                 }
416         
417                 for($i = 0; $i < $iInstances; $i++)
418                 {
419                         $aDBInstances[$i] =& getDB(true);
420                 }
421                 // now execute the query blocks, in the first round for placex, then for osmline, 
422                 // because insert_osmline depends on the placex table
423                 echo 'Inserting from place to placex.';
424                 $aQueries = $aQueriesPlacex;
425                 for($j = 0; $j < 2; $j++)
426                 {
427                         $bAnyBusy = true;
428                         while($bAnyBusy)
429                         {
430                                 $bAnyBusy = false;
431
432                                 for($i = 0; $i < $iInstances; $i++)
433                                 {
434                                         if (pg_connection_busy($aDBInstances[$i]->connection)) 
435                                         {
436                                                 $bAnyBusy = true;
437                                         }
438                                         else if (count($aQueries) > 0)
439                                         {
440                                                 $query = array_pop($aQueries);
441                                                 if (!pg_send_query($aDBInstances[$i]->connection, $query))
442                                                 {
443                                                         fail(pg_last_error($oDB->connection));
444                                                 }
445                                                 else
446                                                 {
447                                                         pg_get_result($aDBInstances[$i]->connection);
448                                                         $bAnyBusy = true;
449                                                 }
450                                         }
451                                 }
452                                 sleep(1);
453                                 echo '.';
454                         }
455                         echo "\n";
456                         if ($j == 0)  //for the second round with osmline
457                         {
458                                 echo 'Inserting from place to osmline.';
459                                 $aQueries = $aQueriesOsmline;
460                         }
461                 }
462                 
463                 echo "Reanalysing database...\n";
464                 pgsqlRunScript('ANALYSE');
465         }
466
467         if ($aCMDResult['import-tiger-data'])
468         {
469                 $bDidSomething = true;
470
471                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
472                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
473                 $sTemplate = replace_tablespace('{ts:aux-data}',
474                                                 CONST_Tablespace_Aux_Data, $sTemplate);
475                 $sTemplate = replace_tablespace('{ts:aux-index}',
476                                                 CONST_Tablespace_Aux_Index, $sTemplate);
477                 pgsqlRunScript($sTemplate, false);
478
479                 $aDBInstances = array();
480                 for($i = 0; $i < $iInstances; $i++)
481                 {
482                         $aDBInstances[$i] =& getDB(true);
483                 }
484
485                 foreach(glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile)
486                 {
487                         echo $sFile.': ';
488                         $hFile = fopen($sFile, "r");
489                         $sSQL = fgets($hFile, 100000);
490                         $iLines = 0;
491
492                         while(true)
493                         {
494                                 for($i = 0; $i < $iInstances; $i++)
495                                 {
496                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
497                                         {
498                                                 while(pg_get_result($aDBInstances[$i]->connection));
499                                                 $sSQL = fgets($hFile, 100000);
500                                                 if (!$sSQL) break 2;
501                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
502                                                 $iLines++;
503                                                 if ($iLines == 1000)
504                                                 {
505                                                         echo ".";
506                                                         $iLines = 0;
507                                                 }
508                                         }
509                                 }
510                                 usleep(10);
511                         }
512
513                         fclose($hFile);
514
515                         $bAnyBusy = true;
516                         while($bAnyBusy)
517                         {
518                                 $bAnyBusy = false;
519                                 for($i = 0; $i < $iInstances; $i++)
520                                 {
521                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
522                                 }
523                                 usleep(10);
524                         }
525                         echo "\n";
526                 }
527
528                 echo "Creating indexes\n";
529                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
530                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
531                 $sTemplate = replace_tablespace('{ts:aux-data}',
532                                                 CONST_Tablespace_Aux_Data, $sTemplate);
533                 $sTemplate = replace_tablespace('{ts:aux-index}',
534                                                 CONST_Tablespace_Aux_Index, $sTemplate);
535                 pgsqlRunScript($sTemplate, false);
536         }
537
538         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
539         {
540                 $bDidSomething = true;
541                 $oDB =& getDB();
542                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
543                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
544                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
545                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
546                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
547                 $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
548                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
549
550                 if (CONST_Use_Extra_US_Postcodes)
551                 {
552                         $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
553                         $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
554                         $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
555                         if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
556                 }
557         }
558
559         if ($aCMDResult['osmosis-init'] || ($aCMDResult['all'] && !$aCMDResult['drop'])) // no use doing osmosis-init when dropping update tables
560         {
561                 $bDidSomething = true;
562                 $oDB =& getDB();
563
564                 if (!file_exists(CONST_Osmosis_Binary))
565                 {
566                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
567                         if (!$aCMDResult['all'])
568                         {
569                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
570                         }
571                 }
572                 else
573                 {
574                         if (file_exists(CONST_InstallPath.'/settings/configuration.txt'))
575                         {
576                                 echo "settings/configuration.txt already exists\n";
577                         }
578                         else
579                         {
580                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_InstallPath.'/settings');
581                                 // update osmosis configuration.txt with our settings
582                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_InstallPath.'/settings/configuration.txt');
583                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_InstallPath.'/settings/configuration.txt');
584                         }
585
586                         // Find the last node in the DB
587                         $iLastOSMID = $oDB->getOne("select max(osm_id) from place where osm_type = 'N'");
588
589                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
590                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
591                         $sLastNodeXML = file_get_contents($sLastNodeURL);
592                         preg_match('#timestamp="(([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}):([0-9]{2}):([0-9]{2})Z)"#', $sLastNodeXML, $aLastNodeDate);
593                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
594
595                         // Search for the correct state file - uses file timestamps so need to sort by date descending
596                         $sRepURL = CONST_Replication_Url."/";
597                         $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
598                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
599                         // planet.openstreetmap.org: <a href="273/">273/</a>                    2013-03-11 07:41    -
600                         preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
601                         if ($aRepMatches)
602                         {
603                                 $aPrevRepMatch = false;
604                                 foreach($aRepMatches as $aRepMatch)
605                                 {
606                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
607                                         $aPrevRepMatch = $aRepMatch;
608                                 }
609                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
610
611                                 $sRepURL .= $aRepMatch[1];
612                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
613                                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
614                                 $aPrevRepMatch = false;
615                                 foreach($aRepMatches as $aRepMatch)
616                                 {
617                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
618                                         $aPrevRepMatch = $aRepMatch;
619                                 }
620                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
621
622                                 $sRepURL .= $aRepMatch[1];
623                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
624                                 preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
625                                 $aPrevRepMatch = false;
626                                 foreach($aRepMatches as $aRepMatch)
627                                 {
628                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
629                                         $aPrevRepMatch = $aRepMatch;
630                                 }
631                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
632
633                                 $sRepURL .= $aRepMatch[1].'.state.txt';
634                                 echo "Getting state file: $sRepURL\n";
635                                 $sStateFile = file_get_contents($sRepURL);
636                                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
637                                 file_put_contents(CONST_InstallPath.'/settings/state.txt', $sStateFile);
638                                 echo "Updating DB status\n";
639                                 pg_query($oDB->connection, 'TRUNCATE import_status');
640                                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
641                                 pg_query($oDB->connection, $sSQL);
642                         }
643                         else
644                         {
645                                 if (!$aCMDResult['all'])
646                                 {
647                                         fail("Cannot read state file directory.");
648                                 }
649                         }
650                 }
651         }
652
653         if ($aCMDResult['index'] || $aCMDResult['all'])
654         {
655                 $bDidSomething = true;
656                 $sOutputFile = '';
657                 $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
658                 passthruCheckReturn($sBaseCmd.' -R 4');
659                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
660                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
661                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
662                 passthruCheckReturn($sBaseCmd.' -r 26');
663         }
664
665         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
666         {
667                 echo "Search indices\n";
668                 $bDidSomething = true;
669
670                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
671                 $sTemplate = replace_tablespace('{ts:address-index}',
672                                                 CONST_Tablespace_Address_Index, $sTemplate);
673                 $sTemplate = replace_tablespace('{ts:search-index}',
674                                                 CONST_Tablespace_Search_Index, $sTemplate);
675                 $sTemplate = replace_tablespace('{ts:aux-index}',
676                                                 CONST_Tablespace_Aux_Index, $sTemplate);
677
678                 pgsqlRunScript($sTemplate);
679         }
680
681         if (isset($aCMDResult['create-website']))
682         {
683                 $bDidSomething = true;
684                 $sTargetDir = $aCMDResult['create-website'];
685                 if (!is_dir($sTargetDir))
686                 {
687                         echo "You must create the website directory before calling this function.\n";
688                         fail("Target directory does not exist.");
689                 }
690
691                 @symlink(CONST_InstallPath.'/website/details.php', $sTargetDir.'/details.php');
692                 @symlink(CONST_InstallPath.'/website/reverse.php', $sTargetDir.'/reverse.php');
693                 @symlink(CONST_InstallPath.'/website/search.php', $sTargetDir.'/search.php');
694                 @symlink(CONST_InstallPath.'/website/search.php', $sTargetDir.'/index.php');
695                 @symlink(CONST_InstallPath.'/website/lookup.php', $sTargetDir.'/lookup.php');
696                 @symlink(CONST_InstallPath.'/website/deletable.php', $sTargetDir.'/deletable.php');
697                 @symlink(CONST_InstallPath.'/website/polygons.php', $sTargetDir.'/polygons.php');
698                 @symlink(CONST_InstallPath.'/website/status.php', $sTargetDir.'/status.php');
699                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
700                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
701                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
702                 echo "Symlinks created\n";
703
704                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
705                 if (!$sTestFile)
706                 {
707                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
708                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
709                 }
710         }
711
712         if ($aCMDResult['drop'])
713         {
714                 // The implementation is potentially a bit dangerous because it uses
715                 // a positive selection of tables to keep, and deletes everything else.
716                 // Including any tables that the unsuspecting user might have manually
717                 // created. USE AT YOUR OWN PERIL.
718                 $bDidSomething = true;
719
720                 // tables we want to keep. everything else goes.
721                 $aKeepTables = array(
722                    "*columns",
723                    "import_polygon_*",
724                    "import_status",
725                    "place_addressline",
726                    "location_property*",
727                    "placex",
728                    "search_name",
729                    "seq_*",
730                    "word",
731                    "query_log",
732                    "new_query_log",
733                    "gb_postcode",
734                    "spatial_ref_sys",
735                    "country_name",
736                    "place_classtype_*"
737                 );
738
739                 $oDB =& getDB();
740                 $aDropTables = array();
741                 $aHaveTables = $oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
742                 if (PEAR::isError($aHaveTables))
743                 {
744                         fail($aPartitions->getMessage());
745                 }
746                 foreach($aHaveTables as $sTable)
747                 {
748                         $bFound = false;
749                         foreach ($aKeepTables as $sKeep)
750                         {
751                                 if (fnmatch($sKeep, $sTable))
752                                 {
753                                         $bFound = true;
754                                         break;
755                                 }
756                         }
757                         if (!$bFound) array_push($aDropTables, $sTable);
758                 }
759
760                 foreach ($aDropTables as $sDrop)
761                 {
762                         if ($aCMDResult['verbose']) echo "dropping table $sDrop\n";
763                         @pg_query($oDB->connection, "DROP TABLE $sDrop CASCADE");
764                         // ignore warnings/errors as they might be caused by a table having
765                         // been deleted already by CASCADE
766                 }
767
768                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
769                 {
770                         if ($aCMDResult['verbose']) echo "deleting ".CONST_Osm2pgsql_Flatnode_File."\n";
771                         unlink(CONST_Osm2pgsql_Flatnode_File);
772                 }
773         }
774
775         if (!$bDidSomething)
776         {
777                 showUsage($aCMDOptions, true);
778         }
779         else
780         {
781                 echo "Setup finished.\n";
782         }
783
784         function pgsqlRunScriptFile($sFilename)
785         {
786                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
787
788                 // Convert database DSN to psql parameters
789                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
790                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
791                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
792
793                 $ahGzipPipes = null;
794                 if (preg_match('/\\.gz$/', $sFilename))
795                 {
796                         $aDescriptors = array(
797                                 0 => array('pipe', 'r'),
798                                 1 => array('pipe', 'w'),
799                                 2 => array('file', '/dev/null', 'a')
800                         );
801                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
802                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
803                         $aReadPipe = $ahGzipPipes[1];
804                         fclose($ahGzipPipes[0]);
805                 }
806                 else
807                 {
808                         $sCMD .= ' -f '.$sFilename;
809                         $aReadPipe = array('pipe', 'r');
810                 }
811
812                 $aDescriptors = array(
813                         0 => $aReadPipe,
814                         1 => array('pipe', 'w'),
815                         2 => array('file', '/dev/null', 'a')
816                 );
817                 $ahPipes = null;
818                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
819                 if (!is_resource($hProcess)) fail('unable to start pgsql');
820
821
822                 // TODO: error checking
823                 while(!feof($ahPipes[1]))
824                 {
825                         echo fread($ahPipes[1], 4096);
826                 }
827                 fclose($ahPipes[1]);
828
829                 $iReturn = proc_close($hProcess);
830                 if ($iReturn > 0)
831                 {
832                         fail("pgsql returned with error code ($iReturn)");
833                 }
834                 if ($ahGzipPipes)
835                 {
836                         fclose($ahGzipPipes[1]);
837                         proc_close($hGzipProcess);
838                 }
839
840         }
841
842         function pgsqlRunScript($sScript, $bfatal = true)
843         {
844                 global $aCMDResult;
845                 // Convert database DSN to psql parameters
846                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
847                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
848                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
849                 if ($bfatal && !$aCMDResult['ignore-errors'])
850                         $sCMD .= ' -v ON_ERROR_STOP=1';
851                 $aDescriptors = array(
852                         0 => array('pipe', 'r'),
853                         1 => STDOUT, 
854                         2 => STDERR
855                 );
856                 $ahPipes = null;
857                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
858                 if (!is_resource($hProcess)) fail('unable to start pgsql');
859
860                 while(strlen($sScript))
861                 {
862                         $written = fwrite($ahPipes[0], $sScript);
863                         if ($written <= 0) break;
864                         $sScript = substr($sScript, $written);
865                 }
866                 fclose($ahPipes[0]);
867                 $iReturn = proc_close($hProcess);
868                 if ($bfatal && $iReturn > 0)
869                 {
870                         fail("pgsql returned with error code ($iReturn)");
871                 }
872         }
873
874         function pgsqlRunRestoreData($sDumpFile)
875         {
876                 // Convert database DSN to psql parameters
877                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
878                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
879                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
880
881                 $aDescriptors = array(
882                         0 => array('pipe', 'r'),
883                         1 => array('pipe', 'w'),
884                         2 => array('file', '/dev/null', 'a')
885                 );
886                 $ahPipes = null;
887                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
888                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
889
890                 fclose($ahPipes[0]);
891
892                 // TODO: error checking
893                 while(!feof($ahPipes[1]))
894                 {
895                         echo fread($ahPipes[1], 4096);
896                 }
897                 fclose($ahPipes[1]);
898
899                 $iReturn = proc_close($hProcess);
900         }
901
902         function pgsqlRunDropAndRestore($sDumpFile)
903         {
904                 // Convert database DSN to psql parameters
905                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
906                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
907                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
908
909                 $aDescriptors = array(
910                         0 => array('pipe', 'r'),
911                         1 => array('pipe', 'w'),
912                         2 => array('file', '/dev/null', 'a')
913                 );
914                 $ahPipes = null;
915                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
916                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
917
918                 fclose($ahPipes[0]);
919
920                 // TODO: error checking
921                 while(!feof($ahPipes[1]))
922                 {
923                         echo fread($ahPipes[1], 4096);
924                 }
925                 fclose($ahPipes[1]);
926
927                 $iReturn = proc_close($hProcess);
928         }
929
930         function passthruCheckReturn($cmd)
931         {
932                 $result = -1;
933                 passthru($cmd, $result);
934                 if ($result != 0) fail('Error executing external command: '.$cmd);
935         }
936
937         function replace_tablespace($sTemplate, $sTablespace, $sSql)
938         {
939                 if ($sTablespace)
940                         $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"',
941                                             $sSql);
942                 else
943                         $sSql = str_replace($sTemplate, '', $sSql);
944
945                 return $sSql;
946         }
947
948         function create_sql_functions($aCMDResult)
949         {
950                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
951                 $sTemplate = str_replace('{modulepath}', CONST_InstallPath.'/module', $sTemplate);
952                 if ($aCMDResult['enable-diff-updates'])
953                 {
954                         $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate);
955                 }
956                 if ($aCMDResult['enable-debug-statements'])
957                 {
958                         $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
959                 }
960                 if (CONST_Limit_Reindexing)
961                 {
962                         $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
963                 }
964                 if (!CONST_Use_US_Tiger_Data)
965                 {
966                         $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate);
967                 }
968                 if (!CONST_Use_Aux_Location_data)
969                 {
970                         $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate);
971                 }
972                 pgsqlRunScript($sTemplate);
973
974         }
975