Support for the Netherlands (NL) location.nl.inc location.xx.inc

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?

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.

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

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

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

give me your email or setup your contact in your profile

Changing component to Data Update.

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

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

an example of a location.xx.inc file

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/

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:

/**
 * 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'.

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.

Never worked with cvs or creating patches, I'll give it a try.

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

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.

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.

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

A more general solution for the distance filter can be found in this issue:

http://drupal.org/node/343487

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

location-proximity-nl.patch kicks ass. No need for the database anymore :)

Thanks for this!

FYI: location-proximity-nl.patch does kick ass.
But it would kick even more ass if the functionality for that was included in general location.inc code; there's no real reason it should be NL-specific.
Trying to get comments on that at http://drupal.org/node/662892#comment-2409692 .

Hmm no need for database. Maybe this would be more likely to get in, if there was a setting like "use calculated" (or whatever, I haven't looked at this in detail) or "use database". So that it would be a config option and would not break what someone might already have working.

The patch first checks if a location is in the DB, if it's not then it will try to look it up. So this patch is useful in both scenario's (start with empty or with an already filled 'zipcodes' table).

Subscribing, interesting in also location proximity with province in holland or anywhere else..
greetings, Martijn

Subscribing.

Subscribing, great patch!

All data updates have to be rolled against latest 7.x-3.x-dev and after that backported to 6.x-3.x

bot

I couldn't get the patch to apply on the 3.8-beta2 tag so here's a new patch for that purpose.

Found some issues with the patch after getting it to apply, so here's a fixed version.