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