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 = true 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_INT8)
 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(PGint64(*((uint64_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 from place_addressline join placex on (address_place_id = placex.place_id) where isaddress and 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 as 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
 184 xmlTextWriterPtr nominatim_exportXMLStart(const char *structuredoutputfile)
 185 {
 186     xmlTextWriterPtr writer;
 187
 188     writer = xmlNewTextWriterFilename(structuredoutputfile, 0);
 189         if (writer==NULL)
 190         {
 191                 fprintf(stderr, "Unable to open %s\n", structuredoutputfile);
 192                 exit(EXIT_FAILURE);
 193         }
 194         xmlTextWriterSetIndent(writer, 1);
 195     if (xmlTextWriterStartDocument(writer, NULL, "UTF8", NULL) < 0)
 196     {
 197                 fprintf(stderr, "xmlTextWriterStartDocument failed\n");
 198                 exit(EXIT_FAILURE);
 199     }
 200     if (xmlTextWriterStartElement(writer, BAD_CAST "osmStructured") < 0)
 201     {
 202                 fprintf(stderr, "xmlTextWriterStartElement failed\n");
 203                 exit(EXIT_FAILURE);
 204     }
 205     if (xmlTextWriterWriteAttribute(writer, BAD_CAST "version", BAD_CAST "0.1") < 0)
 206     {
 207                 fprintf(stderr, "xmlTextWriterWriteAttribute failed\n");
 208                 exit(EXIT_FAILURE);
 209     }
 210     if (xmlTextWriterWriteAttribute(writer, BAD_CAST "generator", BAD_CAST "Nominatim") < 0)
 211     {
 212                 fprintf(stderr, "xmlTextWriterWriteAttribute failed\n");
 213                 exit(EXIT_FAILURE);
 214     }
 215     if (xmlTextWriterStartElement(writer, BAD_CAST "add") < 0)
 216     {
 217                 fprintf(stderr, "xmlTextWriterStartElement failed\n");
 218                 exit(EXIT_FAILURE);
 219     }
 220
 221     return writer;
 222 }
 223
 224 void nominatim_exportXMLEnd(xmlTextWriterPtr writer)
 225 {
 226         // End <add>
 227     if (xmlTextWriterEndElement(writer) < 0)
 228     {
 229                 fprintf(stderr, "xmlTextWriterEndElement failed\n");
 230                 exit(EXIT_FAILURE);
 231     }
 232         // End <osmStructured>
 233     if (xmlTextWriterEndElement(writer) < 0)
 234     {
 235                 fprintf(stderr, "xmlTextWriterEndElement failed\n");
 236                 exit(EXIT_FAILURE);
 237     }
 238     if (xmlTextWriterEndDocument(writer) < 0)
 239     {
 240                 fprintf(stderr, "xmlTextWriterEndDocument failed\n");
 241                 exit(EXIT_FAILURE);
 242     }
 243     xmlFreeTextWriter(writer);
 244 }
 245
 246 /*
 247  * Requirements: the prepared queries must exist
 248  */
 249 void nominatim_exportPlace(uint64_t place_id, PGconn * conn, xmlTextWriterPtr writer, pthread_mutex_t * writer_mutex)
 250 {
 251         PGresult *              res;
 252         PGresult *              resNames;
 253         PGresult *              resAddress;
 254
 255         int                     i;
 256
 257     const char *        paramValues[1];
 258     int                 paramLengths[1];
 259     int                 paramFormats[1];
 260     uint64_t            paramPlaceID;
 261
 262
 263         paramPlaceID = PGint64(place_id);
 264     paramValues[0] = (char *)&paramPlaceID;
 265     paramLengths[0] = sizeof(paramPlaceID);
 266     paramFormats[0] = 1;
 267
 268     res = PQexecPrepared(conn, "placex_details", 1, paramValues, paramLengths, paramFormats, 0);
 269         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 270         {
 271                 fprintf(stderr, "placex_details: SELECT failed: %s", PQerrorMessage(conn));
 272                 PQclear(res);
 273                 exit(EXIT_FAILURE);
 274         }
 275
 276         resNames = PQexecPrepared(conn, "placex_names", 1, paramValues, paramLengths, paramFormats, 0);
 277         if (PQresultStatus(resNames) != PGRES_TUPLES_OK)
 278         {
 279                 fprintf(stderr, "placex_names: SELECT failed: %s", PQerrorMessage(conn));
 280                 PQclear(resNames);
 281                 exit(EXIT_FAILURE);
 282         }
 283
 284         resAddress = PQexecPrepared(conn, "placex_address", 1, paramValues, paramLengths, paramFormats, 0);
 285         if (PQresultStatus(resAddress) != PGRES_TUPLES_OK)
 286         {
 287                 fprintf(stderr, "placex_address: SELECT failed: %s", PQerrorMessage(conn));
 288                 PQclear(resAddress);
 289                 exit(EXIT_FAILURE);
 290         }
 291
 292         if (writer_mutex) pthread_mutex_lock( writer_mutex );
 293
 294         xmlTextWriterStartElement(writer, BAD_CAST "feature");
 295         xmlTextWriterWriteFormatAttribute(writer, BAD_CAST "place_id", "%li", place_id);
 296         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(res, 0, 0));
 297         xmlTextWriterWriteAttribute(writer, BAD_CAST "id", BAD_CAST PQgetvalue(res, 0, 1));
 298         xmlTextWriterWriteAttribute(writer, BAD_CAST "key", BAD_CAST PQgetvalue(res, 0, 2));
 299         xmlTextWriterWriteAttribute(writer, BAD_CAST "value", BAD_CAST PQgetvalue(res, 0, 3));
 300         xmlTextWriterWriteAttribute(writer, BAD_CAST "rank", BAD_CAST PQgetvalue(res, 0, 9));
 301         xmlTextWriterWriteAttribute(writer, BAD_CAST "importance", BAD_CAST PQgetvalue(res, 0, 10));
 302
 303         if (PQgetvalue(res, 0, 4) && strlen(PQgetvalue(res, 0, 4)))
 304         {
 305                 xmlTextWriterStartElement(writer, BAD_CAST "names");
 306
 307                 for(i = 0; i < PQntuples(resNames); i++)
 308                 {
 309                         xmlTextWriterStartElement(writer, BAD_CAST "name");
 310                         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(resNames, i, 0));
 311                         xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(resNames, i, 1));
 312                         xmlTextWriterEndElement(writer);
 313                 }
 314
 315                 xmlTextWriterEndElement(writer);
 316         }
 317
 318         if (PQgetvalue(res, 0, 5) && strlen(PQgetvalue(res, 0, 5)))
 319         {
 320                 xmlTextWriterStartElement(writer, BAD_CAST "houseNumber");
 321                 xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 5));
 322                 xmlTextWriterEndElement(writer);
 323         }
 324
 325         if (PQgetvalue(res, 0, 8) && strlen(PQgetvalue(res, 0, 8)))
 326         {
 327                 xmlTextWriterStartElement(writer, BAD_CAST "adminLevel");
 328                 xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 8));
 329                 xmlTextWriterEndElement(writer);
 330         }
 331
 332         if (PQgetvalue(res, 0, 6) && strlen(PQgetvalue(res, 0, 6)))
 333         {
 334                 xmlTextWriterStartElement(writer, BAD_CAST "countryCode");
 335                 xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 6));
 336                 xmlTextWriterEndElement(writer);
 337         }
 338
 339         if (PQntuples(resAddress) > 0)
 340         {
 341                 xmlTextWriterStartElement(writer, BAD_CAST "address");
 342                 for(i = 0; i < PQntuples(resAddress); i++)
 343                 {
 344                         xmlTextWriterStartElement(writer, BAD_CAST getRankLabel(atoi(PQgetvalue(resAddress, i, 5))));
 345                         xmlTextWriterWriteAttribute(writer, BAD_CAST "rank", BAD_CAST PQgetvalue(resAddress, i, 5));
 346                         xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(resAddress, i, 0));
 347                         xmlTextWriterWriteAttribute(writer, BAD_CAST "id", BAD_CAST PQgetvalue(resAddress, i, 1));
 348                         xmlTextWriterWriteAttribute(writer, BAD_CAST "key", BAD_CAST PQgetvalue(resAddress, i, 2));
 349                         xmlTextWriterWriteAttribute(writer, BAD_CAST "value", BAD_CAST PQgetvalue(resAddress, i, 3));
 350                         xmlTextWriterWriteAttribute(writer, BAD_CAST "distance", BAD_CAST PQgetvalue(resAddress, i, 4));
 351                         xmlTextWriterEndElement(writer);
 352                 }
 353                 xmlTextWriterEndElement(writer);
 354         }
 355
 356         xmlTextWriterStartElement(writer, BAD_CAST "osmGeometry");
 357         xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 7));
 358         xmlTextWriterEndElement(writer);
 359
 360         xmlTextWriterEndElement(writer); // </feature>
 361
 362         if (writer_mutex) pthread_mutex_unlock( writer_mutex );
 363
 364         PQclear(res);
 365         PQclear(resNames);
 366         PQclear(resAddress);
 367 }
 368
 369 const char * getRankLabel(int rank)
 370 {
 371         switch(rank)
 372         {
 373         case 0:
 374         case 1:
 375                 return "continent";
 376         case 2:
 377         case 3:
 378                 return "sea";
 379         case 4:
 380         case 5:
 381         case 6:
 382         case 7:
 383                 return "country";
 384         case 8:
 385         case 9:
 386         case 10:
 387         case 11:
 388                 return "state";
 389         case 12:
 390         case 13:
 391         case 14:
 392         case 15:
 393                 return "county";
 394         case 16:
 395                 return "city";
 396         case 17:
 397                 return "town";
 398         case 18:
 399                 return "village";
 400         case 19:
 401                 return "unknown";
 402         case 20:
 403                 return "suburb";
 404         case 21:
 405                 return "postcode";
 406         case 22:
 407                 return "neighborhood";
 408         case 23:
 409                 return "postcode";
 410         case 24:
 411                 return "unknown";
 412         case 25:
 413                 return "postcode";
 414         case 26:
 415                 return "street";
 416         case 27:
 417                 return "access";
 418         case 28:
 419                 return "building";
 420         case 29:
 421         default:
 422                 return "other";
 423         }
 424 }