]> git.openstreetmap.org Git - nominatim.git/blob - utils/setup.php
Merge remote-tracking branch 'upstream/master'
[nominatim.git] / utils / setup.php
1 #!/usr/bin/php -Cq
2 <?php
3
4         require_once(dirname(dirname(__FILE__)).'/lib/init-cmd.php');
5         ini_set('memory_limit', '800M');
6
7         $aCMDOptions = array(
8                 "Create and setup nominatim search system",
9                 array('help', 'h', 0, 1, 0, 0, false, 'Show Help'),
10                 array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
11                 array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
12
13                 array('osm-file', '', 0, 1, 1, 1, 'realpath', 'File to import'),
14                 array('threads', '', 0, 1, 1, 1, 'int', 'Number of threads (where possible)'),
15
16                 array('all', '', 0, 1, 0, 0, 'bool', 'Do the complete process'),
17
18                 array('create-db', '', 0, 1, 0, 0, 'bool', 'Create nominatim db'),
19                 array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'),
20                 array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'),
21                 array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'),
22                 array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
23                 array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
24                 array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
25                 array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'),
26                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
27                 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
28                 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
29                 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
30                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
31                 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
32                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
33                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
34                 array('create-roads', '', 0, 1, 0, 0, 'bool', ''),
35                 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
36                 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
37                 array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
38                 array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
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         );
42         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
43
44         $bDidSomething = false;
45
46         // Check if osm-file is set and points to a valid file if --all or --import-data is given
47         if ($aCMDResult['import-data'] || $aCMDResult['all'])
48         {
49                 if (!isset($aCMDResult['osm-file']))
50                 {
51                         fail('missing --osm-file for data import');
52                 }
53
54                 if (!file_exists($aCMDResult['osm-file']))
55                 {
56                         fail('the path supplied to --osm-file does not exist');
57                 }
58
59                 if (!is_readable($aCMDResult['osm-file']))
60                 {
61                         fail('osm-file "'.$aCMDResult['osm-file'].'" not readable');
62                 }
63         }
64
65
66         // This is a pretty hard core default - the number of processors in the box - 1
67         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
68         if ($iInstances < 1)
69         {
70                 $iInstances = 1;
71                 echo "WARNING: resetting threads to $iInstances\n";
72         }
73         if ($iInstances > getProcessorCount())
74         {
75                 $iInstances = getProcessorCount();
76                 echo "WARNING: resetting threads to $iInstances\n";
77         }
78
79         // Assume we can steal all the cache memory in the box (unless told otherwise)
80         $iCacheMemory = (isset($aCMDResult['osm2pgsql-cache'])?$aCMDResult['osm2pgsql-cache']:getCacheMemoryMB());
81         if ($iCacheMemory > getTotalMemoryMB())
82         {
83                 $iCacheMemory = getCacheMemoryMB();
84                 echo "WARNING: resetting cache memory to $iCacheMemory\n";
85         }
86
87         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
88         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
89
90         if ($aCMDResult['create-db'] || $aCMDResult['all'])
91         {
92                 echo "Create DB\n";
93                 $bDidSomething = true;
94                 $oDB =& DB::connect(CONST_Database_DSN, false);
95                 if (!PEAR::isError($oDB))
96                 {
97                         fail('database already exists ('.CONST_Database_DSN.')');
98                 }
99                 passthruCheckReturn('createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
100         }
101
102         if ($aCMDResult['setup-db'] || $aCMDResult['all'])
103         {
104                 echo "Setup DB\n";
105                 $bDidSomething = true;
106                 // TODO: path detection, detection memory, etc.
107
108                 $oDB =& getDB();
109
110                 $sVersionString = $oDB->getOne('select version()');
111                 preg_match('#PostgreSQL ([0-9]+)[.]([0-9]+)[.]([0-9]+) #', $sVersionString, $aMatches);
112                 if (CONST_Postgresql_Version != $aMatches[1].'.'.$aMatches[2])
113                 {
114                         echo "ERROR: PostgreSQL version is not correct.  Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
115                         exit;
116                 }
117
118                 passthru('createlang plpgsql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
119                 $pgver = (float) CONST_Postgresql_Version;
120                 if ($pgver < 9.1) {
121                         pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
122                         pgsqlRunScriptFile(CONST_BasePath.'/sql/hstore_compatability_9_0.sql');
123                 } else {
124                         pgsqlRunScript('CREATE EXTENSION hstore');
125                 }
126
127                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
128                 $sVersionString = $oDB->getOne('select postgis_full_version()');
129                 preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches);
130                 if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2])
131                 {
132                         echo "ERROR: PostGIS version is not correct.  Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
133                         exit;
134                 }
135
136                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
137                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
138                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
139                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
140                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode.sql');
141                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
142                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
143                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
144                 pgsqlRunScriptFile(CONST_BasePath.'/data/worldboundaries.sql');
145         }
146
147         if ($aCMDResult['import-data'] || $aCMDResult['all'])
148         {
149                 echo "Import\n";
150                 $bDidSomething = true;
151
152                 $osm2pgsql = CONST_Osm2pgsql_Binary;
153                 if (!file_exists($osm2pgsql))
154                 {
155                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
156                         fail("osm2pgsql not found in '$osm2pgsql'");
157                 }
158                 $osm2pgsql .= ' --tablespace-slim-index ssd --tablespace-main-index ssd --tablespace-main-data ssd --tablespace-slim-data data';
159                 $osm2pgsql .= ' -lsc -O gazetteer --hstore';
160                 $osm2pgsql .= ' -C 16000';
161                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
162                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
163                 passthruCheckReturn($osm2pgsql);
164
165                 $oDB =& getDB();
166                 $x = $oDB->getRow('select * from place limit 1');
167                 if (PEAR::isError($x)) {
168                         fail($x->getMessage());
169                 }
170                 if (!$x) fail('No Data');
171         }
172
173         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
174         {
175                 echo "Functions\n";
176                 $bDidSomething = true;
177                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
178                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
179                 $sTemplate = str_replace('{modulepath}', CONST_BasePath.'/module', $sTemplate);
180                 if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
181                 if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
182                 pgsqlRunScript($sTemplate);
183         }
184
185         if ($aCMDResult['create-minimal-tables'])
186         {
187                 echo "Minimal Tables\n";
188                 $bDidSomething = true;
189                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
190
191                 $sScript = '';
192
193                 // Backstop the import process - easliest possible import id
194                 $sScript .= "insert into import_npi_log values (18022);\n";
195
196                 $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
197                 if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
198
199                 while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
200                 {
201                         list($sClass, $sType) = explode(' ', trim($sLine));
202                         $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
203                         $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
204
205                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
206                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
207
208                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
209                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
210                 }
211                 fclose($hFile);
212                 pgsqlRunScript($sScript);
213         }
214
215         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
216         {
217                 echo "Tables\n";
218                 $bDidSomething = true;
219                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
220
221                 // re-run the functions
222                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
223                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
224                 pgsqlRunScript($sTemplate);
225         }
226
227         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
228         {
229                 echo "Partition Tables\n";
230                 $bDidSomething = true;
231                 $oDB =& getDB();
232                 $sSQL = 'select partition from country_name order by country_code';
233                 $aPartitions = $oDB->getCol($sSQL);
234                 if (PEAR::isError($aPartitions))
235                 {
236                         fail($aPartitions->getMessage());
237                 }
238                 $aPartitions[] = 0;
239
240                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
241                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
242                 foreach($aMatches as $aMatch)
243                 {
244                         $sResult = '';
245                         foreach($aPartitions as $sPartitionName)
246                         {
247                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
248                         }
249                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
250                 }
251
252                 pgsqlRunScript($sTemplate);
253         }
254
255
256         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
257         {
258                 echo "Partition Functions\n";
259                 $bDidSomething = true;
260                 $oDB =& getDB();
261                 $sSQL = 'select partition from country_name order by country_code';
262                 $aPartitions = $oDB->getCol($sSQL);
263                 if (PEAR::isError($aPartitions))
264                 {
265                         fail($aPartitions->getMessage());
266                 }
267                 $aPartitions[] = 0;
268
269                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
270                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
271                 foreach($aMatches as $aMatch)
272                 {
273                         $sResult = '';
274                         foreach($aPartitions as $sPartitionName)
275                         {
276                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
277                         }
278                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
279                 }
280
281                 pgsqlRunScript($sTemplate);
282         }
283
284         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
285         {
286                 $bDidSomething = true;
287                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
288                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
289                 if (file_exists($sWikiArticlesFile))
290                 {
291                         echo "Importing wikipedia articles...";
292                         pgsqlRunDropAndRestore($sWikiArticlesFile);
293                         echo "...done\n";
294                 }
295                 else
296                 {
297                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
298                 }
299                 if (file_exists($sWikiRedirectsFile))
300                 {
301                         echo "Importing wikipedia redirects...";
302                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
303                         echo "...done\n";
304                 }
305                 else
306                 {
307                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
308                 }
309         }
310
311
312         if ($aCMDResult['load-data'] || $aCMDResult['all'])
313         {
314                 echo "Drop old Data\n";
315                 $bDidSomething = true;
316
317                 $oDB =& getDB();
318                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
319                 echo '.';
320                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
321                 echo '.';
322                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
323                 echo '.';
324                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
325                 echo '.';
326                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
327                 echo '.';
328                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
329                 echo '.';
330                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
331                 echo '.';
332                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
333                 echo '.';
334                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
335                 echo '.';
336
337                 $sSQL = 'select partition from country_name order by country_code';
338                 $aPartitions = $oDB->getCol($sSQL);
339                 if (PEAR::isError($aPartitions))
340                 {
341                         fail($aPartitions->getMessage());
342                 }
343                 $aPartitions[] = 0;
344                 foreach($aPartitions as $sPartition)
345                 {
346                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
347                         echo '.';
348                 }
349
350                 // used by getorcreate_word_id to ignore frequent partial words
351                 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));
352                 echo ".\n";
353
354                 // pre-create the word list
355                 if (!$aCMDResult['disable-token-precalc'])
356                 {
357                         echo "Loading word list\n";
358                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
359                 }
360
361                 echo "Load Data\n";
362                 $aDBInstances = array();
363                 for($i = 0; $i < $iInstances; $i++)
364                 {
365                         $aDBInstances[$i] =& getDB(true);
366                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
367                         $sSQL .= 'housenumber, street, isin, postcode, country_code, extratags, ';
368                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
369                         if ($aCMDResult['verbose']) echo "$sSQL\n";
370                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
371                 }
372                 $bAnyBusy = true;
373                 while($bAnyBusy)
374                 {
375                         $bAnyBusy = false;
376                         for($i = 0; $i < $iInstances; $i++)
377                         {
378                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
379                         }
380                         sleep(1);
381                         echo '.';
382                 }
383                 echo "\n";
384                 echo "Reanalysing database...\n";
385                 pgsqlRunScript('ANALYSE');
386         }
387
388         if ($aCMDResult['create-roads'])
389         {
390                 $bDidSomething = true;
391
392                 $oDB =& getDB();
393                 $aDBInstances = array();
394                 for($i = 0; $i < $iInstances; $i++)
395                 {
396                         $aDBInstances[$i] =& getDB(true);
397                         if (!pg_query($aDBInstances[$i]->connection, 'set enable_bitmapscan = off')) fail(pg_last_error($oDB->connection));
398                         $sSQL = 'select count(*) from (select insertLocationRoad(partition, place_id, calculated_country_code, geometry) from ';
399                         $sSQL .= 'placex where osm_id % '.$iInstances.' = '.$i.' and rank_search between 26 and 27 and class = \'highway\') as x ';
400                         if ($aCMDResult['verbose']) echo "$sSQL\n";
401                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
402                 }
403                 $bAnyBusy = true;
404                 while($bAnyBusy)
405                 {
406                         $bAnyBusy = false;
407                         for($i = 0; $i < $iInstances; $i++)
408                         {
409                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
410                         }
411                         sleep(1);
412                         echo '.';
413                 }
414                 echo "\n";
415         }
416
417         if ($aCMDResult['import-tiger-data'])
418         {
419                 $bDidSomething = true;
420
421                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_start.sql');
422
423                 $aDBInstances = array();
424                 for($i = 0; $i < $iInstances; $i++)
425                 {
426                         $aDBInstances[$i] =& getDB(true);
427                 }
428
429                 foreach(glob(CONST_BasePath.'/data/tiger2011/*.sql') as $sFile)
430                 {
431                         echo $sFile.': ';
432                         $hFile = fopen($sFile, "r");
433                         $sSQL = fgets($hFile, 100000);
434                         $iLines = 0;
435
436                         while(true)
437                         {
438                                 for($i = 0; $i < $iInstances; $i++)
439                                 {
440                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
441                                         {
442                                                 while(pg_get_result($aDBInstances[$i]->connection));
443                                                 $sSQL = fgets($hFile, 100000);
444                                                 if (!$sSQL) break 2;
445                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
446                                                 $iLines++;
447                                                 if ($iLines == 1000)
448                                                 {
449                                                         echo ".";
450                                                         $iLines = 0;
451                                                 }
452                                         }
453                                 }
454                                 usleep(10);
455                         }
456
457                         fclose($hFile);
458
459                         $bAnyBusy = true;
460                         while($bAnyBusy)
461                         {
462                                 $bAnyBusy = false;
463                                 for($i = 0; $i < $iInstances; $i++)
464                                 {
465                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
466                                 }
467                                 usleep(10);
468                         }
469                         echo "\n";
470                 }
471
472                 echo "Creating indexes\n";
473                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_finish.sql');
474         }
475
476         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
477         {
478                 $bDidSomething = true;
479                 $oDB =& getDB();
480                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
481                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
482                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
483                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
484                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
485                 $sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
486                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
487
488                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
489                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
490                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
491                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
492         }
493
494         if ($aCMDResult['osmosis-init'] || $aCMDResult['all'])
495         {
496                 $bDidSomething = true;
497                 $oDB =& getDB();
498
499                 if (!file_exists(CONST_Osmosis_Binary)) fail("please download osmosis");
500                 if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
501                 {
502                         echo "settings/configuration.txt already exists\n";
503                 }
504                 else
505                 {
506                         passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
507                         // server layout changed afer license change, fix path to minutely diffs
508                         passthru("sed -i 's:minute-replicate:replication/minute:' ".CONST_BasePath.'/settings/configuration.txt');
509                 }
510
511                 // Find the last node in the DB
512                 $iLastOSMID = $oDB->getOne("select max(osm_id) as osm_id from place where osm_type = 'N'");
513
514                 // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
515                 $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID;
516                 $sLastNodeXML = file_get_contents($sLastNodeURL);
517                 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);
518                 $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
519
520
521                 // Search for the correct state file - uses file timestamps
522                 $sRepURL = 'http://planet.openstreetmap.org/replication/minute/';
523                 $sRep = file_get_contents($sRepURL);
524                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a> *(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
525                 $aPrevRepMatch = false;
526                 foreach($aRepMatches as $aRepMatch)
527                 {
528                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
529                         $aPrevRepMatch = $aRepMatch;
530                 }
531                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
532
533                 $sRepURL .= $aRepMatch[1];
534                 $sRep = file_get_contents($sRepURL);
535                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a> *(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
536                 $aPrevRepMatch = false;
537                 foreach($aRepMatches as $aRepMatch)
538                 {
539                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
540                         $aPrevRepMatch = $aRepMatch;
541                 }
542                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
543
544                 $sRepURL .= $aRepMatch[1];
545                 $sRep = file_get_contents($sRepURL);
546                 preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a> *(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
547                 $aPrevRepMatch = false;
548                 foreach($aRepMatches as $aRepMatch)
549                 {
550                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
551                         $aPrevRepMatch = $aRepMatch;
552                 }
553                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
554
555                 $sRepURL .= $aRepMatch[1].'.state.txt';
556                 echo "Getting state file: $sRepURL\n";
557                 $sStateFile = file_get_contents($sRepURL);
558                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
559                 file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
560                 echo "Updating DB status\n";
561                 pg_query($oDB->connection, 'TRUNCATE import_status');
562                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
563                 pg_query($oDB->connection, $sSQL);
564         }
565
566         if ($aCMDResult['index'] || $aCMDResult['all'])
567         {
568                 $bDidSomething = true;
569                 $sOutputFile = '';
570                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
571                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
572                 passthruCheckReturn($sBaseCmd.' -R 4');
573                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
574                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
575                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
576                 passthruCheckReturn($sBaseCmd.' -r 26');
577         }
578
579         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
580         {
581                 echo "Search indices\n";
582                 $bDidSomething = true;
583                 $oDB =& getDB();
584                 $sSQL = 'select partition from country_name order by country_code';
585                 $aPartitions = $oDB->getCol($sSQL);
586                 if (PEAR::isError($aPartitions))
587                 {
588                         fail($aPartitions->getMessage());
589                 }
590                 $aPartitions[] = 0;
591
592                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
593                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
594                 foreach($aMatches as $aMatch)
595                 {
596                         $sResult = '';
597                         foreach($aPartitions as $sPartitionName)
598                         {
599                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
600                         }
601                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
602                 }
603
604                 pgsqlRunScript($sTemplate);
605         }
606
607         if (isset($aCMDResult['create-website']))
608         {
609                 $bDidSomething = true;
610                 $sTargetDir = $aCMDResult['create-website'];
611                 if (!is_dir($sTargetDir))
612                 {
613                         echo "You must create the website directory before calling this function.\n";
614                         fail("Target directory does not exist.");
615                 }
616
617                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
618                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
619                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
620                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
621                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
622                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
623                 @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
624                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
625                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
626                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
627                 echo "Symlinks created\n";
628         }
629
630         if (!$bDidSomething)
631         {
632                 showUsage($aCMDOptions, true);
633         }
634
635         function pgsqlRunScriptFile($sFilename)
636         {
637                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
638
639                 // Convert database DSN to psql parameters
640                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
641                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
642                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -f '.$sFilename;
643
644                 $aDescriptors = array(
645                         0 => array('pipe', 'r'),
646                         1 => array('pipe', 'w'),
647                         2 => array('file', '/dev/null', 'a')
648                 );
649                 $ahPipes = null;
650                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
651                 if (!is_resource($hProcess)) fail('unable to start pgsql');
652
653                 fclose($ahPipes[0]);
654
655                 // TODO: error checking
656                 while(!feof($ahPipes[1]))
657                 {
658                         echo fread($ahPipes[1], 4096);
659                 }
660                 fclose($ahPipes[1]);
661
662                 proc_close($hProcess);
663         }
664
665         function pgsqlRunScript($sScript)
666         {
667                 // Convert database DSN to psql parameters
668                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
669                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
670                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
671                 $aDescriptors = array(
672                         0 => array('pipe', 'r'),
673                         1 => STDOUT, 
674                         2 => STDERR
675                 );
676                 $ahPipes = null;
677                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
678                 if (!is_resource($hProcess)) fail('unable to start pgsql');
679
680                 while(strlen($sScript))
681                 {
682                         $written = fwrite($ahPipes[0], $sScript);
683                         $sScript = substr($sScript, $written);
684                 }
685                 fclose($ahPipes[0]);
686                 proc_close($hProcess);
687         }
688
689         function pgsqlRunRestoreData($sDumpFile)
690         {
691                 // Convert database DSN to psql parameters
692                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
693                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
694                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
695
696                 $aDescriptors = array(
697                         0 => array('pipe', 'r'),
698                         1 => array('pipe', 'w'),
699                         2 => array('file', '/dev/null', 'a')
700                 );
701                 $ahPipes = null;
702                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
703                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
704
705                 fclose($ahPipes[0]);
706
707                 // TODO: error checking
708                 while(!feof($ahPipes[1]))
709                 {
710                         echo fread($ahPipes[1], 4096);
711                 }
712                 fclose($ahPipes[1]);
713
714                 proc_close($hProcess);
715         }
716
717         function pgsqlRunDropAndRestore($sDumpFile)
718         {
719                 // Convert database DSN to psql parameters
720                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
721                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
722                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
723
724                 $aDescriptors = array(
725                         0 => array('pipe', 'r'),
726                         1 => array('pipe', 'w'),
727                         2 => array('file', '/dev/null', 'a')
728                 );
729                 $ahPipes = null;
730                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
731                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
732
733                 fclose($ahPipes[0]);
734
735                 // TODO: error checking
736                 while(!feof($ahPipes[1]))
737                 {
738                         echo fread($ahPipes[1], 4096);
739                 }
740                 fclose($ahPipes[1]);
741
742                 proc_close($hProcess);
743         }
744
745         function passthruCheckReturn($cmd)
746         {
747                 $result = -1;
748                 passthru($cmd, $result);
749                 if ($result != 0) fail('Error executing external command: '.$cmd);
750         }