Complex Store Procedure

[sahoong]

How do you write a store procedure to meet the following conditions:

PROXIMITY SERACH PROCEDURE

COMPANY table

PK company_id int
company_name varchar(100)
premise varchar(10)
street varchar(30)
town varchar(100)
postcode char(7)
x_loc int
y_loc int

COMPANY_KEYWORD table

PK, FK1 company_id int
PK keyword varchar(100)

The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,

A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.

proximity_search
@search_expression varchar(255)
,@x_loc int
,@y_loc int
,@max_distance int
,@max_records int

Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT - Return all companies that have all of the
keywords specified.
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.

Distance can be calculated using the following formula:

Distance = sqrt( square(x1-x2) + square(y1-y2) )

THE QUESTION
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be
appropriate.

[ericstenson]

DISTANCE CANNOT BE CALCULATED AS YOU HAVE STATED ABOVE! BUT, you can achieve what you want. Let's give you a simple solution here... (a square, not a circle)

Approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1)
and y = 53.0 * (lon2 - lon1)

you can improve on that by:

where x = 69.1 * (lat2 - lat1)
and y = 69.1 * (lon2 - lon1) * cos(lat1/57.3)

---

Based on the user input for the number of miles, calculate the value to add and subtract from both the long and the lat. Then write your select query...

Select * from Businesses where Long > LOmin and Long < LOmax and LAT > LAmin and LAT < LAmax.

[Zadj]

Hi,

The distance calculation that sahoong used appears to work fine. Here is a test script that shows it selects the correct records.

Create table #COMPANY (
company_id int,
company_name varchar(100),
premise varchar(10),
street varchar(30),
town varchar(100),
postcode char(7),
x_loc int,
y_loc int,
)

insert into #COMPANY values(1,'A','','','','',4,3)
insert into #COMPANY values(2,'B','','','','',-3,-4)
insert into #COMPANY values(3,'C','','','','',4,4)
insert into #COMPANY values(4,'D','','','','',5,0)

declare @y int, @x int, @dist int
set @y = 0
set @x = 0
set @dist =5

select * from #COMPANY
where sqrt( square(@x-x_loc) + square(@y-y_loc) )<=@dist


Drop table #COMPANY

As for passing the conditions in as a parameter in the way that you have described, you are going to be in for a tough time.

I have worked with something similar. It was a complex search using many different parameters and conditions. The solution that we used was to create a dynamic SQL statement which was then executed. This is not an efficient solution but for us the query was only being run once every few minutes at most. It can also be really hard to maintain.

If it is within the scope of the project you could limit the amount of keywords (does not need to be a small limit) and make them separate parameters that are passed in along with parameters for each logic step. This can then be put into a where condition. The condition will be long and complex but is achievable.

There may be other solutions. It may be possible to use recursive common table expressions, but I suspect that this could be just as complicated as splitting the parameters.

Good luck with this, and let us know what solution you come up with.

Zadjil