How can I search for all points with a specific range (radius) of another point?

Question

The syntax I use to query for polygons in range of other Points

SELECT  *
FROM    dbo.myTable
INNER JOIN
    dbo.myPOI ON myTable.geog.STIntersects(myPOI.geog) = 1

But, to query points of interests, I use a radius and I'm using this.

SELECT  *
FROM    dbo.myTable
INNER JOIN
    dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius

Is there a better way to write this? Can the queries be improved?

Answer 1

Can the queries themselves be improved?

...
dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius

Yes. That's going to be slow. You could massively improve that query by buffering the point and finding the intersects:

ON myTable.geog.STIntersects(myPOI.geog.STBuffer(PP.Point_Radius)) = 1

You can make this even better in your case because the radius is provided on the row, rather than in the query. You can store the geometery returned by STBuffer on the table instead of the point. If you ever want to to find the center point (which you'll probably never need), you can use the GIS function STCentroid.

Other implementations

As a side note, this type of query is done with ST_DWithin in PostGIS and will run on the index.