Post reply

Finding a zipcode

  • August 8, 2005 at 4:33 pm

    #102693

    I have a table filled with zipcodes and longitude and latitude of the zipcode location.

    I'm trying to write a SQL Server T-SQL statement to pull all the zipcodes within a 2 mile radius. Can anyone help with this query?

    SELECT *, SQRT(POWER((38.492497-latitude),­2)+POWER((-121.404807-Longi­tude),2)) AS distance FROM location WHERE distance < .016  ORDER BY distance

    The error I keep getting is that distance is not a valid column.

     TIA,

    -Dewayne

  • August 8, 2005 at 4:47 pm

    #580556

    Distance is a derived column, and you cannot use it in the where clause, It is okay in the order by though.

    Your code has some parsing error, but the example below illustrates your problem

    use pubs

    -- This does not work in where clause because sales is a derived column

    select price * ytd_Sales as sales

    from titles

    where sales > 777.0000

    order by sales

    -- this does work because calculation used in where clause

    select price * ytd_Sales as sales

    from titles

    where price * ytd_Sales  > 777.0000

    order by sales

     

  • August 8, 2005 at 6:03 pm

    #580564

    try something like this...

    select * from

    (

    SELECT *, SQRT(POWER((38.492497-latitude),­2)+POWER((-121.404807-Longi­tude),2)) AS distance FROM location

    )q

    where q.distance < .16

    order by q.distance asc

    HTH

    Mathew J Kulangara
    sqladventures.blogspot.com

  • August 8, 2005 at 8:42 pm

    #580578

    Or like this:

    SELECT SQRT(POWER((38.492497-latitude),­2)+POWER((-121.404807-Longitude),2)) AS distance, *

    FROM location

    WHERE SQRT(POWER((38.492497-latitude),­2)+POWER((-121.404807-Longitude),2)) < .016 

    ORDER BY 1

  • August 9, 2005 at 7:45 am

    #580713

    ok i have that same zipcode database , and this SQL seems to work:

    select  SQRT(POWER((38.492497 - latitude),-2)  + POWER((-121.404807 - Longitude),2) )  as distance ,zipcodes.* from zipcodes

    where SQRT(POWER((38.492497 - latitude),-2)  + POWER((-121.404807 - Longitude),2) )   < 0.16

    and seems to report items withing a radius: however, i'd like to know the conversion for distance to miles, or distance to kilometers; if .16 = 2 miles, then if i multiply distance * 12.5, is that the miles? (2 / 0.16)? it seems like an awful lot of cities within that 2 mile radius. i thought Washingtion would be a bit more spread out than as it appears below. that "radius" covers 3 counties and an additional town in oregon...is that right?

     

    --------------------

    distanceZIPCODELATITUDELONGITUDEZIP_CLASSPONAMESTATEFIPSCOUNTYSTATE
    ----------------------------------------------------------------------------
    0.1211445417672624798267+48.537813-121.473840NULLMARBLEMOUNT53057WA
    0.1420598465436310597040+45.650790-121.379020NULLMOSIER41065OR
    0.100094954917817998246+48.558127-121.417034NULLBOW53057WA
    0.132253544375944898224+47.767103-121.481395PBARING53033WA
    0.11498288+47.722245-121.369352PSKYKOMISH53033WA
    0.1598624408671405298623+45.807631-121.487682PHUSUM53039WA
    0.1147388338793801898068+47.445171-121.431033PSNOQUALMIE PASS53033WA
    0.1363158097947556698672+45.828588-121.405863NULLWHITE >SALMON53039WA
    0.1447204201210043698605+45.721519-121.447323NULLBINGEN53039WA

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2005 at 7:56 am

    #580721

    http://www.travel-images.com/metric-imperial-conversion.html

  • August 9, 2005 at 8:01 am

    #580727

    sorry; i wanted the conversion for the distance calculation to miles; the "distance" column is  a value, but i don't know what kind of units it's supposed to be.

    i don't think .16 units of the distance calculation above is 2 miles; once i have that conversion value, converting to other units of measure is a snap.

    also DOH! i had a negative power in my calculation, which put my data in washington....when the correct formula is used, it lists a bunc of post offices in sacramento:

    select  SQRT(POWER((38.492497 - latitude),2)  + POWER((-121.404807 - Longitude),2) )  as distance ,zipcodes.* from zipcodes

    where SQRT(POWER((38.492497 - latitude),2)  + POWER((-121.404807 - Longitude),2) )   < .16

     

    distanceZIPCODELATITUDELONGITUDEZIP_CLASSPONAMESTATEFIPSCOUNTYSTATE
    0.1217168846134339794234+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794244+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794248+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794268+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794278+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794296+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794205+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794230+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794232+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794250+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794262+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794273+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794204+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794245+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794253+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794254+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794261+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794274+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794279+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794283+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794207+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794246+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794267+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794290+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794257+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794284+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794287+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794209+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794280+38.377411-121.444429PSACRAMENTO06067CA
    0.147915516427452695608+38.621360-121.332191NULLCARMICHAEL06067CA
    6.0638271743182127E-295820+38.536606-121.446414NULLSACRAMENTO06067CA
    9.3343451832466534E-295825+38.585804-121.402213NULLSACRAMENTO06067CA
    0.1217168846134339794295+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794229+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794243+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794249+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794258+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794259+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794282+38.377411-121.444429PSACRAMENTO06067CA
    9.4805063155930652E-295822+38.511356-121.497716NULLSACRAMENTO06067CA
    3.8897300677553447E-295823+38.481354-121.442071NULLSACRAMENTO06067CA
    0.0584380013347479295826+38.547639-121.385459NULLSACRAMENTO06067CA
    0.1217168846134339795853+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794206+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794263+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794297+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339795812+38.377411-121.444429PSACRAMENTO06067CA
    1.8867962264113206E-295828+38.488446-121.423245NULLSACRAMENTO06067CA
    0.108880668623957395832+38.435014-121.497276NULLSACRAMENTO06067CA
    0.1217168846134339794235+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794239+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794240+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794269+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794293+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794299+38.377411-121.444429PSACRAMENTO06067CA
    0.1152518980320931795814+38.579055-121.480905NULLSACRAMENTO06067CA
    0.1217168846134339795851+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794277+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794291+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339795743+38.377411-121.444429URANCHO CORDOVA06067CA
    0.1217168846134339795873+38.377411-121.444429USACRAMENTO06067CA
    0.1217168846134339794294+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339795611+38.377411-121.444429PCITRUS HEIGHTS06067CA
    0.1217168846134339795894+38.377411-121.444429USACRAMENTO06067CA
    0.1217168846134339794252+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794289+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339795813+38.377411-121.444429PSACRAMENTO06067CA
    0.0839047078536121295819+38.568305-121.440764NULLSACRAMENTO06067CA
    0.1500399946680884395834+38.584193-121.523566NULLSACRAMENTO06067CA
    0.1566173681301023595838+38.645103-121.440015NULLSACRAMENTO06067CA
    0.1217168846134339794208+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794236+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794237+38.377411-121.444429PSACRAMENTO06067CA
    0.147241298554447795758+38.347205-121.428681NULLELK GROVE06067CA
    0.1387263493356615595821+38.627204-121.437964NULLSACRAMENTO06067CA
    0.1018822850156002395827+38.552752-121.322653NULLSACRAMENTO06067CA
    0.1217168846134339795887+38.377411-121.444429USACRAMENTO06067CA
    0.1217168846134339794211+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794247+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794256+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794285+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794288+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794298+38.377411-121.444429PSACRAMENTO06067CA
    0.1008910303248014395816+38.571505-121.467549NULLSACRAMENTO06067CA
    8.3330666623998628E-295829+38.495328-121.321524NULLSACRAMENTO06067CA
    0.1217168846134339795867+38.377411-121.444429USACRAMENTO06067CA
    0.1217168846134339794271+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339794286+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339795759+38.377411-121.444429PELK GROVE06067CA
    4.3657759905886148E-295824+38.517256-121.440764NULLSACRAMENTO06067CA
    0.1217168846134339795852+38.377411-121.444429PSACRAMENTO06067CA
    0.1101862060332417595624+38.441480-121.307142NULLELK GROVE06067CA
    0.1384846561897743295630+38.594181-121.310796NULLFOLSOM06067CA
    0.1376989469821755895655+38.554406-121.281810NULLMATHER06067CA
    0.148687591950370995830+38.490022-121.256140NULLSACRAMENTO06067CA
    0.1217168846134339795840+38.377411-121.444429USACRAMENTO06067CA
    9.7411498294605853E-295864+38.586554-121.379467NULLSACRAMENTO06067CA
    0.1217168846134339795865+38.377411-121.444429PSACRAMENTO06067CA
    0.1217168846134339795857+38.377411-121.444429USACRAMENTO06067CA
    0.1217168846134339795899+38.377411-121.444429USACRAMENTO06067CA
    0.1217168846134339795671+38.377411-121.444429UREPRESA06067CA
    0.1217168846134339795741+38.377411-121.444429PRANCHO CORDOVA06067CA
    0.1064659570003482495815+38.589505-121.448665NULLSACRAMENTO06067CA
    7.3966208500909389E-295817+38.549232-121.452264NULLSACRAMENTO06067CA
    0.1117810359586991895818+38.557255-121.495915NULLSACRAMENTO06067CA
    0.126641225515232695831+38.497863-121.531332NULLSACRAMENTO06067CA
    0.1217168846134339795866+38.377411-121.444429PSACRAMENTO06067CA
    5.6133768802744753E-295673+38.478399-121.459135NULLRIO LINDA06067CA
    0.1217168846134339795763+38.377411-121.444429PFOLSOM06067CA
    0.1217168846134339795860+38.377411-121.444429PSACRAMENTO06067CA

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2005 at 8:41 am

    #580745

    Sorry Lowell. The .016 is very very close to 1 mile. I should have put .032 for two miles. My bad. Also, please note that the radius we're getting is for a flat circle and we'll be off because we're not taking into consideration the curvature of the earth. For my application that's acceptable. More accurately the latitude is .014 and the longitude is .018. I was cutting corners and just went with the median for one calculation. Hope that helps.

    And thanks to all that helped. This community ROCKS!

    -Dewayne

  • August 9, 2005 at 9:12 am

    #580766

    You can't use simple geometry for latitudes & longitudes.  To begin with, latitude is constant anywhere but the size of a degree of longitude changes with latitude.  A better approximation (near 40 deg latitude) might be SQRT(SQUARE(lat1-lat2) + 0.5867 * SQUARE(lon1-lon2)).

    You should be using spherical geometry, but the problem is that a two-mile distance represents a very small angle when measured from the center of the earth and the SINE function gets flaky.  You could have errors of as much as 10 miles for any distance under 10 miles.

    If you check with your friendly neighborhood ancient mariner, he'd tell you to use the haversine formula.  His parrot might explain that is short for "half of the versed sine", but you probably don't want the details.  Translated into T-SQL it is:

    CREATE FUNCTION dbo.fn_latlondistance (@lat1 float, @lon1 float, @lat2 float, @lon2 float)

    RETURNS float

    AS

    BEGIN 

     DECLARE @rlat1 float, @rlon1 float, @rlat2 float, @rlon2 float

     DECLARE @a float, @C float, @d float

     SELECT @rlat1 = RADIANS(@lat1), @rlon1 = RADIANS(@lon1), @rlat2 = RADIANS(@lat2), @rlon2 = RADIANS(@lon2)

     SET @C = SQUARE(SIN((@rlat2 - @rlat1) / 2.0)) + COS(@rlat1) * COS(@rlat2) * SQUARE(SIN((@rlon2 - @rlon1) / 2.0))

     SET @a = 2.0 * ATN2(SQRT(@c), SQRT(1.0 - @C))

     SET @d = 3956.088331329 * @a

     RETURN @d

    END

    This function returns the distance in miles for any two sets of lat-long.  Admittedly this does not correct for altitude or radius variations from a perfect sphere, but it is pretty accurate.

    There is a lot of number crunching involved, so I try to avoid running this on my entire zipcode database.  I use simpler approximations to select all locations near the target, then this formula to get the exact distance only for that subset.

  • August 9, 2005 at 9:30 am

    #580781

    I'll tell you, i love this distraction; Scott's function works well, but I seem to get a much smaller result set than Dewaynes.

    I used this SQL to find items within 4 miles; within two miles produced just a single record(the 4th item in the result set below,1.03 miles?)

    select dbo.fn_latlondistance(38.492497,-121.404807,latitude,Longitude) as distance, zipcodes.* from zipcodes

    where dbo.fn_latlondistance(38.492497,-121.404807,latitude,Longitude)  < 4

    3.785285628345305195820+38.536606-121.446414NULLSACRAMENTO06067CA
    2.155942775990461795823+38.481354-121.442071NULLSACRAMENTO06067CA
    3.948237317943220995826+38.547639-121.385459NULLSACRAMENTO06067CA
    1.03497112252122795828+38.488446-121.423245NULLSACRAMENTO06067CA
    2.587894564987880495824+38.517256-121.440764NULLSACRAMENTO06067CA
    3.093437533483996395673+38.478399-121.459135NULLRIO LINDA06067CA

     

    would anyone like to take a guess at the discrepancy between the two calculations ? i don't think the curvature of the earth would make that big of a difference  in the results.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2005 at 9:40 am

    #580784

    Thanks Scott! Great function! You are 100% correct, but I'm just wanting to return zipcodes within an approximation of one set of coordinates. For me I only need an accuracy of about 10 miles, and if two or more zipcodes are returned within that distance I'm only using the first zipcode in the result set. In this case it's like horseshoes or hand-grenades. Close enough is good enough. Thank God I don't have to be spot-on accurate for this application. At least for now.

    Thanks for your help Scott. I'm pigeonholing this code just in case!

    -Dewayne

  • August 9, 2005 at 9:52 am

    #580789

    You probably don't need the full accuracy, especially since you're talking about some location (geometric center?) in the middle of a zipcode and not individual lat/lon for 9-digit zips.

    But you could still use the function to help you convert the latitude and longitude degrees to miles, then use your simple distance function:

    DECLARE @MilesPerLatDegree FLOAT, @MilesPerLonDegree FLOAT

    DECLARE @Lat FLOAT, @Lon FLOAT

    SELECT @Lat = 38.492497, @Lon = -121.404807

    SET @MilesPerLatDegree = fn_latlondistance(@Lat, @Lon, @Lat+1, @Lon)

    SET @MilesPerLonDegree = fn_latlondistance(@Lat, @Lon, @Lat, @Lon+1)

    SELECT * FROM zipcodes

    WHERE SQRT(SQUARE(@MilesPerLatDegree*(Latitude-@Lat))

      + SQUARE(@MilesPerLonDegree*(Longitude-@Lon))) <= 4

    Even better would be to drop the SQRT function and use WHERE SQUARE() + SQUARE() <= 16.

  • August 9, 2005 at 11:42 am

    #580823

     

    Scott:  Good call on the SQRT.  Comparing the square of the distance to the square of the desired distance is a great way to speed up the query.  I was just about to mention that.

    Now, there's another way to speed this up as well.  As it stands, the query must calculate the distance for every point in the table.  This can be worked around by using a minimum bounding rectangle.  (Or its spherical equivalent).  To do this, you would calculate the max and min lat & long that could possibly satisfy the result set.  This could be thought of as the "flat-land" square in which the search circle is circumscribed. 

    Adding the minimum bounds to the where clause would allow SQL server to use indexes to narrow the set of values upon which it would have to perform the expensive spatial calcs.

    Sql server may not figure this out, so it might require the use of an inline view or temp table.

    hth jg

  • August 9, 2005 at 12:36 pm

    #580832

    I use a zipcode table with a clustered primary key on zip5+zip4, and secondary indexes on latitude and longitude.  A properly-constructed query with BETWEEN conditions is the fastest, but you have to check the execution plan to make sure that it is using index seek operations to limit the rows before it starts calculating.  Sometimes it seems to assume that all the heavy-duty calculations occur in zero time.

    The simple distance calculation is probably close enough for small distances, but I sometimes have to go out to 50 or 100 miles.  This is enough to significantly distort a circle drawn with flat geometry, but the area of uncertainty is only around the edge of the circle.  I might use a simple distance approximation to get all points inside 55 miles and use the precise calculation to recheck points in the 45-55 mile ring.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply