[postgis-users] SQL Code to update a geometry column cell by oid?

[Kevin Neufeld]

It looks like you are using the single quote to denote your string 
literal being passed into conn.exec().  I suspect you'll need to escape 
the quote wrapping your POINT string.

I don't know Ruby's syntax for quote escaping, but you could try:
conn.exec(
'UPDATE geograwdata SET LatLon = GeometryFromText(\'POINT('
+ result.coordinates[0].to_s + ' '
+ result.coordinates[1].to_s +
')\', 4326) WHERE oid = (a+17178);')

Also, the others are right. The use of an oid column has been 
deprecated.  You should put a primary key or unique index on a serial 
column on your table instead.

Further, I would recommend against using GeometryFromText since using a 
textual representation will introduce coordinate drifting and precision 
loss. 
Use MakePoint instead and you won't have to do any string escaping. Perhaps:

conn.exec(
'UPDATE geograwdata SET LatLon = SetSRID(MakePoint('
+ result.coordinates[0].to_s + ', '
+ result.coordinates[1].to_s +
'), 4326) WHERE oid = (a+17178);')

Ideally, you would do this using something like Java's PreparedStatement 
and simply set the fields to their actual double value.

Cheers,
Kevin


gameguy56 wrote:
> Alright, well, it's still not quite working.
> ...
>   
>     conn.exec('UPDATE geograwdata SET LatLon =
> GeometryFromText('POINT(result.coordinates[0].to_s+'
> '+result.coordinates[1].to_s)',4326) WHERE oid = (a+17178);')
>
>   
>