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