Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am developing the vehicle tracking system using postgresql.

When the data comes from device to my DB server, I am calling the following functions after inserting data into log table.

  1. Distance Update -- picking up the last inserted row (latitude, longitude) of the specific device from log table and calculating distance with newly inserted row... updating the distance in the same table

  2. Trip function -- gets the list of all locations'(stations) latitude, longitude, radius and again calculating the distance [by using this I am able to get the details of trip start date and trip end date and inserting those details into anotehr table]

  3. Geofence functions

  4. Overspeed function

  5. some other functions

I will get almost 2000 rows per minute.

Will this hit my performance ... otherwise do I need to write the trigger for this...

What happens if the data increases in the table more than 10,00,000... because we are picking the last inserted data based on the device everytime?

Do i get any db schema for basic vehicle tracking?

Thanks in advance, AJ

share|improve this question

2 Answers 2

up vote 2 down vote accepted

You going need to do some indexing on the device identifier attribute and latlng attribute as scanning the table will become slower with large quantity of rows. Why not use a auxiliary table to hold the last device inserted row? Postgresql offers several kinds of index to solve slow speed cases.

Also large trips may be slower to compute due to great number of locations your function will need to locate. Well I did not know your data but you should must have those things in mind and a large memory will help postgres find things quicker without doing disk writes.

share|improve this answer
    
Thanks for gave me very usefull infomation. My data from device will give around 10 fields.. like lat,lng,speed,fuel date and time etc.. I am planing to put the latest information in one table (this table contains only one latest row per device).. Can u pls answer the following questions.. 1)What will happen if i take the latest infomrmation from latest table and do the operations(w.r.t. Device Data) in trigger and inserting into log table and updating the same into latest infomation table? 2)What will happen if i do the same above process in PHP when data comes from the device –  varma Dec 26 '12 at 8:26
    
1 - Trigger MUST run fast. To do a distance update you only need calculate the distance from latest point and store the sum into distance field. Very simple if you have the last point at latest_information table. That should all be done inside the trigger. 2 - PHP will be slower since it will need to call database and wait it to process then send another statement wait. So use PHP to unpack data from client into fields and call the database to do the calculations with the field data. –  cavila Dec 26 '12 at 12:29
    
Thanks... Yes i have some calculation but it need select statments for trips,location,geofence tables which contains max of 10000 rows and updates into the same table... can i do that same in db? Can i split logics(trips/distance/geofence etc) into functions and calling the functions in trigger? will it does any performance impact(calling many functions)? –  varma Dec 26 '12 at 13:38
    
In my case, the tracer calculates the distance (internal odometer) for me and just sends it along,it's much more accurate too since the device's hardware/software programming can filter out bad ones for you so you don't have to do it centralised –  Glenn Plas Dec 27 '12 at 1:24
1  
10.000 rows should be nothing to Postresql to scan. In trigger you should put all the logic to make data entry and dependencies resolved in function of the new data arrived. If the new row at given table would change a distance value at another table them this should be put in trigger. Take care that something wrong at any trigger function will abort all operations. So do validation at very begin to avoid undoing lots of stuff. Function names also define trigger calling order. –  cavila Dec 27 '12 at 14:32

This won't scale, that's how I started 7 years ago with such an application doing vehicle tracking.

I have 3500 of those assets now, imagine them inserting 1 record at the same time. This was a nightmare, especially when GPRS comms failed at provider level and all those updates where waiting, every device has 50 records to send....

We now keep all data in sqlite files per imei, and it is not served with an http servers (although it speaks http) but with a custom TCP server. The reason is:

  • scalability together with haproxy
  • data overhead over GPRS costs money. So every server header we needlessly send back will end up on our GSM bill.

You should seperate your logic (e.g. customers, devices) and everything that drives your site away from where the events of the trackers end up.

Here's a basic schema that scales (sqlite file PER unique imei):

CREATE TABLE Event_customerx_all (
  record_id INTEGER PRIMARY KEY,
  com_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gps_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  imei bigint(20) NOT NULL,
  switch unsigned int(3) NOT NULL,
  event_id unsigned int(5) NOT NULL,
  latitude unsigned int(10) NOT NULL,
  longitude unsigned int(10) NOT NULL,
  IO unsigned int(3) NOT NULL,
  raw_data BLOB NOT NULL,
  sys_parsed int(3) DEFAULT NULL,
  UNIQUE (imei,raw_data) ON CONFLICT IGNORE
);

And it's simple in design. raw_data is 33 bytes of our events, this is a custom compresion, every package is that large, max of 42 events can be sent (the device uses GET method).

Using a custom class you can extract more information like gps sats in view, or fixed sats, distance, speed .... etc but you won't really need to 'unpack' it all when it comes in. The other data (event_id, switch, gps_date etc) is actually also extractable in raw_data. You unpack when you need it.

now why is this good? well, this scales, imagine you invent a new var, now you need to change the schema when you 'extract' them all. But in this case, your software takes care of it, as your class will be able to extract that event detail.

You application will basically always want to do in (general) for the most part:

  • Select either the latest of information
  • Select a date range (e.g. trip/track).

Most will fall under these 2. So you also keep your latest position (and perhaps latest interesting event like start/stop, as I do ...) separately when it comes in.

In this app, switch and event_id will make you able to figure out what type of raw_data you have, as some events don't have coordinates, you'll want to filter those when drawing a map, perhaps not if you are creating a report....

Do not write a trigger for this... Very bad idea. What I explain above has replaced such a design. And all those 3500 clients are being received by single a 8Gb Linode VPS.

There's much more, but not making this mistake will gain you years if this is a serious application we are talking about. You're welcome ;-)

share|improve this answer
1  
Thanks for gave very usefull infomation. –  varma Dec 26 '12 at 8:18
    
Thanks for gave me very usefull infomation. My data from device will give around 10 fields.. like lat,lng,speed,fuel date and time etc.. I am planing to put the latest information in one table (this table contains only one latest row per device).. Can u pls answer the following questions.. 1)What will happen if i take the latest infomrmation from latest table and do the operations(w.r.t. Device Data) in trigger and inserting into log table and updating the same into latest infomation table? 2)What will happen if i do the same above process in PHP when data comes from the device –  varma Dec 26 '12 at 8:26
    
Your trigger will not scale.... You should just keep 2 different tables and update the latest in a light-weight fashion. Triggers are way too heavy for this. The one I wrote back then was 3 pages long, just to cover the basics. Doing this all in PHP is much less stressy on any systems. That's how I do it, the server receives for example 40 events, now you want to separately save the latest info next to the 40 rows that will go into your historic table. So doing so in code will be much much easier than a table with 10 million records doing trigger work. (ANY database will suffer) –  Glenn Plas Dec 26 '12 at 11:08
    
If you want to do this in a DB itself, you will need some partitioning as well for performance. Back in the days we used Mysql(later mariadb for performance) and we had to partition by data range (3 days per partition). If you don't your queries will take a long time. I'm not sure how this maps on postgresql, I only use postgresql for OpenStreetMap work but It's a very good DB imho, I think it support hooks for custom trigger scripts, which adds another dimension to the possible solution –  Glenn Plas Dec 26 '12 at 11:12
    
as per the conversation any db wil suffer if we use the trigger with millions of rows in a table... i wonder how the fleet management is done ? Mysql has limitations in size(upto 4MB)? how did you do this tracking system by using sqlite.. can you explian it(if u r inetersted :)) –  varma Dec 26 '12 at 11:49

protected by Community Mar 4 '13 at 15:38

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.