4

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1

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.

Improve this answer
3
  • Is there anyway around this, as on a map with lots of LINESTRING routes, the bounding box would have to be impractically large? Commented Oct 25, 2016 at 12:47
  • 1
    @TrueBlue It depends on your objectives. Have you looked at other spatial predicates such as ST_Intersects(), ST_Overlaps and so on? dev.mysql.com/doc/refman/5.6/en/… Commented Oct 25, 2016 at 12:55
  • For anyone wanting to know the solution. Polygons return a null for most of these tests. You just define a polygon using LINESTRING. Commented Oct 25, 2016 at 14:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.