3

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?

2

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.

  • Thanks Evan, Why have you changed the datatype of my columns from Geography to Geometry after you edited my question? – Mazhar Nov 5 '18 at 8:39
  • @Mazhar oversight, there it's fixed. – Evan Carroll Nov 5 '18 at 16:50

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.