Zip Code Searching
-
We have a web page where the user can enter a zip code and select a radius to search around that zip code. My stored procedure that goes and gets the results is very complicated because I build the SQL statement dynamically. We are buying software that will go get us all of the zip codes within the radius. I was planning on just doing a "where zipcode in (@zipcode)", where the @zipcode parameter is a comma separated list of the zip codes returned by the software. Here is the hitch, our zip code field in the table is a varchar, so I can't do the "in" statement. Any ideas? Does anyone of an example of this?
Thanks in advance,
Sabrina
-
I tend to disagree on this one :
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
Go
Select id, name from dbo.SysObjects where name in (Select EachId from dbo.fnSplit_Set ('SysObjects,SysColumns', ','))
/*
3syscolumns
1sysobjects
*/
The Curse and Blessings of Dynamic SQL
-
Here is a nice procedure to strip out a delimited list. I got it from someone else and put some refinements into it. You will note in this instance, the delimiter is a pipe, but you can change that to a comma if you would like.
You should be able to call this and have a WHERE ZipCode IN( SELECT * FROM #List)...
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[ParseDelimitedList]')
AND OBJECTPROPERTY( id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ParseDelimitedList]
GO
CREATE PROCEDURE ParseDelimitedList
@PipeList varchar(2000),
@ColumnType varchar(7) = 'int'
AS
SET NOCOUNT ON
DECLARE @Delimiter char(1),
@LeftDelimter smallint,
@Item varchar(50),
@List varchar(8000)
CREATE TABLE #List( ItemID varchar(25) NOT NULL)
IF NOT( @PipeList IS NULL OR @PipeList = '0' OR LEN( RTRIM( @PipeList)) < 1)
BEGIN
SELECT @LeftDelimter = 1,
@Delimiter = '|',
@List = @Delimiter + @PipeList + @Delimiter
WHILE CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) > 0
BEGIN
SELECT @Item = SUBSTRING( @List, @LeftDelimter + 1, CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) - ( @LeftDelimter + 1))
IF LTRIM( RTRIM( @Item)) <> ''
BEGIN
INSERT INTO #List( ItemID) VALUES( @Item)
END
SELECT @LeftDelimter = CHARINDEX( @Delimiter, @List, @LeftDelimter + 1)
END
END
IF @ColumnType = 'int'
BEGIN
SELECT CONVERT( int, ItemID) AS 'ItemID' FROM #List
END
ELSE
BEGIN
SELECT ItemID FROM #List
END
SET NOCOUNT OFF
I wasn't born stupid - I had to study.
-
Should have figured Remi would beat me to the punch! He writes excellent code, so now you have a choice.
(This is not soooo bulldozer an approach
) I wasn't born stupid - I had to study.
-
You want to do a comma separated list of zip codes to the procedure.
You can do a couple of things, You can pass the records as a well formed xml string and use Openxml
Declare @xmlString varchar(8000)
set @xmlString = '
<zipcodes>
<zipcode value="55544" />
<zipcode value="34455" />
<zipcode value="66544" />
<zipcode value="55677" />
<zipcode value="34456" />
<zipcode value="22445" />
<zipcode value="44667" />
</zipcodes>'
declare @Handle int
exec sp_xml_preparedocument @Handle output, @xmlString
select *
from MyZipcodetable
join openxml(@Handle, 'zipcodes/zipcode', 2)
with (zipcode char(6) '@value') xmlzip on xmlzip.zipcode = mytable.zipcode
exec sp_xml_removedocument @Handle
Or comma separated, read this link it has some good solutions.
http://www.algonet.se/~sommar/arrays-in-sql.html
Alot of people suggest dynamic sql. but I would reccomend a table function that parses your zip code string and returns a recordset you could use to join, You;ll find how in the link
-
Remi,
I used this before, and it seemed slick and all, but I was using the function joining to a partitioned view, and it was causing funk table scans. I instead created a table function that physically parsed the string, and the joins to the view cleaned up and used clustered index seeks.
Wierd
-
Did you cast the eachid value to the correct data type? If there's mismatch and the split value as higer priority, the clustered index value will have to be casted to make the join >>> SCAN.
-
oh yeah, table function returned a number column of ints, and was join on the int primary key. Was quite painfull.
-
No offense, but this seems like a lot of overhead for such a simple task... Maybe it's just me but xml reminds me of 1950s flat file dbs... don't see them around anymore
. -
True that, Just another way to skin the cat.
As suggested not an ideal solution because of the memory management sql uses with openxml documents. and the inherent issues with it.
-
I would have to see the execution plan.. if it's not a cast then you might have to force the optimizer to choose another route. This is a case where there's no statistics to help the optmizier so that might throw him off course.
-
Yeah, no big, that ship has sailed, and out to see for a while. Different company, but that query was fresh in my mind.
Brought back some of the pain that I thought I had forgotten. 
-
Got it... change the in to inner join
>>SET SHOWPLAN_TEXT ON
GO
Select PkADP, ADPname from dbo.ADPS A INNER JOIN (Select EachId from dbo.fnSplit_Set ('CLIENT.adp,Test2001.adp', ',')) dtA on A.ADPName = dtA.Eachid
Select PkADP, ADPname from dbo.ADPS where ADPName IN (Select EachId from dbo.fnSplit_Set ('CLIENT.adp,Test2001.adp', ','))
GO
SET SHOWPLAN_TEXT OFF
GO
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------
Select PkADP, ADPname from dbo.ADPS A INNER JOIN (Select EachId from dbo.fnSplit_Set ('CLIENT.adp,Test2001.adp', ',')) dtA on A.ADPName = dtA.Eachid
(1 ligne(s) affectée(s))
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES
[Numbers].[PkNumber]))|--Filter(WHERE
substring(',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber], len(','))=',' AND len(substring(',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','), charindex(',', ',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','))-[Numbers].[PkNumber]-len(',')))>0))| |--Clustered Index Seek(OBJECT
[Documentation].[dbo].[Numbers].[Pk_Number]), SEEK[Numbers].[PkNumber] < len(',CLIENT.adp,Test2001.adp,')) ORDERED FORWARD)|--Index Seek(OBJECT
[Documentation].[dbo].[ADPS].[IX_ADPS_test] AS [A]), SEEK[A].[ADPName]=substring(',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','), charindex(',', ',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','))-[Numbers].[PkNumber]-len(','))) ORDERED FORWARD)(4 ligne(s) affectée(s))
StmtText
------------------------------------------------------------------------------------------------------------------------------
Select PkADP, ADPname from dbo.ADPS where ADPName IN (Select EachId from dbo.fnSplit_Set ('CLIENT.adp,Test2001.adp', ','))
(1 ligne(s) affectée(s))
StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Semi Join, WHERE
[ADPS].[ADPName]=substring(',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','), charindex(',', ',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','))-[Numbers].[PkNumber]-len(','))))|--Index Scan(OBJECT
[Documentation].[dbo].[ADPS].[IX_ADPS_test]))|--Table Spool
|--Filter(WHERE
substring(',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber], len(','))=',' AND len(substring(',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','), charindex(',', ',CLIENT.adp,Test2001.adp,', [Numbers].[PkNumber]+len(','))-[Numbers].[PkNumber]-len(',')))>0))|--Clustered Index Seek(OBJECT
[Documentation].[dbo].[Numbers].[Pk_Number]), SEEK[Numbers].[PkNumber] < len(',CLIENT.adp,Test2001.adp,')) ORDERED FORWARD)(5 ligne(s) affectée(s))
-
Found this in my virtual briefcase but I never used it. It is in the script library by Klott
Might be helpful. Main thing is getting a good zipcode database.
Some are fairly cheap - I've used http://www.zipinfo.com/products/products.htm
I think it was around $50 - maybe more to load on a server. I would recommend it.
/*
Returns zip codes within specified range.
*/
CREATE Procedure sp_ZipCode_Range
(
@ZipCode Numeric(5, 0) = Null,
@Miles Float
 
As
set nocount on
Declare @Latitude Float(10)
Declare @Longitude Float(10)
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From State Where Zip = @ZipCode
Select
Zip, Zip_Name,
-- Zip_Name,
Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))) As Miles
From
State
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
 
And Zip != @ZipCode
Group by zip, zip_name
Order by miles
return
GO
-
Thanks for everyone's suggestions. I ended up going with the iterative method in the Arrays and Lists in SQL Server article. It's working great as far as I can tell.
Sabrina
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply