2

I have complex linestrings in PostgreSQL (see sample below). Many of these linestrings will cross the international date line (longitude 180 / longitude -180).

The problem is that PostGIS assumes that anything crossing the dateline goes the long-way-round, as per this screenshot:

enter image description here

As you can see, there is a route with various waypoints. One of these waypoints is at Japan and then it should move eastwards to the next waypoint in the Bering Sea. Unfortunately, it moves westwards.

I absolutely understand that PostGIS will not assume shortest route is correct, but is there any way that I can tell PostGIS that the line needs to be treated as the shortest distance between waypoints so that routes going over the international dateline will be correct?

Also, as a potential complication, because the earth happens to be a globe is there any way to force PostGIS to know that this line is curved?

I have Googled this issue and the only thing I can find are answers where people say "You need to split the line at the international date line" but not actually saying how.

The sample here seemed to imply that it would work, but I couldn't get it working with my linestring. ST_WrapX seems promising, but, again, I couldn't get anything working.

So, the short version of this question is:

Using PostGIS, what is the best method of taking the linestring below and ensuring that it goes the shortest way across the international date line while maintaining Great-Circle.

Linestring sample:

SELECT 'SRID=4326;LINESTRING (50.6053303941 26.2328662035,50.62714752 26.2188835732,50.6372755413 26.2078720794,50.6584102286 26.1897371625,50.6668216361 26.1791850693,50.749260916 26.1736534509,51.2534047944 26.2101221742,54.4965183395 26.1951892876,55.1972090277 26.1199979339,55.2885328803 26.1101331796,55.5096327338 26.1723904734,56.3809020824066 26.5203900312896,56.4764804704551 26.5578831103492,56.5502214754958 26.5581338797783,56.611119873567 26.4682481722291,56.8534029861976 25.738946024336,57.481259505 25.3334275659,77.086155649 8.0284849934,80.1617902726 6.0002772495,80.2534574479 5.9538391888,80.6829592523 5.8130953996,94.9542301323 5.8536368282,95.3442447808 5.7545836593,97.6413612917 5.522379156,98.0313759401 5.4731681018,100.0693398073 3.6936714669,100.757245115126 2.9607432279995,100.910337569619 2.85955700929489,100.987621795379 2.78991311421912,101.15454439278 2.70422964295857,101.215085876829 2.67433381980237,101.415683800039 2.57068469004788,101.598297180415 2.42556210718857,101.66910626657 2.37272290662317,101.969378058874 2.13346068781439,102.051689868016 2.06165972656967,102.233129968166 1.8966796200077,102.540315595776 1.7495683037633,102.797736761304 1.63020862233938,103.198549375775 1.37493142294799,103.38616444361 1.20397358272375,103.450766113452 1.16386098143384,103.542388154079 1.10734844315567,103.6560802573 1.0411061977,103.715970404878 1.07999663975176,103.756249759261 1.12130751035416,103.778241662829 1.14047626100384,103.802865088181 1.15926902536647,103.823729242126 1.16979291090826,103.842623851372 1.17774159903804,103.876214028824 1.19295989435007,103.90192004016 1.20875572728521,103.968795305125 1.23553884718814,104.056789074813 1.24492265043924,104.248958814717 1.27018649835435,104.329907544846 1.28521709843332,104.456305708049 1.39394856755492,104.9976789933 2.7261766366,106.2266118303 4.1690770181,116.5256539022 16.8947216277,127.8729760753 26.1566422235,139.1521119518 33.8461079041,-171.5555438179 51.8314023213,-130.4083521498 51.280329003,-128.9629701732 51.047713028,-128.0839758408 50.9031919116,-127.8834753525 50.8811026015,-127.8525763046 50.8676706464,-127.7791052353 50.8477322236,-127.6575689804 50.8230140898,-127.48796754 50.7839587996,-127.4035101425 50.7718024298,-127.2339087021 50.6909628654,-127.166984273 50.6230909464,-127.1440173482 50.6130560919,-127.1130826006 50.6191701941,-127.0131756793 50.6100205009,-126.9798733721 50.5914978187,-126.9408384871 50.5806317083,-126.9346586775 50.5690759147,-126.8817869734 50.5658048924,-126.7920080733 50.5558814023,-126.7260901045 50.5427922867,-126.638886125 50.5170397581,-126.5578619551 50.5008826953,-126.3630540327 50.500009183,-126.319881882 50.4958569973,-126.1235012668 50.4692047699,-126.0668100971 50.4568293153,-125.9677614826 50.4149527869,-125.9007841271 50.39367806,-125.8494573754 50.3886437518,-125.8252989268 50.387676239,-125.7811273393 50.3746668481,-125.7138360796 50.3685355971,-125.6795038042 50.3740099662,-125.6280053911 50.3742289278,-125.5741901112 50.3779511209,-125.5505008412 50.3624338815,-125.5274982167 50.3541105607,-125.507585497 50.3510437062,-125.4849261952 50.3433757032,-125.4591769887 50.3385551819,-125.4303378773 50.3396507979,-125.425188036 50.3109373163,-125.3984088612 50.2749662115,-125.377809496 50.2249124112,-125.3637332631 50.1800828595,-125.3603000355 50.1600717826,-125.3549785328 50.1391721572,-125.3526344171 50.1277113854,-125.322895223 50.1172868754,-125.3043557943 50.1051773816,-125.2705385031 50.0801781094,-125.2504007476 50.0596501363,-125.2349512237 50.0430063189,-125.2280847686 50.0315396703,-125.2073110159 49.9951567531,-125.1724759634 49.9646897002,-125.048879772 49.9222686578,-124.8566190298 49.8877267807,-124.7199765737 49.9027659384,-124.6375791128 49.8868419784,-124.6011869009 49.8686999569,-124.5737210805 49.844352393,-124.5627347524 49.8089158529,-124.528402477 49.7787398553,-124.4302121694 49.7454738036,-124.3333951528 49.7312733409,-124.2818967397 49.7059684722,-124.218725353 49.6250821804,-124.1284937077 49.581588293,-124.0570825748 49.47551643,-123.7399836721 49.3136049387,-123.5783721306 49.0203890951,-123.5921050408 49.0147600825,-123.6065245964 48.9920123905,-123.6181975701 48.9868307705,-123.630900512 48.9868307705,-123.6312438347 48.9791699973,-123.6016907225 48.9529611143,-123.5725082884 48.9414617046,-123.5398926268 48.9272528925,-123.4777512084 48.8976946367,-123.4109350745 48.8567203442,-123.4128233497 48.8465543863,-123.4164282386 48.8346914931,-123.4226377276 48.8263782497,-123.3659894732 48.79856957,-123.3450327782 48.7772977792,-123.3591090111 48.754668139,-123.3876047997 48.7424438931,-123.417588803 48.7342647177,-123.3915233351 48.7106157315,-123.3625125624 48.6999669524,-123.2983112074 48.6896558814,-123.2185174783 48.6527820066,-123.177657493 48.6283256273,-123.1725076517 48.6224822003,-123.1728509744 48.617545963,-123.1706193765 48.6129497219,-123.1690395596 48.6097950477)'::geometry AS myline
Improve this question

