MySQL PHP Geolocation - Zip codes: latitude, longitude, distance

This is one of my first really complex SQL query entries. The premise goes like this: you have a user database with a bunch of people who signed up for your service, which includes the user's zip code. Now you want to write a query so you can show user A all the other users in your database that are within, say, 10 miles of her zip code.

It's called Geolocation.

In order to do this, you need two things: 1. a database of all US zip codes, and their corresponding semi-exact latitudes/longitudes; 2. someone like me to teach you spherical geometry.

Lucky for you, you've come here, because not only do I have a nice 7-zipped archive of a US zip codes to latitude / longitude database for you to download, this also happens to be my website, so I can teach you geometry as well!

Now here's a little PHP/SQL for the actual mathematical processing (nearly all done in the database!):

  1.  
  2. // connect to the database
  3. mysql_connect('localhost', 'username', 'password');
  4. mysql_select_db('awesomedb');
  5.  
  6. // we need the userid of the person who is doing the searching:
  7. $userid = 1;
  8. // and the max distance (in miles) you want to show results from:
  9. $max_distance = 10; // in this case, 10 miles
  10.  
  11. // now we go out to the database and get that user's latitude and longitude based on the user's zipcode
  12. $user_query = mysql_query("SELECT z.latitude, z.longitude FROM users u, zipcodes z WHERE u.id='$userid' AND u.zipcode=z.zipcode");
  13. if (mysql_num_rows($user_query)) {
  14.         $user_data = mysql_fetch_array($user_query);
  15.         $latitude = $user_data['latitude'];
  16.         $longitude = $user_data['longitude'];
  17.         // then we use that latitude and longitude to write our complex SQL query:
  18.         $complex_query = mysql_query("SELECT u.id, truncate((degrees(acos(sin(radians(z.latitude)) * sin(radians(".$latitude.")) + cos(radians(z.latitude)) * cos(radians(".$latitude.")) * cos(radians(z.longitude - ".$longitude.")))) * 69.09),1) as distance FROM users u, zipcodes z WHERE u.id != '$userid' HAVING distance < $max_distance");
  19.         if (mysql_num_rows($complex_query)) {
  20.                 $i = 0;
  21.                 while ($row = mysql_fetch_array($complex_query)) {
  22.                         // this is a nice array of userids that are within 10 miles:
  23.                         $found_user_ids[$i] = $row['id'];
  24.                         $i++;
  25.                 }
  26.         } else {
  27.                 // no results!
  28.                 echo 'Sorry, there are no other users within '.$max_distance.' miles of you';
  29.         }
  30. } else {
  31.         // that user's zipcode is not in the database
  32.         echo 'Sorry, that zipcode is not found in the database!';
  33. }
  34.  

There is one little chunk in the $complex_query that was new to me the first time I used this code, and that was the HAVING portion. In the past, I've always tried to use a WHERE statement to limit results based on a calculated column (in this case, the distance column that is the result of the complicated mathematical formula) but I would get an error since the WHERE portion of a SQL statement is actually required to gather the SELECT data, so data from the SELECT cannot be used to limit itself (take a breath and think about it, it does make sense). Enter HAVING.

I know I told you I'd teach you geometry, but I don't actually completely understand what the math above does on a figure-to-figure basis. I do know that is calculates the mean distances between every user in the database. And I know it does so taking the curvature of the Earth into consideration. Think of it like this: if you go near the North Pole, you can walk over every line of longitude (the vertical lines on the globe) in a few seconds by simply walking all the way around the North Pole. But as you get closer to the equator, the distance becomes greater between lines of longitude. So you need SINs and COSINs and Radians and stuff.

At the top, the lines are close together. In the middle, they are further apart. You can I are somewhere in the middle, unless you live in the tropics, or you're Santa Claus, which would be cool, I mean, how many websites does Santa actually visit?

Share/Save/Bookmark

Perform a MySQL search by relevance in PHP

