Aviation CAD TechNotes

TechNote: MS Excel Drawing Register P-51

June 12, 2015June 17, 2022HughLeave a comment

TechNote: MS Excel Pivot Tables: Drawing Register P-51

The drawing archive collection for the Mustang P-51 includes an NAA Document register in PDF that lists all the Scan Index Numbers, Drawings Number, Aircraft Type and Change (Revision)Number.

To make sense of this large archive; containing thousands of scanned images; it is necessary to first transpose the comprehensive NAA document register into a spreadsheet in order to analyse and filter the data according to requirements.

My requirements are simply to be able to group the data per content; Fuselage, Wings, Equipment etc; and per aircraft type; P-51A, P-51B, P-51C etc.

Further breakdown of data would involve isolating the main assemblies and then parts or sub assemblies belonging to each.

From Adobe Acrobat I extracted the pages of data as spreadsheet tables to which I added a Drawing Description and grouped the data sets together by “Content”…that took a long time to do as the extracted data first had to be checked and then sorted accordingly.

The drawing descriptions came from an index already created by Norman Meyers at Chanute Air Museum, so it was relatively easy to enter this data into my spreadsheet. Its a real pity I had not had access to Normans data earlier; could have saved me a lot of work. My thanks to Norman Meyers.

After sorting the data and inserting descriptions I now have separate worksheets for the content similar to this one.

What I really want now is to identify and organize the drawings belonging to each type of aircraft. For this exercise I use the Pivot Table function in Excel. Pivot Tables are great for organizing and summarizing data according to specific criteria.

Here I have initiated the Pivot Table function and selected the entire data-set of information relating to the Fuselage; as you can see we have a large number of drawings just for this one area!

When working with large data-sets it is good practice to select a new worksheet for inserting this new table.

What we end up with is a new worksheet with the pivot table outline on the left and a selection box on the right. We now select from the latter the columns of data we want…in this case all the main ones plus the P-51D; which will populate the outline table on the left.

Pivot tables by default include a summary row under each entry; I suspect this is more useful for statistics than organizing a document register; which we don’t want.

To remove the summary from the table we just need to select each column in turn using the small arrow as highlighted and turning off this option in “Field Settings” and select “None”.

The final step is to filter the data according to the required criteria; in this case I want all the drawings that have an “X” value in the P-51D column.

This is done by selecting this value from the header drop-down options; which lists by default the unique values in each column from the master table.

We now have a list of all the fuselage drawings and their location in the archive belonging to the P-51D aircraft.

The next step would be to extrapolate all the “assembly” drawings and from there the components that make up each assembly…but that’s for another day.

Pivot Tables are great for this type of job.

846 fuselage drawings for the P-51A
890 fuselage drawings for the P-51B
833 fuselage drawings for the P-51C
923 fuselage drawings for the P-51D
950 fuselage drawings for the P-51H

Many drawings of course are a shared resource for all variants. This drawing register has recently been updated with hyperlinks to all the drawings listed. See this post for details.

For further information on any of these projects please feel free to drop me a line via my contact page or email me at hughtechnotes@gmail.com

Messerschmitt: Bf109 Part

June 11, 2015June 19, 2015Hugh1 Comment

Messerschmitt: Bf109 Part

Scans of the drawings for the Bf109 have long been part of my archive, but I was always reluctant to spend anytime on this aircraft as many others have already done a great deal of work producing CAD data from these designs.

However and occasionally I come across an item that presents a challenge and this part is one of those.

8-10910-2501: Lower Frame mount.

This is a cast aluminium part with a 3 degree draft on all faces (refer second image showing cross section) except the lower edge and the top edge on centre.

The challenging aspect of this part has to do with the top face, where it was important to achieve tangency with the draft faces at each of the mounting holes and maintain this through to the centre portion where the face locally is perpendicular.

The material thickness also increases marginally at the extent of the 2 inward holes.

The front edge of this face has a center portion of a fixed radius; so to achieve the desired results based on the above criteria for the back edge I used a tangential spline. I could have just used a radius arc instead of a spline as the difference was only 0.2mm, but I am keen to get this stuff as accurate as possible.

