Hierarchy view for rolling up data

Standard

Most Hierarchy code I’ve found relies on having write ability to the source system to write back a hierarchy ancestor table. This is a view to roll up data in a method that is better performing and more flexible than I’ve seen. This should support up to 10 levels of parents. This is designed in an Oracle database, but it should work in any SQL environment. Please make sure there is indexes on the ID columns.

WITH RELATIONSHIP_VALUES AS (
 SELECT [ROW_ID] AS CHILD_ID,
 [ROW_PARENT_ID] AS ANCESTOR_ID
 FROM [DATA_SOURCE]
) SELECT ANCESTORS.CHILD_ID,
 CASE DISTANCE
 WHEN 0 THEN ANCESTOR_ID_0
 WHEN 1 THEN ANCESTOR_ID_1
 WHEN 2 THEN ANCESTOR_ID_2
 WHEN 3 THEN ANCESTOR_ID_3
 WHEN 4 THEN ANCESTOR_ID_4
 WHEN 5 THEN ANCESTOR_ID_5
 WHEN 6 THEN ANCESTOR_ID_6
 WHEN 7 THEN ANCESTOR_ID_7
 WHEN 8 THEN ANCESTOR_ID_8
 WHEN 9 THEN ANCESTOR_ID_9
 WHEN 10 THEN ANCESTOR_ID_10
 END
 AS ANCESTOR_ID,
 DISTANCE,
 CASE WHEN LEAFS.ANCESTOR_ID IS NULL
 THEN 1
 ELSE 0
 END
 AS IS_LEAF
FROM (
 SELECT DISTANCE_0.CHILD_ID,
 CASE WHEN DISTANCE_9.ANCESTOR_ID IS NOT NULL THEN 10
 WHEN DISTANCE_8.ANCESTOR_ID IS NOT NULL THEN 9
 WHEN DISTANCE_7.ANCESTOR_ID IS NOT NULL THEN 8
 WHEN DISTANCE_6.ANCESTOR_ID IS NOT NULL THEN 7
 WHEN DISTANCE_5.ANCESTOR_ID IS NOT NULL THEN 6
 WHEN DISTANCE_4.ANCESTOR_ID IS NOT NULL THEN 5
 WHEN DISTANCE_3.ANCESTOR_ID IS NOT NULL THEN 4
 WHEN DISTANCE_2.ANCESTOR_ID IS NOT NULL THEN 3
 WHEN DISTANCE_1.ANCESTOR_ID IS NOT NULL THEN 2
 WHEN DISTANCE_0.ANCESTOR_ID IS NOT NULL THEN 1
 END
 AS MAX_DISTANCE,
 DISTANCE_0.CHILD_ID AS ANCESTOR_ID_0,
 DISTANCE_0.ANCESTOR_ID AS ANCESTOR_ID_1,
 DISTANCE_1.ANCESTOR_ID AS ANCESTOR_ID_2,
 DISTANCE_2.ANCESTOR_ID AS ANCESTOR_ID_3,
 DISTANCE_3.ANCESTOR_ID AS ANCESTOR_ID_4,
 DISTANCE_4.ANCESTOR_ID AS ANCESTOR_ID_5,
 DISTANCE_5.ANCESTOR_ID AS ANCESTOR_ID_6,
 DISTANCE_6.ANCESTOR_ID AS ANCESTOR_ID_7,
 DISTANCE_7.ANCESTOR_ID AS ANCESTOR_ID_8,
 DISTANCE_8.ANCESTOR_ID AS ANCESTOR_ID_9,
 DISTANCE_9.ANCESTOR_ID AS ANCESTOR_ID_10
 FROM RELATIONSHIP_VALUES DISTANCE_0
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_1
 ON DISTANCE_0.ANCESTOR_ID = DISTANCE_1.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_2
 ON DISTANCE_1.ANCESTOR_ID = DISTANCE_2.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_3
 ON DISTANCE_2.ANCESTOR_ID = DISTANCE_3.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_4
 ON DISTANCE_3.ANCESTOR_ID = DISTANCE_4.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_5
 ON DISTANCE_4.ANCESTOR_ID = DISTANCE_5.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_6
 ON DISTANCE_5.ANCESTOR_ID = DISTANCE_6.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_7
 ON DISTANCE_6.ANCESTOR_ID = DISTANCE_7.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_8
 ON DISTANCE_7.ANCESTOR_ID = DISTANCE_8.CHILD_ID
 LEFT JOIN RELATIONSHIP_VALUES DISTANCE_9
 ON DISTANCE_8.ANCESTOR_ID = DISTANCE_9.CHILD_ID
 ) ANCESTORS
LEFT JOIN (
 SELECT ANCESTOR_ID
 FROM RELATIONSHIP_VALUES
 ) LEAFS
ON
 ANCESTORS.CHILD_ID = LEAFS.ANCESTOR_ID
LEFT JOIN (
 SELECT 0 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 1 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 2 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 3 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 4 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 5 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 6 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 7 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 8 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 9 AS DISTANCE
 FROM DUAL
 UNION
 SELECT 10 AS DISTANCE
 FROM DUAL
 ) LEVEL_DISTANCES
