La Bakery Trucks 50-60's

autumn optimize 2009

on a secret bridge at the Elster river a few years, a quarterback has been built into the concrete. Anyone with big wheels can be connected to this real underground spot to have fun. Just as I came here during the production of my Autumn Skate nude photo, 2009, now the fourth photo the series, which is two times continued in the year - the first time as a sequence.

Cost Of Bathroom Window

OpenGeoDB radius search and Yii implement

is in this short article about how the current rebuild OpenGeoDB-Geolokalisationsdatenbank by a PHP script so that a radius search is possible. There are already two tutorials on the subject, namely here and here , which results in my view is that it is inefficient. Nevertheless, I thank these authors for giving me the work have made it easier. Special Thanks I want to convey to the author this tutorial, because it provides the mathematical background of the proximity search very reasonable.

My script uses the Yii Framework , especially the PDO class to build up the database connection.

1) Preparation: The most elegant

data source for our radius search would be a table of all German cities and towns, with a unique code, place name, latitude and longitude. We are preparing a SQL table in consequence:

CREATE TABLE IF NOT EXISTS `City` (`
Plz` INT (11) UNSIGNED NOT NULL, `city` VARCHAR
(80) NOT NULL, `Latitude`
DOUBLE NULL,
`Longitude` DOUBLE NULL,
PRIMARY KEY (`Zip`)) ENGINE = InnoDB
;

2) Extract of geo-data

First we will come but only to fill the gaps Plz and place, since the extraction of the width, - and Longitude is more complicated. This we accomplish described as at this point in OpenGeoDB Manual, as follows: code

SELECT code.text_val as "Zip," name.text_val as "place"
FROM geodb_textdata code geodb_textdata
WHERE name = name.loc_id. loc_id AND
code.text_type = 500300000 / * AREA_CODE * / AND
name.text_type = 500100000 / * NAME * /
ORDER BY 2;

We read this data into your table.

then we produce ourselves as an auxiliary table tmp_stadt that looks exactly like our city table.
using my script will now read out the coordinates for each location:

active = True / / Enable connection

/ / Optional: group by city, then every place listed only once with its main code.
$ command = $ connection-> create command ("select * from city group by location");

$ data = $ command-> queryAll ();

foreach ($ data as $ object) {
$ query = "SELECT zc_id zc_location_name, zc_lat, zc_lon FROM WHERE zip_coordinates zc_zip = '';
/ / integer, since no leading Zeros, we must add here also a
if (strlen ($ object ['Zip']) == 4) $ query .= "0";

$ query .= $ object ['Zip'] . "',";

$ reader = $ connection-> create command ($ query) -> query ();
$ result = $ reader-> read ();

/ / Should not have the spatial data once they have with be written into 0, since otherwise the script aborts:
if ($ result ['zc_lat'] =='') $ result ['zc_lat'] = 0;
if ($ result ['zc_lon'] =='') $ result ['zc_lon'] = 0;

$ query = "Insert into tmp_stadt (Zip, City, Latitude, Longitude) values ('". $ Object [' Plz']."','". $ Object ['location']. "'". $ Result [' '..], $ result ['. zc_lat zc_lon '] ")";

/ / If you want to show you what you do:
if ($ verbose) {
$ i + +;
echo $ i. ":". $ Query. "";
}

if ($ connection-> create command ($ query) -> execute ()) {
echo $ query;
die ("something went wrong!");
}
}
>

? Since
are entered in the OpenGeoDB unfortunately, not all parts of the city limits to use my script out for metropolitan areas such as Hamburg or Berlin, the position of the first found, so the smallest postal code. But if there are any any zip code, you can replace the Group by location Group by by Plz . Since, however, grouped by the SQL field site, one can use the script on any subsequent nachgepflegte data sets, and it would then specify the inventory. through

After the hopefully successful, what will last, depending on the computing power of a few seconds or minutes, we find our results again in tmp_stadt. We call delete city tmp_stadt into town and enjoy our database.

This has but unfortunately rarely isolated a number of gaps, see here:

mysql> select * from tmp_stadt where City = 'Leipzig';
+------+---------+---- ------+-----------+
in Yii implemented:


$ query = "SELECT zip, city, latitude, longitude FROM city WHERE zip = '';

if (strlen ($ zipcode) == 4) $ query .=" 0 "/ / back to eighth leading zeros

$ query .= $ plz. "',";

$ reader = Yii:: app () -> db-> create command ($ query) -> query ();
$ result = $ reader-> readAll ();

$ latitude = $ result [0] ['Latitude'] / 180 * M_PI;

$ longitude = $ result [0] ['Longitude'] / 180 * M_PI;

$ query = "SELECT Code, City, (6368
* SQRT ( 2 * (1-cos (RADIANS (Latitude))
with * (. "Latitude $.") * (Sin (radians (longitude)) *
without (". $ Longitude.") + Os (radians (longitude)) *
with (". $ Longitude.") ) - no (radians (latitude)) * sin (". .")))) $ latitude AS Distance FROM
Stadt where
6368 * sqrt (2 * (1-the (radians (latitude)) *
with (" . $ latitude. ") * (sin (radians (longitude)) *
without (". $ Longitude. ") + the (radians (longitude)) *
with (". $ Longitude. ")) - without (radians (Latitude)) * sin (". ."))) latitude
$ db-> createCommand ($ query) -> query ();
$ result = $ reader-> readAll ();


result in $ I now have an array of all cities within $ radius km from the city with $ Plz plz. Also, I'm in this example, assume that I have the database connection in my application in Yii Yii:: app () - I configured> db.

I use for these examples the version opengeodb-0.2.5a-UTF8 sql.gz. After this run I had

6358 cities in my database, where each city uses only its main ZIP code, city and 8270, when all cities are listed with their districts (Group by ZIP vs. Group by Location).
<= ".$radius." ORDER BY Distance "; $reader = Yii::app()->