Excel data to make circles in 2Dplane is this possible?

[Steve1]

Hi,
about to be V7

when so, I have someone asking me if I can do a job where his previous cartographer used to be given an excel sheet of data and feed it into some CAD prog and it created circles in a 2D plane, which is the landscape., circles being trees.
Then I have to add colour (awaiting artwork to see whats what), not sure if RGB sufficient, hope so as Rhino doesnt do cmyk. I dont use Illustrator. I do have Affinity Designer but never used it. I could use Freehand if it runs on Win10, dont think so.
I could export into Photoshop, a vector eps file but not sure if V7 can do that. V5 cant. Perhaps export as.ai however the artwork ends up in a strange place due to 0,0 location. In Freehand I would draw a box around the artwork then group it and export the box. Repeat that for each different Pshop layer I needed, all registered perfectly.

I will find out in due course but for the moment I wonder if the excel to circles thing is possible ?

Regards

Steve

[flokart]

I would use grasshopper…and yes it should be possible.
You could just prepare a test file and see what kind of solutions poeple came up with.
Maybe you could get the data also as an xls format

[Steve1]

Hi,
I have zero experience with grasshopper, and he wants it early week and the win10 rebuild is now monday !
Grasshopper is on the to do list, but that list is three pages long, .
Can it be done in just V7 ?

Even the to do list for the rebuild has 150 progs to be installed, settings exported, or print-screened, upgrades acquired, set up win 10 as XP/win7 interface etc. many unforeseen hurdles ahead, three days sorting out passwords for emails that did work then the providers have messed with them, sank my friday rebuild and one that wont work so at to get my hands on Msoft Office, and my email prog, one that should have worked, may not even work monday, then I am sunk, hey ho expecting mayhem in fact ! Just what have Msoft broken when it worked in win7 ! As the first step, they have broken my password that was correct for Msoft Office.

Cheers

Steve

[flokart]

Ok this sounds stressfull and to many unknowns are involved to give a clear answer.

Best option to get a vision if its possible or not is prepared file what do you think you will get from the Client and upload it in this forum.

And dont be scared from grasshopper if someone made a prebuild solution for you.

I guess its also possible in R7 but i am not sure without scripting.

[Steve1]

Hi,
as asked…attached is such an excel sheet as gets used. also a dwg that I am told provides the contours and I see its got these circles plotted to it as an end result.
0217(35) - Tree Report.DWG (1.2 MB)
tree data Copy.zip (44.8 KB)
not sure how the excel gets mapped to the contours.

I asked where do contours come from, got told the dwg file.

HTH

Steve

[Helvetosaur]

That Excel file is going to be a bit difficult to extract data from without resorting to some scripting. In looking at the DWG you posted, it looks like the XY coordinates are exactly as in columns B and C, and the tree/circle radius is as in column L. Rhino can’t read Excel files directly, but if you can export a .csv from it, it would be relatively easy to script a custom importer that reads only those columns and makes the circles.

For example, the script below will import all the tree circles from a .csv exported from your supplied excel file above. They correspond to the circles in the DWG file above*.

*you first need to import the DWG into a Rhino file in mm, then change the units to meters (yes to the scale question). Then run the script on the .csv file. The problem is that the units in the dwg file are in mm (incorrect I think), but the Excel file data is in meters.

TestImportTreeCircles.py (910 Bytes)

[Steve1]

Hi,
I have had some feedback from the ‘client’
Does this help or hinder in seeing whats involved. What exactly is involved.

The DWG files for CAD have the site boundary and topographical lines as layers.
The coordinates for tree placement are in the Excel file Columns B & C
Tree tag reference number is column A
For tree canopy mapping the data is made up of north south east and west measurements so that makes the extent of what may be a circle or an ellipse - its fitting a shape to the extent of the four points to make it look tree like. Columns H, I , J , K of the excel spreadsheet

For Root protection area mapping the radius is Column L so from that radius a diameter is produced making a “perfect circle” as per the pdfs.

The canopy data and the RPA data are both to be put into the dwg as separate layers and then the PDFs produced of the final products as per copies sent to you.

To give you an idea 100 - 300 trees can be mapped in a few hours or less so there must be a very easy way to import the data sets into CAD so its not a case of doing them individually by hand. Unfortunately I dont know how but I need to try to find someone who can take on these services and provide what Im looking for quickly and cheaply so going forwards for us I just have to prepare the data and send it out with a turnaround of 1 - 5 days time ideally.

Note generally some coordinates are off and need corrected from a first draft that i have to proof check. Its easy for a handful of trees but not so easy for more.

The pdf there are two one is canopy another is RPA

here are screen comparisons to rhino.
The dwg created various tabs as well in rhino.

