2

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?

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.

  • Is there anyway around this, as on a map with lots of LINESTRING routes, the bounding box would have to be impractically large? – Alexander Oct 25 '16 at 12:47
  • @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/… – tinlyx Oct 25 '16 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. – Alexander Oct 25 '16 at 14:10
0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.