December 12, 2007 at 1:56 pm
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_locint
,@y_locint
,@max_distanceint
,@max_recordsint
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.
December 12, 2007 at 2:31 pm
I know I could be off, but that sure looks like homework.
If you post what you tried that didn't work, you're sure to get help. Just trolling for someone to do it for you is going to bring in a quite a few negative comments.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2007 at 3:18 pm
Yep, I second Grant. At least give us your first stab at this and tell us which requirements you are having a problem handling. Help us help you by first helping yourself :D. The requirements are clear enough to where you should at least be able to come up with a couple of the queries and try to wrap them in CREATE PROCEDURE logic.
Oh, yea. If you really want help, you'll also need to include some sample data for your tables along with expected results.
December 13, 2007 at 9:27 am
I have attempted it. This is what i've done so far
CREATE PROCEDURE proximity_search @max_records int
@search_expression varchar(255) = '',
@x_locint = '',
@y_locint = '',
@max_distanceint = '',
@max_recordsint = '',
AS
SET NO COUNT ON
DECLARE @company_id INT
DECLARE @company_name VARCHAR(100)
DECLARE @premise VARCHAR(10)
DECLARE @street VARCHAR(30)
DECLARE @town VARCHAR(30)
DECLARE @postcode CHAR(7)
DECLARE @x_loc INT
DECLARE @y_loc INT
SELECT company_name, premise, street, town, postcode FROM company
IF @search_expression = 'TESCO AND CASHPOINT' AND @max_distance =
CREATE FUNCTION Distance
--- input dimensions in Metres
(@x_loc (x1,x2), @y_loc (y1,y2))
RETURNS int --- Square Metres
AS
BEGIN
RETURN (sqrt( square(x1-x2) + square(y1-y2))
END
December 13, 2007 at 9:47 am
Your desire to find companies within a certain distance of a given point is fairly simple on the surface,
Your function is not correct tho. (I did not check your formula for calculating distance) I use a function to calculate Miles, and it is much more complex than what you are showing.
CREATE FUNCTION Distance
--- input dimensions in Metres
(@X1 float, @X2 Float, @Y1 float, @y2 float)
RETURNS float --- Square Metres
AS
BEGIN
RETURN (sqrt( square(@X1-@X2) + square(@Y1-@y2)))
END
SELECT company_name, premise, street, town, postcode
FROM company
where dbo.Distance(@x_loc, x_loc, @y_loc, y_loc) <= @Max_Distance -- @x_loc, @y_loc is the center point of your "Search"
and x_loc between (@x_loc -5) and (@x_loc + 5)
and y_loc between (@y_loc -5) and (@y_loc + 5) -- "substitue 5 for whatever value you think would be the max."
The problem here is the query optimizer will be forced to calculate the distance of every company in your table. which will be very slow. you need to set the upper bounds of what the "Maximum" value the user will be able to search so you can add an item to the where clause that will refine available companies.
now for you to pass in such a complex search expression thats different entirely. you need to look up and learn about Dynamic Sql, which has all sorts of architectural implications
December 13, 2007 at 10:14 am
Hi Ray M,
Thanks for this.
I'll try executing it now.
Cheers.
December 13, 2007 at 12:38 pm
SRY, IT DIDN'T WORK AT ALL.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply