Whereabouts Software, Part 1 - MySQL DB

-
The Whereabouts code is comprised of three parts: the webserver code that fetches the data from the iPhones, the database that stores the information, and the Arduino that represents the server data in physical form in the clock.

This post is on the MySQL database that hosts the location data.  I've attached a SQL file that can be loaded into an empty database.  Some tweaking will need to be done for your specific environment which I will detail below.

whereabouts.sql

The 'clock_location' table is a key/value pair table that represents the physical locations on the clock.  The names in the 'name' field are important and must match exactly (case-sensitive) the values that will be used in the Arduino code for clock locations.  The twelve locations provided are the ones I used but may be modified as needed.

The history table is an empty table that will be populated each time the location fetching code is run.  This will be purged after 30 days by the same code.

The location table is a mapping of location coordinates to common names, IDs, clock locations, and users.  This will need to be populated with your own data before use.  Here's how it should be populated:

  • id - autoincrement table primary key
  • clock_location_id - the ID for the clock position.  What location should be pointed at by this location?
  • name - a user-friendly name for this location.  This is only used by humans, so go nuts.
  • latitude/longitude - the lat/long coordinates for this location
  • user_id - the ID of the user that this location is valid for.  A given location might not be the same for all family members!

The user table maps the users to how location data is fetched.  Here's how it should be populated:

  • id - autoincrement table primary key
  • username - A human-readable name for this user.  This will be used in the fetch_current.php script and the Arduino code.  Make sure that this matches in the Arduino code!
  • location_source - this should be 'GPS' for those with iPhones.  To override a location and 'force' a user to be represented at a given location, put 'clock_<clock_location.id>' in this field.  To have a user use the same location as another user (useful for children) put 'user_<user.id>' in this field.
  • apple_id - When using more than one phone on "Find My iPhone" you will need to get the ID of the device and put it here.
  • hand_id - This was supposed to add more flexibility to the Arduino code, but for now it's not used.

Comments

Post a Comment

Popular posts from this blog

V-22 Osprey Project - Flight Control

-
Image
Now that I have a conceptual idea of how to build a V-22 using leftover mCPX BL parts, how do I go about controlling such an aircraft? I own a Taranis X9D+, and it can control up to 32 channels using dual receivers, so that won’t be a problem. I’m sure that I can create a mixing profile in the transmitter to handle just about anything, but I’m also sure that just like any multi-rotor “drone," this thing will need some form of automatic flight stabilization. I own a few multirotor aircraft and I’m familiar with how they function. Some research into the popular flight control software (Betaflight, iNav, Cleanflight, LIbrepilot, etc.) all came up short. No one has written code for off-the-shelf flight controllers to manage a dual-swashplate aircraft. I would have to build my own. I have not delved into writing code for flight controllers, but I have built a number of Arduino projects, and some of them utilize RC inputs to control servos and motors. It occurred to me that I
Read more

V-22 Osprey Project - Design Changes for v3

-
Image
It's been a busy end of 2021, and as we start 2022 I'm getting closer to building version 3 of the Osprey wing.  The single biggest problem I've faced is the amount of backlash in the direct spur gear drive of the tilt mechanism.  It introduces some nasty instability that, left unchecked, would likely destroy the model in flight. I've investigated other tilt mechanisms, but the one I keep coming back to is the rack and pinion design that Tom Mast uses in his model at Rotormast.  His design uses a high-torque digital servo with a small pinion gear driving a rack attached to the rotor nacelle.  The servo has been modified with an external potentiometer that's driven by a gear on the translation tube to give positional feedback to the servo.  I like this design a lot because 1) It works, and his model is proof, and 2) it requires less torque from the servo and has less backlash. I ordered the pair of modified servos, potentiometer, and rack from Tom's site and star
Read more

V-22 Osprey Project - Tuning and Setup

-
ESC SETUP I used two Castle Creations Talon 60 ESCs for the V-22 Osprey.  These ESCs are high-quality with an electronic governer designed for RC helicopters.  Other ESCs with this feature are recommended.  Using the CastleLink software and USB dongle, I set the following RPM parameters: Throttle Setting 2-blade 3-blade 20% 2600 2000 70% 2600 2200 100% 2800 2400 As with any standard airplane or heli it's important to calibrate the ESC endpoints as well. TILT SERVO SETUP The HiTec D85MG servos are digital high-torque metal-gear servos that are programmable.  Using the HiTec programmer, I set the 1000us endpoint to the forward flight (0 degree) position and the 2000us endpoint to the hover (90 degree) position, with the 1500us center point in the middle (45 degrees).  The programming of the servo is a bit challenging, as the endpoints set in the programmer coordinate roughly to 900us and 2
Read more