ST_MakeLine not returning proper latitude/longitude values

Question

I have a table consisting of latitude, longitude and geom values:

 index |         longitude         |         latitude          |                        geom                        | cluster_id 
-------+---------------------------+---------------------------+----------------------------------------------------+------------
 18002 | 10.5185737487922670000000 | 52.2491934541062800000000 | 0101000020110F00000CCD0946EADD3141FD8A5F36DF1C5A41 |          1
 19088 | 10.5185833538704580000000 | 52.2492122543443860000000 | 0101000020110F00002A04C357EBDD314170632611E01C5A41 |          1
 20256 | 10.5185842237361060000000 | 52.2492442532568550000000 | 0101000020110F000058108D70EBDD3141FECB8485E11C5A41 |          1
  9420 | 10.5185872545577310000000 | 52.2492660247580400000000 | 0101000020110F00003139ECC6EBDD31417342DF82E21C5A41 |          1

I calculated my geom column like so:

UPDATE coordinates SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 3857)

I calculate the cluster_id in my python script by using a clustering algorithm (DBSCAN).

I am attempting to make a LineString within clusters, using this query:

SELECT ST_AsGeoJSON(ST_MakeLine(v.geom)) FROM (SELECT geom FROM vehicle_coordinates WHERE cluster_id = 1) as v;

An example of result of the query is: [[1173235.91018827,6845036.84972168],[1173242.1166575,6845037.7691405],[1173248.6137255,6845039.6218353],[1173253.66564705,6845041.21341728]]

Obviously this doesn't make sense as they should be valid GPS coordinates. I'm not sure what I'm doing wrong.

Answer 1

This:

UPDATE coordinates 
 SET geom = ST_Transform(
             ST_SetSRID(
               ST_MakePoint(longitude, latitude),
               4326
               ),
             3857
            )

is transforming your lat-long coordinates in the two columns into a geometry in SRID 3857, which is not degrees lat-long - its a cartesian system used by Google Maps (and other web map systems) and is approximately a measure in metres.

This is what you probably want for a clustering algorithm so your distances are in metres. To get back to lat-long, transform your geometry to SRID 4326. I think

update coordinate set geom = st_transform(geom, 4326);

might do it - if not then help on transforming PostGIS coordinate systems should be easily found.