Hello Experts, I need some help in getting the distance 1. between below mentioned Lat and Long, 2. then find the value which has greater than 70% and return output which is within 3 miles and have value less than 60% with respect to the first column. eg., check the value in Red(92.2), and it will jump to the value in Green(8.66) and get the result as 03RW316-1 since the value is below 60% and within 3 miles of radius.( just an example it moght not be exact within 3 miles.) SiteLatitudeLongitude1900Expected result03RW201-138.70041666-93.2705555512.2403RW201-103RW201-238.70041666-93.2705555522.3103RW201-203RW201-338.70041666-93.2705555525.1103RW201-303RW203-138.74625-93.223555558.3703RW203-103RW203-238.74625-93.223555556.2303RW203-203RW300-139.751-94.840592.2203RW316-103RW300-239.751-94.840571.5603RW300-203RW300-339.751-94.840564.0203RW300-303RW301-139.7794-94.793141.0103RW301-103RW301-239.7794-94.79315.3203RW301-203RW301-339.7794-94.793128.0803RW301-303RW302-139.85939444-94.8169277715.5403RW302-103RW302-239.85939444-94.816927775.6403RW302-203RW302-339.85939444-94.8169277714.9703RW302-303RW312-140.37565555-94.857416664.0603RW312-103RW312-240.37565555-94.857416662.8303RW312-203RW312-340.37565555-94.857416664.1703RW312-303RW313-139.73430555-94.7900525.0403RW313-103RW313-239.73430555-94.7900545.2903RW313-203RW313-339.73430555-94.7900534.9403RW313-303RW316-139.75501666-94.281941668.6603RW316-103RW316-239.75501666-94.2819416634.9203RW316-203RW316-339.75501666-94.281941666.2903RW316-303RW317-139.70467777-94.8866055527.7203RW317-103RW317-239.70467777-94.8866055528.7103RW317-203RW317-339.70467777-94.8866055529.8503RW317-303RW318-139.55928888-95.0329694419.3203RW318-103RW318-239.55928888-95.032969446.4603RW318-203RW318-339.55928888-95.032969446.3103RW318-3 Please Advice.

Find distance between 2 column and get result which has lesser value from the third Column

smfhsan2005

Hello Experts, I need some help in getting the distance

1. between below mentioned Lat and Long,

2. then find the value which has greater than 70% and return output which is within 3 miles and have value less than 60% with respect to the first column.

eg., check the value in Red(92.2), and it will jump to the value in Green(8.66) and get the result as 03RW316-1 since the value is below 60% and within 3 miles of radius.( just an example it moght not be exact within 3 miles.)

Site	Latitude	Longitude	1900	Expected result
03RW201-1	38.70041666	-93.27055555	12.24	03RW201-1
03RW201-2	38.70041666	-93.27055555	22.31	03RW201-2
03RW201-3	38.70041666	-93.27055555	25.11	03RW201-3
03RW203-1	38.74625	-93.22355555	8.37	03RW203-1
03RW203-2	38.74625	-93.22355555	6.23	03RW203-2
03RW300-1	39.751	-94.8405	92.22	03RW316-1
03RW300-2	39.751	-94.8405	71.56	03RW300-2
03RW300-3	39.751	-94.8405	64.02	03RW300-3
03RW301-1	39.7794	-94.7931	41.01	03RW301-1
03RW301-2	39.7794	-94.7931	5.32	03RW301-2
03RW301-3	39.7794	-94.7931	28.08	03RW301-3
03RW302-1	39.85939444	-94.81692777	15.54	03RW302-1
03RW302-2	39.85939444	-94.81692777	5.64	03RW302-2
03RW302-3	39.85939444	-94.81692777	14.97	03RW302-3
03RW312-1	40.37565555	-94.85741666	4.06	03RW312-1
03RW312-2	40.37565555	-94.85741666	2.83	03RW312-2
03RW312-3	40.37565555	-94.85741666	4.17	03RW312-3
03RW313-1	39.73430555	-94.79005	25.04	03RW313-1
03RW313-2	39.73430555	-94.79005	45.29	03RW313-2
03RW313-3	39.73430555	-94.79005	34.94	03RW313-3
03RW316-1	39.75501666	-94.28194166	8.66	03RW316-1
03RW316-2	39.75501666	-94.28194166	34.92	03RW316-2
03RW316-3	39.75501666	-94.28194166	6.29	03RW316-3
03RW317-1	39.70467777	-94.88660555	27.72	03RW317-1
03RW317-2	39.70467777	-94.88660555	28.71	03RW317-2
03RW317-3	39.70467777	-94.88660555	29.85	03RW317-3
03RW318-1	39.55928888	-95.03296944	19.32	03RW318-1
03RW318-2	39.55928888	-95.03296944	6.46	03RW318-2
03RW318-3	39.55928888	-95.03296944	6.31	03RW318-3

Please Advice.

Developer Tools

Accepted answers

jarrod

Gotcha, see the attached workflow for the flow as i understand it along with some notes.

Find Distance.yxmd

jarrod

no problem, See attached.

I had to add a formula tool to make an expected result field for the records that were within the acceptable threshold. Then for the records that need a different site, we have to rename the "source_site" to "Expected result". Note, the original was taking the expected result from the source_expected result when it should have been Source_site anyway. so this actually fixed 2 issues.

Find Distance.yxmd

All comments

jarrod

I don't understand what you mean by "value less than 60%" - less than what? The 1900 column is confusing as well - what is that value? I see you have the expected result in Red is the only one that differs from the Site field. Can you provide some more insight on these figures?

Thanks!

smfhsan2005

Hi Jarrod,

1900 column is in percentage: if you see first value of 1900 column is 12.24(which is less than 70%) so the result should be exactly like Row 2 which is 03RW201-1 .then if we move down continuously like Row 1, then coming to Row 7: Column 1900 is 92.22(greater than 70%)so the example Expected result is the site name other than the Row 7 Site name which is 03RW316-1 instead of 03RW300-1 since the value of this Row is greater than 70% and jump to Row 22 which has 1900 column value 8.66 (less than 60%)...the expected result should have the value less than 60% within 3 miles of site column.

Please let me know it it helps.

Basically Expected result should have multiple conditions, one is replace site and value>70 with lesser than 60% within 3 miles

cgoodman

Yes it would be helpful to have some more information as the distance between the two points you mention is about 30 miles, so I don't follow the specific need.

N.B. I was able to get the distance between the points by creating a centriod using the longitute and latitude columns provided and then joined Expected Site with Site which gives two centroids for one record which enables you to use the distance tool.

Spatial Solution.PNG

jarrod

Gotcha, see the attached workflow for the flow as i understand it along with some notes.

Find Distance.yxmd

jarrod

just saw your notes on miles being dynamic - that can be accomplished by bringing in a field and just referencing the field instead of the hard-coded mileage. adding a secondary limit is the same thing, just add another process to look for a larger distance, but what you'd probably want to do is just look for the largest mileage acceptable then sort by Distance before the unique to find the closest site. the workflow may need some tweaking for your specific purpose.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703