# PostGIS MakeLine issue

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:

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

ST_ShiftLongitude(ST_MakeLine(...))

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:

CASE
WHEN
abs(ST_XMin(geometry)-ST_XMax(geometry)) > 180
THEN
ST_Shift_Longitude(geometry)
ELSE
geometry
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.

• thanks for this, although a proper solution would be to solve this issue even there are point crossing 0 and 180 Commented Nov 26, 2018 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. Commented Nov 26, 2018 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