I think you might be best off considering whether you should use a range of metrics. Some users may consider the average spatialite error to be of concern, but a bigger concern is "how bad does it get". You are presumably looking at this in at least some respect (e.g. temporal vs spatial), I'm just suggesting looking very widely.
I don't have all the metrics that might be used, but one you should look at is the Hausdorff distance. There is an implementation in GEOS (and presumably in JTS). We also support it in SpatiaLite and its in PostGIS too, if you'd prefer to use that.
About different geometry types:
From your description it looks like you should absolutely store your trajectories as linestrings. If you store them as points or multipoints you will have to build linestrings in runtime if you don't only want to do the calculations on the points defining the trajectories but also what is between the points.
an Example (in meter-based projection)
select ST_Distance('MULTIPOINT(10 10, 10 20)'::geometry,'POINT(0 15)'::geometry)
Will return:
11.1803398874989 which is the distance from the closest point in the multipoint to the point.
while:
select ST_Distance('LINESTRING(10 10, 10 20)'::geometry,'POINT(0 15)'::geometry)
will return 10 which is the distance from the point to the closest point to the closest point on the line.
The same things about intersections test. If you only uses the points, a trajectory passing over a polygon without points in the polygon, will not be found if you only calculate on the vertex.points and don't use the points to define linestrings.
About projections you should absolutely transform (with ST_Transform) your data to a local projection if possible. It is faster and you will get a lot more functions to choose from.
About R you should be aware that postgreSQL have have a procedural language handling R directly in the database: PL/R
About indexing:
The spatial index used in postgis is GIST which builds an index of the bounding boxes.
Best Answer
The python module Machine Learning Python (mlpy) has an LCS method including an LCS for real series:
http://mlpy.sourceforge.net/docs/3.5/lcs.html
Perhaps you could also adapt the LCS algorithm for strings and test your own implementation against mlpy.
A quick google search results in the folloing Wikipedia page:
https://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Longest_common_subsequence#Python
I copied the content for backup purpose: