find if an x and y co-ordinate between another co -ordinate.

dilla1988

New Member
Joined
Sep 4, 2014
Messages
22
hi guys,

So i have about 1000s of lat and long values.

for this set of data I have drawn a boundary wall so i wanna see if these co-ordinates are within that boundary. if yes it's simple "YES" if not "NO"

so the boundaries are:
A -37.921484 ,144.983256
B-37.925141, 145.209269
C-38.035877, 144.980122
D -38.039549, 145.206486

boundary wall looks like:
A B
C D

just few of the co-ords are

-37.6649,145.3983
-37.6584,145.3972
-37.9827,145.1018
-37.9846,145.0923
-37.9849,145.0999
-37.9842,145.0986
-37.7262,144.8926

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

So what's the best way to approach this? I'm getting a bit confused with the negative values.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

First of all, your fence looks rather like:

DB
CA

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

Having noticed that, appropraiate approach could be:
find equations of two close to horizontal pieces of fence DB and CA as y1=a1*x+b1 and y2=a2*x+b2
and equations for vertical pieces DC and BA as x1= c1*y+d1 and x2= c2*y+d2

Then for each point check if y is between y1=a1*x+b1 and y2=a2*x+b2 for given x
and whether
x is between x1= c1*y+d1 and x2= c2*y+d2 for given y

If both conditions are met - (x,y) is within the fence

See sample formulas in http://klubexcela.pl/images/files/xls/Testfence.xls
of course formulas from columns D:F could be combined into one, so in D8 one could write a bit longer direct formula:
Code:
=IF(AND(B8>=C8*$H$5+$I$5,B8<=C8*$H$6+$I$6,C8>=B8*$H$2+$I$2,C8<=B8*$H$3+$I$3),"YES","NO")
Where B8 and C8 are coordinates of tested point, and in H2:I5 are located parameters a...d of fence lines




<tbody>
</tbody>
 
Upvote 0
Consider a point X

If X is inside the quadrilateral, the Angle BAX has to be smaller than BAC
Similarly ACX < ACD, CDX < CDB and DBX < DBA
 
Upvote 0
Hi Mike,
Sure, but it will probably (correct me if I'm wrong) need more computing, at least if we try (as I'd do) to find angles based on the law of cosines.
Code:
Angle = arccos ( (BX^2-BA^2-AX^2) / 2*BA*AX )
and Pythagoras' theorem to calculate the length of the AX BA and BX
Code:
Length = sqrt( (X2-X1)^2 + (Y2-Y1)^2 )
 
Upvote 0
Hi Mike,
Sure, but it will probably (correct me if I'm wrong) need more computing, at least if we try (as I'd do) to find angles based on the law of cosines.
Code:
Angle = arccos ( (BX^2-BA^2-AX^2) / 2*BA*AX )
and Pythagoras' theorem to calculate the length of the AX BA and BX
Code:
Length = sqrt( (X2-X1)^2 + (Y2-Y1)^2 )

I was thinking more of using the inner product to find angles.
Cos(Angle ABC) = (A-B).(C-B)/Len(A-B)/Len(B-C)
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top