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 naam@vos.net 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
Kind-Zijn
Salarisadministratie en belastingadvies bij
De Zaak Loont
Zutphense Bomenstichting

Hosting Favorites

 
ANU Internet Services
XelMedia .internet Services
register.com

Blogroll

 
Bomenstichting
LassoSoft
MacFreak
Quality that computes
The Economy of Motion
Wheel 2.0
IntrAktv



Website Hosting bij Xel Media

Marc's Place


 

 Find Postcodes with Lasso Pro

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


I am building a website where I need to find people based on their postcode and have been searching the past month for a good and cheap solution. 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 GEODATAS.net. 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 GeoNames.org, 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 */
CREATE TABLE IF NOT EXISTS geodb
(
  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:

navicatgeo

The Setup

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

siteadmin1

and allow SQL for the host:

siteadmin2

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

The Test Page

geodata0

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

The Code

<html>
   <head>
      <title>Test postcode database</title>
      <style>
      body, td {
         font-family: Lucida Grande, Verdana, Tahoma, Helvetica, sans-serif;
         font-size: 10px;
      }
      .label {
         text-align: right;
         font-weight: bold;
      }
      </style>
   </head>
   <body>
      <h1>Test postcode database</h1>
      <form action="test-postcodes.lasso" method="post">
         <table><tr>
            <td>Postcode:</td><td><input type="text" name="postalcode" value="[action_param('postalcode')]" maxlength="5"/></td>
         </tr><tr>
            <td>Zoek binnen:</td><td><input type="text" name="distance" value="[action_param('distance')]" maxlength="5"/> km.</td>
         </tr><tr>
            <td></td><td><input type="submit" name="Search" value="Search" /></td>
         </tr></table>
      </form>
      <hr> <br>
[
if(action_param('Search')->size);
   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>';
      records;
         '<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;
      /records;
      '</table><br> <br>';
   /inline;

$sqlstring = "
SELECT DISTINCT
      geodb.postalcode,
      geodb.place,
      (
         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
   FROM
      geodb
   WHERE
      (
         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 + "'
      )
   ORDER BY
      postalcode";

   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><b>';#tmp->titlecase;#tmp;'</b></td>';
      /iterate;
      '</tr>';
      records;
         '<tr>
            <td>';field('postalcode');'</td>
            <td>';field('place');'</td>
            <td>';field('Distance');' km.</td>
         </tr>';
      /records;
      '</table><br> <br>';
   /inline;
/if;
]
   </body>
</html>


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