Skip to content

Commit

Permalink
[postgis] expose additional geometry columns as QgsReferencedGeometry
Browse files Browse the repository at this point in the history 
Good bye ewkt
  • Loading branch information
@m-kuhn
m-kuhn committed Nov 19, 2021
1 parent 8e46f2c commit e3fa91b
Show file tree
Hide file tree
Showing 4 changed files with 138 additions and 69 deletions.
10 changes: 5 additions & 5 deletions src/providers/postgres/qgspostgresfeatureiterator.cpp
View file
Expand Up @@ -875,7 +875,7 @@ bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int
int idx = mSource->mPrimaryKeyAttrs.at( 0 );
QgsField fld = mSource->mFields.at( idx );

QVariant v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), QString::number( mConn->getBinaryInt( queryResult, row, col ) ), fld.typeName() );
QVariant v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), QString::number( mConn->getBinaryInt( queryResult, row, col ) ), fld.typeName(), mConn );
pkVal << v;

if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
Expand All @@ -900,11 +900,11 @@ bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int

if ( fld.type() == QVariant::LongLong )
{
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), QString::number( mConn->getBinaryInt( queryResult, row, col ) ), fld.typeName() );
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), QString::number( mConn->getBinaryInt( queryResult, row, col ) ), fld.typeName(), mConn );
}
else
{
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ), fld.typeName() );
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ), fld.typeName(), mConn );
}
primaryKeyVals << v;

Expand Down Expand Up @@ -986,13 +986,13 @@ void QgsPostgresFeatureIterator::getFeatureAttribute( int idx, QgsPostgresResult
}
else
{
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), QString::number( mConn->getBinaryInt( queryResult, row, col ) ), fld.typeName() );
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), QString::number( mConn->getBinaryInt( queryResult, row, col ) ), fld.typeName(), mConn );
}
break;
}
default:
{
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ), fld.typeName() );
v = QgsPostgresProvider::convertValue( fld.type(), fld.subType(), queryResult.PQgetvalue( row, col ), fld.typeName(), mConn );
break;
}
}
Expand Down
151 changes: 106 additions & 45 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -351,7 +351,83 @@ QString QgsPostgresProvider::providerKey()
void QgsPostgresProvider::setTransaction( QgsTransaction *transaction )
{
// static_cast since layers cannot be added to a transaction of a non-matching provider
mTransaction = static_cast<QgsPostgresTransaction *>( transaction );
mTransaction = static_cast<QgsPostgresTransaction *>( transaction );
}

struct Ewkt
{
int srid = -1;
QString wkt;
};

QgsReferencedGeometry QgsPostgresProvider::fromEwkt(const QString &ewkt, QgsPostgresConn *conn)
{
thread_local const QRegularExpression regularExpressionSRID( "^SRID=(\\d+);" );

QRegularExpressionMatch regularExpressionMatch = regularExpressionSRID.match( ewkt );
if ( !regularExpressionMatch.hasMatch() )
return QgsReferencedGeometry();

Ewkt ewktInfo;
ewktInfo.wkt = ewkt.mid( regularExpressionMatch.captured( 0 ).size() );
ewktInfo.srid = regularExpressionMatch.captured( 1 ).toInt();

if ( ewktInfo.srid < 0 )
return QgsReferencedGeometry();

QgsGeometry geom = QgsGeometry::fromWkt( ewktInfo.wkt );
return QgsReferencedGeometry( geom, sridToCoordSystem( ewktInfo.srid, conn ) );
}

QString QgsPostgresProvider::toEwkt(const QgsReferencedGeometry &geom)
{
return QStringLiteral( "SRID=%1;%2" ).arg( QString::number( geom.crs().postgisSrid() ), geom.asWkt() );
}

QString QgsPostgresProvider::geomAttrToString(const QVariant &attr)
{
if ( attr.type() == QVariant::String )
return attr.toString();
else
return toEwkt( attr.value<QgsReferencedGeometry>() );
}

QgsCoordinateReferenceSystem QgsPostgresProvider::sridToCoordSystem(int srid, QgsPostgresConn *conn)
{
QgsCoordinateReferenceSystem crs;

static QMutex sMutex;

QMutexLocker locker( &sMutex );
static QMap<int, QgsCoordinateReferenceSystem> sCrsCache;
if ( sCrsCache.contains( srid ) )
crs = sCrsCache.value( srid );
else
{
if ( conn )
{
QgsPostgresResult result( conn->PQexec( QStringLiteral( "SELECT auth_name, auth_srid, srtext, proj4text FROM spatial_ref_sys WHERE srid=%1" ).arg( srid ) ) );
if ( result.PQresultStatus() == PGRES_TUPLES_OK )
{
const QString authName = result.PQgetvalue( 0, 0 );
const QString authSRID = result.PQgetvalue( 0, 1 );
const QString srText = result.PQgetvalue( 0, 2 );
bool ok = false;
if ( authName == QLatin1String( "EPSG" ) || authName == QLatin1String( "ESRI" ) )
{
ok = crs.createFromUserInput( authName + ':' + authSRID );
}
if ( !ok && !srText.isEmpty() )
{
ok = crs.createFromUserInput( srText );
}
if ( !ok )
crs = QgsCoordinateReferenceSystem::fromProj( result.PQgetvalue( 0, 3 ) );
sCrsCache.insert( srid, crs );
}
}
}
return crs;
}

