]> git.openstreetmap.org Git - nominatim.git/blob - nominatim/export.c
images and javascript
[nominatim.git] / 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 }