Subscribe to this thread
Home - General / All posts - Max values by ID Date (Acoustic telemetry locations)
cycloxslug54 post(s)
#26-Jun-19 23:08

This is likely a trivial SQL query, but I've been banging my head against it for a while with no success.

The setup: I am using a portable hydrophone to listen for acoustic fish tags. The hydrophone records a GPS location (Lat/long), ID, Code Space, and signal strength [Signal (dB)] for each observation. The fish is most likely not actually at the GPS location being recorded, but is likely to be closest to the points with the highest signal strength. So I am generating a convex hull of all points where the fish was heard, and I'd then like to take a subset of the highest signal strength points and either generate a single point or another area to identify the most likely area where the fish is.

I can generate the convex hulls fine on the whole dataset, but I'm having a hard time just generating the maximum values for each individual (id+code space) on each date. There are also likely to be more than one point that shares the same signal strength.

I've attached a sample of the data table I'm querying and my attempt at the sql (which does not run--it doesn't like my [a] , [b] references). Be gentle, my entire SQL learning has been guess and check via the manifold help files!

code 

Select [Date]&[Code Space]&[ID] AS [IDbyDate], [Signal (dB)] 

FROM [pinpointdetections] AS [A]

JOIN 

(Select DISTINCT [Date],[Code Space],[ID], [Date]&[Code Space]&[ID] AS [IDbyDate], Max([Signal (dB)]) AS [MaxSignal] FROM [pinpointdetections] AS [B] GROUP BY [Date],[Code Space],[ID] ) 

ON [A].[IDbyDate] = [B].[IDbyDate] WHERE [B].[MaxSignal]=[A].[Signal (dB)]

Attachments:
pinpointdetections.csv

tjhb

8,958 post(s)
#27-Jun-19 01:05

I'm having a hard time just generating the maximum values for each individual (id+code space) on each date.

Yes that is the first step. So let's just do that bit.

(I'm assuming Manifold 8. You could potentially do a more sophisticated job in Manifold 9. For example, you could produce a weighted average location of the strongest N pings per ID, or the strongest within S standard deviations of the maximum, using signal strength as the relative weight, with a COLLECT construct. Some of that could be done in Manifold 8 too.)

This just gets the highest single ping per composite ID, without its location:

SELECT

    [Date][Code Space][ID]

    CAST([Date] AS TEXT

        & "_" & CAST([Code Space] AS TEXT

        & "_" & CAST([ID] AS TEXT

        AS [IDbyDate]

    MAX([Signal (dB)]AS [MaxSignal] 

FROM [pinpointdetections]

GROUP BY [Date][Code Space][ID];

tjhb

8,958 post(s)
#27-Jun-19 02:37

This approach is a bit more interesting. It might be closer to what you are aiming at, just as an idea.

It finds the maximum signal per individual (as before), then finds all signals for the individual which are within some dB range of that maximum (the default range is currently 3 dB, if you leave the box blank), then finds a weighted average location for that set of signals (weighting by signal strength) and draws a point.

You can link a drawing from the query.

Now, what I am not sure about (out of my depth), is whether weighting by signal strength should use raw dB values, or log(10) dB values, or something else. The current query weights by raw dB values. I think ideally we would like to weight linearly by signal power instead.

Any ideas?

-- SQL8

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]

;

We could add a circle that shows the spread of locations for the strong signals as well, though this is easier in Manifold 9.

Attachments:
Weighted average signal location.txt

tjhb

8,958 post(s)
#27-Jun-19 04:13

Now, what I am not sure about (out of my depth), is whether weighting by signal strength should use raw dB values, or log(10) dB values, or something else. The current query weights by raw dB values. I think ideally we would like to weight linearly by signal power instead.

To weight linearly by signal power, we first need to reverse the log scaling applied to power level expressed in decibels.

In other words to reverse

dB = 10 * Log10(P)

by using

P = 10 ^ (dB / 10)

So adjusting the query above to draw the weighted average locations using linear signal power, instead of dB:

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 linearly by signal power

    -- (dB log scaling removed)

    NewPointLatLon(

        SUM([B].[Longitude] * Pow(10, [B].[Signal (dB)] / 10))

            / SUM(Pow(10, [B].[Signal (dB)] / 10)),

            -- Longitude weighted

        SUM([B].[Latitude] * Pow(10, [B].[Signal (dB)] / 10))

            / SUM(Pow(10, [B].[Signal (dB)] / 10))

            -- 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]

;

Attachments:
Weighted average signal location b.txt

cycloxslug54 post(s)
#29-Jun-19 00:02

Sorry for the slow response -- was out in the field all day collecting more data for this project!

This is amazing and helpful (and much closer to what I was hoping to accomplish)...I'll play around with it and think about what would be appropriate for weighting.

I GREATLY appreciate the time you took to put this together!

tjhb

8,958 post(s)
#30-Jun-19 03:14

I'm very pleased it made sense to you. Happy to collaborate further. Manifold 9 (or Viewer)?

What are the fish?

cycloxslug54 post(s)
#01-Jul-19 20:17

Ok--so I looked at the output from the queries and for all intents and purposes (relative to the likely accuracy of our GPS locations), both give pretty much the same location--and thankfully a much more interpret-able answer than my previous point clouds and convex hulls as I look at the individuals over time.

When we are receiving the pings we are in a boat in a fairly fast current river--although I do my best to hold station while the tag is being heard (about 5-10 s), we likely are in multiple locations for a single read and I assume the GPS is logging the location after the tag is decoded (for a variety of reasons, tags don't always successfully decode, even when they are loud signal-wise).

The fish are blue and flathead catfish that were tagged as part of a larger study looking at large scale movements (10s-100s of miles) within the river. We are trying to learn a bit about the smaller scale habitat use of these fish, particularly because this island complex seems to consistently hold them relative to the rest of the river.

I'm currently using Manifold 8, but have been considering moving to 9. We have a much larger database from several hundred miles of river so having powerful database tools would really help. My limited experience with Viewer is that the back and forth with 8 is not very seamless. As far as I can tell viewer doesn't like the old image servers and it is a pain to relink images every time I open as I pretty much always need the aerial imagery.

adamw


8,775 post(s)
#08-Jul-19 10:03

If it is not too late, which specific old image servers from 8 don't get migrated well into Viewer?

If you could post an example MAP file, that would have been great.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.