locked
How can i get the closest Point or row from given point RRS feed

  • Question

  • How can i get the closest Point or row from given point
     say i have point like 46.3636 & 24.36996 and i want the closest point or line from table
    Prog:\Mohammed Galal
    Wednesday, May 13, 2009 12:15 PM

All replies

  • The simplest method is to calculate the distance of every row from your point, and then just select the top (i.e. closest) row:
    SELECT TOP 1 *
    FROM Table
    ORDER BY
    geomcolumn.STDistance(Point(46.3636,24.26996,0))

    This can be fairly slow, however, because STDistance() is a computationally-expensive function. There are other, quicker methods that generally a two-step process of first identifying a set of candidate geometries, (using STBuffer(), or STDistance() + a numbers table) and then only calling STDistance() on those candidates to find the true closest row.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, May 13, 2009 1:50 PM
    Answerer
  • Tanoshimi, could you elaborate how using an STBuffer() might be faster with some sample code? (I understand how it could be faster by reducing the number of rows to do an STDistance() against...)

    Like, would you put a circular buffer around that point, into a variable table, then distance check against that?
    -Pure Krome-
    Thursday, May 14, 2009 12:09 PM
  • Hi Krome,

    try pages 349-354 - the STBuffer() approach is described under the heading 'Finding nearest neighbors within a fixed search zone'
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, May 14, 2009 12:39 PM
    Answerer