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

In relation to the conversion of data in the Excel spreadsheet to a KML file for animation in Google Earth, I've been looking for relevant software or a macro.

There are various tools online, but it looks like the one detailed at the links below is probably the most flexible freely available tool (allowing for reproduction/testing of the relevant results by anyone following the same steps).

Looking at one of the Google Developers' help pages in relation to KML files, the FAQ states that a KML file can be created in various ways:
preview.tinyurl.com...

Do it programmatically yourself, using your own application to parse a CSV file and create KML out of it. For an example, check out Converting CSV files to KML.
Use an online spreadsheet. For instance, the Google Earth Outreach team has created a Spreadsheet Mapper based on Google Spreadsheets.
Use one of the Excel to KML creation tools, such as [link to tek's thread]this one
Use Google Earth Plus or Pro to load the files and then save them as KML or KMZ files

Since Google Earth Pro is now freely available (as I mentioned above), I will look into how (and with what limits) it can "load the files and then save them as KML or KMZ files".

In the meantime, my eye was caught by the slightly frustringly incomplete line "Use one of the Excel to KML creation tools, such as [link to tek's thread]this one".

The relevant "link to tek's thread" was not included...

However, I came across a tool which was - it turns out - created by someone called tek and there is a discussion thread relating to tek's thread on the Google Developers forum.

That tool ("xls2kml") is described here:
freegeographytools.com...

A support thread in relation to that tool (with a link at the end to the latest version I've found) is at:
preview.tinyurl.com...

It appears that Tek made a manual available on a website which is now defunct, but which (fortunately) can be recovered using the Wayback Machine's Internet Archive:
preview.tinyurl.com...

Assuming I can get my head around that tool and make it work with our spreadsheet, it looks like it may be possible to convert much the key data in the spreadsheet to a KML file for animation in Google Earth with just a fairly small bit of cutting, pasting and rearrangement.

a reply to: IsaacKoi

I followed the "do it programmatically yourself" route, since I am a software systems engineer and everything.... If you need help or have questions let me know. I was going to make a tool and share it with you all.

What kind of Google Earth KML were you looking to create? A "FlyBy" that will simulate the camera view of the video? Or just place markers and LineStrings that will draw the paths of the aircraft and hopefully the UFO? Or what?


By the way, did you ever read my U2U? Its outdated now, but just wondering.

originally posted by: WeAre0ne
I followed the "do it programmatically yourself" route, since I am a software systems engineer and everything. If you need help or have questions let me know. I was going to make a tool and share it with you all.

Thanks. I look forward to seeing the results of your work.

What kind of Google Earth KML were you looking to create? A "FlyBy" that will simulate the camera view of the video? Or just place markers and LineStrings that will draw the paths of the aircraft and hopefully the UFO?

As a first step I had in mind something along the lines of the latter (actually I've created one already using a fairly small sample of data) with the former possibly to follow.

Once the data in the spreadsheet is complete, I can think of various animations that would be fun to make - but I'm not sure how easy some of them will be. Merely animating all the placemarks with a Linestring looks like it will be fairly easy.

By the way, did you ever read my U2U. Its outdated now, but just wondering.

I did read it, but I also saw your subsequent comments here in which said you had extracted all the data using OCR.

I replied in this thread a few days ago to your subsequent comment.

I also look forward to seeing the data you had extracted using OCR.

a reply to: IsaacKoi

I only extracted every 35 frames.

originally posted by: WeAre0ne
I only extracted every 35 frames.

Oh.

I must have misunderstood your earlier remark when I suggested scrolling through a few adjoining frames using the PDFs I created and you said:

I don't need to scroll through a PDF, I wrote a program that flips through each frame image and attempts to OCR the data... I've seen the data.

While work on manual entry by a few people (including me) slowed following your post, that manual entry of data was not completely stopped while awaiting sight of the data you mentioned and the significant columns of the spreadsheet should be completed soon.

Hey Isaac is there any more boring & easy stuff to be done?
I will be back late afternoon & I will help if there's something
I can do.

Cheers
Ektar

originally posted by: Ektar
Hey Isaac is there any more boring & easy stuff to be done?

Sure.

I think all the highest priority columns are complete or soon will be. I'm just waiting to hear back from a couple of the volunteers working on 2 of those columns. The last I heard, they were making good progress.

However, there are a few medium priority columns. At least one of those (Column AM) is one that I think would be useful for testing a few issues in relation to the camera system and the data it generates.

Frankly, I'm not sure whether some of the other medium priority columns are ones we really want/need columns - i.e. R, AJ, AL and AN. I'll think about that before anyone does any work on them. If anyone else wants/needs any of those columns, then feel free to say so.

The other remaining columns are analogue displays that I don't think we need (i.e. U to W and Z).

I will be back late afternoon & I will help if there's something I can do.

Great. I'll take you as volunteering to complete Column AM.

Just let me know if you have any problems.

You have probably seen the basic instructions I posted above:

Basically:
(1) Download the current (incomplete) Excel spreadsheet:
prrr.isaackoi.com...

(2) Pick an incomplete column and post here that you will complete that column. The columns that remain to be done or are currently incomplete are I, R, U, V, W, Z, AD, AH, AJ, AK, AL, AM, AN, AU and AY.

(3) Scroll through the frames and fill in the values. Using the PDF versions of the frames in Abobe Reader is probably the easiest way to quickly scroll through the frames. The values do not change in every frame so you can generally cut and paste (or use the Excel "fill" menu and the down option) to cut and paste a relevant value down a column until the frame where the value changes. The PDF versions of the frames are in two files, at:
prrr.isaackoi.com...
prrr.isaackoi.com...

I should warn you, having done quite a few of the columns myself already, that it is pretty boring work.

I think we are very nearly done with all the high priority columns (and also with some/most of the more useful medium priority columns).

Here is an update:

Columns A to H : COMPLETED FOR ALL 7027 FRAMES
Column I : COMPLETED FOR ALL 7027 FRAMES (Completed by FederWBush)
Columns J to Q : COMPLETED FOR ALL 7027 FRAMES
Column R : Medium (low?) 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 : Medium (low?) priority - Incomplete
Column AK : Medium priority - In progress - FederWBush
Column AL : Medium (low?) priority - Incomplete
Column AM :Medium priority - In progress - Ektar
Column AN : Medium (low?) priority - Incomplete
Column AJ : Medium (low?) 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

So, I wanted to work on this a bit while at work but didn't bring the files with me. I'll knock out another few thousand tonight and hopefully will be done tomorrow night. What's your e-mail?

Edit: Nevermind, I just saw your U2U. They should really make it easier for me to know I have a message. I'll mail out the first batch in around 2 hours when I'm off work.

originally posted by: Excallibacca
I'll knock out another few thousand tonight and hopefully will be done tomorrow night. ... I'll mail out the first batch in around 2 hours when I'm off work.

Great.

It's nearly night time here in England now, but tomorrow afternoon/evening I'll be able to use the first batch to test some conversions to KML and animations in Google Earth - so that when I get the full columns we can hit the ground running.

E-mail is sent.

Progress:
Column AK is 50% complete.
I'll do the rest the day after tomorrow.

originally posted by: IsaacKoi
While work on manual entry by a few people (including me) slowed following your post, that manual entry of data was not completely stopped while awaiting sight of the data you mentioned and the significant columns of the spreadsheet should be completed soon.

Sorry about that! Since you said that, I developed another technique / program that allowed me to manually get the values much faster than OCR. Here I completed column AD and AH in about an hour. So here is the complete ACFT coordinates:

files.abovetopsecret.com...

Right-click the link above and 'save link as' and rename .png to .txt or .csv!! (sneaky me)

Using the completed data above, here is what the entire flight path of the aircraft plotted in Google Earth looks like:





...they basically just circled around the airport and flew away.

Below I added the complete "target" points to Google Earth. Some points seems to be off, probably data entry errors.



You can see that the aircraft basically circled around and kept the camera pointed around the airfield. Meaning the UFO was just hanging out around the airfield as well, and not speeding to some destination like the video creates the illusion of. I've seen balloons travel further distances than this... and in a shorter amount of time.

This flight path data matches pretty close to the data in some of the reports and earlier investigations.

originally posted by: WeAre0ne
Sorry about that!

No big deal.

Since you said that, I developed another technique / program that allowed me to manually get the values much faster than OCR. Here I completed column AD and AH in about an hour.

Excellent! I've incorporated those columns.

I've also changed/corrected a small number of minor inconsistencies in the spreadsheet. (Basically, there are frames where values change and different people can, and have, formed different views as to use the previous number or the new number on the changeover frames. This is only an issue where numbers changed in TWO columns but different people did those different columns and formed different views in relation to their respective columns),.

The revised and updated spreadsheet is at the usual link:
prrr.isaackoi.com...

@WeAreOne : Could you briefly explain the "technique / program" you mention you developed. Apart from usefulness in relation to the remaining columns (some of which are unlikely to be essential), I'd be interested to learn any faster techniques since they potentially could be applied to other videos.

Thanks again to you and the others that have contributed to the spreadsheet so far.

I'll post more about animation possibilities after work today.

a reply to: IsaacKoi

I'd be interested to see how his method compared to our manual entries and how accurate it is. It is good we did some ourselves so we can compare and actually tell whether or not the new method works.

I'd also like to know how a computer program could decide what to make of the vagueness of number changes.

a reply to: IsaacKoi

Hi Isaac sorry it took me a bit to get started...I had to figure out
how to open the spreed sheet... Libre kept trying to open in the
wrong category.

I'm up to Frame 693...I will be back later this afternoon to continue.

Cheers
Ektar

originally posted by: Ektar
I'm up to Frame 693...I will be back later this afternoon to continue.
Ektar

Cheers Ektar. Don't bust a gut at the moment, since WeAreOne has posted above that he has "developed another technique / program" that allowed him to "manually get the values" to complete two columns in "about an hour".

I've asked WeAreOne for a brief explanation of that "technique / program" since we may be able to apply it to the column you are working on (and possibly a few of the low priority columns as well, for the sake of completeness). Anything that makes life easier is a good thing in my book.

That looks like a normal takeoff. We need Zaphod in here.

I have about 4200 frames done so we have a good sample to pull from if we want to check it.

a reply to: Excallibacca