MySQL, geoPHP, select sites near the current site

Question

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 siteid=27200041 and center="POINT(3334036.5863035773 4663797.457954536)

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?

Answer 1

Thanks, I believe that I figured it out. I should be able to store the "Point(333...,466...)" that was returned in the first SELECT and then feed it to second SELECT.

    SELECT
    siteid
FROM
    `aa_allstate_cty`
WHERE
    ST_INTERSECTS(
        `SHAPE`,
        ST_BUFFER(
            GEOMFROMTEXT(
                'POINT(3334036.5863035773 4663797.457954536)'
            ),
            2640
        )
    )

And then use those results to create my listing. As usual, all I needed to do was ask a public question, and then work it out.

Answer 2

There are two ways to integrate a query into another query. One is by using a sub-select:

SELECT siteid
FROM   aa_allstate_cty
WHERE  ST_Intersects(shape,ST_Buffer(
           (SELECT ST_Centroid(shape) FROM aa_allstate_cty WHERE siteid = 27200041),2640))

The other is by using a join:

SELECT t.siteid
FROM   aa_allstate_cty t
JOIN   aa_allstate_cty j ON siteid = 27200041
WHERE  ST_Intersects(t.shape,ST_Buffer(ST_Centroid(j.shape),2640))

Either is faster and more convenient than converting to Well-Known Text, then using that to perform another query.

If the objects being buffered by 1/2 mile are small, you could use the polygon to start the buffer:

SELECT t.siteid
FROM   aa_allstate_cty t
JOIN   aa_allstate_cty j ON siteid = 27200041
WHERE  ST_Intersects(t.shape,ST_Buffer(j.shape,2640))

and if you want all but the buffered site, you can add a siteid constraint:

SELECT t.siteid
FROM   aa_allstate_cty t
JOIN   aa_allstate_cty j ON siteid = 27200041
WHERE  ST_Intersects(t.shape,ST_Buffer(j.shape,2640))
  AND  t.siteid <> j.siteid