How do I select from a table in MySQL 8 using ST_contains?

Question

I have a table called 'community', which has a column called coordinates, of type Point.

I have a rectangle.

I'm trying to select all the rows whose .coordinates fall within that rectangle.

Here's what I'm trying to do:

SELECT * FROM `tribemap-db`.community
WHERE ST_Contains(POLYGON(34.6002788228068 31.821252014924383, 
35.21282317828298 31.821252014924383,
34.6002788228068 32.08220723021857,
35.21282317828298 32.08220723021857), community.coordinates);

This is giving me an error:

"SELECT" is not valid at this position for this server version, etc.

How do I write this query properly?

Answer 1

You can use the following solution:

SELECT * 
FROM `tribemap-db`.`community`
WHERE ST_Contains(ST_GeomFromText('Polygon((35.21282317828298 31.821252014924383, 35.21282317828298 32.08220723021857, 34.6002788228068 32.08220723021857, 34.6002788228068 31.821252014924383, 35.21282317828298 31.821252014924383))'), `community`.`coordinates`);

demo on dbfiddle.uk

I changed the following to solve this:

• You have to put the definition of the polygon as string into ST_GeomFromText to get a geometry object.
• Your current definition of the polygon is not valid and not a rectangle. I changed the order of the points of the polygon to get a definition of a rectangle. I also added the first point as last point to to close the rectangle.

You can check the polygon with MySQL Workbench. There is a Spatial View and Form Editor to see the result of the definition.

Your current definition of the rectangle (with additional first point as last point too):

The changed definition of the rectangle (changed the order of points and added first point as last point too):

---

Are there any functions that will allow me to get a bounding rectangle from MySQL with fewer corners (say, two opposing)?

You can use ST_MakeEnvelope to create the rectangle with two opposing points:

SELECT * 
FROM `tribemap-db`.`community`
WHERE ST_CONTAINS(ST_MakeEnvelope(ST_GeomFromText('Point(35.21282317828298 31.821252014924383)'), ST_GeomFromText('Point(34.6002788228068 32.08220723021857)')), `community`.`coordinates`);

-- or using syntax (without ST_GeomFromText) possible on MySQL 8.0+
SELECT * 
FROM `tribemap-db`.`community`
WHERE ST_Contains(ST_MakeEnvelope(Point(35.21282317828298, 31.821252014924383), Point(34.6002788228068, 32.08220723021857)),  `community`.`coordinates`);

Answer 2

Got it.

All I need is two opposing corners of the bounding rectangle.

And then:

SELECT * 
FROM `tribemap-db`.`community`
WHERE ST_CONTAINS(ST_MakeEnvelope(
            Point(34.60, 32.08),
            Point(35.21, 31.82)
            ), community.coordinates);

That's it!