none
SQL Server Zip Code Search Not Returning Requested Zipcode

    Question

  • I am using this code to return zip codes that are in a 20 mile radius of a specific zip code. Problem is it does not return the specified zipcode along with the ones in the radius. How can I get the zip code itself in the results?

    SELECT h.*
    FROM ZipCodes g
    JOIN ZipCodes h on g.ZipCode <> h.ZipCode
    AND g.ZipCode = '02116'
    WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344) 
    ORDER By ZipCode

    I followed an article on this for SQL Server 2008 which is what I am using. So I am not a super SQL guy to know all the possible issues with this so go gentle on me. ;)
    Friday, November 15, 2013 1:55 AM

Answers

  • There a few mistakes when transposing the code into a code fragment to be used in a SQL subquery. When using UNION ALL to combine two queries, each "arm" (the part before UNION ALL and the part after it) must have the same number of columns. So my original answer needs to end in:

    UNION ALL
    SELECT a.zipcode FROM ZipCodes a WHERE a.ZipCode = @zipCode -- rather than a.*, so that each part will have one column

    The second problem is the placement of the parentheses. If you mean this code fragment to be used as a subquery:

    (@zipCode is null OR
    JobPostings
    .PostalCode IN
    (SELECT h.zipcode FROM zipcodes g
    JOIN zipcodes h on g.zipcode <> h.zipcode
    AND g.zipcode = @zipCode
    AND h.zipcode <> @zipCode
    WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(@range * 1609.344) -- single parens here
      
    UNION ALL
    SELECT a.zipcode FROM ZipCodes a WHERE a.ZipCode = @zipCode)) -- the other two parens belong at the end of your subquery.

    Because your stored procedure has 5-9 different tables and additional user-defined function, I can't reproduce the whole thing to check the syntax.

    I hope you won't take this the wrong way, but if you are maintaining code like this procedure, you need to learn SQL (specifically T-SQL) or hire someone who knows it. Ordinarily, taking a fragment of SQL code and plugging it into a subquery in some more complex piece of SQL code may not always work, that may be the reason for the errors when using the other two suggestions.

    Hope this helps, Cheers, Bob

    • Marked as answer by Armysniper Sunday, November 24, 2013 3:53 AM
    Saturday, November 23, 2013 10:46 PM

