I am using GPS coordinates and I am trying to create lines (routes) from lon,lat. I create a geography column from my point and then I create the line as following:

ST_MakeLine(point_geom order by timestamp asc)

Although I have an issue on -180, 180 producing the following: enter image description here

Does anyone have an idea how I can solve this?


You can use ST_ShiftLongitude() to fix this:

Reads every point/vertex in every component of every feature in a geometry, and if the longitude coordinate is <0, adds 360 to it. The result would be a 0-360 version of the data to be plotted in a 180 centric map


Note, that this will fix issues on 180° Line, but will introduce the very same issue on the 0° line. If you have geometries that cross 0 and 180 degree line, it does not work.

I use something like this to determine if my geometry crosses the dateline:

abs(ST_XMin(geometry)-ST_XMax(geometry)) > 180

So, I can use this:

    abs(ST_XMin(geometry)-ST_XMax(geometry)) > 180
END as geom

This works for any kind of geometry. But Same issue here, works only if you have no Geometries that are really more than 180° wide.

| improve this answer | |
  • thanks for this, although a proper solution would be to solve this issue even there are point crossing 0 and 180 – Nikolaos Epitropakis Nov 26 '18 at 12:39
  • Sure, but unfortunately I have no better solution and afaik there is no automated solution that may fix this, because you just cannot determine how a geometry is intended to be. – pLumo Nov 26 '18 at 12:43

Thanks for the input. Actually I solved it using the following :

**select *, case when ST_Intersects(line::geography, ST_GeomFromText('LINESTRING(180 -90, 180 0, 180 90)', 4326)::geography) = True THEN ST_Shift_Longitude(line) ELSE line END AS newLine From tableName**

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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