Hi,
SYSTEM SETUP:
Amazon Redshift dc2.large 1 node at cluster version 1.0.27085 in the Ohio region (us-east-2)
USE CASE:
When working with the ST_INTERSECTS spatial function, I found a weird behavior. I am passing the same shape as both arguments. When I pass them in as WKBs, the function shows that there are no intersections (1), but when I pass the same shape as WKTs, I get the return value as is expected from ST_INTERSECTS (2)
select ST_INTERSECTS( ST_GEOMFROMWKB('010300000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440', 4326), ST_GEOMFROMWKB('010300000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440', 4326)); (1)
Result: False
select ST_INTERSECTS(ST_GEOMFROMTEXT('POLYGON((30 10,40 40,20 40,10 20,30 10))', 4326), ST_GEOMFROMTEXT('POLYGON((30 10,40 40,20 40,10 20,30 10))', 4326)); (2)
Result: True
Upon further analysis, I found that taking the geometry shape as a WKB, converting that into text and back into geometry also works as expected only if the other shape is also converted from text (3) (4).
select ST_INTERSECTS( ST_GEOMFROMTEXT(ST_ASTEXT(ST_GEOMFROMWKB('010300000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440', 4326)),4326), ST_GEOMFROMTEXT('POLYGON((30 10,40 40,20 40,10 20,30 10))', 4326)); (3)
Result: True
select ST_INTERSECTS( ST_GEOMFROMTEXT(ST_ASTEXT(ST_GEOMFROMWKB('010300000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440', 4326)),4326), ST_GEOMFROMWKB('010300000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440', 4326)); (4)
Result: False
When I do the same check with EWKBs, it seems to work fine (5) suggesting a potential bug with the WKB internal handling
select ST_INTERSECTS(ST_GEOMFROMEWKB('0103000020E610000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440'), ST_GEOMFROMEWKB('0103000020E610000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440')); (5)
Result: True
NOTE: Performing inserts to geometry column tables using ST_GEOMFROMWKB seems to work fine, and the geometry is good when read back out.
