Skip to content

Commit

Permalink
postgres: convert 4d geometries to 2d (fixes #9748)
Browse files Browse the repository at this point in the history
  • Loading branch information
@jef-n
jef-n committed May 31, 2014
1 parent 8089a4e commit 016b03b
Show file tree
Hide file tree
Showing 6 changed files with 75 additions and 45 deletions.
20 changes: 13 additions & 7 deletions src/providers/postgres/qgspostgresconn.cpp
View file
Expand Up @@ -338,7 +338,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP

for ( int i = 0; i < ( hasTopology() ? 3 : 2 ); i++ )
{
QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName;
QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName, dimName;
QgsPostgresGeometryColumnType columnType = sctGeometry;

if ( i == 0 )
Expand All @@ -348,6 +348,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
columnName = "l.f_geometry_column";
typeName = "upper(l.type)";
sridName = "l.srid";
dimName = "l.coord_dimension";
gtableName = "geometry_columns";
columnType = sctGeometry;
}
Expand All @@ -358,6 +359,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
columnName = "l.f_geography_column";
typeName = "upper(l.type)";
sridName = "l.srid";
dimName = "2";
gtableName = "geography_columns";
columnType = sctGeography;
}
Expand All @@ -373,21 +375,22 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
"WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
"END AS type";
sridName = "(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id)";
dimName = "2";
gtableName = "topology.layer";
columnType = sctTopoGeometry;
}

// The following query returns only tables that exist and the user has SELECT privilege on.
// Can't use regclass here because table must exist, else error occurs.
sql = QString( "SELECT %1,%2,%3,%4,%5,c.relkind"
" FROM %6 l,pg_class c,pg_namespace n"
sql = QString( "SELECT %1,%2,%3,%4,%5,%6,c.relkind"
" FROM %7 l,pg_class c,pg_namespace n"
" WHERE c.relname=%1"
" AND %2=n.nspname"
" AND n.oid=c.relnamespace"
" AND has_schema_privilege(n.nspname,'usage')"
" AND has_table_privilege('\"'||n.nspname||'\".\"'||c.relname||'\"','select')" // user has select privilege
)
.arg( tableName ).arg( schemaName ).arg( columnName ).arg( typeName ).arg( sridName ).arg( gtableName );
.arg( tableName ).arg( schemaName ).arg( columnName ).arg( typeName ).arg( sridName ).arg( dimName ).arg( gtableName );

if ( searchPublicOnly )
sql += " AND n.nspname='public'";
Expand All @@ -409,7 +412,8 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
QString column = result.PQgetvalue( idx, 2 );
QString type = result.PQgetvalue( idx, 3 );
QString ssrid = result.PQgetvalue( idx, 4 );
QString relkind = result.PQgetvalue( idx, 5 );
int dim = result.PQgetvalue( idx, 5 ).toInt();
QString relkind = result.PQgetvalue( idx, 6 );
bool isView = relkind == "v" || relkind == "m";

int srid = ssrid.isEmpty() ? INT_MIN : ssrid.toInt();
Expand All @@ -419,12 +423,13 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
srid = INT_MIN;
}

QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 %7" )
QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 %7 %8" )
.arg( gtableName )
.arg( schemaName ).arg( tableName ).arg( column )
.arg( type )
.arg( srid )
.arg( relkind ) );
.arg( relkind )
.arg( dim ) );

layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
Expand All @@ -433,6 +438,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
layerProperty.types = QList<QGis::WkbType>() << ( QgsPostgresConn::wkbTypeFromPostgis( type ) );
layerProperty.srids = QList<int>() << srid;
layerProperty.sql = "";
layerProperty.force2d = dim == 4;
addColumnInfo( layerProperty, schemaName, tableName, isView );

if ( isView && layerProperty.pkCols.empty() )
Expand Down
6 changes: 4 additions & 2 deletions src/providers/postgres/qgspostgresconn.h
View file
Expand Up @@ -64,6 +64,7 @@ struct QgsPostgresLayerProperty
QList<int> srids;
unsigned int nSpCols;
QString sql;
bool force2d;


// TODO: rename this !
Expand Down Expand Up @@ -113,15 +114,16 @@ struct QgsPostgresLayerProperty
sridString += QString::number( srid );
}

