I have created a stored procedure that seems to do the trick. Here it
is:
/*
This stored proceedure will:
-Go through a list of addresses stored in a DB table (markers)
-Calculate the distance of these addresses to the latitude and
longitude passed as parameters
-Then should the distance be less or equal to the radius parameter,
then the address is inserted into a temporary table
-Then data is spewed out as a recordset.
All this is based on the Haversine formula. More info on the formula
can be found at: http://en.wikipedia.org/wiki/Haversine_formula
*/
CREATE PROC dbo.test
@parmLat float,
@parmLng float,
@parmRaduis float
AS
DECLARE @id int
DECLARE @name varChar (60)
DECLARE @address varChar (60)
DECLARE @lat float
DECLARE @lng float
DECLARE @distance float
DECLARE cr_markers CURSOR FOR SELECT id, name, address, lat, lng FROM
markers -- Populate a recordset with all addresses.
CREATE TABLE #tmpMarkers (tmpId int, tmpName varChar (60), tmpAddress
varChar (60), tmpLat float, tmpLng float, tmpDistance float) -- Create
a table to hold the addresses we want to keep.
OPEN cr_markers
FETCH NEXT FROM cr_markers INTO @id, @name, @address, @lat, @lng
WHILE @@FETCH_STATUS = 0 -- Loop through all addresses.
BEGIN
-- 6371 K
-- 3959 Miles
SET @distance = (3959 * acos (cos (radians (@parmLat))
* cos
(radians (@lat)) * cos (radians (@lng) - radians (@parmLng)) + sin
(radians (@parmLat)) * sin (radians (@lat)))) -- A bit of trginometry
to calculate the distance of things. Haversine formula.
IF @distance <= @parmRaduis -- If it is not too far,
then ...
BEGIN -- Add the data to our temporary table.
INSERT INTO #tmpMarkers (tmpId,
tmpName, tmpAddress, tmpLat,
tmpLng, tmpDistance) VALUES (@id, @name, @address, @lat, @lng,
@distance)
END
FETCH NEXT FROM cr_markers INTO @id, @name, @address,
@lat, @lng
END
CLOSE cr_markers -- Get ride of a few things.
DEALLOCATE cr_markers -- Get ride of a few things.
SELECT * FROM #tmpMarkers ORDER BY tmpDistance -- Spew
GO
If you have any comments or ways to optimize this, please reply to me
directly, as I don't really monitor this group.
Thank you all for you assistance.
Jacques Quesnel
On Nov 10, 4:38 pm, quesnelj <[email protected]> wrote:
> Greetings,
>
> The MySQL statement:
>
> SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) *
> cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin
> ( radians( lat ) ) ) ) AS distance
> FROM markers
> HAVING distance < 25
> ORDER BY distance
>
> Does not work with MS SQL Server as "distance" cannot be used in the
> HAVING and ORDER BY clauses.
>
> Does any one have a suggestion?
>
> Thank you,
>
> Jacques
>
> On Nov 8, 12:59 pm,quesnelj<[email protected]> wrote:
>
> > I have not completely read the article yet, but looks like what I've
> > been searching. Thank you. If any one comes up with a classic ASP and
> > MS SQL that would be the best. For now, I'll check this out and hope
> > for the best.
>
> > Thanks again...
>
> > Jacques
>
> > On Nov 2, 4:45 am, Rossko <[email protected]> wrote:
>
> > > > I have a DB containing business location addresses entered by clients.
> > > > I want other clients to enter a zip code and have my application
> > > > return all businesses in my DB that is physically near (20miles,
> > > > 30miles, 40miles, ...) the entered zip. Displaying on a map would be
> > > > nice, but not mandatory.
>
> > > The classic example of using db with maps for 'search
> > > nearby'http://googlemapsapi.blogspot.com/2008/01/article-creating-store-loca...
--
You received this message because you are subscribed to the Google Groups
"Google Maps API" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-maps-api?hl=.
