I am trying to set up a table which consists of regions as POLYGON. I want to be able to query the table to find whether a given POINT lies in any of the stored regions or not.
I read that MySQL spatial extensions provide two types of functions to operate with geometries:
I tried following what worked in this post. Here's how I set up my table:
CREATE TABLE `region` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`rpolygon` polygon NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I inserted a few records in the table after getting the polygon definitions from QGIS. I learned about inserting spatial records into a table from here. Here's how I inserted a record in the table
SET @g= ST_GeomFromText('Polygon ((-117.84293016891291472 33.64825334189644224, -117.8428418279120109 33.64599663087331294, -117.84048874488789238 33.64467151585973426, -117.84002294688312418 33.64517746886491523, -117.83983020288114574 33.64549067786812486, -117.83981414088098916 33.64605284787388939, -117.83983823388122403 33.64646242887808825, -117.84012734988419879 33.64708081588442212, -117.84053693088839054 33.64743417988804453, -117.84130790689629009 33.6479401328932326, -117.84199054190328582 33.64815696989545302, -117.84243224690781915 33.64822121789610776, -117.84293016891291472 33.64825334189644224))');
INSERT INTO region
VALUES(1, 'NORTH-EAST', @g);
Just to do sanity check, I tried running a Point in Polygon query using various methods - MBRContains, ST_Contains etc, all of which result in no results or NULL. Below is a sample query that I tried:
SELECT * FROM region where ST_Contains(rpolygon, ST_GeomFromText('Point(33.646201 -117.841949)'));
I am not sure what is going wrong. Am I storing the values incorrectly, do you set up a geo table in a different way?, or am I querying incorrectly. I couldn't find any good tutorials online or examples that I could follow to do such a setup. Any pointers will be helpful!
