I'm very new at Google API, and thus am not to sure where to start.

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.

Could you please give me an idea of what my options may be.

Regards,

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 <quesnel.jacq...@gmail.com> wrote:

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
WHERE ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) *
cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin
( radians( lat ) ) ) ) < 25
ORDER BY distance

MS SQL doesn't mind the HAVING clause, it just doesn't like using
anything it renames through an AS clause, which is odd to me.

Yes, repeating the calculation in the WHERE clause would work, but I'd
also need to repeat it in the ORDER BY clause. Considering the
calculation to be heavy, I find this to be excessive, and really
inefficient. I might try doing this using a stored procedure. I think
that might be doable...

I wonder if MS SQL is smart enough to recognize that the second and
third calculations are repeats, and actually only do it once?

Thanks,

Jacques

On Nov 10, 5:12 pm, Andrew Leach <andrew.leac...@googlemail.com>
wrote:

/*
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 <quesnel.jacq...@gmail.com> wrote: