QGIS API Documentation  2.8.2-Wien
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
qgsosmdatabase.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgsosmdatabase.cpp
3  --------------------------------------
4  Date : January 2013
5  Copyright : (C) 2013 by Martin Dobias
6  Email : wonder dot sk at gmail dot com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 
16 #include "qgsosmdatabase.h"
17 
18 #include <spatialite.h>
19 
20 #include "qgsgeometry.h"
21 #include "qgslogger.h"
22 
23 
24 
25 QgsOSMDatabase::QgsOSMDatabase( const QString& dbFileName )
26  : mDbFileName( dbFileName )
27  , mDatabase( 0 )
28  , mStmtNode( 0 )
29  , mStmtNodeTags( 0 )
30  , mStmtWay( 0 )
31  , mStmtWayNode( 0 )
32  , mStmtWayNodePoints( 0 )
33  , mStmtWayTags( 0 )
34 {
35 
36 }
37 
39 {
40  if ( isOpen() )
41  close();
42 }
43 
45 {
46  return mDatabase != 0;
47 }
48 
49 
51 {
52  // load spatialite extension
53  spatialite_init( 0 );
54 
55  // open database
56  int res = sqlite3_open_v2( mDbFileName.toUtf8().data(), &mDatabase, SQLITE_OPEN_READWRITE, 0 );
57  if ( res != SQLITE_OK )
58  {
59  mError = QString( "Failed to open database [%1]: %2" ).arg( res ).arg( mDbFileName );
60  close();
61  return false;
62  }
63 
64  if ( !prepareStatements() )
65  {
66  close();
67  return false;
68  }
69 
70  return true;
71 }
72 
73 
74 void QgsOSMDatabase::deleteStatement( sqlite3_stmt*& stmt )
75 {
76  if ( stmt )
77  {
78  sqlite3_finalize( stmt );
79  stmt = 0;
80  }
81 }
82 
83 
85 {
86  deleteStatement( mStmtNode );
87  deleteStatement( mStmtNodeTags );
88  deleteStatement( mStmtWay );
89  deleteStatement( mStmtWayNode );
90  deleteStatement( mStmtWayNodePoints );
91  deleteStatement( mStmtWayTags );
92 
93  Q_ASSERT( mStmtNode == 0 );
94 
95  // close database
96  if ( sqlite3_close( mDatabase ) != SQLITE_OK )
97  {
98  //mError = ( char * ) "Closing SQLite3 database failed.";
99  //return false;
100  }
101  mDatabase = 0;
102  return true;
103 }
104 
105 
106 int QgsOSMDatabase::runCountStatement( const char* sql ) const
107 {
108  sqlite3_stmt* stmt;
109  int res = sqlite3_prepare_v2( mDatabase, sql, -1, &stmt, 0 );
110  if ( res != SQLITE_OK )
111  return -1;
112 
113  res = sqlite3_step( stmt );
114  if ( res != SQLITE_ROW )
115  return -1;
116 
117  int count = sqlite3_column_int( stmt, 0 );
118  sqlite3_finalize( stmt );
119  return count;
120 }
121 
122 
124 {
125  return runCountStatement( "SELECT count(*) FROM nodes" );
126 }
127 
129 {
130  return runCountStatement( "SELECT count(*) FROM ways" );
131 }
132 
133 
135 {
136  return QgsOSMNodeIterator( mDatabase );
137 }
138 
140 {
141  return QgsOSMWayIterator( mDatabase );
142 }
143 
144 
146 {
147  // bind the way identifier
148  sqlite3_bind_int64( mStmtNode, 1, id );
149 
150  if ( sqlite3_step( mStmtNode ) != SQLITE_ROW )
151  {
152  //QgsDebugMsg( "Cannot get number of way members." );
153  sqlite3_reset( mStmtNode );
154  return QgsOSMNode();
155  }
156 
157  double lon = sqlite3_column_double( mStmtNode, 0 );
158  double lat = sqlite3_column_double( mStmtNode, 1 );
159 
160  QgsOSMNode node( id, QgsPoint( lon, lat ) );
161 
162  sqlite3_reset( mStmtNode );
163  return node;
164 }
165 
167 {
168  QgsOSMTags t;
169 
170  sqlite3_stmt* stmtTags = way ? mStmtWayTags : mStmtNodeTags;
171 
172  sqlite3_bind_int64( stmtTags, 1, id );
173 
174  while ( sqlite3_step( stmtTags ) == SQLITE_ROW )
175  {
176  QString k = QString::fromUtf8(( const char* ) sqlite3_column_text( stmtTags, 0 ) );
177  QString v = QString::fromUtf8(( const char* ) sqlite3_column_text( stmtTags, 1 ) );
178  t.insert( k, v );
179  }
180 
181  sqlite3_reset( stmtTags );
182  return t;
183 }
184 
185 
186 QList<QgsOSMTagCountPair> QgsOSMDatabase::usedTags( bool ways ) const
187 {
188  QList<QgsOSMTagCountPair> pairs;
189 
190  QString sql = QString( "SELECT k, count(k) FROM %1_tags GROUP BY k" ).arg( ways ? "ways" : "nodes" );
191 
192  sqlite3_stmt* stmt;
193  if ( sqlite3_prepare_v2( mDatabase, sql.toUtf8().data(), -1, &stmt, 0 ) != SQLITE_OK )
194  return pairs;
195 
196  while ( sqlite3_step( stmt ) == SQLITE_ROW )
197  {
198  QString k = QString::fromUtf8(( const char* ) sqlite3_column_text( stmt, 0 ) );
199  int count = sqlite3_column_int( stmt, 1 );
200  pairs.append( qMakePair( k, count ) );
201  }
202 
203  sqlite3_finalize( stmt );
204  return pairs;
205 }
206 
207 
208 
210 {
211  // TODO: first check that way exists!
212  // mStmtWay
213 
214  // bind the way identifier
215  sqlite3_bind_int64( mStmtWayNode, 1, id );
216 
217  QList<QgsOSMId> nodes;
218 
219  while ( sqlite3_step( mStmtWayNode ) == SQLITE_ROW )
220  {
221  QgsOSMId nodeId = sqlite3_column_int64( mStmtWayNode, 0 );
222  nodes.append( nodeId );
223  }
224 
225  sqlite3_reset( mStmtWayNode );
226 
227  if ( nodes.isEmpty() )
228  return QgsOSMWay();
229 
230  return QgsOSMWay( id, nodes );
231 }
232 
233 /*
234 OSMRelation OSMDatabase::relation( OSMId id ) const
235 {
236  // todo
237  Q_UNUSED(id);
238  return OSMRelation();
239 }*/
240 
242 {
243  QgsPolyline points;
244 
245  // bind the way identifier
246  sqlite3_bind_int64( mStmtWayNodePoints, 1, id );
247 
248  while ( sqlite3_step( mStmtWayNodePoints ) == SQLITE_ROW )
249  {
250  if ( sqlite3_column_type( mStmtWayNodePoints, 0 ) == SQLITE_NULL )
251  return QgsPolyline(); // missing some nodes
252  double lon = sqlite3_column_double( mStmtWayNodePoints, 0 );
253  double lat = sqlite3_column_double( mStmtWayNodePoints, 1 );
254  points.append( QgsPoint( lon, lat ) );
255  }
256 
257  sqlite3_reset( mStmtWayNodePoints );
258  return points;
259 }
260 
261 
262 
264 {
265  const char* sql[] =
266  {
267  "SELECT lon,lat FROM nodes WHERE id=?",
268  "SELECT k,v FROM nodes_tags WHERE id=?",
269  "SELECT id FROM ways WHERE id=?",
270  "SELECT node_id FROM ways_nodes WHERE way_id=? ORDER BY way_pos",
271  "SELECT n.lon, n.lat FROM ways_nodes wn LEFT JOIN nodes n ON wn.node_id = n.id WHERE wn.way_id=? ORDER BY wn.way_pos",
272  "SELECT k,v FROM ways_tags WHERE id=?"
273  };
274  sqlite3_stmt** sqlite[] =
275  {
276  &mStmtNode,
277  &mStmtNodeTags,
278  &mStmtWay,
279  &mStmtWayNode,
280  &mStmtWayNodePoints,
281  &mStmtWayTags
282  };
283  int count = sizeof( sql ) / sizeof( const char* );
284  Q_ASSERT( count == sizeof( sqlite ) / sizeof( sqlite3_stmt** ) );
285 
286  for ( int i = 0; i < count; ++i )
287  {
288  if ( sqlite3_prepare_v2( mDatabase, sql[i], -1, sqlite[i], 0 ) != SQLITE_OK )
289  {
290  const char* errMsg = sqlite3_errmsg( mDatabase ); // does not require free
291  mError = QString( "Error preparing SQL command:\n%1\nSQL:\n%2" )
292  .arg( QString::fromUtf8( errMsg ) ).arg( QString::fromUtf8( sql[i] ) );
293  return false;
294  }
295  }
296 
297  return true;
298 }
299 
300 
301 
302 bool QgsOSMDatabase::exportSpatiaLite( ExportType type, const QString& tableName, const QStringList& tagKeys )
303 {
304  mError.clear();
305 
306  // create SpatiaLite table
307 
308  QString geometryType;
309  if ( type == Point ) geometryType = "POINT";
310  else if ( type == Polyline ) geometryType = "LINESTRING";
311  else if ( type == Polygon ) geometryType = "POLYGON";
312  else Q_ASSERT( false && "Unknown export type" );
313 
314  if ( !createSpatialTable( tableName, geometryType, tagKeys ) )
315  return false;
316 
317  // import data
318 
319  int retX = sqlite3_exec( mDatabase, "BEGIN", NULL, NULL, 0 );
320  Q_ASSERT( retX == SQLITE_OK );
321  Q_UNUSED( retX );
322 
323  if ( type == Polyline || type == Polygon )
324  exportSpatiaLiteWays( type == Polygon, tableName, tagKeys );
325  else if ( type == Point )
326  exportSpatiaLiteNodes( tableName, tagKeys );
327  else
328  Q_ASSERT( false && "Unknown export type" );
329 
330  int retY = sqlite3_exec( mDatabase, "COMMIT", NULL, NULL, 0 );
331  Q_ASSERT( retY == SQLITE_OK );
332  Q_UNUSED( retY );
333 
334  if ( !createSpatialIndex( tableName ) )
335  return false;
336 
337  return mError.isEmpty();
338 }
339 
340 
341 bool QgsOSMDatabase::createSpatialTable( const QString& tableName, const QString& geometryType, const QStringList& tagKeys )
342 {
343  QString sqlCreateTable = QString( "CREATE TABLE %1 (id INTEGER PRIMARY KEY" ).arg( quotedIdentifier( tableName ) );
344  for ( int i = 0; i < tagKeys.count(); ++i )
345  sqlCreateTable += QString( ", %1 TEXT" ).arg( quotedIdentifier( tagKeys[i] ) );
346  sqlCreateTable += ")";
347 
348  char *errMsg = NULL;
349  int ret = sqlite3_exec( mDatabase, sqlCreateTable.toUtf8().constData(), NULL, NULL, &errMsg );
350  if ( ret != SQLITE_OK )
351  {
352  mError = "Unable to create table:\n" + QString::fromUtf8( errMsg );
353  sqlite3_free( errMsg );
354  return false;
355  }
356 
357  QString sqlAddGeomColumn = QString( "SELECT AddGeometryColumn(%1, 'geometry', 4326, %2, 'XY')" )
358  .arg( quotedValue( tableName ) )
359  .arg( quotedValue( geometryType ) );
360  ret = sqlite3_exec( mDatabase, sqlAddGeomColumn.toUtf8().constData(), NULL, NULL, &errMsg );
361  if ( ret != SQLITE_OK )
362  {
363  mError = "Unable to add geometry column:\n" + QString::fromUtf8( errMsg );
364  sqlite3_free( errMsg );
365  return false;
366  }
367 
368  return true;
369 }
370 
371 
372 bool QgsOSMDatabase::createSpatialIndex( const QString& tableName )
373 {
374  QString sqlSpatialIndex = QString( "SELECT CreateSpatialIndex(%1, 'geometry')" ).arg( quotedValue( tableName ) );
375  char *errMsg = NULL;
376  int ret = sqlite3_exec( mDatabase, sqlSpatialIndex.toUtf8().constData(), NULL, NULL, &errMsg );
377  if ( ret != SQLITE_OK )
378  {
379  mError = "Unable to create spatial index:\n" + QString::fromUtf8( errMsg );
380  sqlite3_free( errMsg );
381  return false;
382  }
383 
384  return true;
385 }
386 
387 
388 void QgsOSMDatabase::exportSpatiaLiteNodes( const QString& tableName, const QStringList& tagKeys )
389 {
390  QString sqlInsertPoint = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
391  for ( int i = 0; i < tagKeys.count(); ++i )
392  sqlInsertPoint += QString( ",?" );
393  sqlInsertPoint += ", GeomFromWKB(?, 4326))";
394  sqlite3_stmt* stmtInsert;
395  if ( sqlite3_prepare_v2( mDatabase, sqlInsertPoint.toUtf8().constData(), -1, &stmtInsert, 0 ) != SQLITE_OK )
396  {
397  mError = "Prepare SELECT FROM nodes failed.";
398  return;
399  }
400 
401  QgsOSMNodeIterator nodes = listNodes();
402  QgsOSMNode n;
403  while (( n = nodes.next() ).isValid() )
404  {
405  QgsOSMTags t = tags( false, n.id() );
406 
407  // skip untagged nodes: probably they form a part of ways
408  if ( t.count() == 0 )
409  continue;
410 
412  int col = 0;
413  sqlite3_bind_int64( stmtInsert, ++col, n.id() );
414 
415  // tags
416  for ( int i = 0; i < tagKeys.count(); ++i )
417  {
418  if ( t.contains( tagKeys[i] ) )
419  sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
420  else
421  sqlite3_bind_null( stmtInsert, ++col );
422  }
423 
424  sqlite3_bind_blob( stmtInsert, ++col, geom->asWkb(), ( int ) geom->wkbSize(), SQLITE_STATIC );
425 
426  int insertRes = sqlite3_step( stmtInsert );
427  if ( insertRes != SQLITE_DONE )
428  {
429  mError = QString( "Error inserting node %1 [%2]" ).arg( n.id() ).arg( insertRes );
430  delete geom;
431  break;
432  }
433 
434  sqlite3_reset( stmtInsert );
435  sqlite3_clear_bindings( stmtInsert );
436  delete geom;
437  }
438 
439  sqlite3_finalize( stmtInsert );
440 }
441 
442 
443 void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName, const QStringList& tagKeys )
444 {
445  Q_UNUSED( tagKeys );
446 
447  QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
448  for ( int i = 0; i < tagKeys.count(); ++i )
449  sqlInsertLine += QString( ",?" );
450  sqlInsertLine += ", GeomFromWKB(?, 4326))";
451  sqlite3_stmt* stmtInsert;
452  if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, 0 ) != SQLITE_OK )
453  {
454  mError = "Prepare SELECT FROM ways failed.";
455  return;
456  }
457 
458  QgsOSMWayIterator ways = listWays();
459  QgsOSMWay w;
460  while (( w = ways.next() ).isValid() )
461  {
462  QgsOSMTags t = tags( true, w.id() );
463 
464  QgsPolyline polyline = wayPoints( w.id() );
465 
466  if ( polyline.count() < 2 )
467  continue; // invalid way
468 
469  bool isArea = ( polyline.first() == polyline.last() ); // closed way?
470  // some closed ways are not really areas
471  if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) )
472  {
473  if ( t.value( "area" ) != "yes" ) // even though "highway" is line by default, "area"="yes" may override that
474  isArea = false;
475  }
476 
477  if ( closed != isArea )
478  continue; // skip if it's not what we're looking for
479 
480  QgsGeometry* geom = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline );
481  int col = 0;
482  sqlite3_bind_int64( stmtInsert, ++col, w.id() );
483 
484  // tags
485  for ( int i = 0; i < tagKeys.count(); ++i )
486  {
487  if ( t.contains( tagKeys[i] ) )
488  sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
489  else
490  sqlite3_bind_null( stmtInsert, ++col );
491  }
492 
493  if ( geom )
494  sqlite3_bind_blob( stmtInsert, ++col, geom->asWkb(), ( int ) geom->wkbSize(), SQLITE_STATIC );
495  else
496  sqlite3_bind_null( stmtInsert, ++col );
497 
498  int insertRes = sqlite3_step( stmtInsert );
499  if ( insertRes != SQLITE_DONE )
500  {
501  mError = QString( "Error inserting way %1 [%2]" ).arg( w.id() ).arg( insertRes );
502  delete geom;
503  break;
504  }
505 
506  sqlite3_reset( stmtInsert );
507  sqlite3_clear_bindings( stmtInsert );
508  delete geom;
509  }
510 
511  sqlite3_finalize( stmtInsert );
512 }
513 
514 
515 
516 QString QgsOSMDatabase::quotedIdentifier( QString id )
517 {
518  id.replace( "\"", "\"\"" );
519  return QString( "\"%1\"" ).arg( id );
520 }
521 
522 QString QgsOSMDatabase::quotedValue( QString value )
523 {
524  if ( value.isNull() )
525  return "NULL";
526 
527  value.replace( "'", "''" );
528  return QString( "'%1'" ).arg( value );
529 }
530 
532 
533 
535  : mStmt( 0 )
536 {
537  const char* sql = "SELECT id,lon,lat FROM nodes";
538  if ( sqlite3_prepare_v2( handle, sql, -1, &mStmt, 0 ) != SQLITE_OK )
539  {
540  qDebug( "OSMNodeIterator: error prepare" );
541  }
542 }
543 
545 {
546  close();
547 }
548 
549 
551 {
552  if ( !mStmt )
553  return QgsOSMNode();
554 
555  if ( sqlite3_step( mStmt ) != SQLITE_ROW )
556  {
557  close();
558  return QgsOSMNode();
559  }
560 
561  QgsOSMId id = sqlite3_column_int64( mStmt, 0 );
562  double lon = sqlite3_column_double( mStmt, 1 );
563  double lat = sqlite3_column_double( mStmt, 2 );
564 
565  return QgsOSMNode( id, QgsPoint( lon, lat ) );
566 }
567 
569 {
570  if ( mStmt )
571  {
572  sqlite3_finalize( mStmt );
573  mStmt = 0;
574  }
575 }
576 
578 
579 
581  : mStmt( 0 )
582 {
583  const char* sql = "SELECT id FROM ways";
584  if ( sqlite3_prepare_v2( handle, sql, -1, &mStmt, 0 ) != SQLITE_OK )
585  {
586  qDebug( "OSMWayIterator: error prepare" );
587  }
588 }
589 
591 {
592  close();
593 }
594 
595 
597 {
598  if ( !mStmt )
599  return QgsOSMWay();
600 
601  if ( sqlite3_step( mStmt ) != SQLITE_ROW )
602  {
603  close();
604  return QgsOSMWay();
605  }
606 
607  QgsOSMId id = sqlite3_column_int64( mStmt, 0 );
608 
609  return QgsOSMWay( id, QList<QgsOSMId>() ); // TODO[MD]: ?
610 }
611 
613 {
614  if ( mStmt )
615  {
616  sqlite3_finalize( mStmt );
617  mStmt = 0;
618  }
619 }