I am wondering thus is this within my grasp, can I commit to doing these (boring as they may be).
what is involved to get that excel into this pdf set.

Steve

[flokart]

@Steve1

The DWG files for CAD have the site boundary and topographical lines as layers.
The coordinates for tree placement are in the Excel file Columns B & C
Tree tag reference number is column A
For tree canopy mapping the data is made up of north south east and west measurements so that makes the extent of what may be a circle or an ellipse - its fitting a shape to the extent of the four points to make it look tree like. Columns H, I , J , K of the excel spreadsheet

This works already but the curve iterpolation from H I J K looks a bit different is this a problem ?.
I dont know which method they used.

And you should post that thread with a grasshopper tag.

[Helvetosaur]

@Steve1

To give you an idea 100 - 300 trees can be mapped in a few hours or less so there must be a very easy way to import the data sets into CAD so its not a case of doing them individually by hand.

From the Excel file (converted to .csv) you can import as much of the data as you want via script. The above example was just for the tree circles. Importing takes seconds.

It is also possible to import the crown spread data, however, the “ellipses” are not really either ellipses or free-form interpolated curves - they are made from a series of 4 constant radius circles that overlap trimmed to each other (i.e. 4 arcs). The Quad points correspond with the crown spread data N,S,E,W from the tree center, but how the radii are generated I don’t know.

It’s possible to import any other data as well, create layers, colors etc. But it’s some work, and you would need to know EXACTLY what it is you are trying to achieve.

[Steve1]

Hi,
@Helvetosaur

As I get my head around this.

Q1. I entered 000000 000000 into grid Reference finder and ended up in the ocean west of the Isles of Scilly, (beyond the toe of England !) The example plot is in Scotland.

As such taking row 211 as you did and tree tag 5609 (tree with tag nailed to it) its grid ref is 261627 687106 This is eastings then northings, columns B and C

If I am with units =metres, would that see me have to scroll for ages heading north and east to get to the tree in Rhino if our origin is 0,0 ? i.e. scroll 261627 metres up page etc !

Would I need to establish the origin value lower left of the DWG map rectangle that is supplied for land features and contours for the excel data report , then have script that deducts those values from the B and C columns and plots what values remain thus the trees end up on my imported dwg ?

Q2.

The RPA is circles. Easy. Rhino can make a circle at a given location of a given diameter.

As I see it, as the distances columns H I J K all differ, the canopy is made of four ‘quartered’ elipses, each one denoted by tree centre (B/C) and two points, one the length of the elipse and the other the width. All four elipses get trimmed and joined.
or if there was a command ‘DrawQuarterElipse’ then run that on each of the three points, to get the coloured quarter elipses in image and attached rhino file.

Result with normal elipse command is not an elipse but 4 elipses of differing size with common tangential joints at the four points.

Lets take an example where NSEW all differ.

Tag 5622.

The code would perhaps do this:-

Create circle of dia 0.1metre at location from Eastings Northings data columns B and C

Use Layer named ‘Centre’ for result.

Elipse1 centre BC, length column H 3 and width column K 2

Elipse2 centre BC, length column H 3 and width column J 1.5

Elipse3 centre BC, length column I 3.5 and width column K 2

Elipse4 centre BC, length column I 3.5 and width column J 1.5

Split elipse1 at H and K, delete longer line length of result.

Split elipse2 at H and J, delete longer line length of result.

Split elipse3 at I and K, delete longer line length of result.

Split elipse4 at I and J, delete longer line length of result.

Join the now elipse segments.

Place result on layer name referring to data in column M, so Layer name C2 would receive this result.

Layer named C2 is Dark Grey, B2 is Green, A is Dark Blue U is red.

See attached the sequence.
Tree canopy shape from excel N S E W cells.3dm (70.5 KB)

Trees data sheet Rhino3D project 30aug2022 rev1.zip (47.6 KB)

The dwg file is as posted earlier.

So can this be scripted as I have zero ability in such. Can someone kindly script this for me pleeeeese ‘praying emojee’ (if we had one).
if one can draw a quarter elipse that would bake scripting easier, no splits etc to do.
To run through all rows in the csv file.

Steve

[flokart]

Hi @Steve1 ,

Its not a prbolem to script this but as @Helvetosaur mentioned these are not curves from 4 ellipses.
Try it by youreselfe to replicate one curve from the dwg file with 4 ellipses its not the same geometry.

[jeremy5 (Jeremy)]

@Helvetosaur

they are made from a series of 4 constant radius circles that overlap trimmed to each other (i.e. 4 arcs).

Just to muddy the water, this semi-randomly selected one turns out to be 8 arcs:

