locked
GEOGRAPHY Query to retrieve rows where distance is less than Xmeters ordered by proximity (distance) RRS feed

  • Question

  • Hi, sorry but i don't find any info to get this ..

    Supposing the folllowing table :

    CarsLocation

    CarId(int)     Name(nvarchar)        GeoLocation (geography)
    1        Blue            binary data (eg: 10.10, 05.05)
    2        Orange        11.11, 06.06
    3        Yellow         12.12, 07.07
    4        Red             15.15, 12.12


    I need a query to retrieve all cars near Orange car, that distance is less than 10KM, orderd by distance.
    Anything like:

    SELECT * FROM CarsLocation CL
    WHERE CL.GeoLocation <= 10KM than distance of OrangeCa.GeoLocation
    ORDER BY Distance ¿?

    THAT RETURN SOMETING LIKE
    1        Blue            1000 --meters
    3        Yellow         1200
    4        Red             4000



    Any solutions ?
    Optimized performance for thousands of rows

    Saturday, January 16, 2010 5:13 AM

Answers

  • The simple answer(s):
    DECLARE @OrangeCar geography;
    SELECT @OrangeCar = GeoLocation FROM CarsLocation WHERE Name = 'Orange';
    
    SELECT * FROM CarsLocation CL
    WHERE CL.GeoLocation.STDistance(@OrangeCar) <= 10000
    ORDER BY Distance

    Or:
    DECLARE @OrangeCar geography;
    SELECT @OrangeCar = GeoLocation FROM CarsLocation WHERE Name = 'Orange';
    
    SELECT * FROM CarsLocation CL 
    WHERE CL.GeoLocation.STIntersects(@OrangeCar.STBuffer(10000)) = 1
    ORDER BY Distance

    These examples assume that the SRID used in the GeoLocation column uses linear distances measured in metres.
    As for "optimized" performance, both the STDistance <= operator and the STInteresects()  methods used above can use a spatial index as a primary filter, so you'll need to look into your spatial index settings.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Saturday, January 16, 2010 9:58 AM
    Answerer

All replies

  • The simple answer(s):
    DECLARE @OrangeCar geography;
    SELECT @OrangeCar = GeoLocation FROM CarsLocation WHERE Name = 'Orange';
    
    SELECT * FROM CarsLocation CL
    WHERE CL.GeoLocation.STDistance(@OrangeCar) <= 10000
    ORDER BY Distance

    Or:
    DECLARE @OrangeCar geography;
    SELECT @OrangeCar = GeoLocation FROM CarsLocation WHERE Name = 'Orange';
    
    SELECT * FROM CarsLocation CL 
    WHERE CL.GeoLocation.STIntersects(@OrangeCar.STBuffer(10000)) = 1
    ORDER BY Distance

    These examples assume that the SRID used in the GeoLocation column uses linear distances measured in metres.
    As for "optimized" performance, both the STDistance <= operator and the STInteresects()  methods used above can use a spatial index as a primary filter, so you'll need to look into your spatial index settings.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Saturday, January 16, 2010 9:58 AM
    Answerer

  • Wow, Fantastic ! tanoshimi, you're great .

    I was not believe was easy.


    Can you explain me 2 things.

    1)ORDER BY
    Where i write "Order by Distance" i mean to return results ordered by the most near first and the most far away the last row.
    How can it be done ?

    2)PERFORMANCE
    Where i look for spatial index settings ?
    Can you explain me a litle bit more, plus any links are welcome, about that performace applied to this kind of example/situation ?

    DETAILS: On pre-production will be 2 tables: 1 for customers location and another 1 for cars location, each one with a geography data field.
    Can you give me a litle info to make a good performance using a similar SP like the one you write me (but using that 2 tables), what i need to do ?

    Any case,
    Thanks so much


    Saturday, January 16, 2010 3:45 PM
  • 1.) Sorry, I copy and pasted that wrong. DISTANCE is an alias for CL.GeoLocation.STDistance(@OrangeCar)

    2.) When you create a spatial index, you must define the grid resolution (LOW, MEDIUM, or HIGH) at each of the four levels of the grid, and the number of cells per object to store in the index. When you use a spatial predicate such as STIntersects() or STDistance() to compare two geography instances, SQL Server can perform a primary filter of the results by looking at the grid cells occupied by each instance, based on these settings. Changing the grid affects the efficiency of the comparison made by the primary filter, and hence the overall performance of the query. There's been lots of posts here on the forum about choosing optimum index settings, but to start with try creating a MEDIUM resolution index, with 16 cells per object.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Saturday, January 16, 2010 4:27 PM
    Answerer

  • Fantastic, well job, done !
    Saturday, January 16, 2010 5:08 PM