I have two CTEs:
cluster_groups (cluster_id [int], cluster_centroid [points_geom])
subset_cr (imei [identifier], points_geom [geometry], time_created )
This is my code:
SELECT
cg.cluster_id ,
routes.imei ,
routes.time_created::date as date,
min(routes.time_created) as min_per_imei_cluster
FROM cluster_groups as cg
CROSS JOIN LATERAL (
SELECT routes.time_created,routes.imei
FROM subset_cr as routes
where st_distance(
st_transform(routes.points_geom, 4326)::geography ,
st_transform(cg.cluster_centroid, 4326)::geography ) < 100
) routes
group by 1,2,3
The code generates a result set, but I want to ask if my understanding is correct. Here is what I think is happening:
For each value of cg.cluster_centroid, it is compared one by one to each row of routes.points_geom.
So for example if there are 2 rows of data in each CTE, then the following will happen sequentially AND return a result at each stage, so there will be 4 total outputs of st_distance -
st_distance runs for cluster_1 and the first row of points_geom column
st_distance runs for cluster_1 and the second row of points_geom column
st_distance runs for cluster_2 and the first row of points_geom column
st_distance runs for cluster_2 and the second row of points_geom column
Is my understanding correct? OR is st_distance considering ALL rows of routes.points_geom at once for each cluster.