3

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.

Thanks Mark

5

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.

  • Well answered. As I was reading you answer, it occurred to me that I can add another option which would be to process the individual point data into a multi(linestring) after the track was completed and remove from the table to gain the ability to run the standard geometry queries and reduce the size of the point table. Also, time was not something I had considered, but yes, I think it is worth including. When the data is porcessed, I could run a Ramer Douglas Peucker, but I am unsure if it will handle loops and circles. That may be another question to ask. – Mark Cupitt Feb 24 '14 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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