ST_Buffer equivalent for Circle based searches in MySQL?

Question

I need to search for a row with a point that is within a specified circle using MySQL GIS. Pseudocode example query is:

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

It appears that PostGIS can do this via the ST_Buffer function. Does MySQL GIS offer similar functionality?

Answer 1

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 right, you may not even need a spatial function to perform this query, you could use a "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 would certainly be an issue with this query.

As for the spatial functions in MySQL, it seems that the latest snapshots include new functions like Buffer or Distance. You may want to give it a try:

• http://forge.mysql.com/wiki/GIS_Functions
• http://labs.mysql.com/index.php (Look for MySQL 5.1 GIS Snapshots)

Answer 2

Even if you use PostGIS, you don't need to use ST_Buffer function, but ST_Expand which performs operation being 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, SQL query usually looks as follows:

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 explanation in postgis-users mailing list.

So, ideally would be to replicate similar behaviour with MySQL. I'm not MySQL expert at all, but I suppose it is feasible even if there is no ST_Expand function.

Here is how to mimic 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 query like this:

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 MySQL versions where Distance function is not available, then you can just use the amercader's use of SQRT-based calculation.

I hope it gives you some idea.