2

I have a Postgres table "files" with a lat/lon location field of type geography(GeometryCollection,4326). Right now the field only contains lat/lon polygons. I have succeeded in creating a gist index for the field and in doing a bounding box search with a search polygon:

select fileid, file_name, ST_AsText(location) 
from files 
where location && ST_MakeEnvelope (-81.0, 22.0, -76.0, 27.0);

But, I need to do a fully intersection search but I hit the restriction that ST_Intersection() does not accept a GeometryCollection. Maybe I could use ST_Distance() since I only need to know if a search area intersects a location area, not what the intersection area is. But I don't see any way to specify a spherical earth calculation, and that is probably all I need most of the time. I have been thinking about changing the field to a multipolygon since that would probably work.

It looks like ST_Distance() will work for what I need to do. It accepts a GeometryCollection and actually will do spherical earth calculations.

1 Answer 1

2

You can use ST_Dump to explode your collection and use ST_Intersects against the resulting SET:

select
  fileid,
  file_name,
  st_astext(st_collect(a.geom))
from
  (
  select fileid, file_name, (st_dump(location)).geom as geom
  from files
  ) a
where ST_Intersects(geom::geometry, ST_MakeEnvelope (-81.0, 22.0, -76.0, 27.0, 4326))
group by fileid, file_name

Not sure if PostGIS will use an index on that query or if you can create an index for st_dump. You might need to evaluate with EXPLAIN ANALYZE.

Try using:

CREATE index idx_files_location_dump ON files USING gist((st_dump(location::geometry)).geom)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.