Perform Calculation in Query 2

[gpalmer711]

Hi All,

I'm developing a site in ASP.Net using SQL Server 2005.

It is basically a site for hosting the details of certain venues which people can search for. I'm currently implementing a feature that will allow people to search for venues that are within a certain distance of their location.

I essentially have to code working for this feature, I have the longitudes and latitudes of all the venues in the database and also the longitudes and latitudes of all the postcodes in the uk in another table. A user puts in their postcode and it will first pick out the lat and long of the postcode and then calculate the distance between the venue and the postcode (as the crow flys).

Now there may be as many as 3000 venues in the system within the next few months so what would be the best way of going through all of the records to calculate the distances?

What I have come up with so far is using the BETWEEN operator. This means that when the user enters their post code I calculate what the max and min Lang and Lat are and then only return records that match this criteria. While this works it is not ideal, the reason being as this works on a square grid, i.e. 5 km by 5km. What I would ideally do is have a 5KM radius.

I have the calculation functions already in the code but wondered if there is any way to perform some more advanced functions in the SQL code?

The code that I use to calculate the radius is

Const pi = 3.1415926535897931

    Function distance(ByVal lat1, ByVal lon1, ByVal lat2, ByVal lon2, ByVal unit)
        Dim theta, dist
        theta = lon1 - lon2
        dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * Math.Cos(deg2rad(theta))
        dist = acos(dist)
        dist = rad2deg(dist)
        distance = dist * 60 * 1.1515
        Select Case UCase(unit)
            Case "K"
                distance = distance * 1.609344
            Case "N"
                distance = distance * 0.8684
        End Select
        distance = Format(distance, "Fixed")
    End Function


    '
    '  This function get the arccos function from arctan function    
    '
    Function acos(ByVal rad)
        If Math.Abs(rad) <> 1 Then
            acos = pi / 2 - Math.Atan(rad / Math.Sqrt(1 - rad * rad))
        ElseIf rad = -1 Then
            acos = pi
        End If
    End Function


    '
    '  This function converts decimal degrees to radians             
    '
    Function deg2rad(ByVal Deg)
        deg2rad = CDbl(Deg * pi / 180)
    End Function

    '
    '  This function converts radians to decimal degrees             
    '
    Function rad2deg(ByVal Rad)
        rad2deg = CDbl(Rad * 180 / pi)
    End Function

If you have any ideas or could point me to some relevant reading I would appreciate it.

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[AlexCuse]

<sigh>

I knew it would come to this.

Another thing - what about tables that contains confidential information like social security numbers? You clearly do not want to open this table up to the web. But you can get the information you need from it through a stored procedure.

And then how long does it take you to debug a rather complex 100+ line SQL Statement if you are building it in your front end?

Ignorance of certain subjects is a great part of wisdom

 

[gpalmer711]

Even if I change the database layout I can't see a way of doing this without creating some of the code dynamically. Whether it is on the front end or the sql server.

Here is the situation.

Either the facilities table or the venue table contain details of facilities offered. For this example the options are Pizza, Burgers, Baby Change

George's Place is a restaurant that offers all three options.

Alex's Place is a restaurant that offers Pizza and Burgers

Denis Diner is a restaurant that offers Pizza and Baby Change

All 3 places are within the require radius as returned by the function. However the end user has specified that they want a Place that serves Pizza and has Baby Change facilities. Now if I return all 3 options back to the DB in True or False style then it will only offer Denis Diner as a result as it is the only place that offers the exact services. In this case George's Place should be returned as well because it offers the services required.

Therefore I only see two options, 1) Create the SQL dynamically on the Front End (asp.net) or 2) Use the If statements as I orginally posted.

If someone can think of another option(s) i'm willing to look into it.



Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[gmmastros]

Yes. There is a way to do it. For this example, I am using a table variable instead of a real table.

Declare @Restaurant Table(Name varchar(20), BabyChange Bit, Pizza Bit, Burgers Bit)

Insert Into @Restaurant values('George''s Place', 1, 1, 1)
Insert Into @Restaurant values('Alex''s Place', 0, 1, 1)
Insert Into @Restaurant values('Denis Diner', 1, 1, 0)

Declare @BabyChange Bit
Declare @Pizza Bit
Declare @Burgers Bit

[green]-- this would represent parameters passed
-- in to a stored procedure.

-- Try changing the values and re-running this query
-- to see how it works.[/green]
Set @BabyChange = 1
Set @Pizza = 1
Set @Burgers = 0

Select *
From   @Restaurant
Where  (@BabyChange = 0 Or BabyChange = @BabyChange)
       And 
       (@Pizza = 0 Or Pizza = @Pizza)
       And 
       (@Burgers = 0 or Burgers = @Burgers)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I don't say this often, but thanks goodness for <.!

I spent much time typing basically the same thing George explained here, and I am sure I would have been embarassed had I posted my half-baked, babbling response AFTER he had provided a much better one

Ignorance of certain subjects is a great part of wisdom

 

[gpalmer711]

I swear I tried that when you posted it about 15 posts above and it didn't seem to work properly.

Many Thanks again to all.

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[monksnake]

I don't say this often, but thanks goodness for <.!

That's what I'm talkin about!!

[small]"Mom........MEATLOAF!!!! F***!!!!"[/small]
<.