It worked out rather well.

Autocad Inventor: Splines

June 9, 2015June 19, 2015HughLeave a comment

Spline Technote:

Splines are an absolute necessity when developing the finished profiles from the ordinate points; which occasionally throws up some unexpected results. Invariably at some point we need to manage the curvature of the splines in order to achieve the desired result.

The image shown here is a screenshot from the NAA P-51 fuselage station profiles, which shows clearly the ideal curvature for each station.

This image can be overlayed in CAD to serve as an aid to achieve the correct spline curvature.

Actually manipulating the curvature of a spline needs to be done in a manner that achieves symmetrical results on both sides of the fuselage station profile.

I was working on the tail-end profiles, which were giving me grief as the ordinates points were not sufficient to achieve anything close to the curvature I needed on the lower section.

In Inventor we have constraints for symmetry, which are normally applied when working on a sketch to ensure that changes on one side of a model are reflected exactly in the other.

Using this same technique I activated the spline curvature handles (A&B) on each of the points I wanted to be symmetrical (about center at C) and applied the constraint accordingly to the handles (red).

Now when I adjust one side of the curve the other side automatically reflects the changes.

I should note that the majority of curves generated from the ordinate points are usually very good; requiring very small if any adjustments; so its quite practical to spend some time in the areas where they are not so good.

At some stage the profiles will be lofted as a surface which would then be analysed to verify curvature and alignments.

North American P-51 Mustang: Wings

June 6, 2015March 21, 2021HughLeave a comment

NAA P-51D Mustang: Wing Geometry

Started work today on the wing geometry and ordinates.

This is the last ordinate data-set drawing I have in my P-51 archive and probably the most challenging, thus perhaps the reason why I left it until last!

The quality of the scanned drawing is not that great with much of the data missing or obscured and requiring a fair amount of interpolation to derive the correct values.

The interpolated data is derived through the use of various techniques within Excel, including polynomial curve formulas to determine the values I need from the known data.

So far this has worked out rather well enabling me to make a start on a geometry plan for the wing which will verify the relative dimensions of the Leading Edge, Front Spar and the 25% Chord line.

This drawing is still “work in progress”, which is shown for reference.

The wing ordinates are cross-referenced against 3 different sources to ensure correctness.

Update June 2018:

I have revisited this spreadsheet to include generated excel profiles to check the ordinates and also to derive the XYZ coordinates for input into CAD, centered about the front spar position. The missing and unknown values are now sorted thanks to a new resource…the spreadsheet is complete and verified. See Mustang Ordinates for full details.

P-51D WING ORDINATE

P-51 Wing Profiles

North American P-51 Mustang: Air Scoop

June 4, 2015October 18, 2022HughLeave a comment

North American P-51 Mustang: Air Scoop

Working with ordinates from these archive drawings can be a very time intensive operation. To give some idea of the content of this work I have just started working through the vast amounts of ordinate data for the Air Scoop and Oil Cooler.

2015-06-04_12-23-12 This is a scrap view of the original NAA drawings showing the main ordinates for the Air Scoop.

This drawing shows 2 tables, one of which is the listing for the external contours and the other the internal contours.

The external ordinates comprises a total of 664 points and the internal ordinates comprise a total of 928 ordinate points.

Each point is manually entered into a spreadsheet which lists the Inch dimensions and then converted to Millimeter dimensions. The data has 3 values for the Station location, the Waterline (value along a horizontal axis relative to the ship ctr line at set intervals) and the Buttock line (value along a vertical axis relative to Frame Ref Line ).

These values are then processed using the concatenate function in Excel to extrapolate the required X,Y,Z coordinates.

The points are then grouped and imported into Autocad to derive a point cloud.

The first screenshot is all points combined with the local fuselage contours shown for reference; the second screenshot is the internal point cloud. All these points would then be contoured in Autocad to determine suspect locations and any orphaned points.

The external point cloud had 6 points prominently out of sync with everything else which turned out to be an error in the original data set. This is not uncommon and is usually quickly resolved.

Once I have an initial dataset that satisfies these primary requirements I would then import this data into Inventor or Solidworks for evaluation as a surface in each case.

At this stage, I have spent about 3 days on the data preparation and would expect to spend at least a week to properly evaluate the surface definitions.

It can be very satisfying work when you see for the first time all these data points translated into something tangible as a 3D model depicting the end product first realized all those decades ago.

Update: Decided to pull out all the stops and complete the datasets and point clouds:

North American P-51 Mustang: Fuselage

June 2, 2015July 11, 2015Hugh2 Comments

North American P-51 Mustang: Fuselage

The drawing archive I have contains quite a large selection of legible fuselage frame drawings which I am collating according to the Station reference on the fuselage. I have a spreadsheet that lists all the Mustang drawings including the original drawing number, the scan image number and location within the archive.

Each fuselage frame at each of the designated stations may comprise 3 or more elements, which unfortunately are scattered throughout the many rolls of scans thus requiring some exhaustive work with the spreadsheet data-sets to sort the numbers and folder locations in order to identify and collate the required frames for each assembly per aircraft type.

One such frame was at station 216 which I decided to model; partly due to the fact I was getting fed up looking at and sorting spreadsheet data.

There are several methods to modelling this and whilst I was subject to the vagaries and still limitations of the Inventor product (Solidworks has more options for working with splines) I developed a workflow that obviates some of these limitations and also how the end product is finished.

One way of doing this is to simply create a surface for the main plane and then project a flange line along the edge to create a”folded” surface and then apply thickness but this method gave some unusual iterations in the smoothness of the fillets at the end of the profile. I found that the best way is to create surfaces for all six faces; the splines inside and outside, the top and bottom planes and the ends, then sculpt to create a solid.

I would then go out about creating the notches and cutouts in the solid and then shell the solid to the required thickness. This works very well and ensures the integrity of the original spline ordinate lines (which would have to be split to do this any other way). This method also maintains better continuity of the end fillets and curvature (image 2).

The frame drawings reference the mold line ordinates, which I have for the P-51 B/C Mustang variants.The P-51D is similar with the exception of the ridgeback on the main rear fuselage that has been reduced above the +10″ W.L.

Techy stuff: I mentioned a limitation in the Inventor software which relates to creating a line perpendicular to a spline. In Solidworks you just sketch the line and constrain it perpendicular to the spline, but you cant just do it this way in Inventor (as far as I know). What I did was use sketch construction lines to define the point of intersection with the spline that I wanted the perpendicular line to start from. As I already had a surface projected from the mold frame spline (for above construction) all I had to do was create a new plane perpendicular to this surface at the selected point. It was then quite simple to create a further sketch to define the line I wanted perpendicular to the spline at the correct location.

De Havilland DH82c Tiger Moth

May 31, 2015September 19, 2015HughLeave a comment

De Havilland DH82c Tiger Moth:

This is a selection of parts developed in 3D CAD for the Tiger Moth DH82c (Canadian variant). I had hoped that the original drawings and corresponding data sets would be sufficient to actually develop the entire aircraft, unfortunately I was stumped by the fuselage dimensions which remains incomplete. A few key dimensions were illegible on the drawing copies I had and although I tried to source legible information from various places I was unsuccessful.

I have though interpolated a solution for the rear fuselage based on available information that seems to satisfy the requirements for manufacture and assembly.

The archive drawings suggest that the setting out point between the front fuselage and rear fuselage members is coincident on the centre line. However this would not facilitate a flush connection with the bent plate connector, so I have offset the SOP to the face of the front fuselage upright, which does. Notice too the minor adjustment to the angles.

These setting out dimensions are not verified and the drawing should not be used for any other purpose than for reference. I don’t normally publish stuff without verifying the data but this fuselage has bothered me for a while so I thought it may be prudent to publish what I have in the hope that someone may provide the verifiable data I would need. The model shown is work in progress.

This has proved to be an interesting project and I progressed quite well with the tail and various other elements of the design. I hope to return to this project at some later date as it is a fine aircraft with many examples still flying.

North American P-51 Mustang: NAA Profiles

May 31, 2015July 25, 2016HughLeave a comment

