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.
Comment | File | Size | Author |
---|---|---|---|
zipcodes.be_.CORRECT.mysql_.zip | 45.22 KB | Anonymous (not verified) | |
zipcodes.au_.CORRECT.mysql_.zip | 149.32 KB | Anonymous (not verified) |
Comments
Comment #1
Anonymous (not verified) CreditAttribution: Anonymous commentedOn 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...
Comment #2
Anonymous (not verified) CreditAttribution: Anonymous commentedSummary 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.
Comment #3
Anonymous (not verified) CreditAttribution: Anonymous commentedAnother 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.
Comment #4
roderik@ #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
Comment #5
Cyberwolf CreditAttribution: Cyberwolf commentedSubscribing. 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';
Comment #6
YesCT CreditAttribution: YesCT commentedmarking 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
Comment #7
Cyberwolf CreditAttribution: Cyberwolf commentedLooks like this was fixed under http://drupal.org/node/830352