2

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.

1
  • Don't rely on distances in 3857. At such latitude the distortion is huge, greater than 40%. Instead use a local projection (UTM or else). – JGH Oct 15 '19 at 11:20
3

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.

2
  • Oh yeah, I needed to transform it into a Cartesian system as I needed to do some additional calculations based on distance in meters. That's why it's in that state. – foadster Oct 14 '19 at 20:15
  • You were right. Looks like after I changed my column to 4326 and recalculated the geom value, I'm getting appropriate coordinates. – foadster Oct 14 '19 at 20:56

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.