All replies

  • You're specifically excluding that zipcode in your join's ON clause g.ZipCode <> h.ZipCode. That's usually what folks want. If I'm understanding, you want the row for zipcode '02116' to show up in the answer as well.

    You could specifically include it by adding a UNION ALL like this:

    SELECT h.*
    FROM ZipCodes g
    JOIN ZipCodes h on g.ZipCode <> h.ZipCode
    AND g.ZipCode = '02116'
    WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)
    UNION ALL
    SELECT a.* FROM ZipCodes a WHERE a.ZipCode = '02116'
    ORDER By ZipCode;

    or use a subquery instead of a join like this, and you're not ignoring the 02116 zipcode:

    SELECT g.*
    FROM ZipCodes g
    WHERE g.GeogCol1.STDistance(SELECT h.GeogCol1 FROM ZipCodes h WHERE h.ZipCode = '02116') <=(20 * 1609.344);

    or the equivalent common table expression (some people think that's clearer):

    WITH h(GeogCol1)
    AS
    (
    (SELECT h.GeogCol1 FROM ZipCodes h WHERE h.ZipCode = '02116')
    )
    SELECT g.*
    FROM ZipCodes g
    WHERE g.GeogCol1.STDistance(h.GeogCol1) <=(20 * 1609.344);

    If this doesn't get the performance you want, you can further optimize, generalize, and parameterize it by using two statements, one to get the one row (geography value) you want to compare to and one to perform the spatial query.

    Hope this helps, Bob

    Friday, November 15, 2013 3:54 AM
  • Thanks for the reply and so fast!

    The use of the Union seems best but this is part of a stored proc (see below...I am not a T-SQL expert and this was built by someone else)...and the use of the Union complains about the OrderBy requiring the fields of the Union to be there...So not sure how to fix.

    The other two suggestions you have threw errors even when outside of the stored proc. The Subquery complains about the use of the STDistance expression not returning a Boolean value. The common table expression reports that it cannot find the "h.GeogCol1" value in the WHERE clause. As I am not a T-SQL guy I have no idea what to do in order to fix. Any suggestions on how to get the Stored proc below to work with the UNION?

    ALTER PROCEDURE [dbo].[SearchForJobs_MultiValue]
    (
                    @stateID nvarchar(max) = NULL,
                    @domainID nvarchar(max) = NULL,
                    @functionID nvarchar(max) = NULL,
                    @countryID nvarchar(max) = NULL,
                    @positionTypeID nvarchar(max) = NULL,
                    @sortOrder int = 0,
    		@startDate datetime = NULL,
    		@endDate datetime = NULL,
    		@zipCode varchar(5) = NULL,
    		@range int = 10
    )
    AS
    BEGIN
    
    SELECT    
                      dbo.JobPostings.ItemID
                    , JobPostings.PositionTitle AS [Position Title]
                    , JobPostings.ItemCreatedWhen AS [Date Posted]
    --				, JobPosting_OrganizationInfo.DomainID
                    , dbo.JobPostings.OrganizationName AS [Organization Name]
    				, dbo.JobPostings.City AS [City]
                    , States.State AS [State]
                    , CountryList.CountryName AS [Country]
                    , JobPosting_JobPositionTypes.PositionType AS [Position Type]
    				, JobPostings.PostalCode AS [Postal Code]
    FROM 
                    JobPostings
                    INNER JOIN 
                    States 
                    ON JobPostings.StateID = States.StateID
                    INNER JOIN 
                    JobPosting_JobPositionTypes ON JobPostings.JobPositionTypeID = JobPosting_JobPositionTypes.PositionTypeID
                    INNER JOIN
                    CountryList ON JobPostings.CountryID = CountryList.CountryID
    
    WHERE 
    			JobPostings.StateID IN
    			(SELECT * FROM dbo.BStar_FN_PSVStateToTable(@stateID))
    		AND	
    			dbo.JobPostings.DomainID IN
    			(SELECT * FROM dbo.BStar_FN_PSVDomainToTable(@DomainID))
    		AND	
    			JobPostings.FunctionID IN
    			(SELECT * FROM dbo.BStar_FN_PSVFunctionToTable(@FunctionID))
    		AND	
    			JobPostings.CountryID IN
    			(SELECT * FROM dbo.BStar_FN_PSVCountryToTable(@CountryID))
    		AND	
    			JobPostings.JobPositionTypeID IN
    			(SELECT * FROM dbo.BStar_FN_PSVPositionTypeToTable(@PositionTypeID))
    		AND
    			JobPostings.PostingStatusID = 1
    		AND 
    			(@startDate is null OR ItemCreatedWhen >= @startDate)
    		AND 
    			(@endDate is null OR ItemCreatedWhen <= @endDate)
    		AND
    			(@zipCode is null OR 
    			JobPostings.PostalCode IN 
    			(SELECT h.zipcode FROM zipcodes g 
    			JOIN zipcodes h on g.zipcode <> h.zipcode
    			AND g.zipcode = @zipCode
    			AND h.zipcode <> @zipCode
    			WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(@range * 1609.344)))
    			UNION ALL
    			SELECT a.* FROM ZipCodes a WHERE a.ZipCode = @zipCode
    
    ORDER BY
                    CASE @sortOrder
                    WHEN 0 THEN JobPostings.ItemCreatedWhen 
                    END DESC,
    
                    CASE @sortOrder
                    WHEN 1 THEN dbo.JobPostings.OrganizationName
                    WHEN 2 THEN JobPostings.PositionTitle
                    END
    END

    Sunday, November 17, 2013 7:58 PM
  • There a few mistakes when transposing the code into a code fragment to be used in a SQL subquery. When using UNION ALL to combine two queries, each "arm" (the part before UNION ALL and the part after it) must have the same number of columns. So my original answer needs to end in:

    UNION ALL
    SELECT a.zipcode FROM ZipCodes a WHERE a.ZipCode = @zipCode -- rather than a.*, so that each part will have one column

    The second problem is the placement of the parentheses. If you mean this code fragment to be used as a subquery:

    (@zipCode is null OR
    JobPostings
    .PostalCode IN
    (SELECT h.zipcode FROM zipcodes g
    JOIN zipcodes h on g.zipcode <> h.zipcode
    AND g.zipcode = @zipCode
    AND h.zipcode <> @zipCode
    WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(@range * 1609.344) -- single parens here
      
    UNION ALL
    SELECT a.zipcode FROM ZipCodes a WHERE a.ZipCode = @zipCode)) -- the other two parens belong at the end of your subquery.

    Because your stored procedure has 5-9 different tables and additional user-defined function, I can't reproduce the whole thing to check the syntax.

    I hope you won't take this the wrong way, but if you are maintaining code like this procedure, you need to learn SQL (specifically T-SQL) or hire someone who knows it. Ordinarily, taking a fragment of SQL code and plugging it into a subquery in some more complex piece of SQL code may not always work, that may be the reason for the errors when using the other two suggestions.

    Hope this helps, Cheers, Bob

    • Marked as answer by Armysniper Sunday, November 24, 2013 3:53 AM
    Saturday, November 23, 2013 10:46 PM
  • Thank you for your help. I do have someone who is our SQL admin but I was trying to alleviate his work on this so he can focus on moving our company to a new office, which he is also responsible for. :) I know enough to get in trouble. :)
    Saturday, November 23, 2013 11:54 PM
  • So did the fix work on the real stored procedure? ;-)

    Cheers, Bob

    Sunday, November 24, 2013 3:22 AM
  • yes it did and thanks. I learned something now I have always wondered if I can pull off. thanks!
    Sunday, November 24, 2013 3:53 AM