Calculation of new matrix based on static and dynamic coordinates

[Anonymous]

I have a  set of data containing 4000 points of coordinates with name and etc. 

example:
name1      Long1,Lat1
name2      Long2,Lat2
... name4000      Long4000,Lat4000

First, adding +/- 500meter distance for each coordinate above is needed to create a square around the main coordinate shown above. The distance 500meter in coordinate for Latitude and Longitude is calculated to:

Lat500m: 0,004494451   and    Long500m: 0,00712274

The idea which is used at the moment is as:  

if('Alle Litra'[XKoordinat] > Long1-Long500m && 'Alle Litra'[XKoordinat] < Long1+Long500m && 'Alle Litra'[YKoordinat] > Lat1-Lat1500m && 'Alle Litra'[YKoordinat] < Lat1-Lat1500m;"Name1";

There is currently 4000 lines of code just like above with each Long and Lat coordinates with reduction and addition of the Long500m and Lat500m to make a square around the Name1 coordinate. The data around the main coordinate Lat1 and Long1 is all named Name1. This currently says: calculation too complex if the calculation line is above 450 lines. 
I was wondering if there is another way to read the main coordinates(static) from an excel and add/reduce the distance using the precalculated Lat500m long500m to the data and use it to calculate if the dynamic coordinate is within Name1 or Name2 .... etc. 

So the coordinate is compared to the 500m radius of each Name....

challenge: if the new dynamic coord is within name1 and name2, which is displayed? or both are?

Just to make it clear: The static coordinate data comes from an excel and the dynamic coordinate data comes from an SQL source with no relation as to link them. What is the solution here?

Thanks in advance 🙂

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hi @v-frfei-msft 

As requested, I have prepared 2 excel with the data I am using to calculate.

https://1drv.ms/f/s!AgoC085m8lB1gR7yHz4f8Dqup8eo

Data1-AM is the data which +/-500 meters are added to the coordinates to create a square around the coordinate.

Data2 is the SQL data output. It is here that I would add the names in column A in Data1 if the coordinates in Data2 is within the 500 meters. 

NB! These 2 data have no relation. 

Currently I have used manual calculation which is shown in Data1 in J column. This is copied into

Data2 and thus every coordinate is checked against the Data1 created squares.

 sample from Data2 calculation:

Baliser1 = if('Alle Litra'[XKoordinat] > 9,911567375&& 'Alle Litra'[XKoordinat] <9,920556277&& 'Alle Litra'[YKoordinat] >57,03694347&& 'Alle Litra'[YKoordinat] <57,05118895;"Balise: BY120 PU X1 - mod Frederikshavn";
if('Alle Litra'[XKoordinat] > 9,903641692&& 'Alle Litra'[XKoordinat] <9,912630594&& 'Alle Litra'[YKoordinat] >57,04818412&& 'Alle Litra'[YKoordinat] <57,0624296;"Balise: Y120  UÆ- mod Frederikshavn";
if('Alle Litra'[XKoordinat] > 9,908205689&& 'Alle Litra'[XKoordinat] <9,917194591&& 'Alle Litra'[YKoordinat] >57,04040669&& 'Alle Litra'[YKoordinat] <57,05465217;"Balise: BY120 SU Z - mod Frederikshavn";
if('Alle Litra'[XKoordinat] > 9,912981742&& 'Alle Litra'[XKoordinat] <9,921970644&& 'Alle Litra'[YKoordinat] >57,03391376&& 'Alle Litra'[YKoordinat] <57,04815924;"Balise: BY120 PU S4 - mod Randers";
if('Alle Litra'[XKoordinat] > 9,913151824&& 'Alle Litra'[XKoordinat] <9,922140726&& 'Alle Litra'[YKoordinat] >57,0339011&& 'Alle Litra'[YKoordinat] <57,04814658;"Balise: BY120 PU S3 - mod Randers";
if('Alle Litra'[XKoordinat] > 9,907925369&& 'Alle Litra'[XKoordinat] <9,916914271&& 'Alle Litra'[YKoordinat] >57,02115783&& 'Alle Litra'[YKoordinat] <57,03540331;"Balise: BY120 SPSK 06 - mod Randers";
if('Alle Litra'[XKoordinat] > 10,057485139&& 'Alle Litra'[XKoordinat] <10,066474041&& 'Alle Litra'[YKoordinat] >55,37600439&& 'Alle Litra'[YKoordinat] <55,39024987;"Balise: PUE3";
if('Alle Litra'[XKoordinat] > 10,051382059&& 'Alle Litra'[XKoordinat] <10,060370961&& 'Alle Litra'[YKoordinat] >55,37597951&& 'Alle Litra'[YKoordinat] <55,39022499;"Balise: PUG3";
if('Alle Litra'[XKoordinat] > 10,065211289&& 'Alle Litra'[XKoordinat] <10,074200191&& 'Alle Litra'[YKoordinat] >55,3756607&& 'Alle Litra'[YKoordinat] <55,38990618;"Balise: IA(I2521)";

[Anonymous]

Hi @v-frfei-msft 

Did you come to any conclusion?