Using SQL for rails parameter/value

Question

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.

Andrew Cetinic · Accepted Answer · 2012-11-30 02:58:23Z

1

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.

answered Nov 30, 2012 at 2:58

Andrew Cetinic

2,83530 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

n_b Over a year ago

I was considering just doing an update after the initial insert, but that defeats the purpose of trying to do it in rails. Also, would have to put in a dummy value as location is NOT NULL.

Andrew Cetinic Over a year ago

Ye if you need NOT NULL then can't do it this way. Will have to look into a gem as zetetic mentioned.

zetetic · Accepted Answer · 2012-11-30 02:59:58Z

0

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

answered Nov 30, 2012 at 2:59

zetetic

47.6k10 gold badges114 silver badges119 bronze badges

Collectives™ on Stack Overflow

Using SQL for rails parameter/value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related