1

How do I calculate the lat/lon for a given location from a postal code database when there are multiple codes for a given location? For example, New York City has 165 postal codes.

SQL example (There is also a Latitude and Longitude column in the table):

SELECT City, [State], StateAbbr, Country, COUNT(*) AS ctCodes
FROM PostalCodes
WHERE (City = 'New York City' AND StateAbbr = 'NY')
GROUP BY City, [State], StateAbbr, Country
ORDER BY ctCodes DESC

Returns:

City          | State    | StateAbbr | Country | ctCodes
New York City | New York | NY        | US      | 165

I considered calculating the lat/lon of the center of the bounds, which gets a little complicated. Consider this query:

SELECT City, [State], StateAbbr, Country, 
    COUNT(*) AS ctCodes, 
    MIN(Latitude) AS south, 
    MAX(Latitude) AS north,
    MIN(Longitude) AS west,
    MAX(Longitude) AS east
FROM PostalCodes
WHERE (City = 'New York City' AND StateAbbr = 'NY')
GROUP BY City, [State], StateAbbr, Country
ORDER BY ctCodes DESC

Returns:

City          | State    | StateAbbr | Country | ctCodes | south  |  north |   west  |  east
New York City | New York | NY        | US      | 165     |40.69640|40.86620|-74.02530|-73.67310

Getting the bounding rectangle works for north america, but it obviously wouldn't work for the southern hemisphere or east of the Prime Meridian. Am I going down the right road? Is there a better way to do this? Any help would be much appreciated.

Redtopia
  • 4,947
  • 7
  • 45
  • 68
  • Can you just average all the coordinates for a given city? Won't that work anywhere on the globe? – Matt Oct 07 '13 at 22:29
  • Interesting... I'm not sure how well that would work and I have to admit I never thought of that. – Redtopia Oct 07 '13 at 23:21
  • 1
    I've experienced this pain before...you'll find different floors of a building will have their own postal code. There are 'shape' files out there that have these regions (or similiar region breakdowns) contained as polygons which work a lot better than bounding boxes like this...why do the work when it already exists on the web for free? :) You then find a point in the polygon. What database are you working in?...geocoding varies from DB to DB. – Twelfth Oct 08 '13 at 00:05
  • I can't remember where I got the db... I downloaded it a couple years ago for free and it's worldwide. I just need to get something working until we either have time to dial this in better, or can offload it to a service. – Redtopia Oct 08 '13 at 03:02

1 Answers1

2

Computing a bounding box or averaging the lat-long coordinates will more-or-less work for locations that are not on the 180th meridian; that is, is pretty much anywhere but in Fiji.

An approach that will work anywhere on the globe, Fiji included, is converting the coordinates into points on a 3D sphere, computing the midpoint, and projecting the midpoint to the surface of the sphere. This of course will be computationally more expensive.

First convert each lat-lng pair into 3D cartesian coordinates:

x = cos(lat)*cos(lng)
y = cos(lat)*sin(lng)
z = sin(lat)

Then compute the midpoint by averaging the coordinates, giving (x', y', z'). Now convert this back into lat-lng, and you'll have the coordinates for the center point:

  r  = sqrt(x'² + y'² + z'²)
lat' = asin(z'/r)
lng' = atan2(y', x')
Joni
  • 108,737
  • 14
  • 143
  • 193
  • OK, so if I got this straight, I will take each coordinate (NW, NE, SE, SW) and convert them to 3D cartesian coordinates (seems straight forward). But what do you mean by "averaging the coordinates, giving (x', y', z')" Do I average the X for each of the 4 points, and do the same for the other 3, which will give me X(1), Y(1), Z(1)? – Redtopia Oct 08 '13 at 03:06
  • 1
    Yes: average the x,y, and z separately. You'll probably get better results if you calculate the average over all points, not just the 4 corner points of the bounding box though. – Joni Oct 08 '13 at 06:40
  • You mean calculate the Cartesian coordinates for each of the postal codes? – Redtopia Oct 08 '13 at 20:02
  • Yes; you can do all of this in the database. – Joni Oct 08 '13 at 20:22