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?

Centroclinal

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!

Ensiform

Actually I went ahead and implemented the ORM a little. Depending on the columntype it build an object (SpatialPoint, SpatialPolygon). These classes have methods to give me the right DB statements as well. I don't know about the speed, but in most cases the app won't spawn more than a couple hundred objects anyway. Thanks for the vote of confidence on MySQL, I can't really afford the time to make a switch to postgreSQL. – Centroclinal

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.

Houselights

Any suggestions on how to handle/store a lot of points coming from the vehicles? – Centroclinal

But for run of the mill storing lots of data points, even mysql is ok. If you data grows too big, you can always consider using NoSQL databases like Cassandra, PNuts, or HBase – Houselights

Answer 3

On "how to handle/store a lot of points coming from the vehicles":

I'm working on a very similar project. I've solved this problem by maintaining 2 tables (using MySQL but this holds true for any other DB):

• one for tracking objects (vehicles, users, whatever)

  this table would have the object id as primary key and any updates that violates the primary key constraint would update the data stored for this key. It can be easily achieved with "ON DUPLICATE KEY UPDATE" This makes the lookup extremely fast for tracking and keeps only one instance of location data/object. I have also implemented server side logic for deleteing records of obsolate data(after a certain ammount of time these data needs to be deleted if no updates received on them)

• one for history/lookup purposes

  this table would have the object id and the timestamp as composite primary key. The table can be partitioned on the timestamp column.

Any update on an object's location would insert to both tables.

I hope this helps.

Cody