Database design for search of geocoordinates

Question

In a MySQL database I have tracking data of animals (elephants). Currently the design is a follows:

timestamp(INT) | individualID(INT) | longitude(DECIMAL) | latitude(DECIMAL)

Now I'd like to ask several questions to the database

1. What is the closest individual at time t?
2. Which individuals are around (perimeter 50km) at time t?
3. Which individuals are present in the reservation at time t? (reservation is given by a polygon)

Is there some built-in functionality available for MySQL? Should I choose a different database?

Answer 1

There is a MySQL Spatial Extension, that implements GEOMETRY, based on OGC SFS (simple features specification). It is a base standard for SPATIAL data implementation for a lot of database engines.

For client-side there are OpenLayers, HTML5 SVG, Google Maps. Look at GIS.StackExchange.com.

---

From my experince (if it might be take into a count, as it was 2 years ago), MySQL implementation of a spatial data is sufficient, but not the best. It is implemented better in Oracle and MS SQL Server (IMHO). If you are not going to build very complex map - MySQL functionality is good enough. For complex functionality I would suggest to use different database engine.

---

There are similar question on SO: Fastest Way to Find Distance Between Two Lat/Long Points, where accepted answer used MySQL Spatial Extension. If you will research more, you may find out answers on your other questions.

---

I also may try to supply query basis:

• Question #1: Select shortes line between 2 different individuals (MIN() + GLength() + LineString + Points involved).
• Question #2: Select all individuals in the area around given individual (Contains() + Polygon + Points involved).
• Question #3: Select all individuals in the specified polygon (Contains() + Polygon + Points involved).

Answer 2

I overcame the first 2 problems in my PHP app using the following code http://www.geodatasource.com/developers/php there is also a SQL version of the solution there.

Basically you need to calculate the length of an arc on the surface of the globe, using trigonometry to get the distance between 2 grid points.

You can then compare the distances of multiple individuals from your base location and eliminate all with a distance of more than 50km to get your group of animals within the perimeter.

Part 3 of your question requires some more complicated algebra and is not a problem that I have tackled.