Combining spatial datasets using coordinates conditional of being in the same state

[Jose Martin]

Dear all,

I have two datasets in which for every observation there are two variables, lat and long, providing the geographic coordinates to that observation. In dataset A I have a panel data of firms and in dataset B I have the coordinates for each regulator field offices in each state. The two datasets look like as follows:

Dataset A (firms)

firm_id	year	state_A	regulator_agency_A	lat	long	_id
1	1	TX	SEC	32.3755	-86.3031	1
1	2	TX	SEC	32.3755	-86.3031	2
1	3	TX	SEC	32.3755	-86.3031	3
2	1	NY	SEC	37.7912	-122.397	4
2	2	NY	SEC	37.7912	-122.397	5
2	3	NY	SEC	37.7912	-122.397	6
3	1	TX	FDIC	33.4127	-86.7146	7
3	2	TX	FDIC	33.4127	-86.7146	8
3	3	TX	FDIC	33.4127	-86.7146	9

Dataset B (field office)

state_B	regulator_agency_B	latitude_fo	longitude_fo	_idfo
TX	SEC	32.3755	-86.3031	1
TX	SEC	32.8767	-86.5054	2
AR	SEC	33.4127	-86.7146	3
NY	SEC	33.47	-86.8298	4
NY	FDIC	34.7496	-92.399	5
TX	FDIC	35.834	-90.7048	6
VI	FDIC	36.1793	-94.1902	7

What I need is to find the closes regulator field office for each firm that is located in the same state for the regulatory agency that is in charge of the firm

So far I am using the command geonear as follows:

geonear _id lat long using "fieldoffice.dta", n(_idfo latitude_fo longitude_fo)

However, I am not sure whether I can use conditioning using something like "if" (i.e. if id state_A==state_B and regulator_agency_A==regulator_agency_B then find the closes field office for each firm).

I am using State 14.2

Thank you a lot in advance.

Best regards,

[Robert Picard]

You want to find the nearest regulator's office from the same state. geonear (from SSC) does not support by-groups but you can use runby (also from SSC) to handle the data management gymnastics to get there. In the future, please provide data examples using dataex (preinstalled on up-to-date Stata 14 or higher or from SSC). Note that long is not a valid variable name, it is a reserved word used to specify an integer data type.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(firm_id year) str2 state_a str4 regulator_agency_a float(lat lon) byte _id
1 1 "TX" "SEC"  32.3755 -86.3031 1
1 2 "TX" "SEC"  32.3755 -86.3031 2
1 3 "TX" "SEC"  32.3755 -86.3031 3
2 1 "NY" "SEC"  37.7912 -122.397 4
2 2 "NY" "SEC"  37.7912 -122.397 5
2 3 "NY" "SEC"  37.7912 -122.397 6
3 1 "TX" "FDIC" 33.4127 -86.7146 7
3 2 "TX" "FDIC" 33.4127 -86.7146 8
3 3 "TX" "FDIC" 33.4127 -86.7146 9
end
save "dataexA.dta", replace

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 state_b str4 regulator_agency_b float(latitude_fo longitude_fo) byte _idfo
"TX" "SEC"  32.3755 -86.3031 1
"TX" "SEC"  32.8767 -86.5054 2
"AR" "SEC"  33.4127 -86.7146 3
"NY" "SEC"    33.47 -86.8298 4
"NY" "FDIC" 34.7496  -92.399 5
"TX" "FDIC"  35.834 -90.7048 6
"VI" "FDIC" 36.1793 -94.1902 7
end
save "dataexB.dta", replace

clear all
program match_in_state
    local state = state_a[1]
    preserve
    use if state_b == "`state'" using "dataexB.dta", clear
    tempfile regulators
    save "`regulators'"
    restore
    geonear _id lat lon using "`regulators'", n(_idfo latitude_fo longitude_fo)
end

use "dataexA.dta"
runby match_in_state, by(state_a) verbose
sort _id
list firm_id regulator_agency_a _id nid km_to_nid, sepby(firm_id)

and the results

. list firm_id regulator_agency_a _id nid km_to_nid, sepby(firm_id)

     +--------------------------------------------+
     | firm_id   regula~a   _id   nid   km_to_nid |
     |--------------------------------------------|
  1. |       1        SEC     1     1           0 |
  2. |       1        SEC     2     1           0 |
  3. |       1        SEC     3     1           0 |
     |--------------------------------------------|
  4. |       2        SEC     4     5   2698.8082 |
  5. |       2        SEC     5     5   2698.8082 |
  6. |       2        SEC     6     5   2698.8082 |
     |--------------------------------------------|
  7. |       3       FDIC     7     2    62.70208 |
  8. |       3       FDIC     8     2    62.70208 |
  9. |       3       FDIC     9     2    62.70208 |
     +--------------------------------------------+

.