MySQL LineString intersections with Linestring or Polygon

Question

I have a problem with mySQL spatial functions. My target is to find out if a LINESTRING object is passing through a POLYGON object or not. To determine that I've tried to experiment with two LINESTRING objects to determine if they cross or not.

SET @ls='LINESTRING (0 0, 1 1, 2 2, 3 3)'; -- original linestring
SET @lp='LINESTRING (0 1, 1 2, 2 3, 3 4)'; -- parallel linestring
SET @lx='LINESTRING (0 3, 1 2, 2 1, 3 0)'; -- crossed linestring

i've tried several functions to reach my goal:

SELECT crosses(GeomFromText(@ls), GeomFromText(@lx)); -- crossing linestrings
returns 0;

SELECT intersects(GeomFromText(@ls), GeomFromText(@lp)); -- parallel linestrings
returns 1;

SELECT overlaps(GeomFromText(@ls), GeomFromText(@lp)); -- parallel linestrings
returns 1;

I understand that it is the question of boundary comparison or sth but is there a way (or a function, or a simple solution) how to reach my goal? Other possibility would be to check if a POINT in LINESTRING is within a POLYGON but I was wondering if there's another way to do this?

solution provided in MySQL Great Circle intersection (do two roads cross?) doesn't help me unfortunately..

Answer 1

From the MySQL documentation for 'Functions That Test Spatial Relationships Between Geometries' in 5.6.1:

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

Your intersects function, for example, returns true because the minimum bounding rectangle (MBR) of the two shapes do intersect. If you use ST_Intersects instead (provided you have MySQL 5.6.1 or higher) then it will return false as intended. These functions can be used with points, lines and polygons - so should solve your 'line intersecting polygon' query also ( ST_Crosses also exists if required).