Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I am using POSTGIS I have written the following code which gives the last time when a ping is recieved within a radius of a

I am using POSTGIS

I have written the following code which gives the last time when a ping is recieved within a radius of a point using the max function (in a given time window). However what I want is getting the timestamp of each time pings enter or leave the radius in the given time window, I can not figure out how to accomplish this.

cluster_groups as ( select uc_id_imported, uc_name_imported , id as cluster_id, points_geom as cluster_centroid from team_support_centers tsc where uc_id_imported in (876,877,878,879,880) ) , dept_cluster_raw as ( SELECT routes.uc_id , cg.cluster_id , cg.cluster_centroid , routes.imei , routes.time_created::date as campaign_date, max(routes.time_created) as max_per_imei_cluster FROM cluster_groups as cg CROSS JOIN LATERAL ( SELECT routes.time_created,routes.imei, routes.uc_id FROM orig_dataset as routes where st_distance( st_transform(routes.points_geom, 4326)::geography , st_transform(ST_SetSRID(cg.cluster_centroid, 4326),4326)::geography ) < 250 and (routes.time_created between '2023-01-17 06:00:00 +05:00' and '2023-01-17 11:00:00 +05:00' ) ) routes group by 1,2,3,4,5 ) , dept_master_data as ( select imei,uc_id,cluster_id,campaign_date, max_per_imei_cluster as individual_dept_time, avg(max_per_imei_cluster::time) over (partition by uc_id,cluster_id,campaign_date) as avg_dept_time from dept_cluster_raw )

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Data Management Databases And Organizations

Authors: Richard T. Watson

2nd Edition

0471180742, 978-0471180746

More Books

Students also viewed these Databases questions

Question

e. What difficulties did they encounter?

Answered: 1 week ago

Question

2. Explain how the role of training is changing.

Answered: 1 week ago

Question

7. General Mills

Answered: 1 week ago