See if lat / long falls within a polygon using mysql

by

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.

More Related Contents:

  1. Fastest Way to Find Distance Between Two Lat/Long Points
  2. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  3. How can I make SQL case sensitive string comparison on MySQL?
  4. Library not loaded: libmysqlclient.16.dylib error when trying to run ‘rails server’ on OS X 10.6 with mysql2 gem
  5. Count the occurrences of DISTINCT values
  6. Column count doesn’t match value count at row 1
  7. How to search JSON data in MySQL?
  8. MySQL > Table doesn’t exist. But it does (or it should)
  9. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  10. How do I modify a MySQL column to allow NULL?
  11. Connect to mysql in a docker container from the host
  12. Set NOW() as Default Value for datetime datatype?
  13. MySQL update CASE WHEN/THEN/ELSE [duplicate]
  14. SQL DELETE with JOIN another table for WHERE condition
  15. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  16. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  17. mysql update query with sub query
  18. How can I implement single table inheritance using Laravel’s Eloquent?
  19. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  20. Integrity constraint violation: 1452 Cannot add or update a child row:
  21. How to change the mysql root password
  22. How can I set autoincrement format to 0001 in MySQL?
  23. What does SQL Select symbol || mean?
  24. Why MYSQL IN keyword not considering NULL values
  25. MySQL. Can’t create table errno 150
  26. INSERT INTO Table from multiple tables
  27. Execute INSERT if table is empty?
  28. Mysql How do you create a clustered index?
  29. Counting associated entries with Sequelize
  30. MySQL data directory location

Leave a Comment