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

 

[gmmastros]

You MUST read this: thread183-1197121

Post back with any followup questions (if there are any).

-George

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

 

[gmmastros]

By the way, what I am suggesting is that you perform ALL of the calculations within SQL Server, and ONLY return records that match. You'll still want to use a 'bounding box' so that you can quickly discard venues that are too far away. Then, you perform the distance calculations on those venues that fall within the bounding box. This will reduce the number of calculations that need to be performed (to improve performance).

I don't know how many post codes there are in the UK, but in the US, there are appoximately 40,000 zip codes. With 3000 venues and 40,000 zipcodes, you should easily be able to return the results in less than 1 second. If your query doesn't perform well enough, then you are doing something inefficiently. In that case, post the query and we'll help you to improve the performance.



-George

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

 

[gpalmer711]

Hi gmmastros,

Thanks for that it was really informative.

Actually as i'm not using the full postcode, i'm using outcodes. In the uk the postcode is made up of two parts, PE1 is the area part of the code then 4RE is the locale part of the code making PE1 4RE. While using the outcode is not as accurate as using the full postcode it will work fine for my needs, it also reduces the number of codes from 10's of thousands down to just over 2600.

Thanks Again

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[gpalmer711]

Thanks again gmmastros I have the procedure working just as I wanted it.

I have another little question. I want to give the end user the ability to narrow down the choice of venue based on the facilities that it offers. So in the Table that contains the venue name and Lat and Long etc I also have a list of potential features with a data type of bit. I then pass the the choice of the end user to the procedure, see code below.

