CODEWITHSUNDEEP
mysqlvariablesgeometry
user2869135
user2869135

Reputation: 11

Inserting Geometry into mySQL database with variables

I am trying to be able to generate geometry and insert it into a table. I was able to successfully do this in my line that is commented out. I however can not seem to figure out what I am doing wrong when I use variables. I'm missing something I'm sure. Could anyone take a look?

SET @minX = 1;
SET @minY = 2;
SET @maxX = 43;
SET @maxY = 86;
SET @minXminY = concat((@minX),' ',(@minY));
SET @maxXminY = concat((@maxX),' ',(@minY));
SET @maxXmaxY = concat((@maxX),' ',(@maxY));
SET @minXmaxY = concat((@minX),' ',(@maxY));
SET @g1 = concat((@minXminY),", ",(@maxXminY),", ",(@maxXmaxY),", ",(@minXmaxY),", ",(@minXminY));
SET @g = 'POLYGON((@g1))';
##SET @g = 'POLYGON((1 2,43 2,43 86,1 86,1 2))';

INSERT INTO `db`.`table`
(`name`,
`geometry`)
VALUES
('test4',
(GeomFromText(@g)));

Upvotes: 0

Views: 655

Answers (1)

Marc B
Marc B

Reputation: 360592

SQL isn't like a scripting language, where something "hello $foo" will detect the variable $foo embedded in the string and replace the variable with the variable's contents.

Given

SET @g = 'POLYGON((@g1))';

It should be

SET @g = CONCAT('POLYGON((', @g1, '))')

So that the variable can be seen/used as an actual variable, and not simply some plain-text from the DB's point of view.

Upvotes: 1

Related Questions