This is an exact copy of a question I asked on Stackoverflow a few weeks back and still haven't found a good answer for it. The original question is from here: original question.
I've been searching about this for a while and can't seem to find anything useful.
In my database, I have an outline of the world coastline saved as lines. I want to take another line and determine where on the coastline that line intersects. My current query is like this:
SELECT st_intersection(makeline(p1.latlong, p2.latlong)::geography, the_geom::geography) FROM worldcoastline, points p1, points p2 WHERE p1.id = 1 AND p2.id = 2
All is well and good, and everything seems to line up when I test it in KML and such, except when the dateline is involved. Instead of going across the dateline, it will go all the way around the world, and show me all these collisions it couldn't have had (across Russia, Europe, America). The most interesting thing I find though is if I throw in a st_intersects on the
makeline as a geography, it returns false. I'm considering adding it to the where clause, but the problem is lines that cross the dateline and intersect land will return really whacky results anyways!
Is there anything I can do in PostGIS to fix this problem?
EDIT: I just found this in the reference:
ST_Intersection — (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84.
So I guess that's why it's having dateline problems. Any alternatives or suggestions?
EDIT: I just wanted to point out that I did add the st_instersects WHERE clause, but of course if a line does intersect the dateline as well as a coastline I will end up with a messed up ST_Intersection because it will still try to find the intersections going the long way around the world instead of across the dateline