views:

545

answers:

1

I've been working in Postgis for point in poly's and i have this function:

select * from table_name where st_contains(column, st_setsrid(st_makepoint(-92.095109, 46.804100),4326));

the column is a multipoly(((points)))

I'm trying to find a way to do a similar exercise with MySQL, and wondered if there were similar functions to do so. The dev site for MySQL is very limited with the descriptions and documentation on the Multipolys.

Has anyone done something like this?

+1  A: 

MySQL does not implement a lot of spatial queries -- I don't know for sure about contains but there are ways of approximating it and other functions. For example, you can do distances between points by pulling all results with coordinates in a given rectangle and then calculating the distance as described here. You can also use rectangles for an approximation of your contains problem. First, a more general WKT syntax for your select:

select * from table_name where contains(the_polygon, GeomFromText('POINT(12.3 45.6)');

I think the open specification for contains is not implemented, but there is a MySQL function for using a minimum bounding rectangle to approximate whether your point is in the polygon:

select * from table_name where MBRContains(the_polygon, GeomFromText('POINT(12.3 45.6)');

I have ended up using a lot of rectangles in general on MySQL because it is easy to calculate the outer bounds of coordinate sets. Overall PostGIS is much better, but you can do a few cross-database things if you have to.

UPDATE: After this question was asked a nice summary was posted to opengeo.org ; it seems that even where it's not obvious or stated MySQL often uses MBR. Also look at the native postgres types point, lseg, box, path, polygon, and circle if you want to understand more about how PostGIS works and why its 2d feature implementers have a shorter row to hoe in general.

bvmou
Thanks, Got this working perfectly. Thank you for your help and wisdom!
Frederico