Zipcode Latitude Longitude data format?

[delacombo]

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.

[ssystems]

How are you pulling this? From a console if MySQL, from SSMS? Is there processing in between except from straight TSQL?

[delacombo]

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.

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

[ssystems]

You did not answer the question. Run this on SSMS and tell us what the result where

SELECT TOP 5 * FROM ZIPCODES

[criterion9]

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.

[delacombo]

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.

[ssystems]

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

[delacombo]

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.

[ssystems]

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.

[delacombo]

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).

[ssystems]

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

[delacombo]

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?

[ssystems]

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.

[delacombo]

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.

[eval(BadCode)]

zipcodes are for postal routes; iirc. They rarely change. Most likely you would see new zipcodes appearing, and not old ones changing.