I'm working with archaeological sites, stored as polygons in a MariaDB 10.3 table, and I have PHP 7.2 with GEOS and geoPHP available. I'm creating a page that displays all the related information for a site, and I would like to include nearby sites. The site layer is stored in a Mercator coordinate system (Edit: actually Illinois State Transverse Mercator - SR-ORG:8669), with feet as the units, so I think I can use easy distance methods.
I'd like to list any sites within a 1/2 (or maybe 1) mile buffer of the centroid of the current site. I can get the centroid:
SELECT siteid,st_AsWKT(ST_Centroid(aa_allstate_cty.shape)) AS center FROM aa_allstate_cty WHERE siteid = 27200041;
And I get back
But I don't yet understand how to go the next step of going back and selecting other sites(polygons) which overlap a buffer around that point. What would be the format for the next SQL query?