CODEWITHSUNDEEP
mysqlmysql-workbench
Thring
Thring

Reputation: 23

MYSQL geometry functions polygon

I'm struggling with MySQL geometry functions. I really want to draw a polygon from various different tables in my database. But I cant seem to figure it out.

Firstly the following code returns "polygon(-10 -5,-10 5,10,5,-10 -5)" which to me looks to be a closed polygon function with four points. 

    set @n_Yval = -5;
    set @p_Yval = +5;
    set @n_Xval = -10;
    set @p_Xval = +10;

    SET @text = (concat('polygon (', @n_Xval, ' ', @n_Yval, ', ', @n_Xval, ' ', @p_Yval, ', ', @p_Xval, ' ', @p_Yval, ',', @n_Xval, ' ', @n_Yval, ')')); 

    select @text;

However the following returns "Error code 3037 Invalid GIS data provided for function st_geometryfrom text"

    set @n_Yval = -5;
    set @p_Yval = +5;
    set @n_Xval = -10;
    set @p_Xval = +10;

    SET @text = (concat('polygon (', @n_Xval, ' ', @n_Yval, ', ', @n_Xval, ' ', @p_Yval, ', ', @p_Xval, ' ', @p_Yval, ',', @n_Xval, ' ', @n_Yval, ')')); 

    set @poly = polygonfromtext(@text);

I am using MySQL 5.7 and its been pointed out that there may be a bugs with geometry functions when I was trying to draw lines using points in a previous post. https://bugs.mysql.com/bug.php?id=80697. If so does anyone know a work around?

Upvotes: 1

Views: 397

Answers (1)

mitkosoft
mitkosoft

Reputation: 5316

You have missed brackets while defining the POLYGON string. And avoid using TEXT which is a reserved word. Also POLYGONFROMTEXT is deprecated, better use ST_POLYGONFROMTEXT, so:

SET @n_Yval = -5; 
SET @p_Yval = +5; 
SET @n_Xval = -10; 
SET @p_Xval = +10; 

SET @txt = CONCAT('POLYGON((', @n_Xval, ' ', @n_Yval, ', ', @n_Xval, ' ', @p_Yval, ', ', @p_Xval, ' ', @p_Yval, ', ', @n_Xval, ' ', @n_Yval, '))');

SELECT @txt; 

SET @poly = ST_POLYGONFROMTEXT(@txt);

SELECT @poly;

Outputs:

+----------------------------------------+
| @txt                                   |
+----------------------------------------+
| POLYGON((-10 -5, -10 5, 10 5, -10 -5)) |
+----------------------------------------+
1 row in set (0.00 sec)

+-----------------------------------------------------------------------------------+
| @poly                                                                             |
+-----------------------------------------------------------------------------------+
| 0x000000000103000000010000000400000000000000000024C000000000000014C000000000000024C000000000000014400000000000002440000000000000144000000000000024C000000000000014C0 |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions