I am seeing confusing results when using ST_Intersection with geography types. For example this query
select
st_astext(
st_intersection(
ST_GeographyFromText('LINESTRING(-40 40, 40 40)'),
ST_GeographyFromText('LINESTRING(0 30, 0 50)')
))
returns POINT(0 40)
. This is what is would expect from the intersection of geometry objects. Because the linestrings should be interpreted as along great circles in the geography type I was expecting the intersection to be approximately POINT(0 47.6)
.
What is more confusing is that the query
select
st_intersects(
ST_GeographyFromText('LINESTRING(-40 40, 40 40)'),
ST_GeographyFromText('LINESTRING(0 39, 0 41)')
)
actually returns false. Am i missing something, or is the ST_INTERSECTION function not behaving properly for geography types?
As pointed out by @Damien_The_Unbeliever the ST_INTERSECTION function is just a thin wrapper over the geometry based function and does not perform any spherical calculations. If you want to get (an approximation) of the correct result you can first segmentize the geography (which does perform spherical calcs) as below
select
st_astext(
st_intersection(
ST_Segmentize(ST_GeographyFromText('LINESTRING(-40 40, 40 40)'), 1000),
ST_Segmentize(ST_GeographyFromText('LINESTRING(0 30, 0 50)'), 1000)
))
This returns POINT(0 47.60591395513534)
as expected.
The takeaway from this for me is not to assume the PostGIS will necessarily do spherical/ellispoidal calcs on geography objects - always check the docs.
In this case we end up with the confusing situation where it's no longer true that
ST_INTERSECTS(A, B) iff ST_INTERSECTION(A, B) != 0