return QString( "%1.%2.%3 type=%4 srid=%5 pkCols=%6 sql=%7 nSpCols=%8" )
return QString( "%1.%2.%3 type=%4 srid=%5 pkCols=%6 sql=%7 nSpCols=%8 force2d=%9" )
.arg( schemaName )
.arg( tableName )
.arg( geometryColName )
.arg( typeString )
.arg( sridString )
.arg( pkCols.join( "|" ) )
.arg( sql )
.arg( nSpCols );
.arg( nSpCols )
.arg( force2d ? "yes" : "no" );
}
#endif
};
Expand Down
77 changes: 44 additions & 33 deletions src/providers/postgres/qgspostgresfeatureiterator.cpp
View file
Expand Up @@ -156,7 +156,9 @@ bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature& feature )
bool QgsPostgresFeatureIterator::prepareSimplification( const QgsSimplifyMethod& simplifyMethod )
{
// setup simplification of geometries to fetch
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) && simplifyMethod.methodType() != QgsSimplifyMethod::NoSimplification && !simplifyMethod.forceLocalOptimization() )
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) &&
simplifyMethod.methodType() != QgsSimplifyMethod::NoSimplification &&
!simplifyMethod.forceLocalOptimization() )
{
QgsSimplifyMethod::MethodType methodType = simplifyMethod.methodType();

Expand Down Expand Up @@ -279,43 +281,51 @@ bool QgsPostgresFeatureIterator::declareCursor( const QString& whereClause )
{
mFetchGeometry = !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) && !mSource->mGeometryColumn.isNull();

#if 0
// TODO: check that all field indexes exist
//if ( !hasAllFields )
//{
// rewind();
// return false;
//}
if ( !hasAllFields )
{
rewind();
return false;
}
#endif

QString query( "SELECT " ), delim( "" );

const QgsSimplifyMethod& simplifyMethod = mRequest.simplifyMethod();
if ( mFetchGeometry )
{
QString geom = QgsPostgresConn::quotedIdentifier( mSource->mGeometryColumn );

QString query = "SELECT ", delim = "";
if ( mSource->mSpatialColType == sctGeography )
geom += "::geometry";

bool isPointLayer = QGis::flatType( QGis::singleType( mSource->mRequestedGeomType != QGis::WKBUnknown ? mSource->mRequestedGeomType : mSource->mDetectedGeomType ) ) == QGis::WKBPoint;
if ( mFetchGeometry && !simplifyMethod.forceLocalOptimization() && simplifyMethod.methodType() != QgsSimplifyMethod::NoSimplification && !isPointLayer )
{
QString simplifyFunctionName = simplifyMethod.methodType() == QgsSimplifyMethod::OptimizeForRendering
? ( mConn->majorVersion() < 2 ? "snaptogrid" : "st_snaptogrid" )
: ( mConn->majorVersion() < 2 ? "simplifypreservetopology" : "st_simplifypreservetopology" );

double tolerance = simplifyMethod.tolerance() * 0.8; //-> Default factor for the maximum displacement distance for simplification, similar as GeoServer does

query += QString( "%1(%5(%2%3,%6),'%4')" )
.arg( mConn->majorVersion() < 2 ? "asbinary" : "st_asbinary" )
.arg( QgsPostgresConn::quotedIdentifier( mSource->mGeometryColumn ) )
.arg( mSource->mSpatialColType == sctGeography ? "::geometry" : "" )
.arg( QgsPostgresProvider::endianString() )
.arg( simplifyFunctionName )
.arg( tolerance );
delim = ",";
}
else if ( mFetchGeometry )
{
query += QString( "%1(%2%3,'%4')" )
.arg( mConn->majorVersion() < 2 ? "asbinary" : "st_asbinary" )
.arg( QgsPostgresConn::quotedIdentifier( mSource->mGeometryColumn ) )
.arg( mSource->mSpatialColType == sctGeography ? "::geometry" : "" )
.arg( QgsPostgresProvider::endianString() );
if ( mSource->mForce2d )
{
geom = QString( "%1(%2)" )
.arg( mConn->majorVersion() < 2 ? "force2d" : "st_force2d" )
.arg( geom );
}

if ( !simplifyMethod.forceLocalOptimization() &&
mRequest.simplifyMethod().methodType() != QgsSimplifyMethod::NoSimplification &&
QGis::flatType( QGis::singleType( mSource->mRequestedGeomType != QGis::WKBUnknown
? mSource->mRequestedGeomType
: mSource->mDetectedGeomType ) ) != QGis::WKBPoint )
{
geom = QString( "%1(%2,%3)" )
.arg( mRequest.simplifyMethod().methodType().methodType() == QgsSimplifyMethod::OptimizeForRendering
? ( mConn->majorVersion() < 2 ? "snaptogrid" : "st_snaptogrid" )
: ( mConn->majorVersion() < 2 ? "simplifypreservetopology" : "st_simplifypreservetopology" ) )
.arg( geom )
.arg( simplifyMethod.tolerance() * 0.8 ); //-> Default factor for the maximum displacement distance for simplification, similar as GeoServer does
}

geom = QString( "%1(%2,'%3')" )
.arg( mConn->majorVersion() < 2 ? "asbinary" : "st_asbinary" )
.arg( geom )
.arg( QgsPostgresProvider::endianString() );

query += delim + geom;
delim = ",";
}

Expand Down Expand Up @@ -539,6 +549,7 @@ QgsPostgresFeatureSource::QgsPostgresFeatureSource( const QgsPostgresProvider* p
, mSpatialColType( p->mSpatialColType )
, mRequestedSrid( p->mRequestedSrid )
, mDetectedSrid( p->mDetectedSrid )
, mForce2d( p->mForce2d )
, mRequestedGeomType( p->mRequestedGeomType )
, mDetectedGeomType( p->mDetectedGeomType )
, mPrimaryKeyType( p->mPrimaryKeyType )
Expand Down
1 change: 1 addition & 0 deletions src/providers/postgres/qgspostgresfeatureiterator.h
View file
Expand Up @@ -42,6 +42,7 @@ class QgsPostgresFeatureSource : public QgsAbstractFeatureSource
QgsPostgresGeometryColumnType mSpatialColType;
QString mRequestedSrid;
QString mDetectedSrid;
bool mForce2d;
QGis::WkbType mRequestedGeomType; //! geometry type requested in the uri
QGis::WkbType mDetectedGeomType; //! geometry type detected in the database
QgsPostgresPrimaryKeyType mPrimaryKeyType;
Expand Down
15 changes: 12 additions & 3 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -46,6 +46,7 @@ QgsPostgresProvider::QgsPostgresProvider( QString const & uri )
, mPrimaryKeyType( pktUnknown )
, mSpatialColType( sctNone )
, mDetectedGeomType( QGis::WKBUnknown )
, mForce2d( false )
, mRequestedGeomType( QGis::WKBUnknown )
, mShared( new QgsPostgresSharedData )
, mUseEstimatedMetadata( false )
Expand Down Expand Up @@ -2558,12 +2559,12 @@ bool QgsPostgresProvider::getGeometryDetails()
}
}

QString detectedType = mRequestedGeomType == QGis::WKBUnknown ? "" : QgsPostgresConn::postgisWkbTypeName( mRequestedGeomType );
QString detectedType;
QString detectedSrid = mRequestedSrid;
if ( !schemaName.isEmpty() && ( detectedType.isEmpty() || detectedSrid.isEmpty() ) )
if ( !schemaName.isEmpty() )
{
// check geometry columns
sql = QString( "SELECT upper(type),srid FROM geometry_columns WHERE f_table_name=%1 AND f_geometry_column=%2 AND f_table_schema=%3" )
sql = QString( "SELECT upper(type),srid,coord_dimension FROM geometry_columns WHERE f_table_name=%1 AND f_geometry_column=%2 AND f_table_schema=%3" )
.arg( quotedValue( tableName ) )
.arg( quotedValue( geomCol ) )
.arg( quotedValue( schemaName ) );
Expand All @@ -2576,6 +2577,8 @@ bool QgsPostgresProvider::getGeometryDetails()
{
detectedType = result.PQgetvalue( 0, 0 );
detectedSrid = result.PQgetvalue( 0, 1 );
if ( result.PQgetvalue( 0, 2 ).toInt() == 4 )
mForce2d = true;
mSpatialColType = sctGeometry;
}
else
Expand Down Expand Up @@ -2667,6 +2670,10 @@ bool QgsPostgresProvider::getGeometryDetails()
}
}
}
else
{
detectedType = mRequestedGeomType == QGis::WKBUnknown ? "" : QgsPostgresConn::postgisWkbTypeName( mRequestedGeomType );
}