void QgsPostgresProvider::disconnectDb()
Expand Down Expand Up @@ -1078,7 +1154,6 @@ bool QgsPostgresProvider::loadFields()
}
else if ( fieldTypeName == QLatin1String( "text" ) ||
fieldTypeName == QLatin1String( "citext" ) ||
fieldTypeName == QLatin1String( "geometry" ) ||
fieldTypeName == QLatin1String( "geography" ) ||
fieldTypeName == QLatin1String( "inet" ) ||
fieldTypeName == QLatin1String( "cidr" ) ||
Expand All @@ -1094,6 +1169,11 @@ bool QgsPostgresProvider::loadFields()
fieldType = QVariant::String;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "geometry" ) )
{
fieldType = QVariant::UserType;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "bpchar" ) )
{
// although postgres internally uses "bpchar", this is exposed to users as character in postgres
Expand Down Expand Up @@ -2448,10 +2528,11 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
}
else if ( fieldTypeName == QLatin1String( "geometry" ) )
{
QString val = geomAttrToString( v );
values += QStringLiteral( "%1%2(%3)" )
.arg( delim,
connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
quotedValue( v.toString() ) );
quotedValue( val ) );
}
else if ( fieldTypeName == QLatin1String( "geography" ) )
{
Expand Down Expand Up @@ -3054,9 +3135,11 @@ bool QgsPostgresProvider::changeAttributeValues( const QgsChangedAttributesMap &
}
else if ( fld.typeName() == QLatin1String( "geometry" ) )
{
QString val = geomAttrToString( siter.value() );

sql += QStringLiteral( "%1(%2)" )
.arg( connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
quotedValue( siter->toString() ) );
quotedValue( val ) );
}
else if ( fld.typeName() == QLatin1String( "geography" ) )
{
Expand Down Expand Up @@ -3418,9 +3501,10 @@ bool QgsPostgresProvider::changeFeatures( const QgsChangedAttributesMap &attr_ma

if ( fld.typeName() == QLatin1String( "geometry" ) )
{
QString val = geomAttrToString( siter.value() ) ;
sql += QStringLiteral( "%1(%2)" )
.arg( connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
quotedValue( siter->toString() ) );
quotedValue( val ) );
}
else if ( fld.typeName() == QLatin1String( "geography" ) )
{
Expand Down Expand Up @@ -4693,40 +4777,8 @@ QgsCoordinateReferenceSystem QgsPostgresProvider::crs() const
QgsCoordinateReferenceSystem srs;
int srid = mRequestedSrid.isEmpty() ? mDetectedSrid.toInt() : mRequestedSrid.toInt();

{
static QMutex sMutex;
QMutexLocker locker( &sMutex );
static QMap<int, QgsCoordinateReferenceSystem> sCrsCache;
if ( sCrsCache.contains( srid ) )
srs = sCrsCache.value( srid );
else
{
QgsPostgresConn *conn = connectionRO();
if ( conn )
{
QgsPostgresResult result( conn->PQexec( QStringLiteral( "SELECT auth_name, auth_srid, srtext, proj4text FROM spatial_ref_sys WHERE srid=%1" ).arg( srid ) ) );
if ( result.PQresultStatus() == PGRES_TUPLES_OK )
{
const QString authName = result.PQgetvalue( 0, 0 );
const QString authSRID = result.PQgetvalue( 0, 1 );
const QString srText = result.PQgetvalue( 0, 2 );
bool ok = false;
if ( authName == QLatin1String( "EPSG" ) || authName == QLatin1String( "ESRI" ) )
{
ok = srs.createFromUserInput( authName + ':' + authSRID );
}
if ( !ok && !srText.isEmpty() )
{
ok = srs.createFromUserInput( srText );
}
if ( !ok )
srs = QgsCoordinateReferenceSystem::fromProj( result.PQgetvalue( 0, 3 ) );
sCrsCache.insert( srid, srs );
}
}
}
}
return srs;
return sridToCoordSystem( srid, connectionRO() );

}

QString QgsPostgresProvider::subsetString() const
Expand Down Expand Up @@ -4847,7 +4899,7 @@ QVariant QgsPostgresProvider::parseJson( const QString &txt )
return QgsJsonUtils::parseJson( txt );
}