NAA P-51 B/C/D Mustang:

The majority of parts created for aircraft like the P-51 are derived from a library of standard section profiles, not unlike the steel and ship building industries. Fortunately for this project we have an extensive collection of these original NAA standard drawings, numbering 208 in total.

To facilitate the long term goal of recreating the P-51 part drawings as 3D models and associated 2D drawings I figured it may be prudent to first recreate the standard profiles as 2D CAD profiles for this purpose.

So far I have drawn the first 24 profiles exactly as shown on the original drawings but with dimensions in dual format inch & mm…many more to go!

North American P-51 Mustang Parts

May 31, 2015June 19, 2015HughLeave a comment

NAA P-51 B/C/D Mustang:

Ordinates interpretation, translation and development are an important part of my research work and rather essential to the development of the many part and frame components.

It would be great to be able to develop a full parts library for any of these aircraft projects in 3D CAD and recreate the original drawings. However the number of parts in any one aircraft is prohibitive to achieving this goal. I do though occasionally indulge in developing some of the part drawings; in 3D and 2D; if nothing more than a challenge to recreate something that was first designed 75 years ago!

These are some of the parts modelled for the P-51 Mustang:

73-47058 Flange: Oil Tank Filler Neck 73-52144 Fitting: Aileron Control Stick

99-33463 Clip at Wing Station 56.625 99-318116 Link: Cockpit Encl Exit Hatch

99-318134 Bracket: Cockpit Enclosure 106-48343 Bracket: Fuel Valve Support

102-53391 Outlet Assembly: Heat & Vent Cockpit

102-33338 & 102-58181 Landing Gear Fairing Door Strut.

North American P-51 Mustang Project: Ordinates

May 30, 2015March 20, 2021Hugh1 Comment

NAA P-51 B/C/D Mustang

This is one of those projects that gets started and then for some reason gets put on the back-burner until now. One of the key reasons was due to the challenge of obtaining a good surface representation for the lower cowling for the P-51 B/C where I was having problems with the duct intake profile.

As usual I started with listing the ordinates in a spreadsheet and converting these to millimeters and extracting the X,Y.Z coordinates for further work in CAD.

I prefer to work in millimeters as I know that at some stage the end profile may need micro adjustment which is so much easier to do using millimeter units.

This is a scrap view from the NAA drawing showing the ordinates table and the contours.

Thankfully for this part most of the ordinates were quite legible with only a few requiring interpolation; which would be done as a consequence of developing the data in CAD.

As it turns out there were a few orphaned ordinates that for some unknown reason did not align with the CAD developed surfaces; so these were simply ignored instead of trying to invent a purpose for them being there!

The drawing (right) shows the end result of transferring the spreadsheet data into a CAD product. This drawing is simply a record of the translation process and surmises the ordinates in relation to the Thrust line and Fuselage Reference line.

The main body of the cowling did not present any real problems but the intake duct portion did.

This area is less than 3 inches deep and is actually quite a complex surface; so any small deviation from exactness results in some crazy surface deformations. The original data tables are actually extremely good requiring only one small adjustment to get the result I was after…the trick was identifying which ordinates to use for the end profile and which ones we needed to check we have it done right.

After many (about a dozen) test developments of surface development and interrogation of the original data tables to ensure correct translations I eventually determined the correct ordinates and profiles to use to get this right.

The resulting surface is based on the selected original ordinates with only one requiring micro adjust. In most cases the adjustment is simply an error in interpreting the sometimes indistinct values in the original data where a 6 could be an 8 or a 3 could be 5.

The 2 magenta coloured contours are generated profiles from a surface section cut overlayed on the ordinate controlled contours from the NAA tables which provides a check to ensure the surface conforms to the original design.

This surface will be converted to a T-Spline surface to facilitate final development.

It may be that this particular part does not warrant this level of exactness nor indeed the time expended in getting to this point. However it is a testament to the many fine designers of this era to be able to reproduce their work that was done to an extraordinary degree of accuracy.

Update Jan 2021: A comprehensive Ordinate/CAD package incorporating all known ordinate data points is now available for download. See this post for details.

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: