6

I found that there is a difference in result between using A && B operator or one of the geo functions like ST_Intersects(A,B)

While the ST_Intersects functions return query results without errors the operator version SOMETIMES results in errors over the SRID:

ERROR: Operation on two geometries with different SRID's.

ERROR: Operation on two geometries with different SRIDs SQL state: XX000

All of the records seem have the correct SRID, none missing.

The query is generated by MAPSERV WMS server, so what is there to do?

Sample query:

  • Operator &&

    select encode(AsBinary(force_collection(force_2d("geometry")),'NDR'),'hex') as geom, "id" 
    from (select * from soils.vw_zones) as vw
    where geometry && GeomFromText('POLYGON((75722.1945223652   437331.342330005,75722.1945223652 503295.946741193,169438.897876772 503295.946741193,169438.897876772 437331.342330005,75722.1945223652 437331.342330005))',find_srid('','soils.vw_zones','geometry')) 
    
  • ST_Intersects

    select encode(AsBinary(force_collection(force_2d("geometry")),'NDR'),'hex') as geom, "id" 
    from (select * from soils.vw_zones) as vw
    where st_intersects(geometry,GeomFromText('POLYGON((75722.1945223652 437331.342330005,75722.1945223652 503295.946741193,169438.897876772 503295.946741193,169438.897876772 437331.342330005,75722.1945223652 437331.342330005))',find_srid('','soils.vw_zones','geometry')))
    

closed as unclear what you're asking by Evan Carroll, BERA, HDunn, whyzar, nmtoken Nov 25 '17 at 19:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • When I have tested && and ST_Intersects before, the && version just compares bounding boxes. So it is very quick but in a way not that accurate. Where as the ST_Intersect does a bounding box to reduce the number of features to check against, and then does a full geometry check feature against feature. – tjmgis Nov 21 '13 at 15:00
  • It's not clear what this question about, the answer is about the difference between the two, the question is about error-generation on the two and has nothing to do with the answer. – Evan Carroll Nov 25 '17 at 8:03
9
  • ST_Intersects checks if 2 geometry really intersects
  • && checks if the BBox of both geometries intersect. This use only the index if available. That's why it is really really fast.

For the SRID error look at this answer:

https://gis.stackexchange.com/a/12082/22703

  • I don't thin this answers the question at all. It just documents some of the unrelated differences. – Evan Carroll Nov 25 '17 at 8:16
0

I think what you're looking for is addressed in #2320: "Operator && does not detect mixed SRIDs",

PostGIS's Regina says

This has always been an issue from the beginning of time and not one that is easy to fix. Boxes simply don't have srids so to add a check like that would significantly slow things down. && work on the boxes of geometries and in fact usually just touch the index if there is one and rarely the geometry.

There is a simple test case of that too on the ticket.

Earlier you can find it mentioned on #520: "No check for different srid on operators when index is used", again Regina says,

FWIW, I really don't think this is a big issue and would just assume forget about it if its hard to fix. Usually when people use &&, they know what they are doing and I suspect trying to fix this will just slow things down. NOTE: This more common query does give error: [query at link above demonstrating ST_Intersects]

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