Good Morning/Afternoon,
Fairly new at SQL in general, but I've been able to put together a few queries that give me the output I need for a certain business case, but I have a new requirement that I am struggling with.
In a nutshell, I am extracting a list of "sites" (physical locations) for a given customer along with the lat/lon, and then feeding each location to Google Maps to plot a point for each site. What I am trying to do is query for unique sites. If there are multiple sites that are within 1/2 mile of each other, they are lumped as a single site. For example, if a customer has 10 sites within 1/2 mile of each other, they technically have 1 site, not 10.
Here is an example of what I am doing:
select c.id, i.site_id, s.name, max(i.captured_at), s.center_lat, s.center_lng, CONCAT(s.center_lat, ',' , s.center_lng) AS LOCATION
from images i
inner join sites s on s.id = i.site_id
inner join customers c on c.id = s.customer_id
where i.hidden = 'false' and i.copied_from_id is null and i.status = 'complete' and c.id = '353'
group by c.id, i.site_id, s.name, s.center_lat, s.center_lng
order by max DESC
Here is an example of the output:
As it stands now, it returns a count of 4 sites (I am rendering the results in Google Data Studio displaying the count of records returned), which works fine for another scenario. However, since these sites are within 1/2 mile of each other, they are technically 1 site, not 4. I am trying to determine how to come up with a count of 1 site vs. 4 in this scenario. If there was another entry where the lat/lon (location) was more than 1/2 mile away, I would be looking for a count of 2 sites. I hope this all makes sense.
Currently trying to research where to start so if there are any references, or a push in the right direction, that would be awesome. Thanks very much.
