MySQL 8.0: Create a polygon as a circle around a point

Question

I want to generate a polygon as a circle with a 10-kilometre radius around a defined point in MySQL (no PHP or other programming languages). The point is provided as a geographical coordinate with longitude-latitude, e.g. "100.8160803 13.7538929" for the city of Bangkok, the SRID is 4326.

The polygon does not have to be a perfectly rounded circle, a hexagon or octagon would be fine, too.

Tried to use ST_Buffer but this does not work because it can't handle SRIDs (apart from SRID 0, of course).

I found many tutorials/queries on how to locate points in a certain radius around a point, like here: MySQL - Find points within radius from database

And here is the code for such a query:

SELECT id, 
( 6371 * 
    ACOS( 
        COS( RADIANS( db_latitude ) ) * 
        COS( RADIANS( $user_latitude ) ) * 
        COS( RADIANS( $user_longitude ) - 
        RADIANS( db_longitude ) ) + 
        SIN( RADIANS( db_latitude ) ) * 
        SIN( RADIANS( $user_latitude) ) 
    ) 
) 
AS distance FROM the_table HAVING distance <= $the_radius ORDER BY distance ASC"

But I still don't know how to generate a circular polygon around a point.

ST_Buffer does not work with SRID 4326. That is a known limitation of this otherwise useful function. — sts, May 14 '19 at 21:53
I know. But st_transform can transform geometries between SRSs. — Shadow, May 14 '19 at 21:55
I read the documentation of ST_Buffer, but I can't find a solution for my case. If you have an idea about a query that works, please post it. In general I would like to work with SRID 4326. — sts, May 14 '19 at 22:06
St_transform is a separate function - perhaps you should read its documentation, not just the st_buffer's. — Shadow, May 14 '19 at 22:28
sorry, typing error: i was refering to ST_Transform in my last post, not to ST_Buffer. — sts, May 14 '19 at 22:41
MySQL ST_Transform does not work with different projections (yet), so no use here. https://dev.mysql.com/worklog/task/?id=8684 — JN01, Nov 15 '19 at 12:43

score -1 · Answer 1 · answered May 15 '19 at 09:39

If the goal is to find points within a distance of a certain point you don't need a circular polygon. Spatial indexes use bounding rectangles, so you aren't going to get a better index using a better approximation of a circle.

Instead of trying to make a circle polygon, make a rectangular one which contains the circle inside. The points of the rectangle for a point (x,y) will be like x-r y-r, x+r y-r, x+r y+r, x-r y+r

Then use the spatial index (with e.g. MBRContains) to get the candidate points (fast), and then use the distance calculation to filter out the points which are within the bounding rectangle, but not in the circle (slow, but for only a few candidates).

Check this answer for more details.

The goal is to create a polygon (radius around a point) that can be compared with other polygons (distribution maps for species) via ST_Intersects. The use case is this: "I am in e.g. Bangkok. Which species live in an area within 100 km from here?" — sts, May 15 '19 at 22:32

MySQL 8.0: Create a polygon as a circle around a point

1 Answers1