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