By Taylor Hawkes on August 24, 2012
We will actually be doing this all in MySQL, the title is a bit misleading but in my defense this is still the best way to find all the points in a given polygon using PHP + MySQL we just simply don’t use PHP.
An overview of what we will do goes like this: First we will need to create a MySQL point in polygon function. Secondly we will need to format our table properly so that this function is able to run efficiently on our table.Then finally we need to build a query that uses that function and selects the proper results.
In this particular case we will be working with geographical coordinates lat,lng – this tutorial could also be used in the application of non geographical x,y coordinates.
1. Here is the Mysql Point In Polygon function aka (GISWhithin). Run the below code in MySQL to create this function:
DELIMITER //
CREATE FUNCTION GISWithin(pt POINT, mp MULTIPOLYGON) RETURNS INT(1) DETERMINISTIC
BEGIN
DECLARE str, xy TEXT;
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE p, pb, pe INT DEFAULT 0;
SELECT MBRWithin(pt, mp) INTO p;
IF p != 1 OR ISNULL(p) THEN
RETURN p;
END IF;
SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str;
SET str = REPLACE(str, 'POLYGON((','');
SET str = REPLACE(str, '))', '');
SET str = CONCAT(str, ',');
SET pb = 1;
SET pe = LOCATE(',', str);
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p1x = SUBSTRING(xy, 1, p - 1);
SET p1y = SUBSTRING(xy, p + 1);
SET str = CONCAT(str, xy, ',');
WHILE pe > 0 DO
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p2x = SUBSTRING(xy, 1, p - 1);
SET p2y = SUBSTRING(xy, p + 1);
IF p1y < p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y > m THEN
IF p1y > p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y <= m THEN
IF p1x > p2x THEN SET m = p1x; ELSE SET m = p2x; END IF;
IF x <= m THEN
IF p1y != p2y THEN
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x;
END IF;
IF p1x = p2x OR x <= xinters THEN
SET counter = counter + 1;
END IF;
END IF;
END IF;
END IF;
SET p1x = p2x;
SET p1y = p2y;
SET pb = pe + 1;
SET pe = LOCATE(',', str, pb);
END WHILE;
RETURN counter % 2;
END;
DELIMITER ;
2. Setting up the database table
So the next thing we need to do is create or format a table that has a “Points” Column that column should have a spacial index on it. In this example I will call that column LngLatCoords.
IMPORTANT NOTE: the column is named LngLat not LatLng because Longitude corresponds with the X axis and Latitude corresponds with Y axis ,this can be confusing because things like google maps will give you Lat,Lng coordinates – so you would need to flip this around in order to get corresponding X,Y coordinates. If you are working with non geographical points perhaps XYCoords is better name.
Assuming you have a table with Lat,Lng columns you will need to take those to columns and create one LngLatCoords column. Here is the query for that.
ALTER TABLE mytable ADD coords LngLatCoords;
UPDATE myTable SET LngLatCoords = GeometryFromText( CONCAT( 'POINT(', Lng, ' ', Lat, ')' ) );
CREATE SPATIAL INDEX sx_mytable_coords ON mytable(LatLngCoords);
3. The query for getting points
Lets say this is our polygon points in format “lng lat,lng lat” format.
$polygon = " -89 36, -90 35,-84 35, -81 36, -89 36";
IMPORTANT:
- first and last point must be the same for this GISWithin function to work.
- polygon format is “lng lat,lng lat” – you will pry have to reformat from “lat lng, lat lng”.
- Mysql Polygon(()) is enclosed by two brackets ”((” not just one “(“.
So the query to get all the points within that polygon looks like this:
SELECT * from mytable where GISWithin(LngLatPoint, GeomFromText('Polygon(( -89 36, -90 35,-84 35, -81 36, -89 36))'));
If you have done everything successfully this query will get all the Lng Lat points that you have in your table that fall within Tennessee. Pretty cool huh!