nominatim/export.c

   1 /*
   2 */
   3
   4 #include <stdio.h>
   5 #include <unistd.h>
   6 #include <stdlib.h>
   7 #include <string.h>
   8 #include <assert.h>
   9 #include <time.h>
  10 #include <stdint.h>
  11 #include <pthread.h>
  12
  13 #include <libpq-fe.h>
  14
  15 #include "nominatim.h"
  16 #include "export.h"
  17 #include "postgresql.h"
  18
  19 extern int verbose;
  20
  21 void nominatim_export(int rank_min, int rank_max, const char *conninfo, const char *structuredoutputfile)
  22 {
  23     xmlTextWriterPtr writer;
  24
  25         int rankTotalDone;
  26
  27         PGconn *conn;
  28         PGresult * res;
  29         PGresult * resSectors;
  30         PGresult * resPlaces;
  31
  32         int rank;
  33         int i;
  34         int iSector;
  35     int tuples;
  36
  37     const char *paramValues[2];
  38     int         paramLengths[2];
  39     int         paramFormats[2];
  40     uint32_t    paramRank;
  41     uint32_t    paramSector;
  42     uint32_t    sector;
  43
  44     Oid pg_prepare_params[2];
  45
  46         conn = PQconnectdb(conninfo);
  47     if (PQstatus(conn) != CONNECTION_OK) {
  48         fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
  49         exit(EXIT_FAILURE);
  50     }
  51
  52     pg_prepare_params[0] = PG_OID_INT4;
  53     res = PQprepare(conn, "index_sectors",
  54         "select geometry_sector,count(*) from placex where rank_search = $1 and indexed_status = 0 group by geometry_sector order by geometry_sector",
  55         1, pg_prepare_params);
  56     if (PQresultStatus(res) != PGRES_COMMAND_OK) exit(EXIT_FAILURE);
  57     PQclear(res);
  58
  59     pg_prepare_params[0] = PG_OID_INT4;
  60     pg_prepare_params[1] = PG_OID_INT4;
  61     res = PQprepare(conn, "index_sector_places",
  62         "select place_id from placex where rank_search = $1 and geometry_sector = $2",
  63         2, pg_prepare_params);
  64     if (PQresultStatus(res) != PGRES_COMMAND_OK) exit(EXIT_FAILURE);
  65     PQclear(res);
  66
  67     nominatim_exportCreatePreparedQueries(conn);
  68
  69         // Create the output file
  70         writer = nominatim_exportXMLStart(structuredoutputfile);
  71
  72     for (rank = rank_min; rank <= rank_max; rank++)
  73     {
  74         printf("Starting rank %d\n", rank);
  75
  76         paramRank = PGint32(rank);
  77         paramValues[0] = (char *)&paramRank;
  78         paramLengths[0] = sizeof(paramRank);
  79         paramFormats[0] = 1;
  80         resSectors = PQexecPrepared(conn, "index_sectors", 1, paramValues, paramLengths, paramFormats, 1);
  81         if (PQresultStatus(resSectors) != PGRES_TUPLES_OK)
  82         {
  83             fprintf(stderr, "index_sectors: SELECT failed: %s", PQerrorMessage(conn));
  84             PQclear(resSectors);
  85             exit(EXIT_FAILURE);
  86         }
  87                 if (PQftype(resSectors, 0) != PG_OID_INT4)
  88                 {
  89             fprintf(stderr, "Sector value has unexpected type\n");
  90             PQclear(resSectors);
  91             exit(EXIT_FAILURE);
  92                 }
  93                 if (PQftype(resSectors, 1) != PG_OID_INT8)
  94                 {
  95             fprintf(stderr, "Sector value has unexpected type\n");
  96             PQclear(resSectors);
  97             exit(EXIT_FAILURE);
  98                 }
  99
 100                 rankTotalDone = 0;
 101         for (iSector = 0; iSector < PQntuples(resSectors); iSector++)
 102         {
 103                         sector = PGint32(*((uint32_t *)PQgetvalue(resSectors, iSector, 0)));
 104
 105                         // Get all the place_id's for this sector
 106                 paramRank = PGint32(rank);
 107                 paramValues[0] = (char *)&paramRank;
 108                 paramLengths[0] = sizeof(paramRank);
 109                 paramFormats[0] = 1;
 110                 paramSector = PGint32(sector);
 111                 paramValues[1] = (char *)&paramSector;
 112                 paramLengths[1] = sizeof(paramSector);
 113                 paramFormats[1] = 1;
 114                 resPlaces = PQexecPrepared(conn, "index_sector_places", 2, paramValues, paramLengths, paramFormats, 1);
 115                 if (PQresultStatus(resPlaces) != PGRES_TUPLES_OK)
 116                 {
 117                     fprintf(stderr, "index_sector_places: SELECT failed: %s", PQerrorMessage(conn));
 118                     PQclear(resPlaces);
 119                     exit(EXIT_FAILURE);
 120                 }
 121                         if (PQftype(resPlaces, 0) != PG_OID_INT4)
 122                         {
 123                     fprintf(stderr, "Place_id value has unexpected type\n");
 124                     PQclear(resPlaces);
 125                     exit(EXIT_FAILURE);
 126                         }
 127
 128                         tuples = PQntuples(resPlaces);
 129                         for(i = 0; i < tuples; i++)
 130                         {
 131                                 nominatim_exportPlace(PGint32(*((uint32_t *)PQgetvalue(resPlaces, i, 0))), conn, writer, NULL);
 132                                 rankTotalDone++;
 133                                 if (rankTotalDone%1000 == 0) printf("Done %i (k)\n", rankTotalDone/1000);
 134                 }
 135             PQclear(resPlaces);
 136         }
 137         PQclear(resSectors);
 138     }
 139
 140     nominatim_exportXMLEnd(writer);
 141
 142     PQfinish(conn);
 143 }
 144
 145 void nominatim_exportCreatePreparedQueries(PGconn * conn)
 146 {
 147     Oid pg_prepare_params[2];
 148         PGresult * res;
 149
 150     pg_prepare_params[0] = PG_OID_INT8;
 151     res = PQprepare(conn, "placex_details",
 152         "select osm_type, osm_id, class, type, name, housenumber, country_code, ST_AsText(geometry), admin_level, rank_address, rank_search from placex where place_id = $1",
 153         1, pg_prepare_params);
 154     if (PQresultStatus(res) != PGRES_COMMAND_OK)
 155         {
 156                 fprintf(stderr, "Error preparing placex_details: %s", PQerrorMessage(conn));
 157                 exit(EXIT_FAILURE);
 158         }
 159     PQclear(res);
 160
 161     pg_prepare_params[0] = PG_OID_INT8;
 162     res = PQprepare(conn, "placex_address",
 163         "select osm_type,osm_id,class,type,distance,cached_rank_address,isaddress from place_addressline join placex on (address_place_id = placex.place_id) where place_addressline.place_id = $1 and address_place_id != place_addressline.place_id order by cached_rank_address asc",
 164         1, pg_prepare_params);
 165     if (PQresultStatus(res) != PGRES_COMMAND_OK)
 166         {
 167                 fprintf(stderr, "Error preparing placex_address: %s", PQerrorMessage(conn));
 168                 exit(EXIT_FAILURE);
 169         }
 170     PQclear(res);
 171
 172     pg_prepare_params[0] = PG_OID_INT8;
 173     res = PQprepare(conn, "placex_names",
 174         "select (each(name)).key,(each(name)).value from (select name from placex where place_id = $1) as x",
 175         1, pg_prepare_params);
 176     if (PQresultStatus(res) != PGRES_COMMAND_OK)
 177         {
 178                 fprintf(stderr, "Error preparing placex_names: %s", PQerrorMessage(conn));
 179                 exit(EXIT_FAILURE);
 180         }
 181     PQclear(res);
 182
 183     pg_prepare_params[0] = PG_OID_INT8;
 184     res = PQprepare(conn, "placex_extratags",
 185         "select (each(extratags)).key,(each(extratags)).value from (select extratags from placex where place_id = $1) as x",
 186         1, pg_prepare_params);
 187     if (PQresultStatus(res) != PGRES_COMMAND_OK)
 188         {
 189                 fprintf(stderr, "Error preparing placex_extratags: %s", PQerrorMessage(conn));
 190                 exit(EXIT_FAILURE);
 191         }
 192     PQclear(res);
 193 }
 194
 195 xmlTextWriterPtr nominatim_exportXMLStart(const char *structuredoutputfile)
 196 {
 197     xmlTextWriterPtr writer;
 198
 199     writer = xmlNewTextWriterFilename(structuredoutputfile, 0);
 200         if (writer==NULL)
 201         {
 202                 fprintf(stderr, "Unable to open %s\n", structuredoutputfile);
 203                 exit(EXIT_FAILURE);
 204         }
 205         xmlTextWriterSetIndent(writer, 1);
 206     if (xmlTextWriterStartDocument(writer, NULL, "UTF8", NULL) < 0)
 207     {
 208                 fprintf(stderr, "xmlTextWriterStartDocument failed\n");
 209                 exit(EXIT_FAILURE);
 210     }
 211     if (xmlTextWriterStartElement(writer, BAD_CAST "osmStructured") < 0)
 212     {
 213                 fprintf(stderr, "xmlTextWriterStartElement failed\n");
 214                 exit(EXIT_FAILURE);
 215     }
 216     if (xmlTextWriterWriteAttribute(writer, BAD_CAST "version", BAD_CAST "0.1") < 0)
 217     {
 218                 fprintf(stderr, "xmlTextWriterWriteAttribute failed\n");
 219                 exit(EXIT_FAILURE);
 220     }
 221     if (xmlTextWriterWriteAttribute(writer, BAD_CAST "generator", BAD_CAST "Nominatim") < 0)
 222     {
 223                 fprintf(stderr, "xmlTextWriterWriteAttribute failed\n");
 224                 exit(EXIT_FAILURE);
 225     }
 226     if (xmlTextWriterStartElement(writer, BAD_CAST "add") < 0)
 227     {
 228                 fprintf(stderr, "xmlTextWriterStartElement failed\n");
 229                 exit(EXIT_FAILURE);
 230     }
 231
 232     return writer;
 233 }
 234
 235 void nominatim_exportXMLEnd(xmlTextWriterPtr writer)
 236 {
 237         // End <add>
 238     if (xmlTextWriterEndElement(writer) < 0)
 239     {
 240                 fprintf(stderr, "xmlTextWriterEndElement failed\n");
 241                 exit(EXIT_FAILURE);
 242     }
 243         // End <osmStructured>
 244     if (xmlTextWriterEndElement(writer) < 0)
 245     {
 246                 fprintf(stderr, "xmlTextWriterEndElement failed\n");
 247                 exit(EXIT_FAILURE);
 248     }
 249     if (xmlTextWriterEndDocument(writer) < 0)
 250     {
 251                 fprintf(stderr, "xmlTextWriterEndDocument failed\n");
 252                 exit(EXIT_FAILURE);
 253     }
 254     xmlFreeTextWriter(writer);
 255 }
 256
 257 /*
 258  * Requirements: the prepared queries must exist
 259  */
 260 void nominatim_exportPlace(uint64_t place_id, PGconn * conn, xmlTextWriterPtr writer, pthread_mutex_t * writer_mutex)
 261 {
 262         PGresult *              res;
 263         PGresult *              resNames;
 264         PGresult *              resAddress;
 265         PGresult *              resExtraTags;
 266
 267         int                     i;
 268
 269     const char *        paramValues[1];
 270     int                 paramLengths[1];
 271     int                 paramFormats[1];
 272     uint64_t            paramPlaceID;
 273
 274
 275         paramPlaceID = PGint64(place_id);
 276     paramValues[0] = (char *)&paramPlaceID;
 277     paramLengths[0] = sizeof(paramPlaceID);
 278     paramFormats[0] = 1;
 279
 280     res = PQexecPrepared(conn, "placex_details", 1, paramValues, paramLengths, paramFormats, 0);
 281         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 282         {
 283                 fprintf(stderr, "placex_details: SELECT failed: %s", PQerrorMessage(conn));
 284                 PQclear(res);
 285                 exit(EXIT_FAILURE);
 286         }
 287
 288         resNames = PQexecPrepared(conn, "placex_names", 1, paramValues, paramLengths, paramFormats, 0);
 289         if (PQresultStatus(resNames) != PGRES_TUPLES_OK)
 290         {
 291                 fprintf(stderr, "placex_names: SELECT failed: %s", PQerrorMessage(conn));
 292                 PQclear(resNames);
 293                 exit(EXIT_FAILURE);
 294         }
 295
 296         resAddress = PQexecPrepared(conn, "placex_address", 1, paramValues, paramLengths, paramFormats, 0);
 297         if (PQresultStatus(resAddress) != PGRES_TUPLES_OK)
 298         {
 299                 fprintf(stderr, "placex_address: SELECT failed: %s", PQerrorMessage(conn));
 300                 PQclear(resAddress);
 301                 exit(EXIT_FAILURE);
 302         }
 303
 304         resExtraTags = PQexecPrepared(conn, "placex_extratags", 1, paramValues, paramLengths, paramFormats, 0);
 305         if (PQresultStatus(resExtraTags) != PGRES_TUPLES_OK)
 306         {
 307                 fprintf(stderr, "placex_extratags: SELECT failed: %s", PQerrorMessage(conn));
 308                 PQclear(resExtraTags);
 309                 exit(EXIT_FAILURE);
 310         }
 311
 312         if (writer_mutex) pthread_mutex_lock( writer_mutex );
 313
 314         xmlTextWriterStartElement(writer, BAD_CAST "feature");
 315         xmlTextWriterWriteFormatAttribute(writer, BAD_CAST "place_id", "%li", place_id);
 316         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(res, 0, 0));
 317         xmlTextWriterWriteAttribute(writer, BAD_CAST "id", BAD_CAST PQgetvalue(res, 0, 1));
 318         xmlTextWriterWriteAttribute(writer, BAD_CAST "key", BAD_CAST PQgetvalue(res, 0, 2));
 319         xmlTextWriterWriteAttribute(writer, BAD_CAST "value", BAD_CAST PQgetvalue(res, 0, 3));
 320         xmlTextWriterWriteAttribute(writer, BAD_CAST "rank", BAD_CAST PQgetvalue(res, 0, 9));
 321         xmlTextWriterWriteAttribute(writer, BAD_CAST "importance", BAD_CAST PQgetvalue(res, 0, 10));
 322
 323         if (PQntuples(resNames))
 324         {
 325                 xmlTextWriterStartElement(writer, BAD_CAST "names");
 326
 327                 for(i = 0; i < PQntuples(resNames); i++)
 328                 {
 329                         xmlTextWriterStartElement(writer, BAD_CAST "name");
 330                         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(resNames, i, 0));
 331                         xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(resNames, i, 1));
 332                         xmlTextWriterEndElement(writer);
 333                 }
 334
 335                 xmlTextWriterEndElement(writer);
 336         }
 337
 338         if (PQgetvalue(res, 0, 5) && strlen(PQgetvalue(res, 0, 5)))
 339         {
 340                 xmlTextWriterStartElement(writer, BAD_CAST "houseNumber");
 341                 xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 5));
 342                 xmlTextWriterEndElement(writer);
 343         }
 344
 345         if (PQgetvalue(res, 0, 8) && strlen(PQgetvalue(res, 0, 8)))
 346         {
 347                 xmlTextWriterStartElement(writer, BAD_CAST "adminLevel");
 348                 xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 8));
 349                 xmlTextWriterEndElement(writer);
 350         }
 351
 352         if (PQgetvalue(res, 0, 6) && strlen(PQgetvalue(res, 0, 6)))
 353         {
 354                 xmlTextWriterStartElement(writer, BAD_CAST "countryCode");
 355                 xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 6));
 356                 xmlTextWriterEndElement(writer);
 357         }
 358
 359         if (PQntuples(resAddress) > 0)
 360         {
 361                 xmlTextWriterStartElement(writer, BAD_CAST "address");
 362                 for(i = 0; i < PQntuples(resAddress); i++)
 363                 {
 364                         xmlTextWriterStartElement(writer, BAD_CAST getRankLabel(atoi(PQgetvalue(resAddress, i, 5))));
 365                         xmlTextWriterWriteAttribute(writer, BAD_CAST "rank", BAD_CAST PQgetvalue(resAddress, i, 5));
 366                         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(resAddress, i, 0));
 367                         xmlTextWriterWriteAttribute(writer, BAD_CAST "id", BAD_CAST PQgetvalue(resAddress, i, 1));
 368                         xmlTextWriterWriteAttribute(writer, BAD_CAST "key", BAD_CAST PQgetvalue(resAddress, i, 2));
 369                         xmlTextWriterWriteAttribute(writer, BAD_CAST "value", BAD_CAST PQgetvalue(resAddress, i, 3));
 370                         xmlTextWriterWriteAttribute(writer, BAD_CAST "distance", BAD_CAST PQgetvalue(resAddress, i, 4));
 371                         xmlTextWriterWriteAttribute(writer, BAD_CAST "isaddress", BAD_CAST PQgetvalue(resAddress, i, 6));
 372                         xmlTextWriterEndElement(writer);
 373                 }
 374                 xmlTextWriterEndElement(writer);
 375         }
 376
 377         if (PQntuples(resExtraTags))
 378         {
 379                 xmlTextWriterStartElement(writer, BAD_CAST "tags");
 380
 381                 for(i = 0; i < PQntuples(resExtraTags); i++)
 382                 {
 383                         xmlTextWriterStartElement(writer, BAD_CAST "tag");
 384                         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(resExtraTags, i, 0));
 385                         xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(resExtraTags, i, 1));
 386                         xmlTextWriterEndElement(writer);
 387                 }
 388
 389                 xmlTextWriterEndElement(writer);
 390         }
 391
 392
 393         xmlTextWriterStartElement(writer, BAD_CAST "osmGeometry");
 394         xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 7));
 395         xmlTextWriterEndElement(writer);
 396
 397         xmlTextWriterEndElement(writer); // </feature>
 398
 399         if (writer_mutex) pthread_mutex_unlock( writer_mutex );
 400
 401         PQclear(res);
 402         PQclear(resNames);
 403         PQclear(resAddress);
 404 }
 405
 406 const char * getRankLabel(int rank)
 407 {
 408         switch(rank)
 409         {
 410         case 0:
 411         case 1:
 412                 return "continent";
 413         case 2:
 414         case 3:
 415                 return "sea";
 416         case 4:
 417         case 5:
 418         case 6:
 419         case 7:
 420                 return "country";
 421         case 8:
 422         case 9:
 423         case 10:
 424         case 11:
 425                 return "state";
 426         case 12:
 427         case 13:
 428         case 14:
 429         case 15:
 430                 return "county";
 431         case 16:
 432                 return "city";
 433         case 17:
 434                 return "town";
 435         case 18:
 436                 return "village";
 437         case 19:
 438                 return "unknown";
 439         case 20:
 440                 return "suburb";
 441         case 21:
 442                 return "postcode";
 443         case 22:
 444                 return "neighborhood";
 445         case 23:
 446                 return "postcode";
 447         case 24:
 448                 return "unknown";
 449         case 25:
 450                 return "postcode";
 451         case 26:
 452                 return "street";
 453         case 27:
 454                 return "access";
 455         case 28:
 456                 return "building";
 457         case 29:
 458         default:
 459                 return "other";
 460         }
 461 }