Advanced Search

Working with spatial extension

/ Published in: MySQL
Save to your folder(s)
In this snippet we have one table with GPS points of Queensland (Australia) borders and table of polygons. One of the polygons are created from QLD points by procedure **createPolygon**. Function **checkCoordinatesInPolygon** can check whether GPS coordinates are within some polygon or not.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DROP TABLE IF EXISTS `points`;
  2. CREATE TABLE `points` (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `coordinates` point NOT NULL,
  5.   `type` varchar(20) DEFAULT 'click',
  6.   `to_text` varchar(255) DEFAULT '',
  7.   `description` text DEFAULT '',
  8.   PRIMARY KEY (`id`),
  9.   SPATIAL KEY `coordinates` (`coordinates`)
  10. ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  11.  
  12. DROP TABLE IF EXISTS `polygons`;
  13. CREATE TABLE `polygons` (
  14.   `id` int(11) NOT NULL AUTO_INCREMENT,
  15.   `polygon_data` polygon NOT NULL,
  16.   `type` varchar(20) DEFAULT 'click',
  17.   `description` varchar(255) DEFAULT '',
  18.   PRIMARY KEY (`id`),
  19.   SPATIAL KEY `polygon_data` (`polygon_data`)
  20. ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  21.  
  22. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.88866 138.164063)') , type = 'QLD', to_text = '-16.88866 138.164063', `description` = 'Queensland border'; # First/Last point
  23. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.972532 138.186035)'), type = 'QLD', to_text = '-25.972532 138.186035', `description` = 'Queensland border';
  24. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.972532 141.262207)'), type = 'QLD', to_text = '-25.972532 141.262207', `description` = 'Queensland border';
  25. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.897268 141.262207)'), type = 'QLD', to_text = '-28.897268 141.262207', `description` = 'Queensland border';
  26. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.820292 148.952637)'), type = 'QLD', to_text = '-28.820292 148.952637', `description` = 'Queensland border';
  27. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.279873 150.270996)'), type = 'QLD', to_text = '-28.279873 150.270996', `description` = 'Queensland border';
  28. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.704722 151.501465)'), type = 'QLD', to_text = '-28.704722 151.501465', `description` = 'Queensland border';
  29. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.550429 153.391113)'), type = 'QLD', to_text = '-28.550429 153.391113', `description` = 'Queensland border';
  30. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-22.97323 150.534668)') , type = 'QLD', to_text = '-22.97323 150.534668', `description` = 'Queensland border';
  31. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.994263 146.052246)'), type = 'QLD', to_text = '-18.994263 146.052246', `description` = 'Queensland border';
  32. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-14.705467 144.470215)'), type = 'QLD', to_text = '-14.705467 144.470215', `description` = 'Queensland border';
  33. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-14.620439 143.635254)'), type = 'QLD', to_text = '-14.620439 143.635254', `description` = 'Queensland border';
  34. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-11.280414 142.536621)'), type = 'QLD', to_text = '-11.280414 142.536621', `description` = 'Queensland border';
  35. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.988152 141.350098)'), type = 'QLD', to_text = '-16.988152 141.350098', `description` = 'Queensland border';
  36. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.03585 140.251465)') , type = 'QLD', to_text = '-18.03585 140.251465', `description` = 'Queensland border';
  37. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.88866 138.164063)') , type = 'QLD', to_text = '-16.88866 138.164063', `description` = 'Queensland border'; # First/Last point
  38.  
  39.  
  40. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-19.367813 141.04248)') , type = 'QLD_AREA_1', to_text = '-19.367813,141.04248', `description` = 'Queensland block/1';
  41. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.100192 141.306152)') , type = 'QLD_AREA_1', to_text = '-25.100192,141.306152', `description` = 'Queensland block/1';
  42. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-22.97323 150.534668)') , type = 'QLD_AREA_1', to_text = '-22.97323,150.534668', `description` = 'Queensland block/1';
  43. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.994263 146.052246)') , type = 'QLD_AREA_1', to_text = '-18.994263,146.052246', `description` = 'Queensland block/1';
  44. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-19.367813 141.04248)') , type = 'QLD_AREA_1', to_text = '-19.367813,141.04248', `description` = 'Queensland block/1';
  45.  
  46. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-21.646536 143.217773)') , type = 'click', to_text = '-21.646536,143.217773', `description` = 'QLD, IN!';
  47. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-20.662941 134.165039)') , type = 'click', to_text = '-20.662941,134.165039', `description` = 'Northern territory, OUT!';
  48. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-27.867569 144.008789)') , type = 'click', to_text = '-27.867569,144.008789', `description` = 'QLD, IN!';
  49. INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(50.068658 14.430542)') , type = 'click', to_text = '50.068658,14.430542', `description` = 'Europe/prague, OUT!';
  50.  
  51. -- PROCEDURE THAT CREATES QLD MAP POLYGON
  52. DROP PROCEDURE IF EXISTS createPolygon;
  53. DELIMITER //
  54. CREATE PROCEDURE createPolygon(IN `pointType` VARCHAR(50))
  55. 	BEGIN
  56. 		DECLARE pointIdStart int;
  57. 		DECLARE pointIdEnd int;
  58. 		DECLARE polygonData text DEFAULT '';
  59. 		DECLARE polygonToInsert polygon;
  60. 		DECLARE coord text DEFAULT '';
  61.  
  62. 		SET pointIdStart = (SELECT MIN(`id`) as `id` FROM `points` WHERE `type` = pointType LIMIT 1);
  63. 		SET pointIdEnd = (SELECT MAX(`id`) as `id` FROM `points` WHERE `type` = pointType LIMIT 1);
  64.  
  65. 		WHILE pointIdStart <= pointIdEnd DO
  66. 			SET coord = (SELECT `to_text`  FROM `points` WHERE `type` = pointType AND `id` = pointIdStart LIMIT 1);
  67. 			SET coord = REPLACE(coord, ',', ' ');
  68. 			SET polygonData = concat(coord, ',', polygonData);
  69. 			SET pointIdStart = pointIdStart + 1;
  70. 		END WHILE;
  71. 		SET polygonToInsert = GeomFromText(CONCAT('POLYGON((', SUBSTR(polygonData, 1, CHAR_LENGTH(polygonData) - 1) , '))'));
  72. 		INSERT INTO `polygons` SET `polygon_data` = polygonToInsert, `type` = pointType, `description` = '';
  73. 	END//
  74. DELIMITER ;
  75. CALL createPolygon('QLD');
  76. CALL createPolygon('QLD_AREA_1');
  77.  
  78. /**
  79.  * FUNCTION THAT CHECKS WHETHER coordinate IS WITHIN polygon
  80.  * @param VARCHAR(50) Lat,Lng
  81.  * @param VARCHAR(50) Name/Type of polygon
  82.  */
  83. DROP FUNCTION IF EXISTS checkCoordinatesInPolygon;
  84. DELIMITER //
  85. CREATE FUNCTION checkCoordinatesInPolygon(`coordinates` VARCHAR(50),`polygonType` VARCHAR(50)) RETURNS int
  86. 	BEGIN
  87. 	DECLARE polygonData polygon;
  88. 	SET polygonData = (SELECT polygon_data FROM `polygons` WHERE `type` = polygonType LIMIT 1);
  89. 	SET coordinates = REPLACE(coordinates, ',', ' ');
  90. 	RETURN (SELECT contains(polygonData, GeomFromText(CONCAT('POINT(', coordinates ,')'))) LIMIT 1);
  91. 	END//
  92. DELIMITER ;
  93. SELECT checkCoordinatesInPolygon('-20.730086,144.470215', 'QLD_AREA_1'), checkCoordinatesInPolygon('-22.97323,139.680176', 'QLD_AREA_1'), checkCoordinatesInPolygon('-21.386249,143.986816', 'QLD_AREA_1');

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.