I have to discover if a given point is at least 500 meters (ou other distance) from a route line that exist in my database recorded using the MySQL Spatial.

I see there is no similar function in MySQL Spatial, and find a previous answer that doesn't work for me because the line is too big (more than 300km) to check with this solution point by point: https://stackoverflow.com/questions/12684148/find-n-nearest-linestring-from-a-point-using-mysql-spatial-extensions

I'm even can't create a buffer (a circle/polygon with a given radius) to the point to check if is even touch.

UPDATE - 12/7 I did it, but MySQL Spatial it is seams not trustable. I made a createBuffer function, to create a 20 points Polygon around the given Point with a meters given distance for the radius: http://pastebin.com/xEFb8ZXi

I'm testing with the QGis the given results from this buffer, and everything is fine with the function (except the meters to decimal degress value that generate smaller then expected, but it is not the issue right now).

And made a few Intersects checks, and this is aways return true, even if the result polygon is not intersects the line. I remade the same tests using just the center point, and the results is the same. I discovery now that the INTERSECT doesn't check the LineString with the Point or Polygon, but the Bounding Box of the LineString, when a indicate a point OUTSIDE the Linestring BBox.

Intersects QUERY Where "rota" is the Linestring data:

 SELECT Intersects(rota, createBuffer(GeomFromText('POINT(-19.7736 -43.7255)'),500)) 
 FROM log_viagem WHERE rota IS NOT NULL;

How can I trust the MySQL Spatial now? Or my concept about INTERSECTS is wrong?

SOLVED: I didn't read the important note at 5.5 version of MySQL:

Note 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.

The Solution is taking with the server administrator to update to 5.6.1, there is an upgrade in the note

Note 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.

As of MySQL 5.6.1, there are also ST_ aliases for existing spatial functions that were already exact. For example, ST_IsEmpty() is an alias for IsEmpty()

  • 1
    Can you add the details in your edit as "an answer", the mark it as "the answer"? – BradHards Dec 28 '13 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.