Rails - Mar 2008 - Update geometry field using SQL with ActiveRecord

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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> AndrewDubinskywrote:
> > 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
>
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Andrew Dubinsky wrote:
> 
> Am I missing something straightforward, like clearing the connection
> or using transactions?
> 
> TIA
> 
> On Mar 7, 10:18�pm, Ilan Berci
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---