www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Zipcode Latitude Longitude data format?

  1. #1
    Join Date
    Aug 2012
    Posts
    9

    Zipcode Latitude Longitude data format?

    I'm working on a dealer locator update from maybe 6 years ago, where it uses a database table with zipcode, latitude and longitude as the columns. I am confused though, because the latitude and longitude are in this format:

    zip: 99786 latitude: 1.1809500904783123 longitude: -2.7378482906264079

    However, if I pull an available database of zips and lat/long info, the format for the lat and long are:

    zip: 99780 latitude: 63.66 longitude: -143.19

    Am I missing something? The dealer locator has a stored procedure that grabs zips within 100 miles, and is based on the radius of the earth, so I'm assuming that maybe those aren't actually the lat/long?

    Thanks for any insight.

  2. #2
    Join Date
    Oct 2009
    Posts
    658
    How are you pulling this? From a console if MySQL, from SSMS? Is there processing in between except from straight TSQL?

  3. #3
    Join Date
    Aug 2012
    Posts
    9
    It's being pulled using Visual Basic.net via a stored procedure. The stored procedure has some calculations (below), which may shed light on the format they're in? I'm simply wanting to update the database with updated zipcode and lat/long information, but the format is different.

    I did try using the newer format in a staging environment, and the Dealer Locator web page doesn't pull any dealers, because it's not pulling a radius of zip codes with the newer lat/long format. I hope this makes sense.

    Thanks for your reply.

    Code:
    ALTER PROCEDURE [dbo].[dealer_get_nearby_zips]
    	@zipcode	char(5)	=null,	-- zipcode
    	@radius		int		=100	-- radius in miles to limit search to
    
    as
    set nocount on
    
    /* required parameters */
    if (@zipcode is null)
    	return -1
    
    -- set the number of rows to return
    -- set rowcount @maxrows
    
    if exists(select Zipcode from ZIPCODES where Zipcode = @zipcode) begin
    
    	/*
    	** return zips ordered by distance ascending
    	*/
    	SELECT
    		zipcode,
    		0 as miles
    	FROM
    		ZIPCODES z
    	WHERE
    		z.Zipcode = @zipcode
    	UNION
    	SELECT 
    		z2.Zipcode,
    		acos(sin(z.Latitude) * sin(z2.Latitude) + cos(z.Latitude) * cos(z2.Latitude) * cos(z2.Longitude - z.Longitude)) 
    			* 3963 as miles
    	FROM
    		ZIPCODES z,
    		ZIPCODES z2
    	WHERE
    		z.Zipcode = @zipcode AND
    		z2.Zipcode <> @zipcode AND
    		acos(sin(z.Latitude) * sin(z2.Latitude) + cos(z.Latitude) * cos(z2.Latitude) * cos(z2.Longitude - z.Longitude)) 
    			* 3963 <= @radius
    	ORDER BY miles
    
    end 
    
    return 0

  4. #4
    Join Date
    Oct 2009
    Posts
    658
    You did not answer the question. Run this on SSMS and tell us what the result where

    Code:
    SELECT TOP 5 * FROM ZIPCODES

  5. #5
    Join Date
    Jan 2009
    Posts
    3,346
    Quote Originally Posted by delacombo View Post
    I'm working on a dealer locator update from maybe 6 years ago, where it uses a database table with zipcode, latitude and longitude as the columns. I am confused though, because the latitude and longitude are in this format:

    zip: 99786 latitude: 1.1809500904783123 longitude: -2.7378482906264079

    However, if I pull an available database of zips and lat/long info, the format for the lat and long are:

    zip: 99780 latitude: 63.66 longitude: -143.19

    Am I missing something? The dealer locator has a stored procedure that grabs zips within 100 miles, and is based on the radius of the earth, so I'm assuming that maybe those aren't actually the lat/long?

    Thanks for any insight.
    The more numbers after the period the more precise the location is. 2 digits after the period is not very precise at all, where 8 or 10 digits can get down to the centimeter.

  6. #6
    Join Date
    Aug 2012
    Posts
    9
    DOh! Sorry, here's the top 5 results:

    Current Zipcodes database:
    00501 0.71232258897326217 -1.2748999772892755
    00544 0.71232511970067769 -1.2749505918375836
    00601 0.31705563925266389 -1.1645469800225865
    00602 0.32084396366058021 -1.1726265629156738
    00603 0.32172065999714949 -1.1720226964477762

    "Updated" data in Staging Zipcodes database:
    00501 40.81 -73.04
    00544 40.81 -73.04
    00601 18.16 -66.72
    00602 18.38 -67.18
    00603 18.43 -67.15

    In reply to the decimal places... My concern is how most of the lat/long in the current database, all the lat start with 0.X, and the long start with -1.X.

  7. #7
    Join Date
    Oct 2009
    Posts
    658
    Quote Originally Posted by delacombo View Post
    In reply to the decimal places... My concern is how most of the lat/long in the current database, all the lat start with 0.X, and the long start with -1.X.
    South is Negative, North is positive. Longitude follow the same logic.

    On SSMS Expand Tables -> ZIPCODE -> COLUMNS. You'll see the data type and data precision. Make sure both are the same for staging and live database. Then if they are the same let us know how you "updated" the data to the staging server

  8. #8
    Join Date
    Aug 2012
    Posts
    9
    Both Table's appear to be exactly the same, but after I imported I updated the column for zips to char(5) rather than varchar(5), but the data didn't change, so I don't think that would be an issue. The data was a .CSV file and the lat/long formats are the same as they were in the .CSV file. I saved as .XLS format and imported using SSMS import.

  9. #9
    Join Date
    Oct 2009
    Posts
    658
    No the zips shouldn't be an issue. if lat long have [DECIMAL] (19, 15) for both database check the xls file itself. Since you saved it from csv (which have no formatting) to xls (which has formatting) it is possible that the xls formatting defaulted to 2 decimal places.

  10. #10
    Join Date
    Aug 2012
    Posts
    9
    The lat/long from the original .CSV file is the same (numbers and decimals). Maybe I'm missing something, but I'm more confused that the numbers themselves don't match up (despite the decimals, or if rounded).

  11. #11
    Join Date
    Oct 2009
    Posts
    658
    Quote Originally Posted by delacombo View Post
    The lat/long from the original .CSV file is the same (numbers and decimals). Maybe I'm missing something, but I'm more confused that the numbers themselves don't match up (despite the decimals, or if rounded).
    Grab a 3 or 4 items on that table and make it csv again. Check data. Then do the xls translation you did. Check data on the xls itself. Create the same table on a different test db. Import the data from the xls you made. Check data. Let's see where that would take us. Remember check data every step along the way

  12. #12
    Join Date
    Aug 2012
    Posts
    9
    I did this to begin with. The data hasn't changed, other than I added 0's to pad the zip code to 5 places, but the lat/long info has stayed the same through the original .CSV, to the importing into the staging table for Zipcodes. Is this what you mean?

  13. #13
    Join Date
    Oct 2009
    Posts
    658
    When did you pad it? While it is as a CSV or as a XLS or while on the table alread? Don't manipulate anything yet.

    The purpose of the above instruction is to isolate where the problem. If you do stuffs along the way "assuming" that it wouldn't affect it then it would be a faulty debug procedure and we all. The key to debugging is small changes at a time.

    I haven't encountered anything that would loose precision from table to table with the same data structure. My best bet is during the csv->xls you lost something. Not even sure why you have to convert it to xls.

  14. #14
    Join Date
    Aug 2012
    Posts
    9
    I think my original issue is not clear (and I thank you for your help along the way). My original issue is that the original data I downloaded has different numbering (both numbers and decimal places) than what is in the current database, which is probably 6 years old.

    Since the data (numbers and decimal places) is the same from .CSV file to the staging data table it is in now.

    I just don't get why the number for the lat and long are so different, and am sure that is why the dealer locator isn't working correctly.

  15. #15
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    zipcodes are for postal routes; iirc. They rarely change. Most likely you would see new zipcodes appearing, and not old ones changing.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles