/ Published in: MySQL
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
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 INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.972532 138.186035)'), type = 'QLD', to_text = '-25.972532 138.186035', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.972532 141.262207)'), type = 'QLD', to_text = '-25.972532 141.262207', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.897268 141.262207)'), type = 'QLD', to_text = '-28.897268 141.262207', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.820292 148.952637)'), type = 'QLD', to_text = '-28.820292 148.952637', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.279873 150.270996)'), type = 'QLD', to_text = '-28.279873 150.270996', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.704722 151.501465)'), type = 'QLD', to_text = '-28.704722 151.501465', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.550429 153.391113)'), type = 'QLD', to_text = '-28.550429 153.391113', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-22.97323 150.534668)') , type = 'QLD', to_text = '-22.97323 150.534668', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.994263 146.052246)'), type = 'QLD', to_text = '-18.994263 146.052246', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-14.705467 144.470215)'), type = 'QLD', to_text = '-14.705467 144.470215', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-14.620439 143.635254)'), type = 'QLD', to_text = '-14.620439 143.635254', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-11.280414 142.536621)'), type = 'QLD', to_text = '-11.280414 142.536621', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.988152 141.350098)'), type = 'QLD', to_text = '-16.988152 141.350098', `description` = 'Queensland border'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.03585 140.251465)') , type = 'QLD', to_text = '-18.03585 140.251465', `description` = 'Queensland border'; 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 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'; 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'; 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'; 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'; 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'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-21.646536 143.217773)') , type = 'click', to_text = '-21.646536,143.217773', `description` = 'QLD, IN!'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-20.662941 134.165039)') , type = 'click', to_text = '-20.662941,134.165039', `description` = 'Northern territory, OUT!'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-27.867569 144.008789)') , type = 'click', to_text = '-27.867569,144.008789', `description` = 'QLD, IN!'; INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(50.068658 14.430542)') , type = 'click', to_text = '50.068658,14.430542', `description` = 'Europe/prague, OUT!'; -- PROCEDURE THAT CREATES QLD MAP POLYGON DELIMITER // WHILE pointIdStart <= pointIdEnd DO END WHILE; SET polygonToInsert = GeomFromText(CONCAT('POLYGON((', SUBSTR(polygonData, 1, CHAR_LENGTH(polygonData) - 1) , '))')); END// DELIMITER ; /** * FUNCTION THAT CHECKS WHETHER coordinate IS WITHIN polygon * @param VARCHAR(50) Lat,Lng * @param VARCHAR(50) Name/Type of polygon */ DELIMITER // END// DELIMITER ; 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');