I was using a views proximity filter on a Gmap to show nodes. It worked fine, except when I tried Australia and Belgium. Then I checked the actual MySQL queries and found this:

A location in Belgium with lat 51.067631, and lon 3.733049 could not be found because the mysql proximity query was doing this: (...) AND (location.latitude > 2.27597446672 AND location.latitude < 5.16741753328 AND location.longitude > 49.6052325058 AND location.longitude < 52.5027874942).

Looked like lon/lat might be swapped. So I compared the zipcodes table with the location table. Guess what?

The following files have mixed up lon/lat columns:

zipcodes.au.mysql
zipcodes.be.mysql

Attached are the NEW & CORRECT files! Please update!! (location/database/zipcodes.xx.mysql).

I also noticed that zipcodes.in.mysql for India does not contain any lon/lat information. So it cannot be used for proximity filter.

UPDATE zipcodes SET latitude=longitude, longitude=@temp WHERE (@temp:=latitude) IS NOT NULL AND country='au';

Then I also noticed for India, zipcodes.in.mysql does not contain any lon/lat information! So that one can't possibly work at all. Please remove it.

CommentFileSizeAuthor
zipcodes.be_.CORRECT.mysql_.zip45.22 KBAnonymous (not verified)
zipcodes.au_.CORRECT.mysql_.zip149.32 KBAnonymous (not verified)
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Anonymous’s picture

On top of that, the lon/lats in zipcodes.no.mysql are simply WRONG. Compare:

WRONG (Sandnes according to zipcodes.no.mysql) - http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=69.666667%2F...

RIGHT (Sandnes geocoded by Google) - http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=58.879569%2F...

Anonymous’s picture

Summary of findings
- zipcodes.au.mysql (Australia) swapped lon/lat columns
- zipcodes.be.mysql (Belgium) swapped lon/lat columns
- zipcodes.in.mysql (India) missing lon/lat info (everything is 0)
- zipcodes.no.mysql (Norway) completely wrong lon/lat info

And my resulting engine: http://www.magentosites.net/stores/locator finally works.

Anonymous’s picture

Another point of interest, in order to improve the zipcodes:

Often in European countries zipcodes can have two formats:

80802 or D-80802 (D for Germany)
3000 or B-3000 (Belgium)
1000 or N-1000 (Norway)
2312 or A-2312 (A for Austria)
Etc.

As I cant force my users to enter the right format (numbers only), I have to solve this programmatically. Like a kind of preg_replace action in location_views_handler_filter_proximity.inc.

Have to work something out for this., because its country dependent.

roderik’s picture

@ #3:
Country dependent code should be in supported/location.CC.inc.

I'm trying to gather comments on a new API function location_standardize_postalcode() (which calls location.CC.inc:location_standardize_postalcode_CC() ) in http://drupal.org/node/662892#comment-2409692

Cyberwolf’s picture

Subscribing. I also encountered the issue of swapped longitude/latitude column values for Belgium.
Quick fix SQL (might be MySQL specific):


UPDATE zipcodes SET latitude=(@temp:=latitude), latitude = longitude, longitude = @temp WHERE country='be';

YesCT’s picture

Title: Longitude & latitude are swapped in zipcodes.mysql for BE and AU » Longitude & latitude are swapped in zipcodes.mysql for BE and AU (location.xx.inc)
Component: Miscellaneous » Data update
Priority: Critical » Normal
Status: Active » Closed (duplicate)

marking duplicate of #175193: Zip code database out of date (master issue for zip code problems)

also including location.xx.inc so this can be found when searching for country specific problems

Cyberwolf’s picture

Looks like this was fixed under http://drupal.org/node/830352