# Update geometry field using SQL with ActiveRecord

**URL:** <https://www.ruby-forum.com/t/update-geometry-field-using-sql-with-activerecord/131321>\
**Category:** Rails\
**Created:** [March 8, 2008, 3:54am UTC](https://www.ruby-forum.com/t/update-geometry-field-using-sql-with-activerecord/131321 "2008-03-08T03:54:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew\_Dubinsky](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ecd19e/32.png) [@Andrew\_Dubinsky](https://www.ruby-forum.com/u/Andrew_Dubinsky)\
**Post date:** [March 8, 2008, 3:54am UTC](https://www.ruby-forum.com/t/update-geometry-field-using-sql-with-activerecord/131321/1 "2008-03-08T03:54:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Andrew\_Dubinsky](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ecd19e/32.png) [@Andrew\_Dubinsky](https://www.ruby-forum.com/u/Andrew_Dubinsky)\
**Post date:** [March 8, 2008, 4:18am UTC](https://www.ruby-forum.com/t/update-geometry-field-using-sql-with-activerecord/131321/2 "2008-03-08T04:18:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Andrew\_Dubinsky](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ecd19e/32.png) [@Andrew\_Dubinsky](https://www.ruby-forum.com/u/Andrew_Dubinsky)\
**Post date:** [March 9, 2008, 3:37pm UTC](https://www.ruby-forum.com/t/update-geometry-field-using-sql-with-activerecord/131321/3 "2008-03-09T15:37:15Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![Andrew\_Dubinsky](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ecd19e/32.png) [@Andrew\_Dubinsky](https://www.ruby-forum.com/u/Andrew_Dubinsky)\
**Post date:** [March 10, 2008, 2:49am UTC](https://www.ruby-forum.com/t/update-geometry-field-using-sql-with-activerecord/131321/4 "2008-03-10T02:49:28Z")

</div>

Andrew Dubinsky wrote:

> Am I missing something straightforward, like clearing the connection  
> or using transactions?
> 
> TIA
> 
> On Mar 7, 10:18�pm, Ilan B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

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