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

postgisst-intersects

I have installed PostGIS on my machine and I am using shapefiles provided by the US Census Bureau for finding zips and counties of coordinates. I am just using the PostGIS provided method for loading data.

I am testing my query using this data: https://gist.github.com/erichurst/7882666

This is my query:

select
  main.latitude,
  main.longitude,
  c.cntyidfp,
  s.statefp,
  z.zcta5ce
from (
  select
    latitude::numeric(15,8) as latitude,
    longitude::numeric(15,8) as longitude,
    st_setsrid(st_makepoint(longitude::numeric(15,8), latitude::numeric(15,8)), 4269) as mypoint
  from temp.sample
) as main
left outer join tiger.county as c
  on st_intersects(c.the_geom, main.mypoint)
left outer join tiger.state as s
  on s.statefp = c.statefp
left outer join tiger.zcta5 as z
  on st_intersects(z.the_geom, main.mypoint)

Now, all of the test data and the production data I will be using are coordinates (single points), but I keep getting this error:

ERROR: Relate Operation called with a LWGEOMCOLLECTION type. This is
unsupported. HINT: Change argument 1:
'GEOMETRYCOLLECTION(LINESTRING(-71.255183 42.736524,-71.255147
42.736554),POLY…'

It may be triggered by other points, this was only an example.

Why would a linestring be created? I am only testing points. I would like to be able to run the query on all of the data at once, not try each record individually. Is there something I can do to extract a point? Am I missing something? I am new to PostGIS but I have done a bit of research on this issue and others and I cannot seem to get around this.

Best Answer

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!

Related Question