Hi Statalist,
I have been trying to solve this for hours but haven't figured anything out. I am describing the problem in UK census area terminology but I expect it should be understandable in a general sense as well, if not will appreciate if you ask for clarification.
I'm trying to find distance between UK airports and MSOA centroids. I intend to use
which would have been quite simple. But, 1. I have data on Airport coordinates in latlong system.
2. I have data on MSOA centroids, but in planar system (ISSUE 1). This is my understanding, because typical X Y values are 532290.4, 181745.9 etc which can't be lat long, but I would love to be told otherwise
3. These two information are in different data sets(ISSUE 2).
4. I can't joinby these two data(ISSUE 3) because airport data does not provide postcodes which I could have used to map to MSOAs.
Is there any way to:
1. convert one coord system to another for comparability?
2. calculate distance between each airport-centroid pair?
Below is sample msoa centroid data:
Below is sample airport location data. This data has been spset using
I would really appreciate any help in this. I have been spending hours trying to figure it out but I seem to be going nowhere.
Thank you very much!
I have been trying to solve this for hours but haven't figured anything out. I am describing the problem in UK census area terminology but I expect it should be understandable in a general sense as well, if not will appreciate if you ask for clarification.
I'm trying to find distance between UK airports and MSOA centroids. I intend to use
Code:
geodist lat1 lon1 lat2 lon2 [if] [in] , generate(newvar) [options]
2. I have data on MSOA centroids, but in planar system (ISSUE 1). This is my understanding, because typical X Y values are 532290.4, 181745.9 etc which can't be lat long, but I would love to be told otherwise
3. These two information are in different data sets(ISSUE 2).
4. I can't joinby these two data(ISSUE 3) because airport data does not provide postcodes which I could have used to map to MSOAs.
Is there any way to:
1. convert one coord system to another for comparability?
2. calculate distance between each airport-centroid pair?
Below is sample msoa centroid data:
Code:
* Example generated by -dataex-. For more info, type help dataex clear input int fid str9 msoa21code str36 globalid float(x y) 1 "E02000001" "6c80fa33-5d36-4455-bb70-cdfc7a20cb4f" 532290.4 181745.94 2 "E02004372" "1d1efe8c-6b00-4acc-a6fa-cd5a3a779d7b" 582475.56 110963.82 3 "E02006584" "36a08448-07b6-4255-b1de-05d9b56d2fc9" 524353.2 135416.27 4 "E02006411" "81f1280d-1a6c-434a-ac96-38f9f01351ea" 504326.2 170345.34 5 "E02003401" "34d0fcda-8ca2-4f5f-9ad3-481c3106ceb2" 470327 172513.7 6 "E02005040" "5053488b-2069-40dc-afb6-9096a37b7832" 561565.94 169067.66 7 "E02000269" "1f833ea4-d825-4cf2-b554-7f6fcbebe04b" 516974.5 179729.97 8 "E02006466" "9ba21b25-c65b-45c3-8687-95c0fae5b3cf" 500972.1 157040.5 9 "E02004783" "b09520d7-f2fb-472e-b88d-3b690f9e3940" 435300.2 112716.07 10 "E02005174" "ae0c49d7-d122-438c-9f79-814e87337458" 577676.6 136203.27 11 "E02000213" "343e0e91-8797-426a-bb0b-b6f8e123d806" 532373.3 166484.3 12 "E02005154" "979eb775-f795-4578-822d-49f997f2885b" 560890.7 157178.13 13 "E02000487" "a02ee84e-7b3b-455b-94c3-c00ebefb4bf5" 556006.06 185779.84 14 "E02000065" "66e19958-a022-48c2-b32e-6e6fa454b874" 547667.44 180941.3 15 "E02000881" "40ebd270-3a2d-40a7-8d97-8fe0c268d77a" 538153.25 181605.5 16 "E02003507" "3e6145d2-1a42-4a07-abff-28c253f025c9" 536116.94 105566.13 17 "E02000784" "2e134c36-7305-45dc-847d-c368658ba985" 522364 177239.14 18 "E02000932" "973b2560-01a9-4e68-ada5-ae1a7400788d" 525004.6 175064.1 19 "E02004688" "965408c4-2502-4640-9343-ea8f8187ee80" 461861.4 151262 20 "E02001835" "3753dc75-e59a-4fea-94e5-796264fe3fdd" 412016.9 295314.8 21 "E02000605" "7a35d09e-4471-4c31-acec-51ac9b1132f7" 520707.4 168341.75 22 "E02003935" "785c62d7-941c-48c8-b70b-05db0581ddbe" 233267.38 84383.52 23 "E02007028" "b3c22294-1add-4557-9dd8-5516827e1e1a" 300890.28 95421.31 24 "E02004281" "9b23264b-7ee8-49a0-b047-158de5214905" 367583.2 83372.55 25 "E02006595" "b07fe3ba-7cb7-4259-a61a-f243022fa66e" 518467.7 129651.28 26 "E02000712" "458f95a4-e5d8-4dbf-a388-c82280d5793a" 525613.94 167188.5 27 "E02000619" "69cd7ffa-5ef5-4078-ac4d-b88d044015b3" 531175.06 178718 28 "E02004258" "d8b51345-1b58-4004-8dd6-e0e4f093a457" 377733.2 115640.52 29 "E02006372" "d5ec4d56-2469-4b4b-bd18-2f469cabfe0e" 517163.75 147598.98 30 "E02000747" "5deb24ab-c33c-4707-b63d-20cacced5e77" 539972.8 181177.9 31 "E02004699" "b1b768c0-cad0-463a-a1ad-55fd9621567d" 471378.4 139234.39 32 "E02003156" "dd6a1d47-b5ca-4b12-bdff-3b2042da84d1" 289491.4 66167.875 33 "E02004202" "97f782e1-b7ff-45c3-ae7e-ed34ee50d07f" 294496.28 87198.1 34 "E02000654" "c81dd43f-057c-4ea4-addb-a5891f95d1d1" 536708.25 177838.2 35 "E02000193" "773ab834-2067-4ac0-87a5-61c64ab31609" 530244.1 181559.25 36 "E02000005" "3b221832-c695-4db0-a0fd-b713bd317768" 548629.94 186875.27 37 "E02004183" "ae4cf4cc-430a-4301-8e18-0f9ea54f8271" 254819.2 132446.14 38 "E02001897" "fbcb0531-761c-415f-b4c0-7e5e445867ff" 408508.7 285408.7 39 "E02000293" "006a26e5-8db8-49d8-b120-d79fa8dcfb2a" 531845.56 195539.45 40 "E02003032" "dbe8dd63-b6ce-4c1e-8b57-7f5ebc7d47db" 363378.1 174828.8 41 "E02005115" "9147d99c-cf82-43bd-8102-c3dfda088084" 592584.3 174683.4 42 "E02003151" "a094d5e5-6b6d-458e-9a0a-fe61c5fbed7f" 252711.64 53221.21 43 "E02003319" "3296fd82-eca5-48a2-ace1-0b4afdd06b19" 573705.3 169727.33 44 "E02000680" "6661de58-0d43-449a-8812-f50c2dca8ca1" 534869.44 172389.77 45 "E02000758" "6685acde-a73d-4d14-b4d1-0abbda0880a6" 542871.3 190488.95 46 "E02003955" "21ed130f-871a-4b8e-a040-629ac57befdb" 181937.1 61188.86 47 "E02006089" "9426b129-cb0e-4e2f-8ec8-7365465088e0" 356187.1 116349.94 48 "E02003082" "b9debdcf-6ee0-4670-86b2-212ffd42069a" 332092 162019 49 "E02005149" "707b6ea6-d7cf-42eb-a4fb-31623c9262c3" 573235.6 161681.34 50 "E02000643" "5f79ba3d-6736-4c12-822f-da201fe678cf" 530395.9 172803.3 51 "E02000577" "2b454d8f-a060-453b-aef5-420b7710f1a0" 524350.06 182020.4 52 "E02004226" "9d9090dd-9442-4c5d-8236-562ab0ec093a" 257281.4 113783.88 53 "E02003428" "13dcf21e-fb87-4402-afbc-c2f5dcf0261c" 486625.7 179384.53 54 "E02006071" "d6a2f3eb-d072-4a80-a340-a7342549c8b3" 328695.8 137247.48 55 "E02004156" "841e547d-fc05-4549-91b8-b17f53edca67" 292014.8 92777.75 56 "E02000202" "5c4999cc-da15-4bb2-bf9c-ef481d2478ec" 531524.75 168329.05 57 "E02004377" "2e3ff1bc-73e3-4902-b16a-5f15aa01d998" 578837.44 109368.5 58 "E02004155" "80e6adee-4d78-403e-ae5e-a2cf3da644e8" 294797.7 92980.04 59 "E02006564" "a75519d3-3305-4d1f-9f1d-88c39f69b75f" 488321.8 121232.8 60 "E02003451" "3f73f218-ef1f-4afd-a146-55c9a37a5edb" 481357.9 168988.3 61 "E02000943" "e406b047-59ab-4110-8331-5b720af36643" 528468.7 173705.63 62 "E02003596" "3bdfdc15-2f9c-4620-a8eb-6c43c1084466" 458373.2 81949.79 63 "E02006366" "5b35b168-2f09-4a17-ac1c-03830645ce8a" 514743.75 156161.16 64 "E02000301" "f4c9e931-5c01-4cbe-9283-676afa893f1a" 535130.75 193751.7 65 "E02000583" "e75a8ba6-5c62-49d8-af9c-2ca9a1c64583" 524381.1 180610.1 66 "E02003343" "f777a7d8-f588-4c65-8de0-3462b0c4c8ac" 580059.94 165271.16 67 "E02004144" "fd1b3354-1206-470b-af0a-cd78ee82acf5" 300681.66 82734.07 68 "E02000140" "20daa9e2-4d79-4450-b53d-ab53bc3e86e1" 546702 169245.9 69 "E02000912" "3f075e87-d788-41fe-812c-c48ac18d4372" 536776.44 188133.9 70 "E02004795" "39930f95-c38c-4834-8a52-ba5288dfef50" 432659.6 95800.12 71 "E02004187" "85bd1afd-2219-4a15-8716-d9d41ad2870c" 271120.3 125866.13 72 "E02000828" "38c29f70-c6e3-41a3-9bae-654ba2034471" 534387.75 176919.5 73 "E02006578" "b5ee3fd0-ed6e-41c8-b89a-4d94c9f68b12" 527583.25 137150.2 74 "E02000439" "316e604c-5dfe-4af4-a285-d4f6088be3ac" 519280.2 190925.7 75 "E02000557" "742586ac-35fa-42d8-a0df-b794d86f018c" 529269.5 186572.7 76 "E02003170" "d581c2cf-2512-488c-ae03-3865dec57271" 292346.06 56072.93 77 "E02007111" "dfd7f149-1818-4a5e-b2d6-1d066f597853" 533414.6 182840.95 78 "E02003321" "84e2f4e0-597c-4ae2-9842-aca377d15cc5" 572057.1 169099.5 79 "E02000186" "a1b9f14b-7d77-4ed3-8f95-c4577ff66ea0" 528977.25 183426.36 80 "E02000375" "2600c6ec-32c3-4cb4-9b62-b704fb196229" 523197.6 180234.53 81 "E02004731" "8107776a-8c83-476f-b7c8-ce81aeed7c6a" 451060.9 106788.48 82 "E02005173" "3b89d50c-89b4-4224-a770-eb9da3f9aed1" 558340.94 138256.92 83 "E02006853" "75153558-4366-4c4e-bc78-f6fadf80ffff" 537498.5 179542 84 "E02003174" "9ee03980-5c2b-4fa9-ab6b-51d965fd9696" 408010.6 95492.81 85 "E02004819" "98d43483-cd24-4897-8327-44eb699a0104" 435925.7 144931.2 86 "E02001911" "11fe421c-9942-42cb-b5d7-d58417384d2a" 401434.4 284182.2 87 "W02000331" "24915741-eba1-4951-91f1-6815472c2c85" 327767.5 195990.64 88 "E02003430" "92f21cb8-7e23-48de-b91a-af65ad18bcbd" 496469.3 177655.5 89 "E02004273" "2661bd9f-d615-4226-b379-c2d0eb52e813" 334381.5 93039.05 90 "E02003124" "2d94b743-4763-4696-8b44-a631aa8a9680" 247140.9 60272.99 91 "E02003455" "0dc0214c-14c6-4294-a828-7495e404d8e9" 471866.6 166781.94 92 "E02003533" "c1a005ab-c657-4c1c-a6d8-8dfed94d996f" 464445.5 102546.13 93 "E02003164" "f92d718b-e17f-4b43-87b3-00071f6b53ad" 288834.97 62189.83 94 "E02006358" "ffefed23-db0c-40cb-a692-71f187af6991" 498633 149763.69 95 "E02003924" "69386aea-29a1-4ef5-86d5-97926f4c075c" 164231.16 40150.7 96 "E02004147" "099ddb6c-656b-4d5a-8eca-8136edb5a5db" 300206.63 81161.23 97 "E02003589" "99302ec0-2fdd-4b41-aa29-e488c7dba7d4" 449421.5 89312.68 98 "E02004390" "7b9fba45-51cf-43d9-bc46-48f33e65773d" 549810.7 99652.92 99 "E02000028" "359b9b09-9e86-4320-a25b-3f5f392197a5" 526195.75 195585.66 100 "E02003172" "e104aed4-4864-470b-8705-ba3850b3a437" 407483 96472.58 end
Code:
spset fid, coord(long_x lat_y) spset, modify coordsys(latlong)
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str27 airport float(lat_y long_x) byte(fid _ID) float(_CX _CY) "Heathrow" 51.47002 -.454296 1 1 -.454296 51.47002 "Gatwick" 51.15366 -.182063 2 2 -.182063 51.15366 "Manchester" 53.3588 -2.27273 3 3 -2.27273 53.3588 "Stansted" 51.88602 .238866 4 4 .238866 51.88602 "Edinburgh" 55.95079 -3.361453 5 5 -3.361453 55.95079 "Aberdeen" 57.2037 -2.200162 6 6 -2.200162 57.2037 "Birmingham" 52.45238 -1.743507 7 7 -1.743507 52.45238 "Glasgow" 55.86908 -4.435053 8 8 -4.435053 55.86908 "Luton" 51.87627 -.371747 9 9 -.371747 51.87627 "London City" 51.50484 .049518 10 10 .049518 51.50484 "East Midlands International" 52.82937 -1.332134 11 11 -1.332134 52.82937 "Bristol" 51.38363 -2.713546 12 12 -2.713546 51.38363 "Newcastle" 55.03852 -1.693079 13 13 -1.693079 55.03852 "Belfast City (George Best)" 54.61761 -5.87183 14 14 -5.87183 54.61761 "Belfast International" 54.65417 -6.224699 15 15 -6.224699 54.65417 "Southampton" 50.95145 -1.357702 16 16 -1.357702 50.95145 "Liverpool (John Lennon)" 53.33671 -2.85744 17 17 -2.85744 53.33671 "Leeds Bradford" 53.86794 -1.661531 18 18 -1.661531 53.86794 "Norwich" 52.66969 1.277217 19 19 1.277217 52.66969 "Cardiff Wales" 51.39667 -3.343333 20 20 -3.343333 51.39667 "Scatsta" 60.43509 -1.296676 21 21 -1.296676 60.43509 "Exeter" 50.73107 -3.416251 22 22 -3.416251 50.73107 "Humberside" 53.57444 -.350833 23 23 -.350833 53.57444 "Isles Of Scilly (St.Marys)" 49.91424 -6.29477 24 24 -6.29477 49.91424 "Kirkwall" 58.95489 -2.902601 25 25 -2.902601 58.95489 "Blackpool" 53.77768 -3.042767 26 26 -3.042767 53.77768 "Inverness" 57.53956 -4.063538 27 27 -4.063538 57.53956 "Southend" 51.56947 .703726 28 28 .703726 51.56947 "Sumburgh" 59.87889 -1.295556 29 29 -1.295556 59.87889 "Prestwick" 55.5089 -4.610981 30 30 -4.610981 55.5089 "Lands End (St Just)" 50.10228 -5.66797 31 31 -5.66797 50.10228 "Bournemouth" 50.77768 -1.832453 32 32 -1.832453 50.77768 "Stornoway" 58.21399 -6.321857 33 33 -6.321857 58.21399 "Newquay" 50.44642 -5.002058 34 34 -5.002058 50.44642 "Durham Tees Valley" 54.50917 -1.429167 35 35 -1.429167 54.50917 "Doncaster Sheffield" 53.47921 -1.00201 36 36 -1.00201 53.47921 "Wick" 58.45341 -3.087708 37 37 -3.087708 58.45341 "City Of Derry (Eglinton)" 55.04285 -7.234927 38 38 -7.234927 55.04285 "Benbecula" 57.47397 -7.371782 39 39 -7.371782 57.47397 "Manston (Kent Int)" 51.34486 1.345809 40 40 1.345809 51.34486 "Islay" 55.68195 -6.256667 41 41 -6.256667 55.68195 "Dundee" 56.45245 -3.024947 42 42 -3.024947 56.45245 "Lerwick (Tingwall)" 60.18916 -1.246519 43 43 -1.246519 60.18916 "Gloucestershire" 51.89374 -2.164502 44 44 -2.164502 51.89374 "Campbeltown" 55.44513 -5.697726 45 45 -5.697726 55.44513 "Tiree" 56.49849 -6.879138 46 46 -6.879138 56.49849 "Barra" 57.0253 -7.449481 47 47 -7.449481 57.0253 "Cambridge" 52.21061 .173581 48 48 .173581 52.21061 "Shoreham" 50.83357 -.290973 49 49 -.290973 50.83357 "Coventry" 52.36963 -1.48005 50 50 -1.48005 52.36963 "Oxford (Kidlington)" 51.82983 -1.316121 51 51 -1.316121 51.82983 "Lydd" 50.95575 .933624 52 52 .933624 50.95575 "Biggin Hill" 51.33189 .02891 53 53 .02891 51.33189 "Penzance Heliport" 50.12933 -5.515831 54 54 -5.515831 50.12933 "Isles Of Scilly (Tresco)" 49.94563 -6.331227 55 55 -6.331227 49.94563 "Plymouth" 50.42277 -4.108763 56 56 -4.108763 50.42277 "Carlisle" 54.94155 -2.808119 57 57 -2.808119 54.94155 "Hawarden" 53.18364 -2.97679 58 58 -2.97679 53.18364 "Swansea" 51.60083 -4.071111 59 59 -4.071111 51.60083 end
I would really appreciate any help in this. I have been spending hours trying to figure it out but I seem to be going nowhere.
Thank you very much!
Comment