Update geometry field using SQL with ActiveRecord

Rails
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

On Mar 7, 10:18 pm, Ilan B. [email protected]

4

Andrew Dubinsky wrote:

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

TIA

On Mar 7, 10:18�pm, Ilan B. [email protected]

Only thing to do now is to debug it… It almost sounds like something to
do with auto_commit but I can’t remember any time AR adapters ever had a
problem in this area.

Check your mysql logs in /var for any clues to your problem as well as
rails dev logs.

I will be watching this post for any more clues you discover…

ilan