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 );
}
QgsOSMTags QgsOSMDatabase::tags( bool way, QgsOSMId id ) const
{
QgsOSMTags t;
sqlite3_stmt* stmtTags = way ? mStmtWayTags : mStmtNodeTags;
sqlite3_bind_int64( stmtTags, 1, id );
while ( sqlite3_step( stmtTags ) == SQLITE_ROW )
{
QString k = QString::fromUtf8(( const char* ) sqlite3_column_text( stmtTags, 0 ) );
QString v = QString::fromUtf8(( const char* ) sqlite3_column_text( stmtTags, 1 ) );
t.insert( k, v );
}
sqlite3_reset( stmtTags );
return t;
}
void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName,
const QStringList& tagKeys,
const QStringList& notNullTagKeys )
{
Q_UNUSED( tagKeys );
QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlInsertLine += QString( ",?" );
sqlInsertLine += ", GeomFromWKB(?, 4326))";
sqlite3_stmt* stmtInsert;
if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK )
{
mError = "Prepare SELECT FROM ways failed.";
return;
}
QgsOSMWayIterator ways = listWays();
QgsOSMWay w;
while (( w = ways.next() ).isValid() )
{
QgsOSMTags t = tags( true, w.id() );
QgsPolyline polyline = wayPoints( w.id() );
if ( polyline.count() < 2 )
continue; // invalid way
bool isArea = ( polyline.first() == polyline.last() ); // closed way?
// filter out closed way that are not areas through tags
if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) )
{
// make sure tags that indicate areas are taken into consideration when deciding on a closed way is or isn't an area
// 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
if (( t.value( "area" ) != "yes" && !t.contains( "amenity" ) && !t.contains( "landuse" ) && !t.contains( "building" ) && !t.contains( "natural" ) && !t.contains( "leisure" ) && !t.contains( "aeroway" ) ) || !closed )
isArea = false;
}
if ( closed != isArea )
continue; // skip if it's not what we're looking for
//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 = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline );
int col = 0;
sqlite3_bind_int64( stmtInsert, ++col, w.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 );
}
if ( !geom.isEmpty() )
sqlite3_bind_blob( stmtInsert, ++col, geom.asWkb(), ( int ) geom.wkbSize(), SQLITE_STATIC );
else
sqlite3_bind_null( stmtInsert, ++col );
int insertRes = sqlite3_step( stmtInsert );
if ( insertRes != SQLITE_DONE )
{
mError = QString( "Error inserting way %1 [%2]" ).arg( w.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
}
void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName, const QStringList& tagKeys )
{
Q_UNUSED( tagKeys );
QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlInsertLine += QString( ",?" );
sqlInsertLine += ", GeomFromWKB(?, 4326))";
sqlite3_stmt* stmtInsert;
if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, 0 ) != SQLITE_OK )
{
mError = "Prepare SELECT FROM ways failed.";
return;
}
QgsOSMWayIterator ways = listWays();
QgsOSMWay w;
while (( w = ways.next() ).isValid() )
{
QgsOSMTags t = tags( true, w.id() );
QgsPolyline polyline = wayPoints( w.id() );
if ( polyline.count() < 2 )
continue; // invalid way
bool isArea = ( polyline.first() == polyline.last() ); // closed way?
// some closed ways are not really areas
if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) )
{
if ( t.value( "area" ) != "yes" ) // even though "highway" is line by default, "area"="yes" may override that
isArea = false;
}
if ( closed != isArea )
continue; // skip if it's not what we're looking for
QgsGeometry* geom = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline );
int col = 0;
sqlite3_bind_int64( stmtInsert, ++col, w.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(), geom->wkbSize(), SQLITE_STATIC );
int insertRes = sqlite3_step( stmtInsert );
if ( insertRes != SQLITE_DONE )
{
mError = QString( "Error inserting way %1 [%2]" ).arg( w.id() ).arg( insertRes );
break;
}
sqlite3_reset( stmtInsert );
sqlite3_clear_bindings( stmtInsert );
delete geom;
}
sqlite3_finalize( stmtInsert );
}