USE [searchforamenu]
GO
/****** Object:  StoredProcedure [dbo].[RetrieveVenues]    Script Date: 02/27/2007 09:42:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[RetrieveVenues] @userlat decimal(9,6), @userlong decimal(9,6), @distance int = 5, @takeaway bit
as
declare @strWhere as varchar
Select *, convert(decimal(4,2), dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)) as VenueDistance

From   menu_venues

Where    dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude) < @distance AND [takeaway] = @takeaway

Order By VenueDistance

This works fine. What I would like to do is set some if statements so that if the value of @takeaway = False it does not add it to the Where part of the query.

I figured I could do something like this

USE [searchforamenu]
GO
/****** Object:  StoredProcedure [dbo].[RetrieveVenues]    Script Date: 02/27/2007 09:42:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[RetrieveVenues] @userlat decimal(9,6), @userlong decimal(9,6), @distance int = 5, @takeaway bit
as
declare @strWhere as varchar
Select *, convert(decimal(4,2), dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)) as VenueDistance

From   menu_venues

set @strWhere = 'dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude) < @distance'

if @takeaway = True set @strWhere = @strWhere & ' AND [takeaway] = @takeaway'

Where @strWhere 

Order By VenueDistance

This errors out at the "Where @strWhere" line. Now i'm generally a VB programmer and am in that mindset hence the code above.

I've done some googling (googleing???) this morning but could not find any relevent. If someone could point me to a resource or point me in the right direction I would really appreciate it.


Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[gmmastros]

What you are trying to do is call Dynamic SQL. I urge you to do a google search on this phrase. What you learn will be startling, but the essence is... don't use it if you don't have to.

Now, looking at your query, it appears as though you don't really need it because you can write the query to effectively do the same thing without it. Unless I am mistaken, then this should work out ok for you.

CREATE Procedure [dbo].[RetrieveVenues] 
  @userlat decimal(9,6), 
  @userlong decimal(9,6), 
  @distance int = 5, 
  @takeaway bit
as
Select *, 
       convert(decimal(4,2), dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)) as VenueDistance
From   menu_venues
Where  dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude) < @distance
       And
       (@TakeAway = 0 or [takeaway] = @takeaway)
Order By dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)

-George

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

 

[gpalmer711]

Hi George,

Thanks again for your help.

Yeah that will work fine - What I was tring to do was fit it into a single Procedure.

What I can do now is have 2 procedures - 1 for if the person just enters the postcode and another for if they enter any of the parameters.

I'll also look into Dynamic SQL as soon as this project is done.


Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[gmmastros]

This should fit in to a single procedure. Notice this part of the where clause...

(@TakeAway = 0 or [takeaway] = @takeaway)

if the @TakeAway parameter is false ( =0 ) then the left side of the OR will evaluate to TRUE, which effetively removes the [takeaway] = @takeaway part of the filter. However, if @TakeAway is true ( =1 ), then the second part of the filter will affect the rows returned.

Now, it's entirely possible that @TakeAway = 0 should actually be @TakeAway = 1. You see, this is your data and I'm not entirely sure which version should actually be used. I encourage you to test the procedure as I wrote it, then you can tell whether the functionality is that way you want it.

-George

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

 

[gpalmer711]

Maybe i'm just confusing it but I don't think it will work in a single procedure.

Basically I have a asp.net form that contains a text box (post code) - Drop Down box (distance/Radius) and several check boxes.

If the check boxes are not checked they will be false (0), if they are checked they will be true(1).

Basically this site will be for food venues, so I want people to be able to search for venues that say, Have Fish dishes, have baby changing facilities and serve pizza.

These will be requirements for the end user, but I want to return all venues that have these as True (1) but they can have anything else as true as well.

The code you have given me is perfect for the other search options where people can get specific and say that they do not want anywhere that serves alcohol etc.. But for the procedure in question it would not work. Hence the reason I was trying to use the IF statements and only include the "AND [takeaway] = @takeaway" if the person specified that they were looking for a takeaway.

I'm more than happy to put it in two procedures but if you have any other thoughts, i'm learning so much, i'd love to hear them.

Many Many thanks again.

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[gpalmer711]

Thinking about it a second procedure will still not help me out. I think i'm going to have to set the sql query in the asp.net code to get this to work properly.

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[gmmastros]

Do you have a seperate column in the table to indicate the 'features' for each venue? Can you share the table structure?

[tt][blue]
Select Column_Name, Data_Type
From Information_Schema.Columns
Where Table_Name = 'menu_venues'
[/blue][/tt]

If the table structure is the way I think it is, this can probably be done in a single procedure.

-George

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

 

[AlexCuse]

You don't want to do that, and if you do some reading on Dynamic SQL you will see why.

I am thinking your problem is that you need to pass in the different request items (like 'Has Baby Changing Facilities' or 'Serves Pizza'). What you could do is use a ListBox or collection of checkboxes on your form, and concatenate selected values into something easily split by SQL server, like a comma separated string.

What we'd need to know is how is your data stored, how can you tell that restaurant x and y both serve pizza, but restaurant z does not? This will help in explaining how to implement your precedure.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gpalmer711]

Here is the table structure

USE [searchforamenu]
GO
/****** Object:  Table [dbo].[menu_venues]    Script Date: 02/27/2007 16:02:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[menu_venues](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](100) NOT NULL,
	[address1] [varchar](100) NOT NULL,
	[address2] [varchar](100) NULL,
	[address3] [varchar](100) NULL,
	[town] [varchar](100) NOT NULL,
	[county] [varchar](100) NOT NULL,
	[postcode] [varchar](8) NOT NULL,
	[country] [varchar](50) NOT NULL,
	[tel_no1] [int] NOT NULL,
	[tel_no2] [int] NULL,
	[fax] [int] NULL,
	[email] [varchar](100) NULL,
	[url] [varchar](100) NULL,
	[opening_hours] [text] NULL,
	[details] [text] NULL,
	[logo] [varchar](100) NULL,
	[mainpic] [varchar](100) NULL,
	[latitude] [decimal](10, 6) NOT NULL,
	[longitude] [decimal](10, 6) NOT NULL,
	[takeaway] [bit] NOT NULL,
	[restaurant] [bit] NOT NULL,
	[pub] [bit] NOT NULL,
	[cafe] [bit] NOT NULL,
	[other] [bit] NOT NULL,
	[alcohol] [bit] NOT NULL,
	[playarea] [bit] NOT NULL,
	[pizza] [bit] NOT NULL,
	[oriental] [bit] NOT NULL,
	[curry] [bit] NOT NULL,
	[friedchicken] [bit] NOT NULL,
	[sandwiches] [bit] NOT NULL,
	[mexican] [bit] NOT NULL,
	[fish] [bit] NOT NULL,
	[burgers] [bit] NOT NULL,
	[babychange] [bit] NOT NULL,
	[bottlewarming] [bit] NOT NULL,
	[vegetarian] [bit] NOT NULL,
	[vegan] [bit] NOT NULL,
 CONSTRAINT [PK_menu_venues] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

So basically all the bits will be set to 1 if the venue offers those facilities/food types etc..

So my original idea was to pass the results of each of the check boxes to the stored procedure.

Then if the value was True(1) then it would be included in the WHERE part of the statement. Basically if the check box is false then I want the SQL to ignore it. Now coding this in VB I could do it in minutes. However george has got me into stored procedures and I want to make the site as future proof as possible now.

Now at this stage if you feel there is a better way to structure the database it is not too late.

Thanks again for all your help

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[AlexCuse]

This may not be the best idea, and I would recommend that you wait to see if George has a different idea, but you could add a computed column (or even a real column if this is a large table and speed is a concern) to your table that contains a varchar string representing the values for ALL the option columns. Like '100001101000011101101000'. Then you could build this string on your front end based on checkbox results and pass as a param (@menukey char(x) = null).

Then your where condition could just be this:

and (@MenuKey is null or MK = @MenuKey)

Does this make sense?

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

I would suggest that you change your table structure. The problem with the structure you have is that it isn't very flexible. For example, I don't see a column for 'handicap accessible', or 'off street parking' or etc....

I would create a table called VenueFeatures

Create Table VenueFeatures (FeatureId Int Primary Key Clustered, Description VarChar(100))

Then, create a record in the table for each feature.

Then, add another table that stores which feature is available for each venue. Ex:

Create Table VenueFeature(VenueId Int, FeatureId int)

Then, insert a record in to this table for each feature associated with each venue. The beenfit of this table structure is that it allows you to easily add more features that you want to track (like handicap accessible). Of course, changing the table structure like this will NOT be an easy task, but I do recommend that you do this because the benefits you gain will be well worth it.

I hope this helps.


-George

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

 

[gpalmer711]

It's not going to be a pain to change the database at this stage as there is just a single entry while I am tying up these loose ends.

It was the way I was originally going to set the data out but I figured it would be better if I could get it all in a single table.

So looking at you post am I right in thinking I will need to use a derived table?? Maybe not - I think I have been looking at this for too long. I'm going to grab a coffee and a little break and take another look at it.

I really can't thank you enough for all your advice. Once I get my head round it I will let you know what code I have used.

Greg Palmer
Freeware Utilities for Windows Administrators.

http://www.palmersoft.co.uk

 

[AlexCuse]

That may be true, but I will stick with stored procs thank you very much! Is making things slightly easier for your application developers worth making your DBA's life a living hell trying to keep track of all the different queries that could be hitting his/her DBs?

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

>>Gentlemen, there's nothing wrong with dynamic SQL as long as you parameterize the user entries.

So because one person tell you that you believe him? for every person that says dynamic SQL is good there are thousands that contradict that

BTW what the author is talking about is not Dynamic SQL but Ad-hoc SQL which is a big difference

yes sp_executesql and prepared statements are better than a EXEC statement but what are you going to do if your SQL statement is over 4000 or 8000 characters? (not that hard if you have a lot of joins and if statements)

what would you rather send over the T1-line
this
exec CustOrderHist @CustomerID 'abc'

or this

SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = 'abc'
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

after a lot of calls that adds up to a lot of bandwith

And not to mention linked servers, execute as functionality etc etc etc





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

myearwood

Have you ever gone to a carnival and watched someone swallow a sword, or juggle with chain saws?

In my opinion, you can probably write an entire sql server based application without using stored procedures, but it would be similar to swallowing a sword. If you are careful enough, you can make a stable AND secure system that uses nothing but dynamic SQL. In my opinion, it is too difficult to be that careful.

-George

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

 

[SQLDenis]

Also when you have a proc that need to do 3 lookups first to get some values and use those values in a calculation you would have to call SQL server 3 times before calling the final select statement

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/