Update geometry field using SQL with ActiveRecord

rubyonrails-talk
1

I am trying to insert lat/long into mysql from a ruby object. Here is the method that mysql requires: "GeomFromText('POINT(35.211232 -111.613529))"

So I need to insert this into the "geometry" field. The only way I can figure out how to do this is to use straight sql. The sql works directly.

UPDATE companies SET location = GeomFromText('POINT(35.211232 -111.613529)') where id =1234

I tried to set the field using assignment:

object.locaiton = "GeomFromText('POINT(35.211232 -111.613529))"

This gives the error: Mysql::Error: #22003Cannot get geometry object from data you send to the GEOMETRY field:

Then I tried:

Company.find_by_sql("UPDATE companies SET location = GeomFromText('POINT(35.211232 -111.613529)') where id = #{object.id}")

Which gives the error:

mysql_adapter.rb:482:in `select': You have a nil object when you didn't expect it! (NoMethodError)

How can I use an update statement in SQL using an ActiveRecord object?

Any help at this point would be appreciated.

2

Andrew Dubinsky wrote:

Company.find_by_sql("UPDATE companies SET location = GeomFromText('POINT(35.211232 -111.613529)') where id = #{object.id}")

class SomeModel < ActiveRecord::Base   def update_geo(point)     res = connection.execute <<- END       update companies set location = GeoFromText('#{point}') where id = #{self.id}     END   end end

hth

ilan

3

Thanks for the help, Ilan.

I implemented that code and the sql errors went away. However, it still does not update the field.

Here is the code I used in the model:

  def update_geo(point)     sql = "UPDATE title_companies set location = PointFromText('POINT(#{point.lat} #{point.long})') WHERE ID=#{self.id};"     self.connection.execute(sql)   end

It runs with no errors, execute returns nil (as documented). I have tried with and without self.save, but with no effect. (GeomFromText == PointFromText)

If I insert the sql directly into the query browser, it works fine.

Am I missing something straightforward, like clearing the connection or using transactions?

TIA