CODEWITHSUNDEEP
mysqlstored-proceduresgisspatial
Chamila Lakmal
Chamila Lakmal

Reputation: 81

Mysql Stored Procedure - Spatial functions

I wanted to update mysql table column using stored procedure. Data type of the column is polygon (spatial data)

SET boundryData = (SELECT boundry FROM tbl_data WHERE tbl_id = currentRow);
SET updatedBoundry = ST_GeomFromText(boundryData);

gives and error saying that invalid data provided to the function and if I hard corded the same data set as follows the stored procedure works as intened

SET updatedBoundry  = ST_GeomFromText('POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003))');

Whole stored procedure as follows

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
    DECLARE rowCount INT;
    DECLARE boundryData longtext;
    DECLARE currentRow INT;
    DECLARE updatedBoundry polygon;

    SET rowCount=(SELECT COUNT(*) FROM tbl_kml_data);
    SET currentRow = 1;
    myUpdateLoop: WHILE (currentRow <= rowCount) DO
        SET @boundryData = (SELECT boundry FROM tbl_kml_data WHERE tbl_id = currentRow);
        SET updatedBoundry  =ST_GeomFromText('POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.54605968900006 80.15315188800008,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003))');        
        /*SET @updatedBoundry = ST_GeomFromText( @boundryData);*/

        UPDATE tbl_kml_data SET f_polygon = ST_GeomFromText(@boundryData) WHERE tbl_id = currentRow;

        SET currentRow = currentRow + 1;
    End WHILE myUpdateLoop;

END

any idea of how to solve this issue

Upvotes: 0

Views: 208

Answers (1)

wchiquito
wchiquito

Reputation: 16551

Do you really need a stored procedure to update the table?. A simple update can do the job:

UPDATE `tbl_kml_data`
SET `f_polygon` = `boundry`;

See dbfiddle.

If you really need to use the stored procedure you can do something like:

DELIMITER //

DROP PROCEDURE IF EXISTS `new_procedure`//

CREATE PROCEDURE `new_procedure`()
BEGIN
  DECLARE `rowCount`, `currentRow` BIGINT UNSIGNED;
  DECLARE `boundryData` LONGTEXT;
  DECLARE `updatedBoundry` POLYGON;

  SET `rowCount` := (
    SELECT COUNT(*)
    FROM `tbl_kml_data`
  );
  SET `currentRow` := 1;

  `myUpdateLoop`: WHILE (`currentRow` <= `rowCount`) DO
    SET `boundryData` := (
      SELECT ST_AsText(`boundry`)
      FROM `tbl_kml_data`
      WHERE `tbl_id` = `currentRow`
    );
    -- SET `updatedBoundry` := ST_GeomFromText('POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.54605968900006 80.15315188800008,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003))');
    -- SET `updatedBoundry` := ST_GeomFromText(`boundryData`);

    UPDATE `tbl_kml_data`
    SET `f_polygon` = ST_GeomFromText(`boundryData`)
    WHERE `tbl_id` = `currentRow`;

    SET `currentRow` = `currentRow` + 1;
  END WHILE `myUpdateLoop`;
END//

DELIMITER ;

See dbfiddle.

It is important to indicate the difference between 9.4 User-Defined Variables and routine variables 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax, are different variables (@boundryData != boundryData).

Upvotes: 1

Related Questions