mDetectedGeomType = QgsPostgresConn::wkbTypeFromPostgis( detectedType );
mDetectedSrid = detectedSrid;
Expand Down Expand Up @@ -2729,6 +2736,7 @@ bool QgsPostgresProvider::getGeometryDetails()
// only what we requested is available
mDetectedGeomType = layerProperty.types[ 0 ];
mDetectedSrid = QString::number( layerProperty.srids[ 0 ] );
mForce2d = layerProperty.force2d;
}
}
else
Expand All @@ -2743,6 +2751,7 @@ bool QgsPostgresProvider::getGeometryDetails()
QgsDebugMsg( QString( "Requested SRID is %1" ).arg( mRequestedSrid ) );
QgsDebugMsg( QString( "Detected type is %1" ).arg( mDetectedGeomType ) );
QgsDebugMsg( QString( "Requested type is %1" ).arg( mRequestedGeomType ) );
QgsDebugMsg( QString( "Force to 2D %1" ).arg( mForce2d ? "Yes" : "No" ) );

mValid = ( mDetectedGeomType != QGis::WKBUnknown || mRequestedGeomType != QGis::WKBUnknown )
&& ( !mDetectedSrid.isEmpty() || !mRequestedSrid.isEmpty() );
Expand Down
1 change: 1 addition & 0 deletions src/providers/postgres/qgspostgresprovider.h
View file
Expand Up @@ -397,6 +397,7 @@ class QgsPostgresProvider : public QgsVectorDataProvider
QgsRectangle mLayerExtent; //! Rectangle that contains the extent (bounding box) of the layer

QGis::WkbType mDetectedGeomType; //! geometry type detected in the database
bool mForce2d; //! geometry type needs to be forced to 2d (eg. ZM)
QGis::WkbType mRequestedGeomType; //! geometry type requested in the uri
QString mDetectedSrid; //! Spatial reference detected in the database
QString mRequestedSrid; //! Spatial reference requested in the uri
Expand Down

0 comments on commit 016b03b

Please sign in to comment.