0

I am considering whether I should add columns to my existing tables or use a new table. The database is about birds, there locations over time and there nesting locations.

The data that I need to store is point (X,Y) location data. This data will come from a GPS, and while two locations may be very close to each other I think it is very unlikely to get the same X,Y twice, even if you think you are standing in the same spot.

First table is called health check (and each health check is related back to a bird, and a bird has many health checks over its life). Each time a health check is done the location and date is recorded.

The second table is called Nest - each nest has a single location. The nests are immutable - ie they don't move.

How will this data be queried?

The health check location of each bird (X,Y) will be (queried) plotted over time to see where a bird has been moving.

The nest locations will be plotted all at once (ie a query to get all nest locations and plot them). Quite often, maybe 50% of the time a birds location during a health check will be the location of the nest from the nest table.

The number of birds involved is about 300. The db is MySQL.

Should the location (X.Y) data go on its own table?

I can provide more information if needed.

2
  • There is a bird_id or something like that? And each (X,Y) is associated with a bird_id. I would not trust {X,Y} to be unique. Unlikely to have dups, yes; but impossible, no.
    – Rick James
    Commented Mar 30, 2022 at 16:05
  • A bird that is sitting in a nest may or may not register the same (x,y) as the nest.
    – Rick James
    Commented Mar 30, 2022 at 16:06

2 Answers 2

0

Will suggest to have all location in one table and have one column of datatype char(1) with values H(health) and L(Location) to differentiate between locations. This will also help yo to add more location types in future.

0

No. I treat GPS locations as just numbers. If you are using a SPATIAL index, then use a POINT(). If not using SPATIAL, then see this for picking a datatype: http://mysql.rjweb.org/doc.php/find_nearest_in_mysql#representation_choices

There is no advantage in attempting to 'normalize' points. Due to the imprecision of GPS, a bird sitting in a nest may or may not register the same (X,Y). All operations must realize this imprecision.

You might need another table that gives the "latest" location for each bird. The "health_check" table gives the history, and the latest entry for each bird is redundant with the one entry in "latest". This will simplify certain operations.

For graphing, I see no problems for a few hundred 'birds' and 'nests'. (Plotting the entire travel history for every bird would take time and lead to a nearly-useless graph.)

For discovering which bird is at a particular nest (or vice versa), it is a few hundred distance computations, hence practical on today's CPUs. (A million birds -- that would be a different matter.)

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.