CODEWITHSUNDEEP
sqlruby-on-rails
n_b
n_b

Reputation: 1156

Using SQL for rails parameter/value

Let's say I have a very simple table that just has a single geometry column (point) that the user can update:

CREATE TABLE m_point (
id int(11) NOT NULL AUTO_INCREMENT,
point geometry NOT NULL,
deleted_at datetime DEFAULT NULL,
created_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
PRIMARY KEY (id) )

How can I insert into this using rails, as the geometry column requires actual geometry?

I would have thought this would work:

loc = MPoint.new
loc.point = "POINT(#{params[:x]},#{params[:y]})"
loc.save!

but I get the error:

Mysql2::Error: Cannot get geometry object from data you send to the GEOMETRY field: INSERT INTO `m_point` (`point`) VALUES ('POINT(35, 10)')

as the POINT(X,Y) is seen as rails as being a string. How do I get it so that rails accepts POINT(#{params[:x]},#{params[:y]}) as an unquoted command?

Yes, it would be simple to do an INSERT, but I am wondering if there is any other way, short of installing a gem, to get this to work with rails.

Upvotes: 3

Views: 252

Answers (2)

zetetic
zetetic

Reputation: 47578

I wouldn't expect this to work with vanilla Rails. The GEOMETRY type is specific to MySQL if I recall correctly, and only works with spatial extensions enabled.

You might look at activerecord-mysql2spatial-adapter

Upvotes: 0

Andrew Cetinic
Andrew Cetinic

Reputation: 2835

One approach to do this would be to include a setter for the point attribute in your MPoint model, then execute manual SQL to update the column.

eg.

def set_point(x, y)
   connection.execute("UPDATE m_point SET point = POINT(#{x}, #{y}) WHERE ID = #{self.id}")
end

You could further improve it by calling it on the after_save callback on the model, and use virtual attributes in your model to use in your set_point method.

Upvotes: 1

Related Questions