Most Efficient method of Storing Point Data in PostGres/PostGIS to later re-create a track [closed]

Question

I am trying to determine the most efficient way to store point data in PostGIS to later re-create tracks

Criteria:

• 1 second updates
• 100- 200 tracks active at any one time
• 1 hour max track time so approx 5400 points per track
• Track data extracted as a report, not real time display, but the reports would be run frequently, many times daily

I can see two possibilities at this stage:

a) Store the point data as a table with a field that will group them to a particular track. So three fields used, gid, trackid, lat/lon, resulting in a lot of rows

or:

b) Store each track as one row in a table with a field that has the point data appended to it at each update

My Questions are:

1. Which is a more efficient way to store the point data, Numeric Fields (two columns), char fields, or GIS POINT/LINESTRING fields
2. Which would be the most efficient way of the two options above, given server/database load to store and recreate the track data. Option a) would require queries to build each reported track whereas option b) would only need to look at one row per track.
3. Is there a better way than either a) or b) to approach this requirement.

Answer 1

I've used 2 approaches to this problem:

1. A table with meta data and a geometry column containing a MultiLineString.
2. A table with track meta data and a numeric primary key, and a second table with a geometry column containing Points, and auto incrementing primary key for ordering, and a relationship to the track table.

Pluses to #1:

• You can run all of the standard geometry queries on the track.
• Fast reads
• Fast deletes

Minuses to #1

• Appends are slow, as far as a know there is not a fast way to add a point to the end of a LineString.

Pluses to #2

• Adding a point is fast, it's just an insert
• Counting points in a track is fast

Minuses to #2

• Lots of standard geometry functions are not easily available(think ST_Length)
• The point table can get large very quickly

As for what type of field to use, Use a geometry field, it may seem convenient to use 2 numeric fields, but at some point this will come back to bite you when you want to use any of the standard geometry functions.

Another thing to consider: do you need to know the time for each point? If you go for option #1 the easiest way to go is to store the time in the M coordinate, but beware, lots of libraries choke on 4 dimensional geometries. If you go for #2 you can store the time in a separate column or in the M coordinate. If you use a separate column you can eliminate the auto incrementing field and sort by time.

Conclusion: If the points come in all at once store them as a (multi)linestring, if they come in one at a time store them in a separate table.