2

I'm trying to update a column wkb_geometry in a table locations by creating a point from the eastings and northings cols from the same table. The database is PostGreSQL with PostGIS extension:

UPDATE locations
SET wkb_geometry = st_geometryfromtext('POINT(locations.easting locations.northing)',2770);

I'm getting the following error:

ERROR: parse error - invalid geometry
SQL state: XX000
Hint: "POINT(lo" <-- parse error at position 8 within geometry

The column has been added like this:

ALTER TABLE locations ADD COLUMN wkb_geometry geometry(Geometry,27700);

I suspect that locations.easting and locations.northing are not being substituted with actual values from the respective columns but am not sure how to fix this. The answer I'm after will show how to substitute the easting and northing string with actual values from the column

Alex
  • 479
  • 1
  • 3
  • 12
  • 1
    ST_GeomeyryFromText requires input in Well Known Text (WKT) format, which you are not providing. ST_MakePoint does not (but it requires an ST_SetSRID) – Vince May 10 '17 at 11:58

2 Answers2

6

It is a string, so it can't substitute the values from your columns. You have to concatenate the string:

UPDATE locations
SET wkb_geometry = st_geometryfromtext('POINT('|| easting ||' '|| northing ||')',27700);
Rob
  • 198
  • 2
  • 16
nunatak
  • 86
  • 5
5

As nunatak points out, you're not correctly substituting your values within the string. However, you can avoid string substitution altogether by using http://www.postgis.net/docs/ST_MakePoint.html e.g.

UPDATE locations
SET wkb_geometry = ST_SetSRID(ST_MakePoint(locations.easting,locations.northing),27700);

Note from the postgis docs

ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you have raw coordinates rather than WKT.

I'd always promote using Makepoint over text representation for speed and readability; and you can see using string concatenation etc. ends up looking very messy.

Guy
  • 1,678
  • 11
  • 13
  • Agreed, the ST_MakePoint method makes it clearer where the values are coming from. I suppose the end result is the same though? I'll be using the wkb_geometry column to retrieve records within a given bounding box – Alex May 10 '17 at 12:15
  • The result may well be the same. However, advantages of using the ST_MakePoint include (1) being faster and (2) lending itself to any type checking you may have available – Guy Feb 02 '18 at 11:54