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)

  • 1
    Coordinates are entered X,Y, so your loc_geo values may be backwards. – Vince Mar 19 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 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 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 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 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;
|improve this answer|||||
  • SELECT ST_PointFromText('POINT(' || locdata.loc_geo ||')', 4326) worked(i removed the comma, but what do the || do in a query? – Bas Mar 21 at 23:51
  • As the answer states, || is the string concatenation operator – Vince Mar 22 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.