1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| DELIMITER $$
DROP PROCEDURE IF EXISTS `hakim_test`.`map_hotels`$$
CREATE PROCEDURE `hakim_test`.`map_hotels` (in swLat double,in swLon double,in neLat double,in neLon double)
BEGIN
declare done INT DEFAULT 0;
declare hotel_id int ;
declare lat_hotel double;
declare long_hotel double;
declare curseur1 CURSOR FOR SELECT id,latitude, longitude from accom_details;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
delete from geo_hotel;
OPEN curseur1;
REPEAT
FETCH curseur1 INTO hotel_id,lat_hotel,long_hotel ;
while hotel_id is not null
INSERT INTO geo_hotel (id,lat,lon,g )
VALUES( hotel_id,lat_hotel,long_hotel, GeomFromText(CONCAT('POINT(', lat_hotel,' ', long_hotel,')')));
end while
END REPEAT;
CLOSE curseur1;
END$$
DELIMITER ; |
Partager