QGIS API Documentation  2.18.21-Las Palmas (9fba24a)
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 #include "qgsslconnect.h"
18 #include "qgsgeometry.h"
19 #include "qgslogger.h"
20 
21 
23  : mDbFileName( dbFileName )
24  , mDatabase( nullptr )
25  , mStmtNode( nullptr )
26  , mStmtNodeTags( nullptr )
27  , mStmtWay( nullptr )
28  , mStmtWayNode( nullptr )
29  , mStmtWayNodePoints( nullptr )
30  , mStmtWayTags( nullptr )
31 {
32 }
33 
35 {
36  if ( isOpen() )
37  close();
38 }
39 
41 {
42  return nullptr != mDatabase;
43 }
44 
45 
47 {
48  // open database
49  int res = QgsSLConnect::sqlite3_open_v2( mDbFileName.toUtf8().data(), &mDatabase, SQLITE_OPEN_READWRITE, nullptr );
50  if ( res != SQLITE_OK )
51  {
52  mError = QString( "Failed to open database [%1]: %2" ).arg( res ).arg( mDbFileName );
53  close();
54  return false;
55  }
56 
57  if ( !prepareStatements() )
58  {
59  close();
60  return false;
61  }
62 
63  return true;
64 }
65 
66 
67 void QgsOSMDatabase::deleteStatement( sqlite3_stmt*& stmt )
68 {
69  if ( stmt )
70  {
71  sqlite3_finalize( stmt );
72  stmt = nullptr;
73  }
74 }
75 
76 
78 {
79  deleteStatement( mStmtNode );
80  deleteStatement( mStmtNodeTags );
81  deleteStatement( mStmtWay );
82  deleteStatement( mStmtWayNode );
83  deleteStatement( mStmtWayNodePoints );
84  deleteStatement( mStmtWayTags );
85 
86  Q_ASSERT( !mStmtNode );
87 
88  // close database
89  if ( QgsSLConnect::sqlite3_close( mDatabase ) != SQLITE_OK )
90  {
91  //mError = ( char * ) "Closing SQLite3 database failed.";
92  //return false;
93  }
94  mDatabase = nullptr;
95  return true;
96 }
97 
98 
99 int QgsOSMDatabase::runCountStatement( const char* sql ) const
100 {
101  sqlite3_stmt* stmt;
102  int res = sqlite3_prepare_v2( mDatabase, sql, -1, &stmt, nullptr );
103  if ( res != SQLITE_OK )
104  return -1;
105 
106  res = sqlite3_step( stmt );
107  if ( res != SQLITE_ROW )
108  return -1;
109 
110  int count = sqlite3_column_int( stmt, 0 );
111  sqlite3_finalize( stmt );
112  return count;
113 }
114 
115 
117 {
118  return runCountStatement( "SELECT count(*) FROM nodes" );
119 }
120 
122 {
123  return runCountStatement( "SELECT count(*) FROM ways" );
124 }
125 
126 
128 {
129  return QgsOSMNodeIterator( mDatabase );
130 }
131 
133 {
134  return QgsOSMWayIterator( mDatabase );
135 }
136 
137 
139 {
140  // bind the way identifier
141  sqlite3_bind_int64( mStmtNode, 1, id );
142 
143  if ( sqlite3_step( mStmtNode ) != SQLITE_ROW )
144  {
145  //QgsDebugMsg( "Cannot get number of way members." );
146  sqlite3_reset( mStmtNode );
147  return QgsOSMNode();
148  }
149 
150  double lon = sqlite3_column_double( mStmtNode, 0 );
151  double lat = sqlite3_column_double( mStmtNode, 1 );
152 
153  QgsOSMNode node( id, QgsPoint( lon, lat ) );
154 
155  sqlite3_reset( mStmtNode );
156  return node;
157 }
158 
160 {
161  QgsOSMTags t;
162 
163  sqlite3_stmt* stmtTags = way ? mStmtWayTags : mStmtNodeTags;
164 
165  sqlite3_bind_int64( stmtTags, 1, id );
166 
167  while ( sqlite3_step( stmtTags ) == SQLITE_ROW )
168  {
169  QString k = QString::fromUtf8(( const char* ) sqlite3_column_text( stmtTags, 0 ) );
170  QString v = QString::fromUtf8(( const char* ) sqlite3_column_text( stmtTags, 1 ) );
171  t.insert( k, v );
172  }
173 
174  sqlite3_reset( stmtTags );
175  return t;
176 }
177 
178 
180 {
182 
183  QString sql = QString( "SELECT k, count(k) FROM %1_tags GROUP BY k" ).arg( ways ? "ways" : "nodes" );
184 
185  sqlite3_stmt* stmt;
186  if ( sqlite3_prepare_v2( mDatabase, sql.toUtf8().data(), -1, &stmt, nullptr ) != SQLITE_OK )
187  return pairs;
188 
189  while ( sqlite3_step( stmt ) == SQLITE_ROW )
190  {
191  QString k = QString::fromUtf8(( const char* ) sqlite3_column_text( stmt, 0 ) );
192  int count = sqlite3_column_int( stmt, 1 );
193  pairs.append( qMakePair( k, count ) );
194  }
195 
196  sqlite3_finalize( stmt );
197  return pairs;
198 }
199 
200 
201 
203 {
204  // TODO: first check that way exists!
205  // mStmtWay
206 
207  // bind the way identifier
208  sqlite3_bind_int64( mStmtWayNode, 1, id );
209 
210  QList<QgsOSMId> nodes;
211 
212  while ( sqlite3_step( mStmtWayNode ) == SQLITE_ROW )
213  {
214  QgsOSMId nodeId = sqlite3_column_int64( mStmtWayNode, 0 );
215  nodes.append( nodeId );
216  }
217 
218  sqlite3_reset( mStmtWayNode );
219 
220  if ( nodes.isEmpty() )
221  return QgsOSMWay();
222 
223  return QgsOSMWay( id, nodes );
224 }
225 
226 /*
227 OSMRelation OSMDatabase::relation( OSMId id ) const
228 {
229  // todo
230  Q_UNUSED(id);
231  return OSMRelation();
232 }*/
233 
235 {
236  QgsPolyline points;
237 
238  // bind the way identifier
239  sqlite3_bind_int64( mStmtWayNodePoints, 1, id );
240 
241  while ( sqlite3_step( mStmtWayNodePoints ) == SQLITE_ROW )
242  {
243  if ( sqlite3_column_type( mStmtWayNodePoints, 0 ) == SQLITE_NULL )
244  return QgsPolyline(); // missing some nodes
245  double lon = sqlite3_column_double( mStmtWayNodePoints, 0 );
246  double lat = sqlite3_column_double( mStmtWayNodePoints, 1 );
247  points.append( QgsPoint( lon, lat ) );
248  }
249 
250  sqlite3_reset( mStmtWayNodePoints );
251  return points;
252 }
253 
254 
255 
257 {
258  const char* sql[] =
259  {
260  "SELECT lon,lat FROM nodes WHERE id=?",
261  "SELECT k,v FROM nodes_tags WHERE id=?",
262  "SELECT id FROM ways WHERE id=?",
263  "SELECT node_id FROM ways_nodes WHERE way_id=? ORDER BY way_pos",
264  "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",
265  "SELECT k,v FROM ways_tags WHERE id=?"
266  };
267  sqlite3_stmt** sqlite[] =
268  {
269  &mStmtNode,
270  &mStmtNodeTags,
271  &mStmtWay,
272  &mStmtWayNode,
273  &mStmtWayNodePoints,
274  &mStmtWayTags
275  };
276  int count = sizeof( sql ) / sizeof( const char* );
277  Q_ASSERT( count == sizeof( sqlite ) / sizeof( sqlite3_stmt** ) );
278 
279  for ( int i = 0; i < count; ++i )
280  {
281  if ( sqlite3_prepare_v2( mDatabase, sql[i], -1, sqlite[i], nullptr ) != SQLITE_OK )
282  {
283  const char* errMsg = sqlite3_errmsg( mDatabase ); // does not require free
284  mError = QString( "Error preparing SQL command:\n%1\nSQL:\n%2" )
285  .arg( QString::fromUtf8( errMsg ), QString::fromUtf8( sql[i] ) );
286  return false;
287  }
288  }
289 
290  return true;
291 }
292 
294  const QStringList& tagKeys,
295  const QStringList& notNullTagKeys )
296 {
297  mError.clear();
298 
299  // create SpatiaLite table
300 
301  QString geometryType;
302  if ( type == Point ) geometryType = "POINT";
303  else if ( type == Polyline ) geometryType = "LINESTRING";
304  else if ( type == Polygon ) geometryType = "POLYGON";
305  else Q_ASSERT( false && "Unknown export type" );
306 
307  if ( !createSpatialTable( tableName, geometryType, tagKeys ) )
308  return false;
309 
310  // import data
311 
312  int retX = sqlite3_exec( mDatabase, "BEGIN", nullptr, nullptr, nullptr );
313  Q_ASSERT( retX == SQLITE_OK );
314  Q_UNUSED( retX );
315 
316  if ( type == Polyline || type == Polygon )
317  exportSpatiaLiteWays( type == Polygon, tableName, tagKeys, notNullTagKeys );
318  else if ( type == Point )
319  exportSpatiaLiteNodes( tableName, tagKeys, notNullTagKeys );
320  else
321  Q_ASSERT( false && "Unknown export type" );
322 
323  int retY = sqlite3_exec( mDatabase, "COMMIT", nullptr, nullptr, nullptr );
324  Q_ASSERT( retY == SQLITE_OK );
325  Q_UNUSED( retY );
326 
327  if ( !createSpatialIndex( tableName ) )
328  return false;
329 
330  return mError.isEmpty();
331 }
332 
333 
334 bool QgsOSMDatabase::createSpatialTable( const QString& tableName, const QString& geometryType, const QStringList& tagKeys )
335 {
336  QString sqlCreateTable = QString( "CREATE TABLE %1 (id INTEGER PRIMARY KEY" ).arg( quotedIdentifier( tableName ) );
337  for ( int i = 0; i < tagKeys.count(); ++i )
338  sqlCreateTable += QString( ", %1 TEXT" ).arg( quotedIdentifier( tagKeys[i] ) );
339  sqlCreateTable += ')';
340 
341  char *errMsg = nullptr;
342  int ret = sqlite3_exec( mDatabase, sqlCreateTable.toUtf8().constData(), nullptr, nullptr, &errMsg );
343  if ( ret != SQLITE_OK )
344  {
345  mError = "Unable to create table:\n" + QString::fromUtf8( errMsg );
346  sqlite3_free( errMsg );
347  return false;
348  }
349 
350  QString sqlAddGeomColumn = QString( "SELECT AddGeometryColumn(%1, 'geometry', 4326, %2, 'XY')" )
351  .arg( quotedValue( tableName ),
352  quotedValue( geometryType ) );
353  ret = sqlite3_exec( mDatabase, sqlAddGeomColumn.toUtf8().constData(), nullptr, nullptr, &errMsg );
354  if ( ret != SQLITE_OK )
355  {
356  mError = "Unable to add geometry column:\n" + QString::fromUtf8( errMsg );
357  sqlite3_free( errMsg );
358  return false;
359  }
360 
361  return true;
362 }
363 
364 
366 {
367  QString sqlSpatialIndex = QString( "SELECT CreateSpatialIndex(%1, 'geometry')" ).arg( quotedValue( tableName ) );
368  char *errMsg = nullptr;
369  int ret = sqlite3_exec( mDatabase, sqlSpatialIndex.toUtf8().constData(), nullptr, nullptr, &errMsg );
370  if ( ret != SQLITE_OK )
371  {
372  mError = "Unable to create spatial index:\n" + QString::fromUtf8( errMsg );
373  sqlite3_free( errMsg );
374  return false;
375  }
376 
377  return true;
378 }
379 
380 
381 void QgsOSMDatabase::exportSpatiaLiteNodes( const QString& tableName, const QStringList& tagKeys, const QStringList& notNullTagKeys )
382 {
383  QString sqlInsertPoint = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
384  for ( int i = 0; i < tagKeys.count(); ++i )
385  sqlInsertPoint += QString( ",?" );
386  sqlInsertPoint += ", GeomFromWKB(?, 4326))";
387  sqlite3_stmt* stmtInsert;
388  if ( sqlite3_prepare_v2( mDatabase, sqlInsertPoint.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK )
389  {
390  mError = "Prepare SELECT FROM nodes failed.";
391  return;
392  }
393 
394  QgsOSMNodeIterator nodes = listNodes();
395  QgsOSMNode n;
396  while (( n = nodes.next() ).isValid() )
397  {
398  QgsOSMTags t = tags( false, n.id() );
399 
400  // skip untagged nodes: probably they form a part of ways
401  if ( t.count() == 0 )
402  continue;
403 
404  //check not null tags
405  bool skipNull = false;
406  for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i )
407  if ( !t.contains( notNullTagKeys[i] ) )
408  skipNull = true;
409 
410  if ( skipNull )
411  continue;
412 
414  int col = 0;
415  sqlite3_bind_int64( stmtInsert, ++col, n.id() );
416 
417  // tags
418  for ( int i = 0; i < tagKeys.count(); ++i )
419  {
420  if ( t.contains( tagKeys[i] ) )
421  sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
422  else
423  sqlite3_bind_null( stmtInsert, ++col );
424  }
425 
426  sqlite3_bind_blob( stmtInsert, ++col, geom->asWkb(), ( int ) geom->wkbSize(), SQLITE_STATIC );
427 
428  int insertRes = sqlite3_step( stmtInsert );
429  if ( insertRes != SQLITE_DONE )
430  {
431  mError = QString( "Error inserting node %1 [%2]" ).arg( n.id() ).arg( insertRes );
432  delete geom;
433  break;
434  }
435 
436  sqlite3_reset( stmtInsert );
437  sqlite3_clear_bindings( stmtInsert );
438  delete geom;
439  }
440 
441  sqlite3_finalize( stmtInsert );
442 }
443 
444 
445 void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName,
446  const QStringList& tagKeys,
447  const QStringList& notNullTagKeys )
448 {
449  Q_UNUSED( tagKeys );
450 
451  QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
452  for ( int i = 0; i < tagKeys.count(); ++i )
453  sqlInsertLine += QString( ",?" );
454  sqlInsertLine += ", GeomFromWKB(?, 4326))";
455  sqlite3_stmt* stmtInsert;
456  if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK )
457  {
458  mError = "Prepare SELECT FROM ways failed.";
459  return;
460  }
461 
462  QgsOSMWayIterator ways = listWays();
463  QgsOSMWay w;
464  while (( w = ways.next() ).isValid() )
465  {
466  QgsOSMTags t = tags( true, w.id() );
467 
468  QgsPolyline polyline = wayPoints( w.id() );
469 
470  if ( polyline.count() < 2 )
471  continue; // invalid way
472 
473  bool isArea = ( polyline.first() == polyline.last() ); // closed way?
474  // filter out closed way that are not areas through tags
475  if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) )
476  {
477  // make sure tags that indicate areas are taken into consideration when deciding on a closed way is or isn't an area
478  // and allow for a closed way to be exported both as a polygon and a line in case both area and non-area tags are present
479  if (( t.value( "area" ) != "yes" && !t.contains( "amenity" ) && !t.contains( "landuse" ) && !t.contains( "building" ) && !t.contains( "natural" ) && !t.contains( "leisure" ) && !t.contains( "aeroway" ) ) || !closed )
480  isArea = false;
481  }
482 
483  if ( closed != isArea )
484  continue; // skip if it's not what we're looking for
485 
486  //check not null tags
487  bool skipNull = false;
488  for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i )
489  if ( !t.contains( notNullTagKeys[i] ) )
490  skipNull = true;
491 
492  if ( skipNull )
493  continue;
494 
495  QgsGeometry* geom = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline );
496  int col = 0;
497  sqlite3_bind_int64( stmtInsert, ++col, w.id() );
498 
499  // tags
500  for ( int i = 0; i < tagKeys.count(); ++i )
501  {
502  if ( t.contains( tagKeys[i] ) )
503  sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
504  else
505  sqlite3_bind_null( stmtInsert, ++col );
506  }
507 
508  if ( geom )
509  sqlite3_bind_blob( stmtInsert, ++col, geom->asWkb(), ( int ) geom->wkbSize(), SQLITE_STATIC );
510  else
511  sqlite3_bind_null( stmtInsert, ++col );
512 
513  int insertRes = sqlite3_step( stmtInsert );
514  if ( insertRes != SQLITE_DONE )
515  {
516  mError = QString( "Error inserting way %1 [%2]" ).arg( w.id() ).arg( insertRes );
517  delete geom;
518  break;
519  }
520 
521  sqlite3_reset( stmtInsert );
522  sqlite3_clear_bindings( stmtInsert );
523  delete geom;
524  }
525 
526  sqlite3_finalize( stmtInsert );
527 }
528 
529 
530 
532 {
533  id.replace( '\"', "\"\"" );
534  return QString( "\"%1\"" ).arg( id );
535 }
536 
538 {
539  if ( value.isNull() )
540  return "NULL";
541 
542  value.replace( '\'', "''" );
543  return QString( "'%1'" ).arg( value );
544 }
545 
547 
548 
550  : mStmt( nullptr )
551 {
552  const char* sql = "SELECT id,lon,lat FROM nodes";
553  if ( sqlite3_prepare_v2( handle, sql, -1, &mStmt, nullptr ) != SQLITE_OK )
554  {
555  qDebug( "OSMNodeIterator: error prepare" );
556  }
557 }
558 
560 {
561  close();
562 }
563 
564 
566 {
567  if ( !mStmt )
568  return QgsOSMNode();
569 
570  if ( sqlite3_step( mStmt ) != SQLITE_ROW )
571  {
572  close();
573  return QgsOSMNode();
574  }
575 
576  QgsOSMId id = sqlite3_column_int64( mStmt, 0 );
577  double lon = sqlite3_column_double( mStmt, 1 );
578  double lat = sqlite3_column_double( mStmt, 2 );
579 
580  return QgsOSMNode( id, QgsPoint( lon, lat ) );
581 }
582 
584 {
585  if ( mStmt )
586  {
587  sqlite3_finalize( mStmt );
588  mStmt = nullptr;
589  }
590 }
591 
593 
594 
596  : mStmt( nullptr )
597 {
598  const char* sql = "SELECT id FROM ways";
599  if ( sqlite3_prepare_v2( handle, sql, -1, &mStmt, nullptr ) != SQLITE_OK )
600  {
601  qDebug( "OSMWayIterator: error prepare" );
602  }
603 }
604 
606 {
607  close();
608 }
609 
610 
612 {
613  if ( !mStmt )
614  return QgsOSMWay();
615 
616  if ( sqlite3_step( mStmt ) != SQLITE_ROW )
617  {
618  close();
619  return QgsOSMWay();
620  }
621 
622  QgsOSMId id = sqlite3_column_int64( mStmt, 0 );
623 
624  return QgsOSMWay( id, QList<QgsOSMId>() ); // TODO[MD]: ?
625 }
626 
628 {
629  if ( mStmt )
630  {
631  sqlite3_finalize( mStmt );
632  mStmt = nullptr;
633  }
634 }
QgsOSMWayIterator(sqlite3 *handle)
A way is an ordered list of nodes which normally also has at least one tag or is included within a Re...
Definition: qgsosmbase.h:93
QgsOSMTags tags(bool way, QgsOSMId id) const
QgsOSMNode node(QgsOSMId id) const
void append(const T &value)
bool isOpen() const
Encapsulate iteration over table of ways.
QVector< QgsPoint > QgsPolyline
Polyline is represented as a vector of points.
Definition: qgsgeometry.h:44
int count() const
Definition: qgsosmbase.h:136
A geometry is the spatial representation of a feature.
Definition: qgsgeometry.h:76
QgsOSMWayIterator listWays() const
sqlite3_stmt * mStmt
qint64 QgsOSMId
Definition: qgsosmbase.h:24
int wkbSize() const
Returns the size of the WKB in asWkb().
static int sqlite3_close(sqlite3 *)
bool isNull() const
void clear()
sqlite3_stmt * mStmt
void exportSpatiaLiteNodes(const QString &tableName, const QStringList &tagKeys, const QStringList &notNullTagKeys=QStringList())
int count(const T &value) const
void append(const T &value)
QString fromUtf8(const char *str, int size)
void insert(const QString &k, const QString &v)
Definition: qgsosmbase.h:139
QgsOSMWay way(QgsOSMId id) const
This class is a container of tags for a node, way or a relation.
Definition: qgsosmbase.h:131
QList< QgsOSMTagCountPair > usedTags(bool ways) const
int countNodes() const
static int sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs)
QString quotedIdentifier(QString id)
bool isEmpty() const
bool isEmpty() const
const char * constData() const
A node is one of the core elements in the OpenStreetMap data model.
Definition: qgsosmbase.h:70
const unsigned char * asWkb() const
Returns the buffer containing this geometry in WKB format.
QString value(const QString &k) const
Definition: qgsosmbase.h:140
QVector< QgsPolyline > QgsPolygon
Polygon: first item of the list is outer ring, inner rings (if any) start from second item...
Definition: qgsgeometry.h:50
A class to represent a point.
Definition: qgspoint.h:117
static QgsGeometry * fromPoint(const QgsPoint &point)
Creates a new geometry from a QgsPoint object.
void deleteStatement(sqlite3_stmt *&stmt)
struct sqlite3 sqlite3
int countWays() const
bool createSpatialTable(const QString &tableName, const QString &geometryType, const QStringList &tagKeys)
bool exportSpatiaLite(ExportType type, const QString &tableName, const QStringList &tagKeys=QStringList(), const QStringList &noNullTagKeys=QStringList())
QString & replace(int position, int n, QChar after)
QgsPoint point() const
Definition: qgsosmbase.h:79
QgsOSMId id() const
Definition: qgsosmbase.h:53
QString quotedValue(QString value)
Encapsulate iteration over table of nodes/.
QgsOSMDatabase(const QString &dbFileName=QString())
QgsOSMNodeIterator listNodes() const
char * data()
static QgsGeometry * fromPolyline(const QgsPolyline &polyline)
Creates a new geometry from a QgsPolyline object.
bool createSpatialIndex(const QString &tableName)
QgsPolyline wayPoints(QgsOSMId id) const
static QgsGeometry * fromPolygon(const QgsPolygon &polygon)
Creates a new geometry from a QgsPolygon.
int runCountStatement(const char *sql) const
QgsOSMNodeIterator(sqlite3 *handle)
void exportSpatiaLiteWays(bool closed, const QString &tableName, const QStringList &tagKeys, const QStringList &notNullTagKeys=QStringList())
QString arg(qlonglong a, int fieldWidth, int base, const QChar &fillChar) const
bool contains(const QString &k) const
Definition: qgsosmbase.h:138
QByteArray toUtf8() const