OPTIONS CoordSys("Latitude / Longitude"); PARAMETERS [dB range (3)] DOUBLE; -- leave blank for default of 3 dB SELECT [A].[Date], [A].[Code Space], [A].[ID] AS [Pinger ID], CAST([A].[Date] AS TEXT) & "_" & CAST([A].[Code Space] AS TEXT) & "_" & CAST([A].[ID] AS TEXT) AS [IDbyDate], [A].[MaxSignal], MIN([B].[Signal (dB)]) AS [MinSignal], COUNT(*) AS [signals], [A].[MaxSignal] - MIN([B].[Signal (dB)]) AS [dB range], -- mean location, weighted by signal strength -- (linear weighting by dB value) NewPointLatLon( SUM([B].[Longitude] * [B].[Signal (dB)]) / SUM([B].[Signal (dB)]), -- Longitude weighted SUM([B].[Latitude] * [B].[Signal (dB)]) / SUM([B].[Signal (dB)]) -- Latitude weighted ) AS [Location weighted] FROM -- max signal ( SELECT [Date], [Code Space], [ID], MAX([Signal (dB)]) AS [MaxSignal] FROM [pinpointdetections] GROUP BY [Date], [Code Space], [ID] ) AS [A] INNER JOIN -- all signals within dB range of max [pinpointdetections] AS [B] ON ([A].[Date], [A].[Code Space], [A].[ID]) = ([B].[Date], [B].[Code Space], [B].[ID]) AND [A].[MaxSignal] - [B].[Signal (dB)] <= Coalesce([db range (3)], 3) GROUP BY [A].[Date], [A].[Code Space], [A].[ID], [A].[MaxSignal] ;