ThinkGeo.com    |     Documentation    |     Premium Support

PostgreSqlFeatureLayer - WhereClause

#1

Hallo,

I have some problems with PostgreSqlFeatureLayers WhereClause.
No matter if I use a string- field (WhereClause = " WHERE ((LOWER(bezeichn) like ‘biogas%’)) "
or use a numeric field (WhereClause = " WHERE ((masszahl > 1000)) ".
The DrawCore - methode will raise an exception. If I don’t use the WhereClause - there are no errors.

The Exception is

Tis.Disma.Client.Layers.TisPostgresTabellarischeUebersichtLayerModel.DrawCore System.ArgumentOutOfRangeException: The index and length must refer to a position in the string.
Parametername: length
   bei System.String.Substring(Int32 startIndex, Int32 length)
   bei ThinkGeo.Core.PostgreSqlFeatureSource.nkk=(String sqlStatement, ExecutingSqlStatementType sqlStatementType, NpgsqlConnection& connection)
   bei ThinkGeo.Core.PostgreSqlFeatureSource.nEk=(BaseShape targetShape, Int32 queryType, IEnumerable`1 returningColumnNames)
   bei ThinkGeo.Core.PostgreSqlFeatureSource.GetFeaturesInsideBoundingBoxCore(RectangleShape boundingBox, IEnumerable`1 returningColumnNames)
   bei ThinkGeo.Core.FeatureSource.GetFeaturesForDrawingCore(RectangleShape boundingBox, Double screenWidth, Double screenHeight, IEnumerable`1 returningColumnNames)
   bei ThinkGeo.Core.FeatureSource.GetFeaturesForDrawing(RectangleShape boundingBox, Double screenWidth, Double screenHeight, IEnumerable`1 returningColumnNames)
   bei ThinkGeo.Core.FeatureLayer.DrawCore(GeoCanvas canvas, Collection`1 labelsInAllLayers)
   bei Tis.Disma.Client.Layers.TisPostgresTabellarischeUebersichtLayerModel.DrawCore(GeoCanvas canvas, Collection`1 labelsInAllLayers) in E:\Code\Disma6\DismaWpf\Tis.Disma.Client.Layers\Layers\TisPostgresTabellarischeUebersichtLayerModel.cs:Zeile 2051.

Second: If I use the methode QueryTools.GetFirstFeaturesWellKnownType() with the WhereClause
the following Satement will be generated

select st_asbinary(geometry) from immissionsschutz.anlagenbestand_allgemein LIMIT 1 WHERE ((LOWER(bezeichn) like 'biogas%')

instead of the correct version

select st_asbinary(geometry) from immissionsschutz.anlagenbestand_allgemein WHERE ((LOWER(bezeichn) like 'biogas%')) LIMIT 1

The Version of ThinkGeo.PostgreSql.dll is 13.0.0-beta 270.

Any suggestions

Regards Torsten

#2

hi @Torsten,

This exception was caused by the leading and trailing white space in the WhereClause.

Try using the following code:

WhereClause = "WHERE ((LOWER(bezeichn) like 'biogas%'))" 
WhereClause = "WHERE ((masszahl > 1000))"

Thanks,
Leo

#3

Hello Leo,
you saved my day :grinning:
Thanks Torsten

#4

hi @Torsten,

We also updated our code to remove the leading and trailing white spaces from WhereClause. You could try it with ThinkGeo.PostgreSql 13.0.0-beta337.

Thanks,
Leo