locked
How to incorporate Accuracy of a Lat Long RRS feed

  • Question

  • I'm an old SQL Server hand.  I have been struggling with efficiently storing lat long locations and effectively retrieving them.  The old way works but we have been hitting performance problems with the number of locations we now have defined.  Decided to give the geography data type a go and WOW what an improvement on performance.  Great stuff.  Still trying to figure out how to best implement.

    Our locations come in via devices that supply a lat, long, and accuracy in meters.  In the past we used not only the lat long of the defined locations but the accuracy in order to provide a buffer around them.  So instead of a pin point on a map, it was a circle with a radius that varied depending on the accuracy of the initial definition.  So when another device is trying to find defined locations within a certain distance, we could factor in the accuracy of the requesting device as well as the accuracy of any defined locations.

    So I have convered all of the lat long locations within our database to the geography type but how do I utilize the accuracy?  Any guidance would be much appreciated.  I know it is probably quite simple for you guys but I don't have anyone to bounce this stuff off of.

    Thanks in advance!

    Steve

    Monday, March 28, 2011 4:34 AM

All replies

  • Hi there,

    It's interesting to hear that you find the geography datatype faster than your old method (which I assume used seperate numeric columns for lat/long) - in my experience people tend to find using the spatial datatypes more feature rich, and more accurate, but slower than other alternatives. Anyway...

    Assuming that 'accuracy' of each point can be represented as a single floating point number, then you could use the M (or Z) coordinate value associated with each point to represent its accuracy. i.e. rather than

    POINT(52.6 1.26)

    for a point stated with accuracy of 10m, use:

    POINT(52.6 1.26 10)

    The advantage of this method is that the value becomes serialised as part of the same instance as the lat/long coordinates themselves, can be copied around with them etc. The disadvantage is that, since SQL Server doesn't perform calculations with respect to 3- or 4- dimensions,  the Z and M values associated with an instance may get lost following certain operations. For more info, see the connect issue I raised (marked as "won't fix"): http://connect.microsoft.com/SQLServer/feedback/details/588622/preserve-z-and-m-coordinates-through-geometry-geography-operations

    Seeing as you're familiar with SQL Server, why not just have two columns in your table - one column of the geography datatype, which records the lat.long of each point, and another column of the float/int datatype (whatever is more suitable) to record the associated accuracy?


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Proposed as answer by Peja Tao Monday, April 4, 2011 3:18 AM
    Monday, March 28, 2011 7:49 AM
    Answerer
  • Thank you very much for the response.  Performance improvement... YES!  I had the traditional two columns with LAT/LONG as floats.  Some SELECTS were taking up to 10 seconds.  I added the geography column and index, the same selects went down to less than one second.  I read plenty of posts around the Net purporting the increase in speed.  That is why I went down this path.

    As for using the Z or M, I thought of that too but as you mention they are not used for any built in distance functions.  At least that is what I thought I read.  I do carry the accuracy as a separate field but I was hoping I could use the accuracy for a geometry column (or something else built into one of the types) and be able to take advantage of the speed I've seen with geography.  It seems that if you use anything OTHER than the index on the geography column the speed gain goes out the window.

    Steve

    Monday, March 28, 2011 5:57 PM
  • I should also add this...

    I can compensate for the  accuracy on one side of the seach by increasing the distance "away" that I am searching for a given point.  However, if I would like to be able to compensate for the accuracy of the defined location by including that within a point. 

    Here is a quick example of only using the accuracy of one of device to adjust the distance

    DECLARE
    
    	@Latitude			DECIMAL(18,15),
    	@Longitude			DECIMAL(18,15),
    	@Radius				FLOAT,
    	@Accuracy			INT
    
    SET @latitude	= 35.862509300000000
    SET @longitude	= -86.464001600000000	
    SET @Distance	= 3000			-- The distance
    SET @Accuracy	= 65			-- Accuracy of requesting device
    
    -- Local Variables
    
    DECLARE @target_location GEOGRAPHY
    
    -- Convert Latitude and Longitude to a GEOGRAPHY point
    SET @target_location = geography::STGeomFromText('POINT(' +CONVERT(VARCHAR(20), @Longitude)+ ' ' +CONVERT(VARCHAR(20), @latitude)+ ')', 4326);
    
    -- Adjust the Radius to include the calculated accuracy
    SET @Distance= @Distance + @Accuracy
    
    SELECT	location_id,
    	location_name,
    	accuracy
    		 
    FROM	geo_location g (NOLOCK)
    		
    WHERE	g.location.STDistance(@target_location) <= @Distance
    
    ORDER BY distance ASC

    I was hoping to be able to include something in the definition of the point for the accuracy so that the accuracy of the defined point could be used to adjust both sides, in effect move the point closer or to make it a circle with a radius (accuracy) instead of a single point.  When I factor in the accuracy as shown below the query takes 20 seconds to execute instead of < 1.

    SELECT	location_id,
    	location_name,
    	accuracy
    		 
    FROM	geo_location g (NOLOCK)
    		
    WHERE	g.location.STDistance(@target_location) <= (@Distance + accuracy)
    
    ORDER BY distance ASC

    I COULD do this by 'padding' the accuracy at the outset with an exagerrated accuracy and then filtering the result set with it.  But I have to initially pull (probably) more points back first.

    DECLARE
    
    	@Latitude			DECIMAL(18,15),
    	@Longitude			DECIMAL(18,15),
    	@Distance			FLOAT,
    	@Accuracy			INT
    
    SET @latitude	= 35.862509300000000
    SET @longitude	= -86.464001600000000	
    SET @Distance	= 3000
    SET @Accuracy	= 65
    
    -- Local Variables
    
    DECLARE
    
    	@target_location		GEOGRAPHY,
    	@pad_for_accuracy		INT
    	
    SET @pad_for_accuracy = 3000
    
    -- Convert Latitude and Longitude to a GEOGRAPHY point
    SET @target_location = geography::STGeomFromText('POINT(' +CONVERT(VARCHAR(20), @Longitude)+ ' ' +CONVERT(VARCHAR(20), @latitude)+ ')', 4326);
    
    -- Adjust the Radius to include the calculated accuracy
    SET @Distance = @Distance + @Accuracy
    
    -- Add in the pad for accuracy of the defined locations
    SET @pad_for_accuracy = @Distance + @pad_for_accuracy
    
    SELECT * FROM
    (
    SELECT	location_id,
    		location_name,
    		g.location.STDistance(@target_location) as distance,
    		latitude,
    		longitude,
    		accuracy
    		 
    FROM	geo_location g (NOLOCK)
    		
    WHERE	g.location.STDistance(@target_location) <= @pad_for_accuracy
    		
    ) as initial
    
    WHERE distance < @Distance
    
    ORDER BY
    
    		distance ASC

    While this is okay, the optimal way would be to have brief SELECT that only returns the rows I want.  I was hoping that there was something within one of the two new data types to help me with this.  Perhaps a geometric shape?  Circle instead of point?

    Steve

    Monday, March 28, 2011 6:19 PM