See if lat / long falls within a polygon using mysql

I have the created the table below

CREATE TABLE geom (g GEOMETRY);

and have inserted many rows, example below:

INSERT INTO geom (g)
VALUES(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))')
);

Now I want to search all the data and return the entries where a lat / long I have falls withn any of the polygons.

How can this be done using mysql? or is anyone aware of any links that will point me in the right direction?

Solution 1:

MySQL as of v5.1 only supports operations on the minimum bounding rectangles (MBR). While there is a "Contains" function which would do what you need, it is not fully implemented and falls back to using MBRContains

From the relevant manual page

Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions. This includes functions in the following list other than Distance() and Related().

These functions may be implemented in future releases with full support for spatial analysis, not just MBR-based support.

What you could do is let MySQL give you an approximate result based on MBR, and then post process it to perform a more accurate test. Alternatively, switch to PostGIS!

(Update May 2012 - thanks Mike Toews)

MySQL 5.6.1+ offers functions which use object shapes rather than MBR

MySQL originally implemented these functions such that they used object bounding rectangles and returned the same result as the corresponding MBR-based functions. As of MySQL 5.6.1, corresponding versions are available that use precise object shapes. These versions are named with an ST_ prefix. For example, Contains() uses object bounding rectangles, whereas ST_Contains() uses object shapes.

Solution 2:

If you cannot change dbs to one that has spatial operators implemented correctly like PostgreSQL's PostGIS extension http://postgis.refractions.net/ , you can solve this problem using a two-part approach.

First let MySQL give you an bounding box pre-filtering result based on the bounding box (that is what it does by default) using their intersects operator (http://dev.mysql.com/doc/refman/5.1/en/functions-that-test-spatial-relationships-between-geometries.html#function_intersects). If queries are slow, make sure that you have an index on your geometry field first.

Then hydrate the original geometry that you used in your query into a geometry object of GIS geometry library like GEOS (http://trac.osgeo.org/geos/) (C++ based, although it also has bindings for different languages like Python), Shapely (http://trac.gispython.org/lab/wiki/Shapely), OGR ( or the Java Topology Suite (JTS) http://www.vividsolutions.com/jts/jtshome.htm).

Test each of the geometries that you get back from your query result using the appropriate operator like within or intersects. Any of these libraries will give you a boolean result.

Personally, I would look at the samples for OGR since it has a big community that is ready to help.

Oh yeah, and sorry for putting the links like that... I guess since I am "new" I can only post one link (?)