Creating inverse-distance matrices with cross-sectional data and duplicate locations

[matt jarvis]

Hi,

I am running a hedonic regression on how (log) house prices and distance from a local airport. Because of the spatial relationship between houses I need to use a SAR model.

My data is cross-sectional and contains the latitude and longitude of several homes (I do not have a shapfile).

I am trying to create an inverse-distance matrix but am met with the error "Two or more observations have the same coordinates" which makes sense as some houses share a postcode hence have the same latitude and longitude.

How do I go about creating this matrix? I read about dropping duplicates, creating the matrix and then merging the data sets together but this causes me to lose observations?

Any help would be greatly appreciated!

[matt jarvis]

Here is an example of my dataset where the postcode SS00JQ is duplicated for example.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id long soldprice str7 postcode float(lon lat) str15 propertytype double distancetoairport byte(receptionrooms bedrooms bathrooms) str2 offstreetparking byte(dum_quality_LOW dum_quality_MEDIUM dum_quality_HIGH) float(_ID _CX _CY)
  1 340000 "SS0 0BG" .6741048 51.55582 "Semi-Detached"    2.1 2 4 1 "Y" 0 0 1   1 .6741048 51.55582
  2 165000 "SS0 0DS" .6706402 51.55873 "Terraced"        2.06 1 2 1 "Y" 0 1 0   2 .6706402 51.55873
  3 180000 "SS0 0EB" .6746846  51.5598 "Semi-Detached"   1.75 1 2 1 "Y" 1 0 0   3 .6746846  51.5598
  4 153000 "SS0 0EX" .6701998 51.56279 "Terraced"        1.82 1 2 1 "Y" 0 1 0   4 .6701998 51.56279
  5 175000 "SS0 0HN" .6715448 51.56063 "Semi-Detached"   1.89 1 3 1 "Y" 0 1 0   5 .6715448 51.56063
  6 145000 "SS0 0HS" .6745929 51.56118 "Detached"        1.68 1 2 1 "Y" 1 0 0   6 .6745929 51.56118
  7 175000 "SS0 0JB" .6686473 51.56339 "Semi-Detached"   1.91 1 3 1 "Y" 1 0 0   7 .6686473 51.56339
  8 195000 "SS0 0JE" .6695747 51.56419 "Semi-Detached"    1.8 1 3 2 ""  . . .   8 .6695747 51.56419
  9 175000 "SS0 0JQ" .6717909 51.56335 "Semi-Detached"   1.72 1 3 1 "Y" 1 0 0   9 .6717909 51.56335
 10 147500 "SS0 0JQ" .6717909 51.56335 "Semi-Detached"   1.72 1 3 1 "Y" 0 1 0  10 .6717909 51.56335
 11 130000 "SS0 0LF" .6932387   51.561 "Terraced"        1.03 1 3 2 "Y" 0 1 0  11 .6932387   51.561
 12 175000 "SS0 0NW" .6935303 51.55861 "Semi-Detached"   1.24 1 3 1 "Y" 1 0 0  12 .6935303 51.55861
 13 190000 "SS0 0PB" .6887271 51.55692 "Semi-Detached"    1.5 1 3 1 "Y" 1 0 0  13 .6887271 51.55692
 14 220000 "SS0 0PZ" .6813635 51.55879 "Semi-Detached"    1.5 1 3 1 "Y" 1 0 0  14 .6813635 51.55879
 15 175000 "SS0 0TE" .6946681 51.55125 "Semi-Detached"   2.09 1 3 1 "N" 0 1 0  15 .6946681 51.55125
 16 135000 "SS0 7BB" .7006428 51.54569 "Terraced"        2.75 1 3 1 "N" 0 1 0  16 .7006428 51.54569
 17 150000 "SS0 7BB" .7006428 51.54569 "Terraced"        2.75 2 3 1 "N" 1 0 0  17 .7006428 51.54569
 18 126000 "SS0 7DD" .6975046 51.54385 "Flat Maisonette" 2.92 1 2 1 "N" 1 0 0  18 .6975046 51.54385
 19 119000 "SS0 7DS" .6992558 51.54492 "Flat Maisonette" 2.85 1 1 1 "N" 0 1 0  19 .6992558 51.54492
 20 190000 "SS0 7DT" .6993849 51.54515 "Terraced"        2.78 1 1 1 "N" 1 0 0  20 .6993849 51.54515
 21  97000 "SS0 7DX" .6950423 51.54319 "Flat Maisonette" 2.98 1 3 1 "Y" 0 1 0  21 .6950423 51.54319
 22 150000 "SS0 7HN" .6938508 51.54206 "Flat Maisonette"  3.1 1 3 1 "Y" 1 0 0  22 .6938508 51.54206
 23 270000 "SS0 7HS" .6950704 51.54171 "Semi-Detached"   3.15 1 5 1 "N" 1 0 0  23 .6950704 51.54171
 24 187000 "SS0 7JY"  .697776 51.53966 "Semi-Detached"   3.42 1 3 1 "Y" 0 1 0  24  .697776 51.53966
 25 140000 "SS0 7JZ" .6979819 51.53997 "Semi-Detached"   3.36 1 2 1 "Y" 1 0 0  25 .6979819 51.53997
 26 149000 "SS0 7JZ" .6979819 51.53997 "Semi-Detached"   3.36 2 4 2 "Y" 0 1 0  26 .6979819 51.53997
 27 130000 "SS0 7JZ" .6979819 51.53997 "Semi-Detached"   3.36 3 4 2 "Y" 0 1 0  27 .6979819 51.53997
 28 205000 "SS0 7JZ" .6979819 51.53997 "Detached"        3.36 1 4 3 "Y" 0 0 1  28 .6979819 51.53997
 29 240000 "SS0 7JZ" .6979819 51.53997 "Detached"        3.36 3 4 1 "Y" 1 0 0  29 .6979819 51.53997
 30 137995 "SS0 7LA" .6961677 51.53978 "Flat Maisonette" 2.57 1 3 1 "Y" 0 1 0  30 .6961677 51.53978
 31 147500 "SS0 7ND" .6946978 51.53907 "Flat Maisonette" 3.43 1 3 1 "Y" 1 0 0  31 .6946978 51.53907
 32 130000 "SS0 7NE" .6967922 51.53814 "Flat Maisonette" 3.55 1 1 1 "N" 0 1 0  32 .6967922 51.53814
 33 190000 "SS0 7NP" .6981522   51.537 "Terraced"        3.69 1 2 3 "N" 0 1 0  33 .6981522   51.537
 34  95000 "SS0 7NY" .7041879 51.53859 "Flat Maisonette" 3.59 1 1 1 "Y" 1 0 0  34 .7041879 51.53859
 35 125000 "SS0 7PA" .7059381 51.53794 "Flat Maisonette" 3.64 1 2 1 "Y" 1 0 0  35 .7059381 51.53794
 36 110000 "SS0 7PA" .7059381 51.53794 "Flat Maisonette" 3.64 1 1 1 "N" 1 0 0  36 .7059381 51.53794
 37 132000 "SS0 7PD" .7054361 51.53945 "Flat Maisonette" 3.48 1 2 2 "Y" 1 0 0  37 .7054361 51.53945
 38 354000 "SS0 7PE" .7046452 51.53706 "Terraced"         3.7 1 4 3 "N" 0 0 1  38 .7046452 51.53706
 39 397000 "SS0 7PU" .6992086 51.53584 "Detached"        3.83 2 5 3 "Y" 0 0 1  39 .6992086 51.53584
 40 102000 "SS0 7PW" .7029511 51.53743 "Flat Maisonette" 3.68 1 1 1 "N" 1 0 0  40 .7029511 51.53743
 41 160000 "SS0 7QE" .7015395 51.53502 "Flat Maisonette" 3.99 1 2 1 "N" 1 0 0  41 .7015395 51.53502
 42 125000 "SS0 7QG" .7024436 51.53495 "Flat Maisonette" 3.97 1 2 1 "N" 0 1 0  42 .7024436 51.53495
 43 120000 "SS0 7QX" .6969613 51.53613 "Terraced"        3.77 1 1 1 "N" 1 0 0  43 .6969613 51.53613
 44 340000 "SS0 8AT" .6881031 51.54267 "Detached"        3.06 1 4 3 "Y" 0 1 0  44 .6881031 51.54267
 45 134000 "SS0 8AW" .6919041 51.54231 "Flat Maisonette" 3.11 1 2 1 "Y" 1 0 0  45 .6919041 51.54231
 46 358000 "SS0 8BA"  .688197 51.54377 "Detached"        2.95 1 6 2 "Y" 0 0 1  46  .688197 51.54377
 47 142500 "SS0 8BL" .6870561 51.53983 "Flat Maisonette" 3.36 1 2 1 "Y" 0 0 1  47 .6870561 51.53983
 48 340000 "SS0 8EP" .6843919  51.5372 "Semi-Detached"   3.71 4 6 3 "Y" 1 0 0  48 .6843919  51.5372
 49 450000 "SS0 8HN" .6774602 51.53827 "Detached"        3.69 1 4 2 "Y" 0 1 0  49 .6774602 51.53827
 50 672000 "SS0 8HN" .6774602 51.53827 "Detached"        3.69 . . . ""  . . .  50 .6774602 51.53827
 51 169995 "SS0 8HP" .6797904 51.53866 "Flat Maisonette" 3.59 1 2 1 "Y" 0 1 0  51 .6797904 51.53866
 52 155870 "SS0 8HP" .6797904 51.53866 "Flat Maisonette" 3.59 1 2 1 "Y" 0 1 0  52 .6797904 51.53866
 53 166250 "SS0 8HS"  .681905 51.53857 "Flat Maisonette" 3.57 1 2 1 "Y" 0 1 0  53  .681905 51.53857
 54 640000 "SS0 8LA" .6809458 51.54139 "Detached"        3.28 7 6 6 "Y" 0 0 1  54 .6809458 51.54139
 55 290000 "SS0 8LG" .6810977  51.5398 "Detached"        3.49 1 5 2 "Y" 0 0 1  55 .6810977  51.5398
 56 380000 "SS0 8LH" .6840319 51.54015 "Semi-Detached"   3.37 2 4 3 "Y" 0 1 0  56 .6840319 51.54015
 57 899995 "SS0 8LP"  .685128  51.5426 "Detached"        3.08 4 6 4 "Y" 0 0 1  57  .685128  51.5426
 58 495000 "SS0 8PJ" .6749033 51.54214 "Detached"        3.35 2 5 1 "Y" 1 0 0  58 .6749033 51.54214
 59  88000 "SS0 9AB"  .700367 51.54858 "Flat Maisonette"  2.4 1 1 1 "N" 0 0 1  59  .700367 51.54858
 60 103000 "SS0 9AN" .6989178 51.54896 "Flat Maisonette"    . 1 1 1 "N" 1 0 0  60 .6989178 51.54896
 61 100000 "SS0 9AN" .6989178 51.54896 "Terraced"        2.38 2 3 1 "Y" 0 1 0  61 .6989178 51.54896
 62  87000 "SS0 9BG"  .694446 51.54969 "Flat Maisonette" 2.29 1 1 1 "N" 1 0 0  62  .694446 51.54969
 63 160000 "SS0 9BJ" .6971189 51.54806 "Terraced"        2.47 1 3 1 "N" 0 1 0  63 .6971189 51.54806
 64 160000 "SS0 9BP" .6974806 51.54856 "Terraced"         2.4 2 3 1 "N" 1 0 0  64 .6974806 51.54856
 65 199995 "SS0 9DU" .6949048 51.54818 "Terraced"        2.49 1 4 2 "N" 1 0 0  65 .6949048 51.54818
 66 125000 "SS0 9EB" .6919447  51.5452 "Flat Maisonette" 2.78 1 2 1 "N" 1 0 0  66 .6919447  51.5452
 67 152500 "SS0 9EH" .6926877 51.54899 "Terraced"        2.37 1 3 1 "N" 1 0 0  67 .6926877 51.54899
 68 140000 "SS0 9EP" .6923826 51.55017 "Semi-Detached"   2.19 3 4 1 "N" 1 0 0  68 .6923826 51.55017
 69 160000 "SS0 9EY"  .693514 51.54759 "Terraced"        2.49 1 3 1 "N" 1 0 0  69  .693514 51.54759
 70 112000 "SS0 9HW" .6831158 51.54542 "Flat Maisonette" 2.83 1 2 1 "N" 0 1 0  70 .6831158 51.54542
 71 109000 "SS0 9HW" .6831158 51.54542 "Flat Maisonette" 2.83 1 2 1 "N" 1 0 0  71 .6831158 51.54542
 72  70000 "SS0 9JE" .6897845 51.54672 "Semi-Detached"   2.61 1 2 1 "N" 1 0 0  72 .6897845 51.54672
 73 115000 "SS0 9JF" .6917986 51.54663 "Flat Maisonette"  2.6 1 2 1 "N" 1 0 0  73 .6917986 51.54663
 74 128000 "SS0 9JH" .6912413 51.54745 "Terraced"        2.52 1 3 1 "N" 1 0 0  74 .6912413 51.54745
 75 157000 "SS0 9JR" .6903294 51.54765 "Terraced"        2.51 1 3 2 "N" 0 0 1  75 .6903294 51.54765
 76 185000 "SS0 9JU" .6902166 51.54549 "Semi-Detached"   2.71 1 3 2 "N" 1 0 0  76 .6902166 51.54549
 77  90000 "SS0 9LP" .6897953 51.54789 "Flat Maisonette" 2.43 1 1 1 "N" 0 1 0  77 .6897953 51.54789
 78 193000 "SS0 9LS" .6896799 51.54959 "Semi-Detached"    2.3 1 4 2 "N" 0 0 1  78 .6896799 51.54959
 79 185000 "SS0 9LZ" .6873979 51.55077 "Semi-Detached"   2.19 2 3 1 "Y" 1 0 0  79 .6873979 51.55077
 80 165000 "SS0 9ND" .6891101 51.54947 "Terraced"        2.36 1 2 1 "Y" 0 1 0  80 .6891101 51.54947
 81 150000 "SS0 9ND" .6891101 51.54947 "Terraced"        2.36 1 3 1 "Y" 1 0 0  81 .6891101 51.54947
 82 185000 "SS0 9NE"  .688773 51.54792 "Terraced"        2.44 1 3 2 "N" 0 1 0  82  .688773 51.54792
 83 170000 "SS0 9NG" .6882713 51.54601 "Terraced"         2.7 2 3 1 "N" 0 0 1  83 .6882713 51.54601
 84 165000 "SS0 9NJ" .6881124 51.54943 "Terraced"         2.7 1 3 1 "N" 1 0 0  84 .6881124 51.54943
 85 250000 "SS0 9PT" .6859403 51.54733 "Detached"        2.58 2 4 2 "Y" 0 1 0  85 .6859403 51.54733
 86 232000 "SS0 9QB"  .686793  51.5481 "Detached"        2.43 1 2 1 "Y" 0 1 0  86  .686793  51.5481
 87 107000 "SS0 9QG" .6861327 51.54987 "Flat Maisonette" 2.29 1 1 1 "N" 1 0 0  87 .6861327 51.54987
 88  99000 "SS0 9QL" .6844879 51.54643 "Flat Maisonette" 2.68 1 1 1 "N" 1 0 0  88 .6844879 51.54643
 89  90000 "SS0 9QP" .6846681 51.54998 "Flat Maisonette" 2.36 1 2 1 "N" 1 0 0  89 .6846681 51.54998
 90 187000 "SS0 9QQ"  .685855 51.54833 "Terraced"        2.44 1 3 1 "N" 0 0 1  90  .685855 51.54833
 91 220000 "SS0 9RA" .6831259 51.54657 "Terraced"         2.7 2 3 1 "N" 0 1 0  91 .6831259 51.54657
 92 110000 "SS0 9RA" .6831259 51.54657 "Flat Maisonette"  2.7 1 2 1 "N" 1 0 0  92 .6831259 51.54657
 93 169000 "SS0 9RB" .6834417 51.54851 "Terraced"        2.47 1 4 1 "N" 1 0 0  93 .6834417 51.54851
 94 129995 "SS0 9RB" .6834417 51.54851 "Detached"        2.47 1 3 1 "N" 0 1 0  94 .6834417 51.54851
 95 165000 "SS0 9RE" .6836793 51.55009 "Terraced"        2.33 2 3 1 "N" 0 1 0  95 .6836793 51.55009
 96 159995 "SS0 9RY" .6811971 51.54664 "Terraced"        2.75 1 3 1 "Y" 0 1 0  96 .6811971 51.54664
 97 105000 "SS0 9SA" .6819212  51.5484 "Flat Maisonette" 2.52 2 4 . "N" 0 1 0  97 .6819212  51.5484
 98 130000 "SS0 9SQ" .6804544 51.54848 "Flat Maisonette" 2.57 1 2 1 "N" 1 0 0  98 .6804544 51.54848
 99 150000 "SS0 9SQ" .6804544 51.54848 "Flat Maisonette" 2.57 1 2 1 "N" 0 1 0  99 .6804544 51.54848
100 240000 "SS0 9ST" .6805158 51.54585 "Terraced"        2.83 1 1 1 "Y" 1 0 0 100 .6805158 51.54585
end