Transportation Administration System
Snoezelen Pillows for Dementia
Begeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen | Find Postcodes with Lasso ProfessionalHow 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.
Tweet
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:
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
<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.
