Tag Archives: mysql

programming

radius search with google maps and mysql

Let’s say you’re building a wepapp and you want to give your users the ability to do a radius search for something (e.g. gas stations) in a specific location. On top of that you want your users to provide your app with new gas stations anywhere. First of all you obviously need some kind of form users can post new gas stations to. This step is easy: get the latitude and longitude of the gas station and save it with all other relevant information (e.g. name, address etc.) to your database. The way to do this is Google Maps. Get an api key from Google for your domain and you’re good to go. Now, whenever a user submits a new gas station, you get the coordinates by giving Google the address your user submitted. Here’s how you do that:

$handle = fopen("http://maps.google.com/maps/geo?q=".urlencode($q)."&sensor=false&oe=utf8&gl=en&output=csv&key=".$api_key,"r");
$data = fgetcsv($handle);

where $q is your query (the address of the gas station)┬á and $api_key your Google Maps Api-Key. Now you have in $data[1] your latitude and in $data[2] your longitude. Additionally Google provides you with an accuracy for your search in $data[0]. I strongly recommend using this detail. For more information go to the Maps Api Reference. That’s it, you now got your coordinates and should save them to your database.

radius search with mysql

Now comes the search part. It’s not tricky at all, a little math will do the thing. First of all you need a search form. When the form is submitted you should begin with once again querying Google maps for the coordinates your user submitted in the search form. This step is easy, since it is the same as above (and this time you should really consider using the accuracy Google gives you back!!!).

Now that you have the coordinates of the searchers position, you query your database for any gas stations in the circumference of the given coordinates. I’ll do it within a range of ten kilometers, but this is of course at your choice (better: at your user’s choice). Let’s say you have a table called `stations`, with a primary key called `id` and the station’s `name`, additionaly the coordinates are saved in `latitude` and `longitude`. Here’s how you do your query:

SELECT
`id`,
`name`,
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 AS `distance`
FROM `stations`
WHERE
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 < 10
ORDER BY `distance`

where $fLat is the searchers latitude and $fLon the searchers longitude. The ten in the where clause is the range you want to search within. Remember: This search is based on kilometers. If you want to search in miles, you need to change the 6380 (whoch is our earths radius) to its respective value in miles. That’s it! You now got your result set with all stations in the ambit of ten kilometers.

gentoo linux

Installing apache, mysql and php on gentoo

Just right now I’m setting up my new home server for various things. It’ll do all of my ranking stuff of course, since it’s very easy for me to get a new ip once G is blocking me. Right now most of the scripts are coded in python, but some essentials are still running on php (though not for long), so i need a running lamp (besides torrentflux and so on…). The last time i set up a clean server on gentoo is quite a few years back now. Eventhough it was a lot of fun and always a good chance to learn some things, I must admit it’s gotten quite easy now. It took me quite some time getting the new php5 running with apache a few years ago. Now theres nothing really special you need to do. Just fire up portage and install it. Before you do, you might wanna edit a few files though. First of all make sure you add mysql and apache2 to your global use flags in /etc/make.conf, then you’ll have to think about some apache options. First of all how will the server process run on your system? Since I’ve loads of RAM and don’t really have any traffic except some requests of my one, no virtual hosts, no nothing, I’ll go with a forked apache. For further information refer to the apache manual. For an forked MPM version of apache just do a quick:

#echo "APACHE2_MPMS="prefork" >> /etc/make.conf

There’s one thing left to do: Define what modules you want your apache to run with. As always gentoo provides a very easy way to do that. If you want to know what modules you can install just do a quick

#emerge --pretend --verbose --oneshot apache

to see all available modules. You’ll at least want those to be installed, since they are kind of needed ­čÖé

#echo "APACHE2_MODULES="alias auth_basic auth_digest
rewrite authz_host dir mime" >> /etc/make.conf

Not all of them are mandatory, but you’ll at least need to install authz_host, dir and mime for your apache to run. Now you’re ready to go, just fire up portage with a quick

#emerge apache

Now just install php by firing up portage again, but remember to set the use flags you’ll need by updating your /etc/portage/package.use, since there are quite some options… As always you can see available use flags by executing an pretended oneshot emerge of php! When you’re done, just install php by

#echo "dev-lang/php apache2 bzip2 curl exif ftp gd iconv mysql
mysqli nls pcre session simplexml soap spell spl sqlite ssl
tokenizer truetype unicode xml xmlrpc" >> /etc/portage/package.use

#emerge php

and you’re good to go! Just start your apache with the according init script and everything should work out of the box. You’ll probably want your apache to start automatically too, by adding it to your default runlevel:

#/etc/init.d/apache start

#rc-update add apache default

But just remember: This is a very basic installation of lamp on your system, it’ll work as it is, but you might wanna tune your settings. As always just refer to the man pages and online documentations!