Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jun 26, 2012 03:44 PM by magicmike2011
Jun 25, 2012 09:36 AM|LINK
I need some opinions with regards to the ideal backend for a prototype I am developing:
Brief Info on System Functionality
I am looking to develop a prototye for a system whereby drivers (of delivery vans) download a freely developed app on their mobile smart phone, and then through the GPS sensor on their device, I would be able to track their location on the map at several
intervals (say every minute) and chart their movement on a map which would be visible through their app, as well as through a secured online website. The charting on a map will be somewhat similar to flightradar24.com, just points on a map. When two particular
drivers are close to each other, I would then notify the drivers accordingly.
Now the question I have is what would the ideal backend for such a system be? Since there will be a lot of updates being pushed by the app and since I would need to constantly compare the geo location position of the drivers in order to detect whether any
particular drivers are close to each other, would a database be ideal for such a job? What would you recommend?
Jun 26, 2012 12:33 AM|LINK
Sql 2008 r2 has great support for GeoSpatial data. Aslong as you dont need more than the max storage of the express version (10gb i think now) you could get away with using the Express with Advanced Services version. But if you need more disk space than
that, the liscensing for standard version and up is, well, crazy to say the least unless you are developing for a pretty large client with deep pockets.
Another one often overlooked by web developers but very common among full-blown gps service providers (as well as video games and other high workload software) is PostgreSql. Postgre is probably the best full-featured enterprise level database available
as opensource. Theres virtually NO limits in the liscensing, it may even be completely customised if you choose and has some really great features. It's security is top-notch, features native support for multiple languages such as creating procedures, jobs
and functions directly in java, c++, tcl, and a bunch others. http://www.postgresql.org/
SQLite may even be a consideration for you. It has some great features aswell, performs great and I'm not sure how many people know this but SQLite was originally created for use in navaigation systems of naval ships! lol And now a version of it is being
used for HTML5's local storage feature ;) Could be promising. http://www.sqlite.org/
Hope this helps
Jun 26, 2012 09:08 AM|LINK
Thanks for your opinion. This really gave me a better idea. After checking them out, I might opt for PostgreSQL, though it is still to confirm.
Regarding the GPS position, since this will be updated at regular intervals so that I can do processing based on the location of a number of delivery vans, does it make sense to store all the GPS positions of all the drivers every minute in the database?
Or have a field that is updated every minute? I'm usually familiar with an XML file, though in this case not sure what is the best option when databases come into play..
Jun 26, 2012 03:44 PM|LINK
If you are meaning storing every update of every driver as it's own record, I'd say no, unless you are wanting to do some sort of statistic work later with all the positions summarily at a later time. Even still, that wouldn't be necessary if you configure
the database to automatically generate statistics. Then you could just read from the log file when it's time to generate statistical data for your own use.
Cleanest way and best to protect data integrity would be one record for each driver and updating the appropriate location fields on the incremented time frame (1 minute or so). This way, the logic that does the comparison of positions doesn't have to hang
and wait for any possible latency from a particular drivers mobile device.
I'm sure you probably already have a structural pattern in mind for the communications but I'll mention this for sake of argument. On the server side you could have a web service that listens for requests (using authentication most likely for security) and
the mobile apps could be set to call the service on the incremented time frame to send it's updated location and update the location fields in the database. In the database, you may want to make use of a specialized index on the locations table to help keep
the performance of the procedures that compare the locations of each driver since you will want to keep row-locking on the tables enabled during the querying of the location data from tables.
You also mentioned you have a certain comfort level with xml. Most respectable databases fully support xml integration and have a data type to represent it as a column type and variable type aswell as xml-querying support of some form. I believe all the
above mentioned do aswell. You could opt to use xml columns to store location data if you choose. Most say theres no performance hit by using xml data types in databases because of the query-optimizer however I still think there is some, however minimal it
may be. But in your case, it may be benificial if you end up deciding on an xml-based SOAP service for updating from the mobile devices. Since SOAP services communicate with xml, it could simply be authenticated then passed directly to the database for processing.
Meh, just food for thought.
Anyway, hope this helps.