It's not a simple problem, it involves some kind of forced iteration over the set of candidate points. This chapter from the workshop shows a similar problem, but not exact (your problem is slightly easier)
http://postgis.net/workshops/postgis-intro/advanced_geometry_construction.html
The nearest neighbor searching chapter from the workshop shows the tools you might use to do an index-assisted approach with some external loop driving the query
http://postgis.net/workshops/postgis-intro/knn.html
If your points have a distinct id and you know a distance tolerance (9999) they will all fall within, a self-join and use of the "DISTINCT ON" filter will get you the answer in one go.
WITH unfiltered AS
(
SELECT t1.id AS id1, t2.id AS id2, ST_Distance(t1.geom, t2.geom) as dist
FROM t t1, t t2 WHERE ST_DWithin(t1.geom, t2.geom, 9999) AND t1.id <> t2.id
ORDER BY t1.id, ST_Distance(t1.geom, t2.geom) ASC
)
SELECT DISTINCT ON (id1) id1, id2, dist FROM unfiltered;
It first gathers the candidates combinations of points, and sorts them by distance. Then the "distinct on" filter strips out just the first member of each candidate group, which conveniently is the closest, thanks to the pre-sorting.
If you need to use the road distance as the cost of travel, then you would indeed need to use Network Analyst if you are using ArcGIS and want to take advantage of the restrictions imposed on the roads in the network dataset (one-way, turns, etc.).
The solver you would need for this is OD Cost Matrix. You basically need to generate a matrix where each point is routed to all other points. The OD matrix is usually used for modelling business cases where you have a couple of origins (warehouses) and many consumers (retail network points) and you want to report the travel costs between the origins and the destinations.
In your case, however, because everything is stored in the same feature class, you need to load your feature class points both into origins and destinations (i.e., they will be identical). This is a valid approach, and I am using the OD cost matrix this way quite often myself, too.
The number of lines generated for a feature class with N
points will be (N*N) - N
. This is because after the solve, you would exclude those pairs where the source and the origin are the same. You can filter them out easily by using the SQL query on the output cost (travel time or distance) field (traveltimefield > 0
).
Performance wise, it would be wise to choose None for the Output shape type option. I don't know how many points you have in your feature classes, but ~1500 points both in origins and destinations (resulting in ~2.4 mln lines) never was a problem for me to run in just a couple of minutes (I have a decent laptop with i7 CPU though). If you have more points in your feature class, you can run out of memory in ArcMap (it's a 32bit application that cannot take more than just a couple of GBs of RAM).
If you will hit a RAM limit of ArcMap, you might consider running this solve from Python 64bit process. This is what I usually do when processing large road networks (the RAM consumption for the running Python process was about 12GB at peak, no problem).
There are other options of generating the OD matrix outside of ArcGIS if you are OK with just one-way restrictions and no access to other ArcGIS network dataset based restrictions (vehicle height, turns, etc). networkx - a Python package for working with graphs is something I use often for network analysis, but you would need to program Python then.
Best Answer
It is fairly straightforward to write a single query for this, you just need to break it down into logical blocks. To start with, you need your point geometry sorted by track and time:
Here I assume you have your points in a geometry column. A geography column in this case would achieve the same thing, and actually save a bit of faff in the next step.
(Note here I've used ST_AsText() to make things readable. You should remove this in the final query).
With my simple database of 2 tracks and 6 points, that query gives me:
The next step is to aggregate those points into linestrings. This is done with the ST_MakeLine() function:
Which yields:
Finally you need to get the length of that line over the spheroid of your choice. If you're using GPS then inevitably it'll be the WGS84 spheroid. The function to use is ST_Length_Spheroid().
So putting that all together, my final query ends up looking like this:
And I get these results:
If you want to limit it to a specific track, change your
WHERE
clause to something like this:If you're using geography columns instead of geometry, you can eliminate the spheroid part and just use ST_Length(). Note that the second parameter of ST_Length() when using geography columns should be set to TRUE if you want more accurate but slower calculations. The only drawback is that geography columns are limited to the WGS84 spheroid.