[GIS] MySQL Using ST_CONTAINS to obtain points within a bounding box

geometryMySQLspatial-query

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?

Best Answer

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.

Related Solutions

MySQL GIS – Finding Features Within Given Coordinates and Distance Using MySQL

Mapperz's answer is invalid. Sinus must be calculated from latitude and NOT from longitude. So corect SQL statement is:

SELECT
    id, (
      3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

[GIS] Select all points within a a geojson polygon from Mysql

Although I am not a mysql user, I am pretty sure this can be solved by building an index on your geometry columns. Currently it is doing an 'Block Nested Loop' that checks every geom against every other. When 4 million points have to be checked against 247 polygons for falling inside or outside the polygon, that takes time. The index will first check if it falls inside the bounding box.

Some other tricks that will help:

- store your geometries as mysql geometry (not as geojson)
- use ST_Intersects instead of ST_Within (since points can not be overlapping anyway)
- build spatial indexes on all geometry fiels (as said )

Check the mysql manual on how to do the indexex: https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html