2D Binding Box Query

Group,

I have table columns ProductID, latitude,longitude,timestampGMT and looking for a query to count how many Id's are within this bounding box or envelope.

开发者_JAVA技巧

Any helpful suggestions.

---

SQL Server 2008 supports GEOGRAPHY datatype.

You should store lat, lon in a single column of this datatype, create a SPATIAL index over it and use it in a query:

SELECT  m.*
FROM    mytable
ON      coords.STDistance(@mypoint) <= @mydistance

---

You are looking for the "Great Circle" distance formula

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

Should point you to the correct SQL algorithm

---

This question shouldn't be a community wiki. Switch it if you can. Either way, here is your answer.

@Quassnoi gave a great solution for SQL 2008, but you're asking for 2003, right? 2003 doesn't have the same geography support as 2008. You'll have to roll your own as I did. It's not hard, depending on the level of accuracy that you require. Here's a scalar-function that I came up with based on a popular formula for calculating distance between two sets of coordinates:

-- =====================================================================
-- Author:      Byron Sommardahl
-- Create date: June 15, 2007
-- Description: Calculates the distance between two sets of coordinates.
-- ======================================================================
CREATE FUNCTION [dbo].[Distance]
(
@lat1 float,
@long1 float,
@lat2 float,
@long2 float
)
RETURNS float
AS
BEGIN

RETURN (3958*3.1415926*sqrt((@lat2-@lat1)*(@lat2-@lat1) + cos(@lat2/57.29578)*cos(@lat1/57.29578)*(@long2-@long1)*(@long2-@long1))/180);
END
GO

To use this, just feed in your coords using a SELECT:

SELECT dbo.Distance(MyPlace.Lat, MyPlace.Long, TheirPlace.Lat, TheirPlace.Long);

Then, you could just check whether a product is within a certain radius of your focal point. Not exactly bounding box, but it gets you moving in the right direction.

---

You may also want to take a look at this:

SQL Server ZipCode Latitude Longitude Proximity Distance Search