4

I've seen this question: See if lat / long falls within a polygon using mysql

Which states that the mysql GIS extensions aren't fully implemented yet. Can someone clear this up for me - does this mean if I have a polygon stored and want to detect if a point falls within a certain polygon, it won't be 100% accurate? If so, how accurate will it be?

Also, the SQL required for this, off the top of my head, would it be something along the lines of:

SELECT * WHERE WITHIN(POINT(_LAT_ _LONG_), `polygon`)

(Where polygon is a table column and the point is constructed from given lat and long values.)

If the accuracy is sufficient, how would you find points close to a given Lat and Long? There's differing opinions regarding using COS/SQRT or built in DISTANCE functions, which is best and/or what's the best method of doing?

Essentially:

  • Are the mySQL GIS extensions up to the job for detecting if a point is within a polygon (has to be fairly accurate, polygon is not a defined shape or size).
  • If they are, what is the best method for detecting this, and additionally want is the best method for finding points near a given Lat and Long
Community
  • 1
  • 1
Ashley
  • 5,939
  • 9
  • 39
  • 82

2 Answers2

3

According to the docs, all functions except Distance() and Related() only test MBR relationships.

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().

It's impossible to say whether accuracy of MBR tests is sufficient as this depends a lot on the shape of your polygons.

"Are the mySQL GIS extensions up to the job for detecting if a point is within a polygon (has to be fairly accurate, polygon is not a defined shape or size)?"

No.

Use PostGIS if you can.

An alternative could be to implement a Ray Casting algorithm for the Point-in-Polygon problem.

underdark
  • 1,146
  • 7
  • 22
  • A small example: SELECT ASTEXT(location) FROM points WHERE WITHIN( location, GEOMFROMTEXT( 'POLYGON(( -81.19171 27.3090129,-81.19171 26.1525330, -80.09308 26.1525330,-80.09308 27.3090129,-81.19171 27.3090129 ))')) – shasi kanth Feb 24 '11 at 11:15
0

It seems that it is fully implemented in the MySQL5.6.1 and above (Docs).

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.

Nasser Al-Wohaibi
  • 4,562
  • 2
  • 36
  • 28