1

In my table I have a column filled with text type lat/lon entries. I want to convert these entries to a point? Or something I can use with PostGIS functions

I am aware of SELECT ST_PointFromText('POINT(-71.064544 42.28787)', 4326);, but it does not work when I try to do SELECT ST_PointFromText('POINT('locdata.loc_geo')', 4326) from locdata

That just gets me this error: ERROR: syntax error at or near "locdata" LINE 1: SELECT ST_PointFromText('POINT('locdata.loc_geo')',.

Extra info: my locdata.loc_geo entries are text entries structured like this: 34.032601, 78.551298 (the order doesnt matter, i dont need an exact place, just get the math working)

5
  • 1
    Coordinates are entered X,Y, so your loc_geo values may be backwards.
    – Vince
    Mar 19 '20 at 17:27
  • The order does matter since the math will be wrong. {34.032601, 78.551298} is ~400nm north-by-east of Murmansk, while {78.551298, 34.032601} is on the India/China border.
    – Vince
    Mar 22 '20 at 0:05
  • yeah but its still an existing coordinate on the globe(the main thing was getting the query working at all, the numbers are just something i randomly mashed in)
    – Bas
    Mar 22 '20 at 0:09
  • If you randomly mash in a far longitude and place it in the latitude spot, your parse will fail. Besides, proper random allocation on the globe would allocate by equal-area, so far-north and far-south values should be less frequent than mid-latitudes -- see gis.stackexchange.com/questions/247113/…
    – Vince
    Mar 22 '20 at 0:18
  • well i know that lat has a -90 90 range and long -180 180, so i did keep that in mind. but now that i did get it working, i can work with pre picked existing locations.
    – Bas
    Mar 22 '20 at 0:18
5

The error occurs because stitching two objects (literal string or column) requires a concatenation operator, like SELECT ST_PointFromText('POINT(' || locdata.loc_geo ||')', 4326) from locdata

But it won't work either because of the coma and the lat-long swap. Instead, you can read the coordinates, split them in an array, cast it to floats (or double), then construct the point using the coordinates:

WITH split_src AS (
  SELECT string_to_array(loc_geo, ', ')::float[]  arr
  FROM locdata)
SELECT st_asText(st_makePoint(arr[2],arr[1])) 
FROM split_src;
2
  • SELECT ST_PointFromText('POINT(' || locdata.loc_geo ||')', 4326) worked(i removed the comma, but what do the || do in a query?
    – Bas
    Mar 21 '20 at 23:51
  • As the answer states, || is the string concatenation operator
    – Vince
    Mar 22 '20 at 0:09

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.