Post reply

SQL Spatial - find all locations within X distance of a point.

  • December 11, 2020 at 6:04 pm

    #3819483

    I have a really stupid question, and I'm betting the answer is "Well, what happened when you actually ran the query?", but I'll ask anyway. (Maybe I just need a bigger spatial dataset).

    Say I have a center point P that's my location. I can do all the "find the distance" stuff just fine using STDistance. But assuming I have a ton of points in my dataset, does it make sense to filter them down first to something like "Show me all points within a square with center P" (so something like longitude +- 1/2d, latitude +- 1/2d), and then filtering out all points that are within the square, but not within the circle inscribed inside it?

    Just seems silly to even consider points that are absurdly far apart and then doing unnecessarily complicated math on them for no reason...

    Thanks!

    Pieter

    This article explains briefly how spatial indexing works... (kind of like I was describing... at least I think so!) The Black Art Of Spatial Index Tuning In SQL Server

    • This topic was modified 3 years, 11 months ago by  pietlinden. Reason: More info

  • December 12, 2020 at 6:10 pm

    #3819774

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 18, 2020 at 12:59 pm

    #3822006

    If the table has spatial index ,  SQL use it. (check if inside the box)

    DECLARE @pnt geometry, @crcl geometry;
    DECLARE @dis float = 10000;

    SET @pnt = geometry::Point(210000, 700000, 2039 );
    SET @crcl = @pnt.STBuffer(@dis)

    SELECT [ogr_geometry] FROM [SetlMidName]
      WHERE [ogr_geometry].STWithin(@crcl) = 1;

    SELECT[ogr_geometry] FROM [SetlMidName]
      WHERE @pnt.STDistance( [ogr_geometry] ) <= @dis;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply