Software development, photography, jokes, ....

noyb-logo

Sites by me

 
sds82-logo
tas-logoTransportation Administration System
snoezelkussen-logo-kleinstSnoezelen Pillows for Dementia
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

Hosting Favorites

 
ANU Internet Services
Netim
GoDaddy
XEL Media

Blogroll

 
Bomenstichting
Google Translate
PHP
MySQL
jQuery
jQuery UI
YourHead Stacks API
Favicon Generator.
Password Generator.
Check HTTPS problems



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



Buy me a coffee-1

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.


buy me something-2
© 1997- Marc Vos (and others)   -   Privacy Statement   -    Contact Me

On this website, Google Analytics is used to track visitor statistics. These are anonymised data about the number of visitors, which pages they visit on this site, from which regions they visit, which web browsers they use, etc.. You will also see non-personalised ads via Google AdSense. Cookies from Paddle or Paypal are placed when you click on a 'Buy now!' or 'Donate!' button, and possible cookies from Disqus when you use that system to comment on one or more blogposts.
Privacy Statement