Extracting and animating data from the "UFO Video Captured By Homeland Security"

a reply to: WeAre0ne

Noted

a reply to: jaffo

Of course

a reply to: kkrattiger

Yes

a reply to: IsaacKoi

Column AU is complete , It won't be long until I complete Column I.
And where I can send to you the xlsm file ?

originally posted by: FederWBush
Column AU is complete , It won't be long until I complete Column I.

Great.

Things seem to be coming together now.

And where I can send to you the xlsm file ?

Feel free to either email it to me (my username at gmail dot com) or upload it to dropbox and post the link here. Whatever is easier for you.

a reply to: IsaacKoi

Check your Email inbox please.

originally posted by: FederWBush
Check your Email inbox please.

Column AU safely received. Many thanks.

Column AY has also been safely received from CardDown today.

Both columns have been assimilated. Resistance is futile.

Updated spreadsheet uploaded to the same link as before:
prrr.isaackoi.com...


Just to update the position :
Columns A to H : COMPLETED FOR ALL 7027 FRAMES
Column I : High priority – In progress - FederWBush
Columns J to Q : COMPLETED FOR ALL 7027 FRAMES
Column R : Medium priority - Incomplete
Columns S to T : COMPLETED FOR ALL 7027 FRAMES
Columns U to W : Unnecessary / low priority. (Analogue displays)
Columns X to Y : COMPLETED FOR ALL 7027 FRAMES
Column Z : Unnecessary / low priority. (Analogue displays)
Columns AA to AC : COMPLETED FOR ALL 7027 FRAMES
Column AD : High priority - In progress - Excallibacca
Columns AE to AG : COMPLETED FOR ALL 7027 FRAMES
Column AH : High priority - In progress - Raymundoko
Column AI : COMPLETED FOR ALL 7027 FRAMES
Column AJ to AN : Medium priority - Incomplete
Columns AO to AT : COMPLETED FOR ALL 7027 FRAMES
Column AU : COMPLETED FOR ALL 7027 FRAMES (Completed by kkrattiger and FederWBush)
Columns AV to AX : COMPLETED FOR ALL 7027 FRAMES
Column AY : COMPLETED FOR ALL 7027 FRAMES (Completed by CardDown)
Column AX : COMPLETED FOR ALL 7027 FRAMES

a reply to: IsaacKoi

I've got the first 3k done on row AD

a reply to: IsaacKoi

Column H is a mess.
There is inaccurate details in more than 100 frames, and maybe even more I haven't checked it all out yet.
(Frames 3273 ~ 3372 ,Column H - for example)
Right now i'm working on Column I (about 60% complete) , and I'll fix Column H while i'm doing Column I.
So it might take a bit longer to complete them both.

a reply to: Uggielicious
I highly recommend you to watch this video ( 23:01 )

it's pretty much what's WeAre0ne (God bless him) trying explain to you.

originally posted by: IsaacKoi
Challenge 2 : Plotting and animating some of that data (whether using After Effects, Cinem4D or otherwise) on a map or maps (whether sourced from Google Earth or otherwise).

In terms of animating the data in the spreadsheet (when it is just a bit more complete, i.e. hopefully within the next day or two), I've already mentioned above the possibility of converting the GPS data in the spreadsheet to a KML file with the frame numbers or timecode converted into TimeSpan or TimeStamp data to provide time information for an animation.

I've only realised in the last couple of days that there were two versions of Google Earth:
(1) a basic version (which has always been free) and
(2) a "Pro" version with various additional features, such as the ability to create videos animating paths etc, which used to cost a few hundred dollars.

The latter one is now FREE.

I had been using the basic "Google Earth", but have now upgraded (free of charge) to "Google Earth Pro".

You can download Google Earth PRo from the link below:
www.google.co.uk...

The first time you use it, you have to enter your email address and "GEPFREE" as the password.

The extra features (such as to "make complelling offline films to share") and other features that may be relevant to animating the data in the spreadsheet are well worth the extra cost compared to the basic Google Earth version, i.e. NO extra cost.

