Software development, photography, jokes, ....

Sites by me

tas-logoTransportation Administration System
snoezelkussen-logo-kleinstSnoezelkussens voor verstandelijk gehandicapten
ikzoekeenbegeleider-logoBegeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
logo 50x50Hey Vos! Je eigen emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
nettylogo2Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen
Professionele opvang bij Gastouderbureau
Salarisadministratie en belastingadvies bij
De Zaak Loont
Zutphense Bomenstichting

Hosting Favorites

ANU Internet Services
XelMedia .internet Services


Quality that computes
The Economy of Motion
Wheel 2.0

Website Hosting bij Xel Media

Marc's Place


 Find Postcodes with Lasso Professional

  How to find postcodes in range with Lasso and the Geodatas database

I am building a website with Lasso where I need to find people based on their postcode, and the geodesic distance between them. I have been searching for a good and cheap solution for the postal code data, including GPS coordinates. From solutions costing around €2000 down to solutions costing €500 down to one which costs only €99 for a latitude/longitude database of Dutch postcodes, to be found at On their website is sample code present and I converted the PHP-version to Lasso 8 code. You can also find post code tables and more (of countries worldwide, like largest cities, highest mountains, capitals, postal codes, country statistics) at, where the data is free.

The Data

Download a sample file from their site and use Navicat to create the table and import the .txt file. The SQL to create the table is on their site, but I also list it here for convenience:

/* Build Table Structure */
  countrycode VARCHAR(5) NOT NULL COMMENT 'Country',
  postalcode VARCHAR(8) NOT NULL COMMENT 'Postcode',
  place VARCHAR(50) NOT NULL COMMENT 'City',
  region1 VARCHAR(50) NOT NULL COMMENT 'Region 1',
  region1code VARCHAR(50) NOT NULL COMMENT 'Region 1 Code',
  region2 VARCHAR(50) NOT NULL COMMENT 'Region 2',
  region2code VARCHAR(50) NOT NULL COMMENT 'Region 2 Code',
  region3 VARCHAR(50) NOT NULL COMMENT 'Region 3',
  latitude DECIMAL(23, 20) NOT NULL COMMENT 'Latitude',
  longitude DECIMAL(23, 20) NOT NULL COMMENT 'Longitude'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

/* Set Comments */
ALTER TABLE geodb COMMENT = 'Postcode table';

/* Add Indexes for: geodb */
CREATE INDEX geodb_countrycode_postalcode_Idx ON geodb (countrycode, postalcode);
CREATE INDEX geodb_postalcode_countrycode_Idx ON geodb (postalcode, countrycode);

I loaded the sample for the Netherlands into the always present MySQL database test, into the newly created table geodb:


The Setup

You have to allow Lasso 8 access to the database test and enable table geodb from Lasso’s SiteAdmin:


and allow SQL for the host:


You might not want group AnyUser to do this, but on my local dev-machine this freedom can do no harm.

The Test Page


Remember that these kilometers are not route-planner kilometers, but —> great-circle distances.

The Code

      <title>Test postcode database</title>
      body, td {
         font-family: Lucida Grande, Verdana, Tahoma, Helvetica, sans-serif;
         font-size: 10px;
      .label {
         text-align: right;
         font-weight: bold;
      <h1>Test postcode database</h1>
      <form action="test-postcodes.lasso" method="post">
            <td>Postcode:</td><td><input type="text" name="postalcode" value="[action_param('postalcode')]" maxlength="5"/></td>
            <td>Zoek binnen:</td><td><input type="text" name="distance" value="[action_param('distance')]" maxlength="5"/> km.</td>
            <td></td><td><input type="submit" name="Search" value="Search" /></td>
      <hr> <br>
   var('pi' = decimal('3.141592653589793238462643'));
   var('lng' = decimal(0.0));
   var('lat' = decimal(0.0));
   var('zipcode' = action_param('postalcode'));
   var('distance' = action_param('distance'));

   var('sqlstring' = "SELECT * FROM geodb WHERE postalcode = '" + $zipcode + "' limit 1");
   inline(-database='test', -table='geodb', -sql=$sqlstring);
      '<table cellpadding="4" border="0">
      <tr><td colspan="3" align="left"><h2>My postcode data:</h2></td></tr>';
         '<tr><td class="label">Countrycode</td><td>:</td><td>';field('countrycode');'</td></tr>';
         '<tr><td class="label">Postalcode</td><td>:</td><td>';field('postalcode');'</td></tr>';
         '<tr><td class="label">Place</td><td>:</td><td>';field('place');'</td></tr>';
         '<tr><td class="label">Region 1</td><td>:</td><td>';field('region1');'</td></tr>';
         '<tr><td class="label">Region 1 code</td><td>:</td><td>';field('region1code');'</td></tr>';
         '<tr><td class="label">Region 2</td><td>:</td><td>';field('region2');'</td></tr>';
         '<tr><td class="label">Latitude</td><td>:</td><td>';field('latitude');'</td></tr>';
         '<tr><td class="label">Longitude</td><td>:</td><td>';field('longitude');'</td></tr>';

         $lng = decimal(field('longitude')) / 180 * $pi;
         $lat = decimal(field('latitude')) / 180 * $pi;
      '</table><br> <br>';

$sqlstring = "
         6372.8 * SQRT(
            2 *(
               1 - cos(RADIANS(geodb.latitude))* cos(" + $lat + ")*(
                  sin(RADIANS(geodb.longitude))* sin(" + $lng + ")+ cos(RADIANS(geodb.longitude))* cos(" + $lng + ")
               )- sin(RADIANS(geodb.latitude))* sin(" + $lat + ")
      )AS Distance
         6372.8 * SQRT(
            2 *(
               1 - cos(RADIANS(geodb.latitude))* cos(" + $lat + ")*(
                  sin(RADIANS(geodb.longitude))* sin(" + $lng + ")+ cos(RADIANS(geodb.longitude))* cos(" + $lng + ")
               )- sin(RADIANS(geodb.latitude))* sin(" + $lat + ")
         )<= '" + $distance + "'

   inline(-database='test', -table='geodb', -sql=$sqlstring, -maxrecords='All');
      '<table cellpadding="4" border="0">
      <tr><td colspan="3" align="left"><h2>My neighborhood data:</h2></td></tr><tr>';
      iterate(field_names, local('tmp'));
            <td>';field('Distance');' km.</td>
      '</table><br> <br>';

You might want to use another value for Earth’s radius, depending on where you live and the region you will be searching in. See the Wikipedia-page for common used values. For example, I took the value 6372.8 from that page’s working example, although the PHP-example on Geodatas’ website uses a value of 6367.41 and the ColdFusion example uses 6378.137.
© 1997- Marc Vos (and others) Contact Me