home program overview dashboard contest dev forum

where » create » WHERE geospatial API

WHERE Geo API

v0.2 - beta!

This simple API provides functions for local search. It is intended to be used by developers unfamiliar with spatial data and spatial queries who want to write local search applications/widgets.

Currently, there is a java and php version of this API.

Please keep in mind that the provided functions are written for quick development. If you have very large tables these search functions might take a bit of time.

» Download the WHERE Geo API (.zip)

Documentation

For examples of how to use the API, see the GeoSearchExample.java, GeoCalcExample.java, GeoSearchExample.php, or GeoCalcExample.php.

Both, the java and the php functions are the same. You can, therefore, use the javadoc for both languages.

Distance Calculations

We all know it, the earth is round. This means that a simple distance calculation using Pythagoras won't give us very good results. For the ones interested, here a bit of information about how to calculate distances on the earth's surface.

Yes, we said that using Pythagoras won't work well, but here it is anyway.

c = sqrt(a^2 + b^2)

(Eq. 1)

In case of two points (A and B), the above function looks like this:

distanceAB = sqrt( (latitudeA-latitudeB)^2 + (longitudeA-longitudeB)^2)

(Eq. 2)

This function will give us a distance in degrees. In order to get distances in miles, we need to know how many miles are in a degree.
We can caluclate this using the earth's radius which is 3963.19 miles from which we can calucate the earth's circumfence: 24901.457 miles. Dividing this value by 360, we get 69.17 miles per degree. This value works well for miles per degree latitude. If you look at the longitude, you see how they all meet at the poles. This means, that miles per degree longitude varies depending on how close we are to the poles... it varies depending on the latitude. An average value for miles per degree longitude in North America is 53 degrees. We can now use equation 2 to calculate a distance in miles:

distanceAB = sqrt( (69.17*(latitudeA-latitudeB))^2 + (53*(longitudeA-longitudeB))^2)

(Eq. 3)

Equation 3 is used in getDistanceRough() of the API. As you probably guess, the average value of 53 degrees only works well at that specific latitude. If you go further North or closer to the Equator, your distance calculations will be inaccurate. To get better results, we express the degrees per longitude as a function of the Latitude (the API also provides a function to do this calculation: getMilesPerDegreeLongitude()):

distanceAB = sqrt( (69.17*(latitudeA-latitudeB))^2 + (69.17*(longitudeA-longitudeB)*cos(latitudeA/57.2957795))^2)

(Eq. 4)

Now we have a function for calculating distances that comes pretty close to the real value (this equation is used in getDistanceApproximate() of the API). It is still, however, an approximation. To calculate more accurate values, we have to use the "Great Circle Distance Formula":

distanceAB = 3963.19 * acos( sin(latitudeA/57.2957795) * sin(latitudeB/57.2957795) + cos(latitudeA/57.2957795) * cos(latitudeB/57.2957795) * cos(longitudeB/57.2957795-longitudeA/57.2957795)))

(Eq. 5)

We use this equation in getDistanceAccurate() of the API.

Now, the earth is not exactly a sphere, it's an ellipsoid... but wait, we think the above equation is plenty good enough.

Spatial Queries

Let us look at a simple spatial query to find all the points within a specified distance from a search center. For example, users want to find all the coffee shops within 5 miles from their location. How do we do that?
There are, of course, commercial and some open source tools that help with this task. Oracle with its Oracle Spatial and Locator provide an extensive set for spatial queries, coordinate transformation and much more. If you use PostgreSQL you can use PostGIS. But what about the database that so many web developers use, MySQL? MySQL provides a Spatial Extension with a Geometry data type. While this data type is great for storing polygons and more complex geometric objects, we can simply use a latitude and longitude column for storing points.

Doing a proximity search includes two steps:

• Limit the set of points to all the points within a bounding box.
• Check for points that are within the specified distance.

To limit the set of points to all the points within a bounding box, we first have to create this bounding box (also called the minimum bounding rectangle, or MBR). In order to do this, we first convert the search distance into degrees. We can simply use the values that we calculated above, 69.17 miles per degree latitude, and 53 miles per degree longitude (or use the getMilesPerDegreeLongitude() to get a more accurate value).
Once we have the search distance in degrees for latitude and longitude, we can easily calculate the maximum and minimum latitude and longitude of the bounding box (minLatitude, minLongitude, maxLatitude, maxLongitude).
A SQL statement that returns all the points within the bounding box would look like this:

SELECT * FROM poi_table WHERE latitude >= minLatitude AND latitude <= maxLatitude AND longitude >= minLongitude AND longitude <= maxLongitude

(SQL 1)

Now we can add a distance calculation and a check to see if the points are within the specified distance (using equation 4):

SELECT poi_id, poi_name, sqrt( pow(69.17*(centerLat-latitude), 2) + pow(69.17*(centerLng-longitude )* cos(centerLatitude/57.2957795), 2) ) AS distance FROM poi_table WHERE latitude >= minLatitude AND latitude <= maxLatitude AND longitude >= minLongitude AND longitude <= maxLongitude AND pow(69.17*(centerLat-latitude), 2) + pow(69.17*(centerLng-longitude )* cos(centerLatitude/57.2957795), 2) <= pow(distance, 2) ORDER BY distance

(SQL 2)

The above query is the one returned by getWithinDistanceSQL_Approximate(). To get more accurate results, we can use equation 5:

SELECT poi_id, poi_name, 3963.19 * acos(sin(centerLng/57.2957795)*sin(latitude/57.2957795) + cos(centerLng/57.2957795)*cos(latitude/57.2957795)* cos(centerLng /57.2957795 - centerLatitude/57.2957795) ) AS distance FROM poi_table WHERE latitude >= minLatitude AND latitude <= maxLatitude AND longitude >= minLongitude AND longitude <= maxLongitude AND pow(69.17*(centerLat-latitude), 2) + pow(69.17*(centerLng-longitude )* cos(centerLatitude/57.2957795), 2) <= pow(distance, 2) ORDER BY distance

(SQL 3)

The above query uses a lot of triginometry and is, therefore, a bit slow (although still fast enough on small datasets). It is the query returned by getWithinDistanceSQL_Accurate(). One option to speed up the query execution is to use equation 4 in the check for distance but still return the accurate distance as a column value (using equation 5):

SELECT poi_id, poi_name, 3963.19 * acos(sin(centerLng/57.2957795)*sin(latitude/57.2957795) + cos(centerLng/57.2957795)*cos(latitude/57.2957795)* cos(centerLng /57.2957795 - centerLatitude/57.2957795) ) AS distance FROM poi_table WHERE latitude >= minLatitude AND latitude <= maxLatitude AND longitude >= minLongitude AND longitude <= maxLongitude AND 3963.19 * acos(sin(centerLng/57.2957795)*sin(latitude/57.2957795) + cos(centerLng/57.2957795)*cos(latitude/57.2957795)* cos(centerLng /57.2957795 - centerLatitude/57.2957795) ) <= distance ORDER BY distance

(SQL 4)

The above query is the one returned by getWithinDistanceSQL_Mix().

uLocate Communications, © 2007