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)

Bas
  • 25
  • 4
  • 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 00: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 00: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 https://gis.stackexchange.com/questions/247113/how-to-properly-set-up-indexes-for-postgis-distance-queries/247131#247131 – Vince Mar 22 '20 at 00: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 00:18

1 Answers1

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;
JGH
  • 41,794
  • 3
  • 43
  • 89