a reply to: IsaacKoi

Column I is complete.
minor errors has been fixed in Column H.
I'll start Column AL next, but I'll be busy in the next few days.
so it will be delayed a bit.

Btw, I got some background in AE , dont know if I can animate the data...
But I can check it out at least.

IsaacKoi ,Check your mail inbox to get the .xlsm file.

originally posted by: FederWBush
Column I is complete.
minor errors has been fixed in Column H.

Great.

I have incorporated Column I and replaced the data in Column H.

I have uploaded the updated spreadsheet to the usual link:

prrr.isaackoi.com...

I'll start Column AL next,

If you don't mind, could you possibly turn to Column AK instead (i.e. the aircraft's altitude)? I think Column AK (while a bit less complete than Column AL at the moment) is probably more important when it comes to doing a 3D animation.

In fact, I think that once we have Column AK and the ones already being worked on by other people then I'm not sure we need any of the other medium priority columns... Mmm. I'll need to think about that when we get that far.

but I'll be busy in the next few days.
so it will be delayed a bit.

Understood.

Btw, I got some background in AE , dont know if I can animate the data...
But I can check it out at least.

That would be very useful. My current focus is on Google Earth animations and converting the Excel spreadsheet date to the KML format used by Google Earth - but an After Effects alternative would be very interesting.

In a quick analysis of the data , I found out something kinda odd.
The amount of distance he past between Frame 1110 (frame A) to Frame 1222 (Frame B) is: 0.414 km (414m)
Frame A details : 18.31.23N - 067.07.10W (01:22:44)
Frame B details : 18.31.21N - 067.01.24W (01:22:48)
between frame A and B their is 4 seconds.
which means the object flight was 414m in about 4 seconds. (414 / 4 = 103.5m per second)
Just to make sure everyone knows:
The speed of sound is 313m per second or 1234.8 kmh.
Jet fighter (SU-27 Flanker) can reach speed of 2500 kmh or 694.44m per second.
Which means that the object was flying in a third of the speed of sound or in 14.9% of the speed of jet fighter.
Now I don't want to jump any conclusions , and it was the most basic analysis u can do.
Take the distance he went through and convert it to speed. (Basic physics)
But still , I belive the object's speed were around 100m per second / 370 kmh. (There is high chance that i'm wrong)

EDIT : I'll do Column AK what you saying sound logical.

originally posted by: IsaacKoi
That would be very useful. My current focus is on Google Earth animations and converting the Excel spreadsheet date to the KML format used by Google Earth - but an After Effects alternative would be very interesting.

I wrote a function to convert the coordinates from the data to decimal which is used by Google Earth KML. Give the function below a string like "18:30:10N" and it will convert it to 18.5027777777778. Give it "67:05:48W" and it will return -67.0966666666667.

public double ConvertDegreeAngleToDouble(string point)
[
string longlat_pattern = "(d+)

d+)

d+)(w[1])";
Regex longlat_regex = new Regex(longlat_pattern, RegexOptions.Singleline);
if (longlat_regex.IsMatch(point))
[
Match match = longlat_regex.Match(point);
string deg = match.Groups[1].Value;
string min = match.Groups[2].Value;
string sec = match.Groups[3].Value;
string hem = match.Groups[4].Value;
var multiplier = (hem == "S" || hem == "W") ? -1 : 1;
var degrees = Double.Parse(deg);
var minutes = Double.Parse(min) / 60;
var seconds = Double.Parse(sec) / 3600;
double dec = (degrees + minutes + seconds) * multiplier;
return dec;
]
else
[
throw new Exception("Not correct format!" + point);
]
]

I have an app that will load the Excel or CSV file into a DataTable and iterate through each DataRow and convert the coordinates in specific Columns. I can run it once on your data and give it back to you.

-edit- ATS could use a [CODE][/CODE] tag... it garbled the code.

