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

[gameguy56]

Alright, well, it's still not quite working.
What I'm trying to do is geocode addresses from a PGSQL table and input the
lat/lon values back into a geometry column.

I'm going to post the rest of my ruby code to see if that helps (obvious
identifiers omitted)

require 'postgres'
require 'graticule'

g = Graticule.service(:google).new "api key"

conn = PGconn.connect("localhost", 5432, '', '', "geogalumni", "dbname",
"password")


res  = conn.query('SELECT street_number, street_address, city, country,
zip_code FROM geograwdata')

puts res.length


for a in (0...res.length)

  if (res[a][4]=="")
    result = g.locate :street => (res[a][0].to_s+res[a][1]), :locality =>
res[a][2], :postal_code => res[a][4], :country => 'USA'
  else
    result = g.locate :street => (res[a][0].to_s+res[a][1]), :locality =>
res[a][2], :postal_code => res[a][4], :country => res[a][3]
  end
  
    conn.exec('UPDATE geograwdata SET LatLon =
GeometryFromText('POINT(result.coordinates[0].to_s+'
'+result.coordinates[1].to_s)',4326) WHERE oid = (a+17178);')

end
$end

The geocoder works, and the table oids begin at 17178. Addresses in the USA
have no country value. It's complicated further by me being a novice to both
SQL and Ruby, so any help would be much appreciated.

Thanks




Mark Cave-Ayland-3 wrote:
> 
> Hi Joshua,
> 
> On Monday 24 March 2008 00:26:38 Joshua Klein wrote:
>> 'UPDATE geograwdata SET LatLon =
>> GeometryFromText('POINT(somecoordinates)',
>> 4326) WHERE 'oid' = (someoid)') '
> 
> UPDATE geograwdata SET LatLon = GeometryFromText('POINT(somecoordinates)',
> 4326) WHERE oid = someoid;
> 
> Make sure your point coordinates are space separated.
> 
>> This is the code I came up with and it's not working, what is wrong with
>> my
>> code?
> 
> I think that you just have extra 's and brackets in there - mixed-case
> column names should be quoted with "s rather than 's.
> 
> BTW using internal PostgreSQL OIDs has been deprecated for years (because
> they can eventually run out). You really should be using a SERIAL column
> or PRIMARY KEY instead.
> 
> 
> ATB,
> 
> Mark.
> 
> -- 
> Mark Cave-Ayland
> Sirius Corporation - The Open Source Experts
> http://www.siriusit.co.uk
> T: +44 870 608 0063
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: http://www.nabble.com/SQL-Code-to-update-a-geometry-column-cell-by-oid--tp16243327p16269094.html
Sent from the PostGIS - User mailing list archive at Nabble.com.