[GIS] PostGIS ST_Intersects GeometryCollection

postgisst-intersects

If i'm not terribly mistaken, ST_Intersects does not support GeometryCollection.

ERROR:  Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
HINT:  Change argument 2: 'GEOMETRYCOLLECTION(POLYGON((.......

Currently i am using a wrapper that cycles through the different types in a GeometryCollection and performing a ST_Intersect on each. Needless to say, this is terribly inneficient.

There must be a better way?

Best Answer

Spatial relation operators do not work on geometry collections by design, as inherited from JTS.

Try using ST_CollectionHomogenize, which will return a regular geometry, if possible. Or ST_CollectionExtract to specify a geometry type.

Related Solutions

[GIS] PostGIS ERROR: Relate Operation called with a LWGEOMCOLLECTION type

Answering my own question here:

The issue that I am experiencing is related to how the ZCTAs are stored in the PostGIS database when created using the TIGER shapefiles.

From what I saw when looking through the data, the ZCTAs are stored as MULTIPOLYGONs, POLYGONs, or GEOMETRYCOLLECTIONs. When trying to use st_intersects/within/contains, the same error was popping up because of the GEOMETRYCOLLECTION objects.

One very slow solution is to check the type of each ZCTA geometry when running the query and extract when necessary (the solution given by ziggy). Another is to run the query on ZCTAs that are not GEOMETRYCOLLECTIONs and run another using only GEOMETRYCOLLECTIONs.

Either way, thanks for everyone's help!

PostGIS – Applying ST_Intersects in PostgreSQL Query

Ok. The generalized goal is to coalesce geometries A and B based on a condition.

I'll assume you know the IDs of the features you need to have fixed and that all rows in study_region_all have values in the geom field.

In that case, you can do:

WITH needsfixing as (
SELECT 
a.id,
b.geom
FROM
study_regions_all a
INNER JOIN counties b on ST_INTERSECTS(a.geom,b.geom) is True
--(alternate syntax: from a,b where ST_INTERSECTS(a.geom,b.geom))
WHERE a.id in (###list the ids for bad features###)
)

SELECT c.id,coalesce(d.geom,c.geom) as geom
FROM study_regions_all c
LEFT JOIN needsfixing d ON c.id = d.id

The CTE gives you the counties geometries where study_region_all geometries are problematic based on your list of ids, and the following query coalesces the fixed geometries with the valid geometries.

Best Answer

Related Solutions

[GIS] PostGIS ERROR: Relate Operation called with a LWGEOMCOLLECTION type

PostGIS – Applying ST_Intersects in PostgreSQL Query

Related Question