carto - Can I cross the dateline in CartoDB?

I've created a lovely route map in CartoDB, but one of the lines crosses the dateline (from Colorado to Guam, FWIW) and finds its way there by hitting the dateline north of Alaska, bouncing back around past Greenland to the dateline, bouncing again ... you get the idea.

I tried to manually draw a line that crosses the dateline and found that it gets cut off right at the dateline, no matter what I do.

See for yourself: http://cdb.io/1k1vod6

Is there a way to draw a poly line that crosses the dateline in the Pacific?

Answer

This is a great question and one that I have a part answer too. The problem is a combination of the database and the rendering. Maybe there are smarter simpler answers, but here goes.

The best solution is to create a MultiLineString that contains one linestring to the east of the dateline and one to the west. It also depends a bit on projection. I've gone ahead and created a plpgsql function that can do it for you in EPSG:3857 / Webmercator. What it does is finds the point along the line between two points that WOULD hit the dateline, then splits the line into the two segments.

Here it is at work, http://cdb.io/1dnMhq5

To use it, give the function your start and end points,

SELECT 
  AXH_CrossDateLine(
     cdb_latlng( 24, -158), 
     cdb_latlng(-38,  145)
  ) AS the_geom_webmercator

But you'll see, even though I put in WGS84 it spits out Webmercator. If you put in webmercator, it will spit out webmercator faster :)

The function is here, https://gist.github.com/andrewxhill/8795878. You can load it into your account by executing it from any SQL panel one time.

Perhaps we'll try to make some version of this part of CartoDB in the future.