By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();
| VERSION() |
|---|
| 8.0.25 |
DROP TABLE IF EXISTS `tbl_kml_data`;
CREATE TABLE IF NOT EXISTS `tbl_kml_data` (
`tbl_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`boundry` POLYGON,
`f_polygon` POLYGON
);
INSERT INTO `tbl_kml_data` (`boundry`)
VALUES
(ST_GeomFromText('POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.54605968900006 80.15315188800008,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003))')),
(ST_GeomFromText('POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327,-74.13591384887695 40.93750722242824))'));
SELECT
`tbl_id`,
ST_AsText(`boundry`),
ST_AsText(`f_polygon`)
FROM `tbl_kml_data`;
| tbl_id | ST_AsText(`boundry`) | ST_AsText(`f_polygon`) |
|---|---|---|
| 1 | POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.54605968900006 80.15315188800008,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003)) | null |
| 2 | POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327,-74.13591384887695 40.93750722242824)) | null |
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;
CALL `new_procedure`;
SELECT
`tbl_id`,
ST_AsText(`boundry`),
ST_AsText(`f_polygon`)
FROM
`tbl_kml_data`;
| tbl_id | ST_AsText(`boundry`) | ST_AsText(`f_polygon`) |
|---|---|---|
| 1 | POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.54605968900006 80.15315188800008,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003)) | POLYGON((6.546269854000059 80.15311213900003,6.54627481600005 80.15298415800004,6.54605968900006 80.15315188800008,6.546251038000038 80.15310380500006,6.546269854000059 80.15311213900003)) |
| 2 | POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327,-74.13591384887695 40.93750722242824)) | POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327,-74.13591384887695 40.93750722242824)) |