2 Answers 2

Reset to default
3

First of all, this has nothing to do with the dateline, this is a 180W/E issue. And I would also say this is not a postgis issue, this is a problem coming up in the software presenting the data. If you present the data in PDC Mercator, EPSG:3832, you will have a pacific centered map and your lines will make more sense.

If you want to split the line(s) in postgis, the command you are looking for is probably ST_split. Something along the line of

select st_split(geom,ST_SetSRID(ST_MakeLine(ST_Point(180, -90),ST_Point(180, 90)),4326)) ( , ... other fields you may need...) from (...)

You can make this into a view and you will be able to use that as a read only data set in e.g. QGIS or you will need to insert the result into a new table.

** NOPE, this should work, but testing it with the line you gave above does not split the line. (postgis 3.2, postgresql 14)**

I assume the postgis dataset is just the waypoints, then whatever program presents the data will draw straight lines between the waypoints. If you want to present the lines along the great circles, you would either have to calculate auxilliary points along the lines or you need to (again) change the projection to a projection where (if possible) these great circle lines comes up as straight lines.

Improve this answer
4
  • Hi MortenSickel - thanks for your comment. Firstly, - apologies for referring to it as the date-line. I know technically it's not the dateline (which isn't consistent at 180W/E) - I just referred to it as such for ease. Secondly - I'm afraid I believe it is a postgis issue. The reason I say that is because when I analyse the line (I am calculating which seas / oceans the line intersects) it analyses it as per the image attached in the original post. So, it's not just how postgis happens to be projecting that line on the screenshot I attached, it's actually analysing it in that way too.
    – Thomas Owen
    Commented Jul 11, 2022 at 9:03
  • Hi Thomas, I added a statement that I think do the split you want if you cannot change projection to avoid the "wrong way around the globe"-problem
    – MortenSickel
    Commented Jul 11, 2022 at 9:38
  • Hi MortenSickel - that's great, thanks! My question, then, is how to do the split? As mentioned in my original post "I have Googled the heck out of this issue and the only thing I can find are answers where people say "You need to split the line at the international date line" but not actually saying how." I was hoping that there would be a built-in function to achieve this. I have tried ST_WrapX(geom, 0, 360) - (where geom is the linestring in my original post) but I keep getting the message : ERROR: line_locate_point: 1st arg isn't a line
    – Thomas Owen
    Commented Jul 11, 2022 at 9:47
  • Hi Morten - thanks for the edit. Am looking at your solution now...
    – Thomas Owen
    Commented Jul 11, 2022 at 9:54
1

MortenSickel's answer got me most of the way. What I did was I managed to use this code:

SELECT ST_WrapX(ST_Split((SELECT ST_ShiftLongitude(
    'SRID=4326;LINESTRING (MY LINESTRING COORDINATES)'::geometry
)),(SELECT ST_ShiftLongitude(ST_GeogFromText('LINESTRING(180 90, 180 -90)')::geometry))),180,-360)
AS mysplitline

This has done the trick and has split the linestring correctly at the 180 E/W point.

Thanks all!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.