originally posted by: WeAre0ne
I have an app that will load the Excel or CSV file into a DataTable and iterate through each DataRow and convert the coordinates in specific Columns. I can run it once on your data and give it back to you.

Thanks, but isn't the longtitude/latitude conversion from minutes/degrees/seconds to digital format much simpler just using an Excel formula?

To deal with that issue in relation to the aircraft coordinates (i.e. columns AB, AC, AD, AE), I've just inserted a formula in a further column (new column BA) so, for example, in cell BA1 the formula (which automatically adjusts when simply pasted down the column) is:
=(AB1)+(AC1/60)+(AD1/3600)

I've pasted the formula down the new column BA (using the Fill>Down command) and this seems to give the right values for the coordinates in digital format without using any code or going outside Excel (meaning that the values update automatically if I have to make any corrections or additions).

If there are any advantages to using a coding solution instead of the above formula, I don't currently see them - but as (I've previously said) I'm a just lawyer rather than a technical chap.

originally posted by: FederWBush
In a quick analysis of the data , I found out something kinda odd.
The amount of distance he past between Frame 1110 (frame A) to Frame 1222 (Frame B) is: 0.414 km (414m)
Frame A details : 18.31.23N - 067.07.10W (01:22:44)
Frame B details : 18.31.21N - 067.01.24W (01:22:48)
between frame A and B their is 4 seconds.
which means the object flight was 414m in about 4 seconds. (414 / 4 = 103.5m per second)
...
But still , I belive the object's speed were around 100m per second / 370 kmh. (There is high chance that i'm wrong)

Hi FederWBush,

The coordinate values that you quote from Frame 1110 and Frame 1222 are the values in respect of the aircraft's position, so you are looking at the speed of the aircraft rather than the object.

Frame 1110:
prrr.isaackoi.com...

Frame 1222:
prrr.isaackoi.com...

originally posted by: IsaacKoi
AA: States "ACFT" (short for “Aircraft”) throughout the video. This indicates that the numbers (which are co-ordinates) below that word relate to the position of the aircraft.

AB, AC, AD, AE: These numbers, which changes frequently, give the latitude of the aircraft (e.g. 18:30:22N). The latitude indicates a north-south position. The equator is defined as 0 degrees, the North Pole is 90 degrees north, and the South Pole is 90 degrees south. One degree of latitude is 60 nautical miles, 69 statute miles or 111 km. One minute of latitude is 1 nautical mile, 1.15 statute miles, or 1.85 km.

I think things will be clearer from the animations we are working towards.

a reply to: IsaacKoi

It doesn't really matter how you convert the data. What matters is what you do with it when you are done.

1: Your Excel formula doesn't adjust for the hemisphere. It is ignoring "N" and "W". That means your longitude should be negative because it is in the West. So your values are most likely wrong, unless you just change it manually.

2: Doing it in code, I don't have to touch the source data. I can just get the source data, convert it, and directly generate the XML / KML that Google Earth requires. I can also flip a switch, convert it to other formats and variables to generate data 3D Studio Max needs, or After Effects, and generate anything else that might be needed for this, without changing formulas or double the amount of data in the DataTable. Code is much more robust, and in the end, reusable.

originally posted by: WeAre0ne
1: Your Excel formula doesn't adjust for the hemisphere. It is ignoring "N" and "W". That means your longitude should be negative because it is in the West. So your values are most likely wrong, unless you just change it manually.

Er, the hemisphere doesn't change during the video so I don't need to add coding to deal with a change in hemisphere...

I just set the positive or negative value in the formula. Simple.

why is this video being given so much credibility? because of homeland security? lol sheep.


a reply to: IsaacKoi

a reply to: IsaacKoi

That's great. So next time we get another video similar to this, but in a different part of the world, you will be busy manually changing values and creating another spreadsheet with formulas. I will just be loading it in an app and pressing a button, and at the same time automatically generating KML for Google Earth and keyframe paths for 3D Studio Max... lol

Reusability and reproducibility wins every time.