SQL Code to update a geometry column cell by oid?

[gameguy56]

'UPDATE geograwdata SET LatLon = GeometryFromText('POINT(somecoordinates)',
4326) WHERE 'oid' = (someoid)') '

This is the code I came up with and it's not working, what is wrong with my
code?

geograwdata is the name of the table
LatLon is the name of the column

thanks!
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Mark Cave-Ayland-3]

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: [phone]

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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: [phone]

_______________________________________________
postgis-users mailing list
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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);')
>
>  
>
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users