Posted By

lucien144 on 08/19/10

Tagged

Versions (?)

Last Edited at 08/19/10 05:08pm

Statistics

Viewed 474 times

Favorited by 1 user(s)

Related snippets

Working with spatial extension

/ 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.

Highlighted TextExpand

DROP TABLE IF EXISTS `points`;
CREATE TABLE `points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coordinates` point NOT NULL,
  `type` varchar(20) DEFAULT 'click',
  `to_text` varchar(255) DEFAULT '',
  `description` text DEFAULT '',
  PRIMARY KEY (`id`),
  SPATIAL KEY `coordinates` (`coordinates`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `polygons`;
CREATE TABLE `polygons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `polygon_data` polygon NOT NULL,
  `type` varchar(20) DEFAULT 'click',
  `description` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  SPATIAL KEY `polygon_data` (`polygon_data`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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
DROP PROCEDURE IF EXISTS createPolygon;
DELIMITER //
CREATE PROCEDURE createPolygon(IN `pointType` VARCHAR(50))
	BEGIN
		DECLARE pointIdStart int;
		DECLARE pointIdEnd int;
		DECLARE polygonData text DEFAULT '';
		DECLARE polygonToInsert polygon;
		DECLARE coord text DEFAULT '';
		
		SET pointIdStart = (SELECT MIN(`id`) as `id` FROM `points` WHERE `type` = pointType LIMIT 1);
		SET pointIdEnd = (SELECT MAX(`id`) as `id` FROM `points` WHERE `type` = pointType LIMIT 1);

WHILE pointIdStart <= pointIdEnd DO
			SET coord = (SELECT `to_text`  FROM `points` WHERE `type` = pointType AND `id` = pointIdStart LIMIT 1);
			SET coord = REPLACE(coord, ',', ' ');
			SET polygonData = concat(coord, ',', polygonData);
			SET pointIdStart = pointIdStart + 1;
		END WHILE;
		SET polygonToInsert = GeomFromText(CONCAT('POLYGON((', SUBSTR(polygonData, 1, CHAR_LENGTH(polygonData) - 1) , '))'));
		INSERT INTO `polygons` SET `polygon_data` = polygonToInsert, `type` = pointType, `description` = '';
	END//
DELIMITER ;
CALL createPolygon('QLD');
CALL createPolygon('QLD_AREA_1');

/**
 * FUNCTION THAT CHECKS WHETHER coordinate IS WITHIN polygon
 * @param VARCHAR(50) Lat,Lng
 * @param VARCHAR(50) Name/Type of polygon
 */
DROP FUNCTION IF EXISTS checkCoordinatesInPolygon;
DELIMITER //
CREATE FUNCTION checkCoordinatesInPolygon(`coordinates` VARCHAR(50),`polygonType` VARCHAR(50)) RETURNS int
	BEGIN
	DECLARE polygonData polygon;
	SET polygonData = (SELECT polygon_data FROM `polygons` WHERE `type` = polygonType LIMIT 1);
	SET coordinates = REPLACE(coordinates, ',', ' ');
	RETURN (SELECT contains(polygonData, GeomFromText(CONCAT('POINT(', coordinates ,')'))) LIMIT 1);
	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');

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.