MySQL Using ST_CONTAINS to obtain points within a bounding box

Question

Can someone give me an insight into why the following MySQL code is not working. I have taken some of this code from this link and it is all about just proving the concept before applying to my site. What I want to do is find the points on a line that are bounded by a polygon.

The line is defined by:

LINESTRING (-4 5,5 5,10 5,12 5)

and is entered into the mySQL new_table row new_tablecol as a BLOB. If I inspect the field it is consistent with the LINESTRING above.

I now have defined a bounding box and SQL using the following syntax:

SET @g1 = GeomFromText('Polygon(0 0, 10 0, 10 10, 10 0, 0 0)');
SELECT * FROM `new_table` WHERE ST_CONTAINS(`new_tablecol`,@g1)

Clearly the bounding box includes some of the LINESTRING, but no matter how hard I manipulate this I can't get any points returned.

Can anyone help?

Answer 1

For anyone having the same problem - the answer is that an incorrect syntax was being using for the POLYGON declaration. The POLYGON should have been declared using the following method:

 SET @g1 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');

Notice the double brackets around the POLYGON coordinate enteries.

Answer 2

Clearly the bounding box includes some of the LINESTRING, but no matter how hard I manipulate this I can't get any points returned.

ST_CONTAINS returns true only if one feature contains the other. So the polygon has to include *of the LineString to be returned. But in your LineString, the first point (-4 5) is outside the box.