Where can I find machine-readable data on which US states and DC border each other?

Question

I'm hoping to avoid compiling a dataset like this manually. Ideally, the dataset would look something like this:

state1   state2   share_border
NY       NJ       yes
NY       CA       no

etc. I'm not very knowledgeable about mapping, because otherwise I could probably back out this data from the Census cartography data.

I realize their are some ambiguities here, because (for example) the borders in the Four Corners region could be interpreted differently, but if I have most of the data I can resolve that based on the needs at hand.

Answer 1

Another way to look at it is if the states are within a small distance of each other. Using PostGIS, you can do this rather easily:

SELECT 
  s1.name state1, 
  s2.name state2,
  ST_DWithin(s1.the_geom::geography,s2.the_geom::geography,500) share_border
FROM state_polygons s1, state_polygons s2
WHERE s1.name < s2.name
ORDER BY share_border DESC, state1 ASC, state2 ASC

I have a data table of states in my CartoDB account and ran that query. Depending on the data, you can choose a more accurate number than 500 meters as I did here. I pulled the state polygons from CartoDB's data library. The state polygons were originally from Natural Earth Data.

This produces a data table like this:

Answer 2

It's not in exactly the format you want, but these data are available from this University of Minnesota page (see Table 1, which also includes the length of the border in miles and various US overseas territories.

You could also create your list from this page.

In case the above University of Minnesota link goes dead (it's an old link) here are the data in CSV format:

state1,state2,length
AL,FL,196.1
AL,GA,291.3
AL,MS,323.9
AL,TN,146.7
AR,LA,166.5
AR,MO,325.3
AR,MS,282.8
AR,OK,198.2
AR,TN,125.1
AR,TX,65
AZ,CA,198.4
AZ,CO,0
AZ,NM,391
AZ,NV,181.4
AZ,UT,275.8
CA,NV,608.2
CA,OR,216.3
CO,KS,207.7
CO,NE,173.2
CO,NM,333
CO,OK,52.9
CO,UT,276.1
CO,WY,260.2
CT,MA,86.5
CT,NY,82.3
CT,RI,40.3
DC,MD,24.6
DC,VA,1.9
DE,MD,122
DE,NJ,1.4
DE,PA,22.6
FL,GA,261.4
GA,NC,68.6
GA,SC,249.6
GA,TN,72.6
IA,MN,262.1
IA,MO,235
IA,NE,148.9
IA,SD,85.6
IA,WI,86.7
ID,MT,569.8
ID,NV,153
ID,OR,305.8
ID,UT,153.6
ID,WA,210.3
ID,WY,170.7
IL,IA,200.5
IL,IN,295.5
IL,KY,122.4
IL,MO,331.6
IL,WI,144.4
IN,KY,296
IN,MI,108.4
IN,OH,178.8
KS,MO,307.5
KS,NE,356.5
KS,OK,409.1
KY,MO,59.8
KY,OH,155.5
KY,TN,338.5
KY,VA,119
KY,WV,79.1
LA,MS,456.6
LA,TX,222.7
MA,NH,86.7
MA,NY,49.4
MA,RI,62.6
MA,VT,40.9
MD,PA,195.7
MD,VA,253.7
MD,WV,174
ME,NH,159.3
MI,OH,69.7
MI,WI,205.1
MN,ND,217.1
MN,SD,180.8
MN,WI,291.1
MO,NE,53.6
MO,OK,34.4
MO,TN,60.9
MS,TN,119.2
MT,ND,210.8
MT,SD,65.3
MT,WY,377.7
NC,SC,312.2
NC,TN,218.9
NC,VA,327.7
ND,SD,359
NE,SD,399.4
NE,WY,138
NH,VT,180
NJ,NY,103.4
NJ,PA,150.9
NM,OK,34.5
NM,TX,536.2
NM,UT,0
NV,OR,152.5
NV,UT,344.6
NY,PA,306.1
NY,VT,80.3
OH,PA,92.4
OH,WV,243.5
OK,TX,715.4
OR,WA,385.8
PA,WV,118.6
SD,WY,137.7
TN,VA,110.7
UT,WY,173.1
VA,WV,381.2