[postgis-users] SQL for a GNIS table update and using GeomFromText

[Donald Harter]

I had this table of GNIS data that I had imported.  Coordinates were
imported as real numbers plongdd and platdd.
I wanted to take those and combine them into a  new geometry column, ploc
ploc is of type geometry POINT, 2 dimensions with SRID=4269
I had much difficulty finding the correct postgis syntax to do this.
For a newbie it could be quite difficult to do this, so I am posting the
correct SQL statement that I used to accomplish this.
I have already created the geometry column using documentation found
elsewhere.

This the  successfull SQL statement:

TN=# update tngnis SET ploc = GeomFromText('POINT(' || "plongdd"::text
||' ' ||  "platdd"::text || ')',4269)   WHERE (plongdd IS NOT NULL) and
(platdd is NOT NULL) ;
UPDATE 67926

The "||" is the concatenation operator for text.  "4269" is the SRID
plongdd and platdd are converted to text through a cast.
There has to be  a space inserted between the 2 coordinates so a ' ' is
used.  There is a space between the ' s.