When making a GEOMETRY linestring that crosses the antimeridian, it is common to see a horizontal line across the map. This is because the GEOMETRY data type deals entirely in map units, so interprets the world as being flat. The following route from Perth to Orlando is somewhat questionable.
In How to make a great-circle linestring we discussed turning a GEOGRAPHY line (the shortest route between two points) into a GEOMETRY (map representation of this). Now we must correct for the horizontal line that can cut across the world. I have solved this using a non-optimal method, but welcome suggestions on improvements.
Step 1: Find where this problem exists – select all routes that intersect with two lines – one at -100 longitude, one at +100 longitude.
SELECT gid FROM routes WHERE st_intersects(geom, (ST_GeomFromText('LINESTRING(100.0 89.99, 100.0 -89.99)',4326))) AND st_intersects(geom, (ST_GeomFromText('LINESTRING(-100.0 89.99, -100.0 -89.99)',4326)));
Step 2: Find where this line should cross the antimeridian
SELECT st_y(st_intersection(geom, ST_GeomFromText('LINESTRING(0 89.999, 0 -89.999)', 4326))) FROM routes WHERE gid = 1;
Step 3: Turn the GEOMETRY linestring into a multilinestring
Step 3a: Dump out all of the points
SELECT ST_x((dp).geom), ST_y((dp).geom) FROM (SELECT st_dumppoints(geom) AS dp FROM routes WHERE gid = 1) AS dog
Step 3b: Use some python to split this into a multilinestring – one hemisphere is part1, the other is part2.
"MULTILINESTRING(%s,%s)"% (part1, part2)
Step 4: Regard your newly corrected map with pride, or at least with less embarrassment!