Geek Answers Handbook

Equivalent to ST_Buffer for circle search in MySQL?

I need to find a row with a point within the given circle using MySQL GIS. Pseudo-code request example:

select * from gistable g where isInCircle(g.point, circleCenterPT, radius)

It seems that PostGIS can do this using the ST_Buffer function. Does MySQL GIS have similar functionality?

+4
source share
3 answers

As far as I know, buffer functions are not yet implemented in MySQL:

These functions are not implemented in MySQL. They may appear in future releases.

 * Buffer(g,d) Returns a geometry that represents all points whose distance from the geometry value g is less than or equal to a distance of d.

If I understand your question correctly, you don’t even need a spatial function to execute this query, you can use the “regular” SQL query and the Euclidean distance :

 select * from gistable g where SQRT(POW(circleCenterPT.x - point.x,2) + POW(circleCenterPT.y - point.y,2)) < radius

Hope this helps.

Edit: Performance will undoubtedly be a problem with this query.

Regarding spatial functions in MySQL, it seems that recent snapshots include new features like Buffer or Distance. You can try:

+3
source

Even if you use PostGIS, you do not need to use the ST_Buffer function, but ST_Expand , which performs an operation equivalent to this (pseudo-code):

 -- expand bounding box with 'units' in each direction envelope.xmin -= units; envelope.ymin -= units; envelope.xmax += units; envelope.ymax += units; -- also Z coordinate can be expanded this way

In PostGIS syntax, an SQL query typically looks like this:

 SELECT AsText(geom) FROM mypoints WHERE -- operator && triggers use of spatial index, for better performance geom && ST_Expand(ST_GeometryFromText('POINT(10 20)', 1234), 5) AND -- and here is the actual filter condition Distance(geom, ST_GeometryFromText('POINT(10 20)', 1234)) < 5

Find Buffer vs Expand the explanation on the postgis-users mailing list.

Thus, ideally, it would be to replicate similar behavior with MySQL. I'm not a MySQL expert at all, but I assume that this is possible even if there is no ST_Expand function.

Here's how to simulate the ST_Expand function:

 CONCAT('POLYGON((', X(GeomFromText('POINT(10 20)')) - 5, ' ', Y(GeomFromText('POINT(10 20)')) - 5, ',', X(GeomFromText('POINT(10 20)')) + 5, ' ', Y(GeomFromText('POINT(10 20)')) - 5, ',', X(GeomFromText('POINT(10 20)')) + 5, ' ', Y(GeomFromText('POINT(10 20)')) + 5, ',', X(GeomFromText('POINT(10 20)')) - 5, ' ', Y(GeomFromText('POINT(10 20)')) + 5, ',', X(GeomFromText('POINT(10 20)')) - 5, ' ', Y(GeomFromText('POINT(10 20)')) - 5, '))' );

Then combine this result with the query as follows:

 SELECT AsText(geom) FROM mypoints WHERE -- AFAIK, this should trigger use of spatial index in MySQL -- replace XXX with the of expanded point as result of CONCAT above Intersects(geom, GeomFromText( XXX ) ) AND -- test condition Distance(geom, GeomFromText('POINT(10 20)')) < 5

If you work with older versions of MySQL where the distance function is not available, you can simply use the amercader method to calculate based on SQRT.

Hope this gives you some idea.

+1
source

Starting with MySQL 5.7.6.
ST_Distance_sphere (g1, g2 [, radius])

Returns the minimum spherical distance between two points and / or ellipses on a sphere, in meters or NULL if any geometry argument is NULL or empty

The calculations use spherical earth and a custom radius. The optional radius argument must be specified in meters. If this parameter is omitted, the default radius is 6,370,986 meters. The ER_WRONG_ARGUMENTS error occurs if the radius argument is present but not positive.

0
source

More articles:


All Articles