1

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?

  • you can buffer your centroid and use intersect get the polygons within 2640/5280 feet gis.stackexchange.com/questions/310859/… – Mapperz Apr 16 at 21:01
  • Thanks. I had seen that before, but I get thrown by the examples that either use fake numbers (like simple polygons) or have the added problems of dealing with lat/long. Still, revisiting that helped me focus a bit. – vrteach Apr 16 at 21:28
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.

  • 1
    There's no reason you can't use the output of one query directly, without cutting and pasting the WKT from one to the other. In fact, it would be faster if you don't go through WKT. Note that ST_Centroid can generate values outside of the selected geometry if it is concave or has holes. ST_PointOnSurface is slower, with it won't generate incorrect results. – Vince Apr 16 at 21:48
  • Thanks, I'll look into it. I like the idea of doing it in one query, although the two-step method is moving plenty quick for my uses. – vrteach Apr 17 at 14:30
  • Oh, and I understand that the centroid can be outside the polygon. That's OK in this case because I'm just using it to create a buffer and my list of "nearby sites" is really just a guide for the user. As an aside, we also have a layer of project areas, and for those the centroid would be pretty worthless (think of a county-wide rural waterline survey). – vrteach Apr 17 at 14:39
1

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
  • OK, thanks. When you say "unsuitable for reliable distance calculation", how unreliable are we talking? Remember that I'm dealing with archaeological sites, and the boundary of such sites is usually a very fuzzy interpretation by the field archaeologist. A 10-meter margin of error is negligible in rural areas. Urban areas are a little different, but still "nearby" can mean adjacent 2-3 blocks in distance. – vrteach Apr 17 at 17:05
  • Well, Mercator claims Greenland is 10,100km tall, when it's actually 2600km, so we're not talking about a 10 meter error. The actual error is a function of latitude (over Libya, the error is only 150 kilometers over a 1400km tall country). The rule of thumb is Don't use Mercator for any distance calculation. – Vince Apr 17 at 18:21
  • Ah. Thanks again, very useful. I looked back in my records, and I'm actually using a transverse_mercator projection, described here: spatialreference.org/ref/sr-org/… Hope it's an improvement! I essentially use what I'm given. – vrteach Apr 17 at 20:03
  • Whew. That's why it's good to specify the exact SRID in the question. – Vince Apr 17 at 20:36
  • Yup, sorry about adding extra work for you through an incomplete question on my part. Thanks so much for looking out for me, and particularly for the single-query examples. – vrteach Apr 17 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.