void QgsOSMDatabase::exportSpatiaLiteNodes( const QString& tableName, const QStringList& tagKeys, const QStringList& notNullTagKeys )
{
QString sqlInsertPoint = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlInsertPoint += QString( ",?" );
sqlInsertPoint += ", GeomFromWKB(?, 4326))";
sqlite3_stmt* stmtInsert;
if ( sqlite3_prepare_v2( mDatabase, sqlInsertPoint.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK )
{
mError = "Prepare SELECT FROM nodes failed.";
return;
}
QgsOSMNodeIterator nodes = listNodes();
QgsOSMNode n;
while (( n = nodes.next() ).isValid() )
{
QgsOSMTags t = tags( false, n.id() );
// skip untagged nodes: probably they form a part of ways
if ( t.count() == 0 )
continue;
//check not null tags
bool skipNull = false;
for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i )
if ( !t.contains( notNullTagKeys[i] ) )
skipNull = true;
if ( skipNull )
continue;
QgsGeometry geom = QgsGeometry::fromPoint( n.point() );
int col = 0;
sqlite3_bind_int64( stmtInsert, ++col, n.id() );
// tags
for ( int i = 0; i < tagKeys.count(); ++i )
{
if ( t.contains( tagKeys[i] ) )
sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
else
sqlite3_bind_null( stmtInsert, ++col );
}
sqlite3_bind_blob( stmtInsert, ++col, geom.asWkb(), ( int ) geom.wkbSize(), SQLITE_STATIC );
int insertRes = sqlite3_step( stmtInsert );
if ( insertRes != SQLITE_DONE )
{
mError = QString( "Error inserting node %1 [%2]" ).arg( n.id() ).arg( insertRes );
break;
}
sqlite3_reset( stmtInsert );
sqlite3_clear_bindings( stmtInsert );
}
sqlite3_finalize( stmtInsert );
}
void TestOpenStreetMap::importAndQueries()
{
QString dbFilename = "/tmp/testdata.db";
QString xmlFilename = TEST_DATA_DIR "/openstreetmap/testdata.xml";
QgsOSMXmlImport import( xmlFilename, dbFilename );
bool res = import.import();
if ( import.hasError() )
qDebug( "XML ERR: %s", import.errorString().toAscii().data() );
QCOMPARE( res, true );
QCOMPARE( import.hasError(), false );
qDebug( "import finished" );
QgsOSMDatabase db( dbFilename );
bool dbopenRes = db.open();
if ( !db.errorString().isEmpty() )
qDebug( "DB ERR: %s", db.errorString().toAscii().data() );
QCOMPARE( dbopenRes, true );
// query node
QgsOSMNode n = db.node( 11111 );
QCOMPARE( n.isValid(), true );
QCOMPARE( n.point().x(), 14.4277148 );
QCOMPARE( n.point().y(), 50.0651387 );
QgsOSMNode nNot = db.node( 22222 );
QCOMPARE( nNot.isValid(), false );
// query node tags
QgsOSMTags tags = db.tags( false, 11111 );
QCOMPARE( tags.count(), 7 );
QCOMPARE( tags.value( "addr:postcode" ), QString( "12800" ) );
QgsOSMTags tags2 = db.tags( false, 360769661 );
QCOMPARE( tags2.count(), 0 );
QCOMPARE( tags2.value( "addr:postcode" ), QString() );
QgsOSMTags tagsNot = db.tags( false, 22222 );
QCOMPARE( tagsNot.count(), 0 );
// list nodes
QgsOSMNodeIterator nodes = db.listNodes();
QCOMPARE( nodes.next().id(), ( qint64 )11111 );
QCOMPARE( nodes.next().id(), ( qint64 )360769661 );
nodes.close();
// query way
QgsOSMWay w = db.way( 32137532 );
QCOMPARE( w.isValid(), true );
QCOMPARE( w.nodes().count(), 5 );
QCOMPARE( w.nodes()[0], ( qint64 )360769661 );
QCOMPARE( w.nodes()[1], ( qint64 )360769664 );
QgsOSMWay wNot = db.way( 1234567 );
QCOMPARE( wNot.isValid(), false );
// query way tags
QgsOSMTags tagsW = db.tags( true, 32137532 );
QCOMPARE( tagsW.count(), 3 );
QCOMPARE( tagsW.value( "building" ), QString( "yes" ) );
QgsOSMTags tagsWNot = db.tags( true, 1234567 );
QCOMPARE( tagsWNot.count(), 0 );
// list ways
QgsOSMWayIterator ways = db.listWays();
QCOMPARE( ways.next().id(), ( qint64 )32137532 );
QCOMPARE( ways.next().isValid(), false );
ways.close();
bool exportRes1 = db.exportSpatiaLite( QgsOSMDatabase::Point, "sl_points", QStringList( "addr:postcode" ) );
//bool exportRes = db.exportSpatiaLite( QStringList("amenity") << "name" << "highway" );
if ( !db.errorString().isEmpty() )
qDebug( "EXPORT-1 ERR: %s", db.errorString().toAscii().data() );
QCOMPARE( exportRes1, true );
bool exportRes2 = db.exportSpatiaLite( QgsOSMDatabase::Polyline, "sl_lines", QStringList( "building" ) );
//bool exportRes2 = db.exportSpatiaLite( QStringList("amenity") << "name" << "highway" );
if ( !db.errorString().isEmpty() )
qDebug( "EXPORT-2 ERR: %s", db.errorString().toAscii().data() );
QCOMPARE( exportRes2, true );
// TODO: test exported data
}