GeoNames Forum

Hi Gavin

I fear SQL Server does not have inbuilt spatial support.

There is a spatial extension for mssql 2005 :
http://www.codeplex.com/MsSqlSpatial

And a microsoft paper :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/TblValFuncSQL.asp

The next version of mssql server will have geospatial support :
http://industry.slashgeo.org/article.pl?sid=07/05/11/1329253

In any case you probably can do without geospatial functions and use
simple indices on the lat and lng columns. Use a rectangular bounding
box for your search large enough to include all your records and filter
out the rest based on exact distance. As a rule of thumb you can assume
one degree is around 111km (=40.000/360).

Hope this helps

Marc

Your idea to use a bounding box to filter records is valid. It is used in all commercial spacial search functions. In fact they all rely heavily on indexes to perform covermap filtering based on various techniques, but the bounding box is a good start.

You are missing zip codes because your bounding box calculation is incorrect. The number of miles in a degree of longitude varies by the cosine of the latitude. Think of the equator vs the 89th parallel, there is alot more earth between the lines at the equator, right?

Use this instead...
Code:

DECLARE @LatRange float
 DECLARE @LongRange float
 SET @LatRange = @Miles / 69.0499 
 SET @LongRange = @Miles / (69.0499 * cos(Radians(@Latitude)) )

You are also implementing the ACOS trig calculation which is susceptable to error over small distances. Use the Haversine method for more accurate small distance calculations. If most of your distance calculations are small distances, you can optimize your distance calculation even further by using simple euclidean geometry (insanely fast). I've found it to be accurate to within 10 ft per Mile, this is plenty accurate if your search is just to filter records.

I bet your code is paralizingly slow too. Here is a tip, create a table valued function to return a subset of GeoNameId's that match your boundary condition. Then create a function to calculate the distance between lat/longs. Then index on your GeoNameId (which I assume you already do).

Here is your final product FTW. It searches 40,000 records and returns 56 zips (on my 3yr old laptop) in 10ms! I suspect further speed improvements may be possible using spacial search in Sql Server 2008.

Code:

 select City, ZipCode, dbo.[GetApproxDistanceBetweenLatLong](41.882582, -87.637601, Latitude, Longitude) as Actual
 from Zip
 join dbo.fZipsNearCoverMap(41.882582, -87.637601, 10) covermap on Zip.ZipId = covermap.ZipId
 and dbo.[GetApproxDistanceBetweenLatLong](41.882582, -87.637601, Latitude, Longitude) <= 10
 order by Actual desc

More info at: http://technet.microsoft.com/en-us/library/aa964138.aspx

Hi everyone!

I *think* I've modified this for use with mysql.. since you can't create functions that return a resultset I've changed it to a stored procedure. I'm no SQL guru though, so it would be awesome if someone smarter than me could provide some guidance on improving this if it needs it

Code:

delimiter $$
 
 CREATE PROCEDURE yourdb.getNearbyLocations
 (
  	Latitude decimal(23,20),
  	Longitude decimal(23,20),
  	MaxDistance int
 )
 BEGIN
   SET @MaxDistanceDec = MaxDistance;
   SET @EarthRadius = 6378.137;
   SET @LatRange = (@MaxDistanceDec / 111); -- 111 is number of km in 1 degree lat
   SET @LongRange = ((@MaxDistanceDec / 111) / cos(Radians(Latitude)));
   SET @MinLatitude = Latitude - @LatRange;
   SET @MaxLatitude = Latitude + @LatRange;
   SET @MinLongitude = Longitude - @LongRange;
   SET @MaxLongitude = Longitude + @LongRange;
 
   -- Search
   CREATE TEMPORARY TABLE IF NOT EXISTS GeoNameIDs
    (GeoNameID INT, distance INT);
 
    INSERT INTO GeoNameIDs (GeoNameID, Distance)
    SELECT GeoNameID,
      ROUND(@EarthRadius * ACOS(ROUND(
      (SIN(RADIANS(Latitude)) * SIN(RADIANS(geo.Latitude))) +
      (COS(RADIANS(Latitude)) * COS(RADIANS(geo.Latitude)) *
      COS(RADIANS(geo.Longitude) - RADIANS(Longitude))),15)),0) AS Distance
    FROM yourdb.geonames AS geo
    WHERE -- Below is rough proximity search to super speed the query up
      (Latitude >= @MinLatitude AND Latitude <= @MaxLatitude AND Longitude >= @MinLongitude AND Longitude <= @MaxLongitude)
    -- Below should result in accurate proximity search
    AND ROUND(@EarthRadius * ACOS(ROUND(
        (SIN(RADIANS(Latitude)) * SIN(RADIANS(geo.Latitude))) +
        (COS(RADIANS(Latitude)) * COS(RADIANS(geo.Latitude)) *
        COS(RADIANS(geo.Longitude) - RADIANS(Longitude))),15)),0) <= @MaxDistanceDec
    ORDER BY 2 ASC;
 
    SELECT * FROM GeoNameIDs;
    DROP TABLE GeoNameIDs;
  END;
 $$
 delimiter ;

Code:

call yourdb.getNearByLocations(33.124560000000, -87.057220000000, 2)