topic Re: Problem with XY to Line in Data Management Questions

Problem with XY to Line

BryanMaxwell — Sun, 26 Jan 2014 16:43:28 GMT

I need to calculate distances between 2 different sets of lat/lon coordinates (one representing zip code centroids, the other representing hospitals). I created a small test file with 24 records and am trying to use the "XY to Line" tool to get the distances. For some reason the lines that it outputs are nonsensical.   Clearly I'm doing something wrong with GCS and projection, but I don't know what.

I created the zip codes centroids points from Tiger Line polygons, which use GCS North American 1983.   I created the hospital points by geocoding addresses, and they seem to display perfectly when I set them to the same GCS.

Can anyone tell me what I'm doing wrong? Maybe I need to get out of that GCS and into a projected system right off the bat??

Or is there any easier way to get these distances?   I tried creating a Point Distance table but had other problems with that, and that is a less direct solution anyway because I would then have to somehow lookup values in that table to get distances for my already paired sets of coordinates.

Re: Problem with XY to Line

BryanMaxwell — Sun, 26 Jan 2014 17:22:28 GMT

Sorry, had problems attaching files in original post. Here they are.

I've attached the hospitals, the zip codes, and the small test file of the pairings. Eventually I need to calculate the distances for 17 million pairings (each pairing represents an individual patient's zip code of residence, and the hospital they went to).

Thanks in advance!!!

Re: Problem with XY to Line

JimCousins — Mon, 27 Jan 2014 13:25:17 GMT

I had to rename worksheet to remove spaces, but Get the following as output. I input all values as GCS NAD83. It all looks reasonable.
[ATTACH=CONFIG]30886[/ATTACH]
Regards,
Jim

Re: Problem with XY to Line

RobertBorchert — Mon, 27 Jan 2014 14:04:49 GMT

I would suggest simply using mathematics.

a² + b² = c²

From your excel table.

latitude_stca and fac will = a
longtude_atca and fac = b
Distance between the points is c

Question. Regarding unique id you have numbered as 1 is it about 2.886377129 kilometers (or 9469 feet, or 1mi 1396.6yd) away from the centroid? Assuming your lat and long are in meters.

IF that is so you can populate your Excel table cell H2 with

=(((ABS(C2 - F2) *(10000/90))^2) + ((ABS(D2 - G2) *(10000/90))^2))^(1/2)

and drag it down to calculate all the cells.

The ^ symbol in Excel performs a square function.
The 10000/90 is converting decimal degrees to kilometers
The ^(1/2) performs the square root function.

this is what I came up with.

uniqueptzip latitude_ztcalongitude_ztca fac_id latitude_fac longitude_f KM Miles
1 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
2 94709 37.8792337 -122.2668389 10735 37.76337 -122.25433 12.94855421 8.045858567
3 94601 37.7755447 -122.2187049 10735 37.76337 -122.25433 4.183109881 2.599263973
4 94608 37.836817 -122.2895695 10735 37.76337 -122.25433 9.051487954 5.62433386
5 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
6 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
7 94579 37.6864911 -122.157201 10735 37.76337 -122.25433 13.76361634 8.552314689
8 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
9 94610 37.8114061 -122.2424576 10735 37.76337 -122.25433 5.497946626 3.416265648
10 94579 37.6864911 -122.157201 10735 37.76337 -122.25433 13.76361634 8.552314689
11 94611 37.8294833 -122.2033926 10735 37.76337 -122.25433 9.273343688 5.762188621
12 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
13 94603 37.7365309 -122.1794542 10735 37.76337 -122.25433 8.837855387 5.491588736
14 94803 37.95954 -122.2835546 10735 37.76337 -122.25433 22.03721492 13.6932905
15 94601 37.7755447 -122.2187049 10735 37.76337 -122.25433 4.183109881 2.599263973
16 94609 37.8343906 -122.264374 10735 37.76337 -122.25433 7.969701545 4.952142949
17 94578 37.7061366 -122.1255505 10735 37.76337 -122.25433 15.65831996 9.729628937
18 94601 37.7755447 -122.2187049 10735 37.76337 -122.25433 4.183109881 2.599263973
19 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
20 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
21 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597
22 94612 37.8087999 -122.2691955 10735 37.76337 -122.25433 5.311133083 3.300185094
23 94602 37.8043204 -122.2075451 10735 37.76337 -122.25433 6.908361482 4.292656809
24 94501 37.7737968 -122.278123 10735 37.76337 -122.25433 2.886377129 1.793511597

I need to calculate distances between 2 different sets of lat/lon coordinates (one representing zip code centroids, the other representing hospitals). I created a small test file with 24 records and am trying to use the "XY to Line" tool to get the distances. For some reason the lines that it outputs are nonsensical.   Clearly I'm doing something wrong with GCS and projection, but I don't know what.

I created the zip codes centroids points from Tiger Line polygons, which use GCS North American 1983.   I created the hospital points by geocoding addresses, and they seem to display perfectly when I set them to the same GCS.

Can anyone tell me what I'm doing wrong? Maybe I need to get out of that GCS and into a projected system right off the bat??

Or is there any easier way to get these distances?   I tried creating a Point Distance table but had other problems with that, and that is a less direct solution anyway because I would then have to somehow lookup values in that table to get distances for my already paired sets of coordinates.

Re: Problem with XY to Line

BryanMaxwell — Tue, 28 Jan 2014 02:49:31 GMT

Thanks all. Turns out I just had the lats and lons backwards in the XY to Line tool config. All is well! Very sorry to waste people's time.