1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.