Hypothetical Polygon

**chrisellis250** · 03-17-2020, 05:34 PM

Hi guys,

I'm thinking out loud here, as I'm unsure if this could be done via VBA.

If I had the co-ordinates of a site (either as eastings and northings or lats and longs) could a VBA code then create a hypothetical polygon/triangle?

So for example. Starting from the site it would look at creating a centroid from this point and with a bearing create a shape going out at 1km from the site at this particular angle.

This would then be compared to any nearby sites to see if there would be any cross over in the shapes - imagine looking at a map and drawing circles around locations and seeing where the circles overlap to show a common area etc.

Any ideas/help/guidance is much appreciated

**Andy Pope** · 03-18-2020, 03:33 AM

You can plot circle outlines, or any other shape, using an xyscatter

Could be done with formula rather than VBA.

If you want to do the same with shapes you could. That would require VBA and you would have to handle scaling of distances. i.e. 100pts = 1km

**chrisellis250** · 03-18-2020, 05:36 AM

Hi Andy,

How would you go about doing this? I.e. what data would I require, as I often work with eastings and northings but unsure on whether Lats and Longs would work better etc.

**chrisellis250** · 03-18-2020, 06:32 AM

I suppose essentially I am looking for something like this:
- In Excel spreadsheet there are Pairs of Eastings and Northings that are located within a particular distance of one another (i.e. 1km)
- Each of these eastings and northings will have a bearing (i.e. 90 degrees)
- With that bearing a hypothetical 1km line is drawn from the centre of the location (easting and northing) and then the line is turned into a polygon/semi circle

THEN the same is done with the easting and northing that it is paired with and they are compared to see if the polygon/semi circle "cross over"

How about that?

**MrShorty** · 03-18-2020, 09:24 AM

How much of this question is geometry, how much is generic algorithm development (independent of programming language), and how much is specific to Excel/VBA (you already know how to do this using other tools/programming languages, but need help implementing in Excel/VBA)?

I find that I must first understand the geometry behind a problem like this. In this case, the simplest form of the problem seems to be -- given two points, I can draw circles around those points. Can I, then, find the points where those two circles intersect (if they intersect at all). Which led me to put "find intersection points of 2 circles" into my favorite internet search engine, which brought this page up: https://www.analyzemath.com/CircleEq...ersection.html

Once you can find the intersection points/chord for two circles, then it sounds like your problems expands to three points/three circles, then four points/four circles, and so on. Expanding my internet search to "intersection of multiple circles," I see the algorithm decomposed down to
a) find the intersection points for all circles
b) identify which of these intersection points you are interested in
c) these points are then the vertices of your polygon.

I don't know if any of that helps, or if I have even understood the problem. If I have understood something about the problem, it seems like the heart of the algorithm will be how to find the intersection points for two circles, so I might start by programming that algorithm into my spreadsheet. Then work on expanding it to more circles.

**chrisellis250** · 03-18-2020, 09:29 AM

Hi MrShorty,

I think you are right, but rather than circles it would be based on the "Bearing" or "Azimuth" of each point. So from Point A, if the Bearing/Azumith is 90, then a line would be "drawn" at 90 degrees from that point and then a semi circle/polygon created to complete the shape. Then when comparing this to another point, whether there would be any intersect between Point A and B.

**MrShorty** · 03-18-2020, 09:57 AM

I'm not sure I understand your distinction. It's true that there are two ways to describe circles -- in terms of rectangular coordinates (like my link uses) or polar coordinates (what I think you are describing). If this is the distinction you are making, it seems to me that it is a distinction without a difference. Yes, we can describe our circles either way. We just need to decide which description will be easier to work with. In this case, the rectangular coordinate description seemed easier to work with than a polor coordinate description. Either way will work, we just need to decide how to describe our circles and develop the algorithm from there.