Support for the Netherlands (NL)
BachusII - July 14, 2006 - 14:13
| Project: | Location |
| Version: | 6.x-3.x-dev |
| Component: | Data update |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Description
I have added partial support for the Netherlands.
If the zipcode isn't found in the database an attempt is made to retrieve the lat/lon coordinates from a website, if succesfull they're added to the {zipcodes} table. (max. 8/hour)
Todo:
- add validation
- maybe a cronjob to retrieve those zipcodes (2-4 hourly?) from {location} without matching entry in {zipcodes} to add them?
Any thoughts on the ethics/legalities of sharing the thusly compiled database?
| Attachment | Size |
|---|---|
| location.nl.inc | 4.82 KB |
#1
What is the patch location.nl.inc used for?
How to install it?
How to configure and make it work?
For example, creating a node with a location in United States, I can get a link "Google Map" to link to the map from the node view. But I don't know how to get this support for the Netherlands using the patch location.nl.inc. Could you please explain this patch in detail?
#2
> What is the patch location.nl.inc used for?
The directory modules/location/supported contains location.countycode.inc files for every country known to drupal.
This 'patch' is my adaptation
> How to install it?
Make a backup copy of your location.nl.inc file in the location/supported directory. (For safe keeping)
Replace your file with mine.
> How to configure and make it work?
I provide one map link, to himbuv.com (disabled by default), but it is a very general map. It does show you in what region the zipcode lies in though.
The google map link is provided, but it doesn't work that wel for the Netherlands. It probably works for wel known places like the big cities, but it sucks for the smaller towns. Therefor I commented out the google map link on line 134.
For the US and germany there are files containing the geographic locations for every zip code.
For the Netherlands, sadly, there are not. If you include a Dutch zip code in the location information for your node, my code will check the database whether it knows that zips location. If it can't be found in the DB, the code attempts to retrieve the geographic information and update the DB for future reference.
It works quite wel with the GMap module.
Someday himbuv.com may change it's page layout, or discontinue the service, breaking the code. Until that day there isn't much to configure.
I may be found willing answering further questions in Dutch. (Then again, I may be not.)
Same username as here, and my ISP is planet.nl (Kindly use 'Drupal: ' in the subject.)
#3
Thanks for your guidance. I installed this location patch and have a test. Great! It works when creating a node with the location information in Holland.
I see this patch is able to
1) provide a map link (himbuv) following the location information;
2) automatically retrieve lat/long information and update the corresponding data if entering post code;
3) communicate with gmap.module so that the location can be viewed in a block and on a node map.
So that's a good job! Also one question:
If it's possible to create a map link to more perfect geographic map, such as one from routenet.nl or anwb.nl, instead of the general map from himbuv?
I'm from China and live in Holland. My English is not so good but better than Dutch.
#4
> If it's possible to create a map link to more perfect geographic map, such as one from routenet.nl
> or anwb.nl, instead of the general map from himbuv?
Theoretically, yes.
Practically, why not.
Legally, be very careful.
Do keep in mind that google maps is only commented out (line 134) It's easily re-enabled.
route.anwb.nl seems to be fairly secured.
routenet.nl on the other hand. Uses something like:
http://www.routenet.nl/address.asp?task=map_address&language=NL&addressn...
In other words, easy.
Are you allowed?
I couldn't find any Terms Of Service on routenet.nl. Perusal of the site leads to the most likely answer of "Yes, you can use our service. And that will be X euro for Y requests. What method will you be using to pay our invoice?"
Bearing in mind that I won't be adding this to my site(s), I don't have that large a budget en certainly don't want the legal hassle.
The minimum seems to be
http://www.routenet.nl/address.asp?task=map_address&language=NL&zipcode=...
Though it is still very grainy. Resolution increases with correct street info (streetname + addressnumber).
I'd advise to use something in the line of
http://www.routenet.nl/address.asp?task=map_address&language=NL&zipcode=...
Add an entry in the
location_map_link_nl_providers()function (line 132)And something like:
<?phpfunction location_map_link_nl_routenet($location = array()) {
return (
'http://www.routenet.nl/address.asp?task=map_address&language=NL'
. '&zipcode=' . urlencode($location['postal_code'])
. '&street=' . urlencode($location['street'])
. '&city=' . urlencode($location['city'])
. '&country=NL'
);
}
?>
#5
Thanks for your explanation and code in detail. Following your instruction I started make customization to the file location.nl.inc. I was wondering why I can't copy the php code on drupal pages with mouse selection. So I had to carefully and manually enter your code. But it gave me a surprise because it works and seems without any problem. I mean my location can be linked to the routenet map just one click. That's really amazing.
I only try to use this service to non-profit websites so I think that should be no problem legally. But also thanks for your reminding about this issue. Be careful.
By the way:
>add an entry in the location_map_link_nl-providers() function (line 132)
I added an entry like this
<?php'routenet' => array('name' => 'RouteNet', 'url' => 'http://www.routenet.nl', 'tos' => 'http://www.routenet.nl')
?>
I hope it's all right without potential problems even though it works. Because I really don't know what the right url is. Maybe should be http://www.routenet.nl/map/. Also I don't know what the 'tos' is.
#6
I have created a csv and sql file for all cities in NL with the lat/lon..
The only thing I don't have is the zipcodes for those cities and since the zipcode sql requires a unique zipcode it is somewhat useless right now. There are some 8000+ entries right now and it would be great if we could get the zipcodes in there at least one for each city so it could be somewhat useful. However I don't know of a goodway to quickly get those without doing a lookup on each one.
if anyone has some ideas let me know. Also if you would like the files let me know
#7
Google geocoding will return the numerical part of the zipcodes. Not enough because in NL the 2 appended alfabetical characters are required.
On the one hand this approach will result in 8k+ lookups.
On the other it will result in the compiling of a database that could be used without legal hassle. Provided you pubish it with a public domain/GPL/creative commons type licence of course.
At, let's say, 30 lookups per hour (10 days for 8k lookups) I don't think Google would mind to much.
And yes, I would like a copy of the files.
#8
give me your email or setup your contact in your profile
#9
My ISP is planet.nl, same username as here. Kindly prefix 'Drupal: ' on the subject line, thank you.
#10
Changing component to Data Update.
#11
This needs work. The google geocoder is marked as OK for .nl (and therefore doesn't need to be in the inc file. Also, someone needs to review the services again.)
#12
A year later, this still needs to be looked at. Marking it postponed needs more info.
Connecting it to a collection of other zip code data issues #175193: Zip code database out of date
#13
an example of a location.xx.inc file
#14
I'm interested in this as well. Is the file still available? Looking for a proximity search... but I'm hassling with the postal codes. Found a file with only the 4 numbers on http://download.geonames.org/export/dump/
#15
I added some code from location.us.inc (with minor modifications) and got proximity search working with help of the above 4-number-postal-code database.
Lines I added to location.nl.inc:
<?php
/**
* Returns a lat/lon pair of the approximate center of the given postal code in the given country
*
* @param $location
* An associative array $location where
* 'street' => the street portion of the location
* 'supplemental' => additional street portion of the location
* 'province' => the province, state, or territory
* 'country' => lower-cased two-letter ISO code (REQUIRED)
* 'postal_code' => the international postal code for this location (REQUIRED)
*
* @return
* An associative array where
* 'lat' => approximate latitude of the center of the postal code's area
* 'lon' => approximate longitude of the center of the postal code's area
*
*/
function location_latlon_rough_nl($location = array()) {
if (!isset($location['postal_code'])) {
return NULL;
}
$result = db_query("SELECT latitude, longitude FROM {zipcodes} WHERE country = '%s' AND zip = '%s'", $location['country'], substr(str_pad($location['postal_code'], 4, '0', STR_PAD_LEFT), 0, 4));
if ($row = db_fetch_object($result)) {
return array('lat' => $row->latitude, 'lon' => $row->longitude);
}
else {
return NULL;
}
}
/**
* Returns a lat/lon pair of the approximate center of the given postal code in the given country
*
* @param $location
* An associative array $location where only postal code and country are necessary, but can have the keys:
* 'street' => the street portion of the location
* 'supplemental' => additional street portion of the location
* 'province' => the province, state, or territory
* 'country' => lower-cased two-letter ISO code (REQUIRED)
* 'postal_code' => the international postal code for this location (REQUIRED)
*
* @return
* An associative array where
* 'lat' => approximate latitude of the center of the postal code's area
* 'lon' => approximate longitude of the center of the postal code's area
*
*/
function location_get_postalcode_data_nl($location = array()) {
$dash_index = strpos($location['postal_code'], '-');
// First we strip slash off if we're dealing with a 9-digit US zipcode
if (!($dash_index === FALSE)) {
$location['postal_code'] = substr($location['postal_code'], 0, $dash_index);
}
// Now we pad the thing and query.
$res = db_query("SELECT * FROM {zipcodes} where country = '%s' AND zip = '%s'", $location['country'], str_pad($location['postal_code'], 4, "0", STR_PAD_LEFT));
if ($row = db_fetch_object($res)) {
return array('lat' => $row->latitude, 'lon' => $row->longitude, 'city' => $row->city, 'province' => $row->state, 'country' => $row->country);
}
else {
return NULL;
}
}
/**
* Parameters:
* An associative array $location where
* 'street' => the street portion of the location
* 'supplemental' => additional street portion of the location
* 'province' => the province, state, or territory
* 'country' => lower-cased two-letter ISO code (REQUIRED)
* 'postal_code' => the international postal code for this location (REQUIRED)
*
*
*/
function location_latlon_exact_nl($location = array()) {
return NULL;
}
?>
Note: changed country-code and the number '5' to '4'.
#16
use the dev version, and
http://drupal.org/patch/create
And that would make this a lot easier for the maintainer to get this in.
Post back if you need any help making the patch.
#17
Never worked with cvs or creating patches, I'll give it a try.
#18
Hi Pieter
Is it possible to give an description of the things you did. I'm trying to create also a proximity search with Howto: zip code proximity search using Location Module. But still with no luck....
#19
Recently I had the need for a Dutch postalcode database to do a proximity search.
I used the one at geonames.org (http://www.geonames.org/countries/NL/netherlands.html) with the 4pp format (format: 1234) and converted that one to the format used in the module
For some codes it is lacking the province. Not a big deal for me.
However there is also a 6pp (format: 1234 AB) database located at http://kvdb.net/projects/6pp/downloads.
It contains a lot more details like streetname and such.
According to a message on the site it contains at least 50% of all the Dutch postcodes.
If I have time I would like to see if this version yields better results for searching.
I also need to find out what kind of license is used there. As far I can tell it is free.
For now I can provide you the 4pp version, see attachment.
@joostvdl
The patch contains code for the Dutch proximity search. You will need this code along with the postal-codes to do a proximity search.
Please review and comment.
#20
Since Google provides a geocoding service nowadays I've made patch that used Google to do the postalcode lookup. It first checks if the postalcode is in the zipcodes database table, if it isn't it does a lookup to your geocoding service (through location_latlon_exact()) after which it stores the retrieved zipcode in the database.
#21
Hmm, attached the wrong file :)
This patch is almost the same but it also fills the timezone and dst fields (with a hardcoded 1 and 1 for these Dutch locations).
#22
A more general solution for the distance filter can be found in this issue:
http://drupal.org/node/343487
#23
This thing is breaking my head :P For some reason i cannot get it working.
Here is the deal.
I setted the location gmap en views module like discribed on this page: http://svendecabooter.be/blog/implementing-location-proximity-search-for...
I used the 6pp database which can be downloaded from here: http://kvdb.net/projects/6ppdev I converted it for drupal using this little python program:
./dbconverter.py >> zipcodes.nl.mysql
!/usr/bin/python#import sys
from os.path import exists
Q = list()##def addInsert(line):
x = line.split(',')
Q.append("INSERT INTO zipcodes VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');".format(x[1],x[0],'ZHO',x[6],x[7],1,1,'nl'))
csvfile = "/var/www/localhost/htdocs/SOMESITE/db/6pp_zh.csv"
if not exists(csvfile):
print "path doesnt exists noob"
exit(-1)
try:
f = open(csvfile,'r')#except Exception,e:
print "Cant open {0}: {1}".format(file,e)
l = 1
for line in f:
if l != 1:
addInsert(line.rstrip('\r\n'))
l += 1
for x in Q:
print x
Anyways.. I cant add the database into the attachment sinds it's bigger then 1 mb.. even packed. But this is how it looks:
######################################################################## All Dutch zipcodes
# Based on: http://kvdb.net/projects/6pp/downloads 6PP database
#
# Adapted for the Drupal location module by Remco Janssen 06-11-2009
#
# db # mysql -u root -p drupal < zipcodes.nl.mysql
# Enter password:
# db #
#
#########################################################################
#
# Abbreviations:
#
# DRE Drenthe
# FLE Flevoland
# FRI Friesland
# GEL Gelderland
# GRO Groningen
# LIM Limburg
# NBR Noord-Brabant
# NHO Noord-Holland
# OVE Overijssel
# UTR Utrecht
# ZEE Zeeland
# ZHO Zuid-Holland
#
######################################################################
INSERT INTO zipcodes VALUES('7705 PA','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PB','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PC','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PD','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PE','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PG','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PH','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PJ','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PL','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7705 PM','Drogteropslagen','DRE','52.61666700','6.50000000','1','1','nl');
INSERT INTO zipcodes VALUES('7740 AA','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7740 AD','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7740 AE','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7741 AB','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7741 AC','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7741 AD','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7741 AE','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
INSERT INTO zipcodes VALUES('7741 AG','Coevorden','DRE','52.66666700','6.75000000','1','1','nl');
I cannot seem to get the search working... Are there any Dutchies that go this working and could explain how?
//edit also created a forumtopic here: http://drupal.org/node/628558
//edit2 got it working as discribed in the topic
#24
location-proximity-nl.patch kicks ass. No need for the database anymore :)
Thanks for this!