ON
 LEVEL_DISTANCES.DISTANCE <= ANCESTORS.MAX_DISTANCE
ORDER BY 1,3;
Advertisements

Real – Time Planet Tracking System & Trajectory Prediction (Self adjusting pan mechanism [MPU-9250]){RTPT} with Arduino and GPS

Standard

I might have to wire up my telescope with this, using my LinkIt one.

Projects

Introduction

This project aims to make a system that effectively track celestial bodys (such as planets ) with a fair amount of accuracy.We will be using some algorithms along with a processing unit for the calculations and a servo mechanism to show the location of the planet physically!.The hardware used in the project is pretty much basic and simple because the primary focus of this project is on the software that is to make people understand about the algorithms and their implementations.So please bear with my “un-formatted” hardware.

Not just planet tracking you will learn some additional important things that you can implement in your other projects :

  1. Planet tracking using keplers algorithms
  2. Many co-ordinate systems and their interconversion
  3. pan-tilt programming and servo mapping (3.5 turns Servo and 180 degree Servo )
  4. MPU9250 auto-calibration programming
  5. Using Madwicks/Mahony Filter to Stablise Mpu readings.
  6. Yaw correction using P- controller with MPU9250

The…

View original post 201 more words

MKR1000 AP notes

Standard

Intro

I participated in a contest to receive a MKR1000 (read it out loud, MaKeR 1000), and they liked my idea! Woot, more things to play with. I did some investigating and now I’m doing a quick write up regarding mores on the Arduino 101/MKR1000’s soft AP so you don’t have to learn what I have the long way.

WiFi Access Point?!?!

Yes, it shows up as an access point, not a peer-to-peer point. When my phone connected to it, I ran a scan from Fing (https://play.google.com/store/apps/details?id=com.overlook.android.fing&hl=en) to see what I was up against.

Screenshot_2016-03-01-16-34-26.png

As you can see, everything looks good from here. The MKR1000 lives at .1, and any connected device is .100 by default. That is correct, only ONE device can successfully be connected at any given point. I think this is due to some code where the wifi is not fully capable of handling the IP addresses (like a proper DHCP server). I’ve been trying to tap into the DHCP or DNS server on the board, and I haven’t found anything yet, and this makes me think that the board has a hard-coded string to spit out to any device. “This is my SSID and info” and when connecting “this is your connection info” and these do not change, except the SSID. No password support as of this post.

Conclusion

It makes a proper access point! Sweet! Only one device can connect to it at a time though, and that device lives at .100. Understand these restrictions and you’re good to go! I’m still going to be working for a while on trying to redirect any website queries to the MKR1000’s web page for easier admin.

Notes

  • The source for the code I used as framework is: https://www.arduino.cc/en/Tutorial/Wifi101SimpleWebServerWiFi
  • The wifi chip in the MKR1000 and 101 are the same, so code is cross compatible.
  • I still have yet to find a battery library to query charging state or battery level. I love the LinkIt ONE for having that.
  • The power draw of the board itself has been said to be around 20ma. I can confirm it is about that, although with the wifi on, the board warms up. I haven’t done load tests on power usage under wifi.
  • I will have to do range tests with my Pringles Cantenna.

I2C Bus Splitting with a more Professional Touch

Gallery

This is certainly more robust, although not as quick. I think the stability is better in the long run with this than the prior write up with the shift register.

Hackaday

Last week, I covered some of the bitter details of an interesting hack that lets us split up the I²C clock line into multiple outputs with a demultiplexer, effectively giving us “Chip Selects” for devices with the same address.

This week, I figured it’d be best to layout a slightly more practical method for solving the same problem of talking to I²C devices that each have the same address.

I actually had a great collection of comments mention the same family of chips I’m using to tackle this issue, and I’m glad that we’re jumping off the same lead as we explore the design space.

Recalling the Work of Our Predecessors

Before figuring out a clever way of hacking together our own solution, it’s best to see if someone before us has already gone through all of the trouble to solve that problem. In this case–we’re in luck–so much that the…

View original post 288 more words

I2C Hacks: How to Splice Clocks into Chip-Selects

Gallery

An interesting way to get around only having one I2C address. I will have to try this out. Thanks @hackaday!

Hackaday

There comes a time when you need to wire up three, four, or more identical i2c devices to a common microcontroller. Maybe you’re thinking about driving a whopping 32 seven-segment displays with four of those MAX7219CNG 8-way digit drivers, or maybe you have a robot full of joints–each of which needs a BNO055 inertial sensor for angle estimation. (See above.) Crikey! In both of those cases, you’re best bet might be a schnazzy I²C device that can do most of the work for you. The problem? With a single I²C bus, there’s no standard way defined in the protocol for connecting two or more devices with the same address. Shoot! It would’ve been handy to wire up three BNO055 IMUs or four MAX7219CNGs and call it a day. Luckily, there’s a workaround.

We’ve seen some clever tricks in the past for solving this problem. [Marv G‘s] method involves toggling between…

View original post 1,027 more words