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.


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