If you have a database with multiple entries that are similar and you'd like to do a search, you're going to need a way to sort the results. Sorting by the number of times a keyword appears in a block of text is one way, and it's called search relevence. Here is a PHP functions for performing a MySQL database search and ording the results by relevence:

  1. function relevance_search($keyword_raw, $page, $perpage)
  2. {
  3.         $return = NULL;
  4.         $page = ereg_replace("[^0-9]", "", $page);
  5.         $perpage = ereg_replace("[^0-9]", "", $perpage);
  6.         $offset = ' OFFSET '.(($page-1)*$perpage)-1;
  7.         if ($page == 1) {
  8.                 $offset = '';
  9.         }
  10.         $keywords = explode(' ', $keyword_raw);
  11.         if (is_array($keywords)) {
  12.                 $i = 0;
  13.                 foreach($keywords as $value) {
  14.                         $chunk = mysql_real_escape_string($value);
  15.  
  16.                         if ($i > 0) {
  17.                                 $plus = ' + ';
  18.                                 $or = ' OR ';
  19.                         }
  20.                         $keyword_string = $keyword_string.$plus."(CASE WHEN body LIKE '%$chunk%' THEN 1 ELSE 0 END) + (CASE WHEN title LIKE '%$chunk%' THEN 1 ELSE 0 END)";
  21.                         $where_string = $where_string.$or."body LIKE '%$chunk%' OR title LIKE '%$chunk%'";
  22.                         $i++;
  23.                 }
  24.                 $sql = "SELECT *, (".$keyword_string.") AS relevance FROM table_name WHERE (".$where_string.") ORDER BY relevance DESC LIMIT ".$perpage.$offset;
  25.                 $query = mysql_query($sql);
  26.                 if (mysql_num_rows($query)) {
  27.                         $i = 0;
  28.                         while ($row = mysql_fetch_array($query)) {
  29.                                 $return[$i] = $row;
  30.                                 $i++;
  31.                         }
  32.                         mysql_free_result($query);
  33.                 }
  34.         }
  35.         return $return;
  36. }

This function returns all the data matching to inputted keywords, and return an array of results sorted beginning with most relevent (the highest count of keyword matches).

Share/Save/Bookmark

Compiling an Oracle Instant Client (OCI8) PHP Module

These are the steps involved in creating a PHP module for Oracle Instant Client (OCI8) 1.2.5 (or higher) in Red Hat Enterprise Linux 5.1 or CentOS 5.1. in an x86-64 environment.

Required Packages:

  • gcc
  • glibc
  • httpd
  • libaio
  • make
  • php
  • php-devel
  • php-pear

If you need help installing a package, please read how to install packages using YUM.

32 bit users note: The instructions are exactly the same for a 32 bit OS, just change /client64/ to /client/ in all the commands below.

First things first, go to Oracle:

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

and download and install the RPM packages for Instant Client Package - Basic and Instant Client Package - SDK. An Oracle Technology Network username/password is required to download the files.

Note: As of writing this, the latest version of the Instant Client package available was 11.1.0.1. If you downloaded a newer (or older) version make sure the use those version numbers in the steps below.

 After downloading the packages, install them using the RPM command:

rpm -Uvh oracle-instantclient-basic-11.1.0.1-1.x86_64.rpm
rpm -Uvh oracle-instantclient-devel-11.1.0.1-1.x86_64.rpm

Once those packages are installed, switch into a temporary directory and enter the following commands to set the required environmental variables:

export LD_LIBRARY_PATH=/usr/lib/oracle/11.1.0.1/client64/
export ORACLE_HOME=/usr/lib/oracle/11.1.0.1/client64/

Also, add those lines to the end of /etc/bashrc so the variables stay effective after reboot.

Note: The following command is only necessary for users installing a version of OCI8 prior to 1.2.5. If you just downloaded the latest version, you can skip this step.

The SDK headers need to be copied into the client library so the compiler can find them:

cp /usr/include/oracle/11.1.0.1/client64/* /usr/lib/oracle/11.1.0.1/client64/lib/

Now comes a challenge that took me a little while to figure out. We want to install the OCI8 package from PEAR, however the package is too large (more than 8 mb) to be handled in memory by the standard PECL installer, so we need to perform an extra step. We're going to use PECL to download the OCI8 package:

pecl -v download oci8

in this case the downloaded file is called  oci8-1.2.5.tgz. Now we use PEAR to install it:

pear -v install oci8-1.2.5.tgz

It should scroll through a bunch of stuff making sure you have the proper software and packages installed on the machine. If everything is in order, it will prompt for the location of the Oracle client libraries. We're going to spcify that it use the Instant Client, and we're going to tell it the location of the libraries with the following line:

instantclient,/usr/lib/oracle/11.1.0.1/client64/lib

When the compilation is complete, you should receive the message "Build process completed successfully". To add the module to PHP, switch into the /etc/php.d/ directory and create a file called oci8.ini, and in it put:

extension=oci8.so

64 bit users installing a version of OCI8 prior to 1.2.5 note:

The module that we just created has to be moved into the 64 bit modules folder

cp /usr/lib/php/modules/oci8.so /usr/lib64/php/modules/

Now all we have to do is restart Apache:

service httpd restart

and take a look at phpinfo() and we should now see a section for OCI8. 

If you're getting an error like:

PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/oci8.so' - /usr/lib/oracle/11.1.0.1/client64/lib/libnnz11.so: cannot restore segment pret after reloc: Permission denied in Unknown on line 0

after compiling and installing an Oracle OCI8 module for PHP you might need to change your SELINUX settings to allow the execution of the oci8.so module or simply disable SELINUX altogether

Share/Save/Bookmark