NPGSQL: Relate Operation called with a LWGEOMCOLLECTION type

Question

I have a query which fetches PostGIS data using Npgsql. Its purpose is to take a point (x,y co-ordinates) and work out which (if any) geometry is at that point. For the vast majority of geometries in the database the query works fine, but for at least one I get the following exception:

ERROR: XX000: Relate Operation called with a LWGEOMCOLLECTION type. This is unsupported.

with the top of the stack trace being:

[NpgsqlException (0x80004005): ERROR: XX000: Relate Operation called with a LWGEOMCOLLECTION type. This is unsupported.]
Npgsql.d__0.MoveNext() +3160
Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup) +808 Npgsql.ForwardsOnlyDataReader.GetNextRow(Boolean clearPending) +308 Npgsql.ForwardsOnlyDataReader.Read() +47

All geometries should be valid as I call ST_MakeValid on any which aren't and there are currently none where ST_IsValid returns false. The geometry was created by calling ST_GeomFromKML and is rendered fine on a map as a raster layer using WMS via GeoServer or as a vector layer using ST_AsGeoJSON so the PostGIS data seems to be OK.

Is there any way I can amend my code or data to stop this happening? The part of the code failing is the part where the reader is being read in:

command.CommandText = "SELECT area_code FROM area WHERE ST_INTERSECTS(ST_SetSRID(ST_Point(:x, :y), 4326), shape) AND area_type_code = :typecode";
command.CommandType = CommandType.Text;
var typeCodeParameter = new NpgsqlParameter
{
    DbType = DbType.String,
    ParameterName = "typecode",
    Value = _typeCode
};
var xParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "x",
    Value = _x
};
var yParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "y",
    Value = _y
};
command.Parameters.Add(typeCodeParameter);
command.Parameters.Add(xParameter);
command.Parameters.Add(yParameter);
using (var reader = command.ExecuteReader())
{
    if (reader.Read())
        area = new AreaBasic
        {
            Code = (string)reader["area_code"]
        };
}

EDIT: further information. The same error occurs when running the query with hard coded values in pgAdmin III so the problem isn't Npgsql specific.

Answer 1

This is due to trying to call an intersects or contains type query on a Geometry Collection, ie, where you have some mixture of points, lines and polygons (possibly multi).

There are, at least, a couple of possible fixes. The first one is simpler, but seems a bit hacky, which is simply to buffer your input geometry first by 0, which will result in non-polygons from being removed, so, in your case, simply changing your command.commandText to

SELECT area_code FROM area WHERE ST_INTERSECTS(ST_SetSRID(ST_Point(:x, :y), 4326), 
ST_Buffer(shape, 0)) AND area_type_code = :typecode";

Note, this approach can often be used to fix invalid geometries, ones with self-intersecting loops and the like.

The second approach is to use ST_Dump on your shape field to split into individual geometries and then only use Polygons in the actual query by means of the ST_GeometryType function.

SELECT area_code 
FROM 
  (SELECT area_code, (ST_Dump(area)).geom FROM area) poly  
WHERE ST_INTERSECTS(ST_SetSRID(ST_Point(:x, :y), 4326), poly.geom) 
AND ST_GeometryType(poly.geom) = 'ST_Polygon' 
OR ST_GeometryType(poly.geom) = 'ST_MultiPolygon'
AND area_type_code = :typecode";

This is untested, as I can't test this on you data clearly, but these approaches work in practice.