QVariant QgsPostgresProvider::parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName )
QVariant QgsPostgresProvider::parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName, QgsPostgresConn *conn )
{
int i = 0;
QVariantList result;
Expand All @@ -4859,7 +4911,7 @@ QVariant QgsPostgresProvider::parseOtherArray( const QString &txt, QVariant::Typ
QgsMessageLog::logMessage( tr( "Error parsing array: %1" ).arg( txt ), tr( "PostGIS" ) );
break;
}
result.append( QgsPostgresProvider::convertValue( subType, QVariant::Invalid, value, typeName ) );
result.append( convertValue( subType, QVariant::Invalid, value, typeName, conn ) );
}
return result;
}
Expand Down Expand Up @@ -4919,7 +4971,7 @@ QVariant QgsPostgresProvider::parseMultidimensionalArray( const QString &txt )

}

QVariant QgsPostgresProvider::parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName )
QVariant QgsPostgresProvider::parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName, QgsPostgresConn *conn )
{
if ( !txt.startsWith( '{' ) || !txt.endsWith( '}' ) )
{
Expand All @@ -4933,10 +4985,15 @@ QVariant QgsPostgresProvider::parseArray( const QString &txt, QVariant::Type typ
else if ( type == QVariant::StringList )
return parseStringArray( inner );
else
return parseOtherArray( inner, subType, typeName );
return parseOtherArray( inner, subType, typeName, conn );
}

QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName )
QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName ) const
{
return convertValue( type, subType, value, typeName, connectionRO() );
}

QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName, QgsPostgresConn *conn )
{
QVariant result;
switch ( type )
Expand All @@ -4949,7 +5006,7 @@ QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type
break;
case QVariant::StringList:
case QVariant::List:
result = parseArray( value, type, subType, typeName );
result = parseArray( value, type, subType, typeName, conn );
break;
case QVariant::Bool:
if ( value == QChar( 't' ) )
Expand All @@ -4959,6 +5016,10 @@ QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type
else
result = QVariant( type );
break;
case QVariant::UserType:
result = fromEwkt( value, conn );
break;

default:
result = value;
if ( !result.convert( type ) || value.isNull() )
Expand Down
14 changes: 11 additions & 3 deletions src/providers/postgres/qgspostgresprovider.h
View file
Expand Up @@ -23,6 +23,7 @@
#include "qgspostgresconn.h"
#include "qgsfields.h"
#include "qgsprovidermetadata.h"
#include "qgsreferencedgeometry.h"
#include <memory>

class QgsFeature;
Expand Down Expand Up @@ -218,7 +219,8 @@ class QgsPostgresProvider final: public QgsVectorDataProvider
* \param value the value to convert
* \returns a QVariant of the given type or a null QVariant
*/
static QVariant convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName );
QVariant convertValue(QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName) const;
static QVariant convertValue(QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName, QgsPostgresConn *conn );

QList<QgsRelation> discoverRelations( const QgsVectorLayer *self, const QList<QgsVectorLayer *> &layers ) const override;
QgsAttrPalIndexNameHash palAttributeIndexNames() const override;
Expand Down Expand Up @@ -274,10 +276,10 @@ class QgsPostgresProvider final: public QgsVectorDataProvider
static QString getNextString( const QString &txt, int &i, const QString &sep );
static QVariant parseHstore( const QString &txt );
static QVariant parseJson( const QString &txt );
static QVariant parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName );
static QVariant parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName, QgsPostgresConn *conn );
static QVariant parseStringArray( const QString &txt );
static QVariant parseMultidimensionalArray( const QString &txt );
static QVariant parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName );
static QVariant parseArray(const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName, QgsPostgresConn *conn);


/**
Expand Down Expand Up @@ -500,6 +502,12 @@ class QgsPostgresProvider final: public QgsVectorDataProvider
QgsLayerMetadata mLayerMetadata;

std::unique_ptr< QgsPostgresListener > mListener;

static QgsReferencedGeometry fromEwkt(const QString &ewkt , QgsPostgresConn *conn);
static QString toEwkt( const QgsReferencedGeometry &geom );
static QString geomAttrToString( const QVariant& attr );
static QgsCoordinateReferenceSystem sridToCoordSystem(int srsId , QgsPostgresConn *conn);

};


Expand Down

0 comments on commit e3fa91b

Please sign in to comment.