[postgis-users] St_intersects using GeometryCollection

Nick Ward Nick.Ward at landmark.co.uk
Tue Apr 11 09:26:09 PDT 2017


I need to be able to perform an ST_intersects with a geometry that could be a GeometryCollection.
I understand this is not supported from the PostGis documentation so I have written this query:

select t.geometry
from schema.table t
where (st_intersects(t.geometry, st_collectionextract( st_geometryfromtext(@geometrycollection, 27700),1))  --points
or st_intersects(t.geometry, st_collectionextract( st_geometryfromtext(@geometrycollection, 27700),2))             --lines
or st_intersects(t.geometry, st_collectionextract( st_geometryfromtext(@geometrycollection, 27700),3))             --polygons

Where geometrycollection will be something like 'GEOMETRYCOLLECTION (POINT (290099.9 91499.9), LINESTRING (290099.9 91499.9, 291100.1 91499.9, 291100.1 92500.1, 290099.9 92500.1), POLYGON ((303000 88000, 307000 88000, 307000 84000, 303000 84000, 303000 88000)))'

I have a couple of questions:

Is this the best way to do this? (other people must be doing it?)
Am I missing something as if this works why isn't it a built in function in PostGis?

Any feedback greatly appreciated,
Registered Office: 7 Abbey Court, Eagle Way, Sowton, Exeter, Devon, EX2 7HY. Registered Number 2892803 Registered in England and Wales. The information contained in this e-mail is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not use, copy, distribute or disclose the e-mail or any part of its contents or take any action in reliance on it. If you have received this e-mail in error, please e-mail the sender by replying to this message. All reasonable precautions have been taken to ensure no viruses are present in this e-mail. Landmark Information Group Limited cannot accept responsibility for loss or damage arising from the use of this e-mail or attachments and recommend that you subject these to your virus checking procedures prior to use.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170411/50fbf736/attachment.html>

More information about the postgis-users mailing list