PHP, MySQL, spatial data and design

Question

Im building an application where vehicles coordinates are being logged by GPS. I want to implement a couple of features to start with, such as:

• realtime tracking of vehicles
• history tracking of vehicles
• keeping locations and area's for customer records

I need some guidelines as where to start on database and application design. Anything from best practices, hints to experience would really help me get on the right track.

• How would one tackle ORM for geometry? For example: A location would convert to a class SpatialPoint, where an area would convert to a class SpatialPolygon
• How do i keep the massive data stream comming from the vehicles sane? Im thinking a table to keep the latest points in (for realtime data) and batch parsing this data into PolyLines in a separate table for history purposes (one line per employee shift on a vehicle).
• Mysql is probably not the best choice for this, but I'm planning on using Solr as the index for quick location based searches. Although we need to do some realtime distance calculation like as which vehicle is nearest to customer X. Any thoughts?

Answer 1

I can help you on one bit, mysql definitely is the best choice, I've been down the same path as you many times and the mysql spatial extension is fantastic, infact it's awesomely fast even over tables with 5 million+ rows of spatial data, it's all in the index. The spatial extension is one of the best kept mysql secrets that few use ;)

ORM, I'd recommend skipping for this tbh - if you have a huge amount of data all those instances of classes will kill your application, stick with a v simple array structure for dealing with the data.

RE massive data stream, either consume it live and only store every 10th entry, or just stick it all in the one table - it won't impact speed due to how the table is indexed, but size considerations may be worth considering.

For an alternative coming from PHP, you could try postgis on postgresql, but I've always favoured mysql for ease of use, native support and all round speed.

Good luck!

Answer 2

Yes, I recommend use of Solr as well. Current release is 1.4. It works incredibly well for this problem.

1. ORM - You may need sfSolrPlugin with Doctrine ORM to tie PHP to Solr, see article from LucidWorks entitled Building a search application in 15 person-days

2. real time index updates - That is coming in the next release of Solr, I believe Solr 1.5. You can get it from SVN.

3. Geo-spatial search - I use Spatial Search Plugin for Apache Solr. G-s capabilities might be included in Solr 1.5. I believe that there are already some rudimentary support for g-s, w/o use of plugin.