@Steve1 Given there is inconsistency in the existing data, if I were doing this I would draw a couple of examples (Mitch’s and mine say) using the simplest method available (I’d use InterpCrv). I’d copy and paste in the corresponding originals alongside them, then ask the client whether the difference matters, pointing out that the new way looks better (more organic!) and is more efficient. Further, to reproduce the original method you would need the client to provide details of how the original supplier constructed the curves and how they chose the number of arcs to use in each case.

Regards
Jeremy

[Helvetosaur]

Another way to make a “cleaner” representation of the data - if you are wanting to be more purist/abstract - would be to get the world (NS-EW) bounding box of the 4 extents points and create an inscribed ellipse inside that. Here is a comparison of the original 8 arc approximation, the quarter ellipse approximation and the single ellipse approximation. None of them is “correct” because this is a tree we’re talking about here, and trees do not have regular forms such as this (except in some cases when humans trim them).

Note also: the use of ellipses may not be a good idea in any case - my feeling is that there are age-old programs which use this data and who do not understand anything other than lines and arcs. Therefore handing them something with spline data may not work.

[jeremy5 (Jeremy)]

@Helvetosaur

would be to get the world (NS-EW) bounding box of the 4 extents points and create an inscribed ellipse inside

Hi Mitch, I think that makes a lot of sense as it’s unlikely the widest points of the canopy line up with the trunk so a median position is probably the best compromise - and in any event however this is drawn it is going to be a rough approximation.

It’s a shame the Google satellite imagery of Fintry is so poor the true canopies cannot be derived from it.

@Helvetosaur

there are age-old programs which use this data

Given the goal is to produce PDFs, hopefully this wouldn’t be an issue.

[Helvetosaur]

@jeremy5

Given the goal is to produce PDFs, hopefully this wouldn’t be an issue.

Yep - it is more likely that the original arc-based data was generated by an old program. It would be interesting to have the algorithm used to do so - if they were not somehow individually hand-drawn. IIRC, in ancient prehistoric versions of ACAD before it could really handle splines, the “ellipse” objects were in fact these 4 tangent arc approximations.

[Steve1]

Hi Guys,
@jeremy5
@Helvetosaur
@flokart
I will accept whatever you can do, whichever is easiest to be coded, beggars cant be choosers, I think my client will be happy for anything. He is awaiting something to see if its doable., and I am well overdue now.

Could some kind clever soul please code this so we get tree canopies from that excel sheet.
Hopefully to run in V5 as have had to revert to win7 . (note A)

Then they get placed onto the appropriate layer, so column M sends the canopy to layer C2 or B2 etc etc.
I will colour that lalyer and thus they take on the grey red or green according to the BS cat.

Thats it, we have a map
oh , and how does the easting northing B/C get to work with the dwg, as asked, as 0,0 is off the scilly isles, if thats a Rhino 0,0 then I will have a lot of scrolling to do !

and A has a TAG number and D a tree type, to be text placed at each tree.

Steve

note A
back to win 7 v5 whilst acquiring hardware for a win10 rebuild, probably happening October now. Bank wont give me the credit limit I had on a card I closed down 3 weeks ago so as to allow their ‘robot’ to not misunderstand my intentions, as two cards same bank of same type I understood was disallowed, yet my financial circumstances havent changed so why halve my limit I dont know…

[jeremy5 (Jeremy)]

@Steve1

how does the easting northing B/C get to work with the dwg

The Easting and Northing in the spreadsheet are the X and Y co-ordinates in metres on the Ordnance Survey grid. The DWG file has been set up so its X & Y co-ordinates are the OS ones - except that the drawing units are millimetres (!!!)

So to go from spreadsheet to drawing you could multiply the values by 1000. However given that Rhinos don’t like moving too far from the origin, you would be better served by first changing the drawing units from mm to metres (and saying Yes to scale by 0.001). Then all the spreadsheet numbers can go straight in without adjustment.

[Steve1]

Hi Jeremy,
Sounds Good, Helvetosaur also suggested altering units to metres and yes to scale prompt.

So now if someone please could do a code and see if we get trees onto that dwg please.

Cheers

Steve

[jeremy5 (Jeremy)]

Hi Steve,

The elephant in the room here is that you are aiming to do this for a client, so you will be getting paid. However, rather than develop some code to automate the process yourself, you want to subcontract the development to someone else. The normal arrangement in this situation is that the subcontractor gets paid for their work because it saves you time/makes your contract with your client viable.

What are you prepared to pay?

Cheers
Jeremy

[Helvetosaur]

Creating a script to read in the data is not all that hard, but the main work is in the bookkeeping - getting the items to the proper layers/colors, setting up the text items (height/stacking) so that their placement is near but not on the tree center, etc. So there is some time and work involved in making the magic happen.

Note also that the example DWG you supplied has several different layouts (paper space) with title blocks, etc. in addition to the model space geometry. I don’t know if those figure into your needs.