Re: SQL Code to update a geometry column cell by oid?

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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users