Tuesday, October 22, 2013

Sorting and Classifying Kingdom ZIP Codes

In order to map SCA branches by kingdom, the ZIP and FSA (Forward Sortation Area - Canada) codes have to be classified by Kingdom, region, primary and subsidiary branches.  These classifications will be scale dependent in the final map.  To develop the map, each Kingdom provided a list of ZIP/FSA codes by branch for this project.  Quite a bit of reformatting and editing was required to get the data "database ready".

The final product of this project is an interactive map that revels SCA groups at different scales (or zoom extents).  So if someone zooms out of the map far enough to see North America we should see Kingdom boarders.  Zooming to the region and state level should revel regions within a Kingdom.  Zooming to the State and county level should show Baronies and Shires.  Finally, zooming to the Barony or Province level should show the Barony proper and any cantons/ridings or colleges associated with the Barony or Province.  Keeps and Ports will figure into this as well; though the associated ZIP codes may appear as points rather than areas.

The map design means that each ZIP/FSA code must have a Kingdom, region, primary and, if required, subsidiary group names; where the type of group is part of the name for primary and subsidiary branches.  It's a database thing.  This ZIP/FSA code list will be used to link to a spatial database of ZIP codes.   We are effectively adding additional information to a map point or polygon ZIP/FSA code.

As you can imagine, each Kingdom has a different method of formatting these lists in a spreadsheet.  Most provided only the branch name and list of associated ZIP/FSA codes, while others included the region, type of branch and associations with the ZIP/FSA codes.  For example, in the former case, only the group name and list of ZIP codes was given and in the later case, the type of group (Barony, Canton, College, Shire, etc) and name plus which Barony the Canton or College belongs to as well as the ZIP/FSA codes were provided.  In order to get the information provided into a standard format for the spatial database it was necessary to "fill-in-the-blanks".  It's fortunate that SCA membership affords us the ability to download newsletters from other Kingdoms than our own.  I downloaded the AEstel, Acorn, PikeStaff, Popular Chivalry, and Talewinds newsletters to classify the ZIP/FSA codes.  Each newsletter has a page with group contact information.  Simply listing the names made it possible to determine the region, type of group, affiliation to a parent group, and even the correct spelling of a group name.

Once the ZIP/FSA code list is completely classified, it is possible to summarize the data.  Microsoft Excel's data filtering and Pivot Table tools makes this very easy.  A count of regions, groups within a region or Kingdom, or a count of groups by type is done in a matter of minutes.  A count of ZIP/FSA codes is helpful for the kind of data preparation this project calls for.  Here is a draft summary of the Kingdom of AEthelmearc:

Kingdom of Aethelmearc
REGION PRIMARY SUBSIDIARY Count of ZIPCODE
1 1,064
Barony of Blackstone Mountain 619
Shire of Ballachlagan 23
Shire of Misty Highlands 267
Shire of Port Oasis 84
Shire of Sylvan Glen 71
2 605
Barony Marche of the Debatable Lands 178
Canton of Steltonwald 18
College of Cour d'Or 3
(blank) 157
Shire of Gryffyns Keep 102
Shire of Hunter's Home 53
Canton of Steltonwald 3
(blank) 50
Shire of Kings Crossing 83
Shire of Riversedge 21
Shire of Stormsport 51
Shire of Sunderoak 117
2.3 6
DL--SSB 6
GK--WM 6
3 735
Barony of Endless Hills 230
Barony of Hornwood 69
Barony of St. Swithin's Bog 148
Crown Lands 41
Shire of Abhainn Ciach Ghlais 147
Shire of Nithgaard 100
4 461
Barony of Rhydderich Hael 153
Canton of Beau Fleuve 25
(blank) 128
Barony of Thescorre 166
Shire of Blackwater 29
Shire of Hartstone 42
Shire of Heronter 71
5 490
Barony of Delftwood 114
Dominion of Myrkfaelinn 83
Shire of Angel's Keep 7
Shire of Coppertree 118
Shire of Courtlandtslot 15
Shire of Sterlynge Vayle 88
Shire of Wynterset 65
Grand Total 3,361

In this table, "(blank)" is the barony proper, not the canton or college count of ZIP codes.  There is also the question of the Canton of Steltonwald having ZIP codes in two parent groups and the "2.3" region.  However, we can see that there are 5 regions, 29 baronies, shires, and a "Dominion", as well as 3 cantons and 1 college.  Altogether, there will be 3,361 tabular ZIP codes that need to match polygon or point ZIP code features.

Here's what I have so far for the other Kingdoms on my list:

Kingdom of the East
Region PRIMARY SUBSIDIARY Count of ZIP Code
Principality of Tir Mara 633
Barony of L'ile du Dragon Dormant 215
Barony of Ruantallan 82
Canton of Distant Shore 1
Canton of Seashire 26
Canton of Ynys Y Gwaed 7
Ruantallan Proper 48
Crown Lands 1
La baronnie du Havre des Glaces 113
La Selve d'Aure 25
Shire of Ar n-Eilean 34
Shire of Bois Ardent 23
Shire of Le Dragonet 30
Shire of Lyndhaven 110
Northern 1,256
Barony of Stonemarche 276
Stonemarche Proper 276
Crown Lands 94
Province of Malagentia 158
Malagentia Proper 154
Incipient Riding of Giggleswick 4
Shire of Coldwood 46
Shire of Glenn Linn 63
Shire of Mountain Freehold 80
Shire of Northern Outpost 73
Shire of Panther Vale 127
Shire of Smithwick 53
Barony of Endewearde 244
Incipient Canton of Wyndriche 30
Endewearde Proper 214
Incipient Shire of Hadchester 42
Central 1,673
Barony Beyond the Mountain 248
Canton of Bowman's Rest 84
Canton of Dragon's Aerie 28
Canton of Fennbrycg 84
Canton of Ravenhill 52
Barony of Bergental 160
Bergental Proper 160
Barony of Carolingia 249
Canton of Aschehyrst 20
Canton of The Towers 93
Carolingia Proper 136
Barony of Concordia of the Snows 171
Concordia Proper 171
Barony of Dragonship Haven 168
Canton of Seatygers Rhyn 45
Dragonship Haven Proper 75
Dragonship Proper 48
Barony of Smoking Rocks 170
Smoking Rocks Proper 170
Barony of the Bridge 91
Bridge Proper 91
Crown Lands 168
Shire of Anglespur 29
Shire of Coill Tuar 17
Shire of Frosted Hills 37
Shire of Nordenhalle 48
Shire of Quintavia 117
Southern 2,362
Barony of An Dubhaigeainn 115
An Dubhaigeainn Proper 90
Canton of Hawk's Reach 25
Barony of Bhakail 170
Balliwick of Ivyeinrust 10
Bhakail Proper 141
Canton of Black Icorndall 19
Barony of Carillion 164
Canton of Forestgate 46
Canton of the Keep by the Endless Sea 23
Carillion Proper 95
Barony of Iron Bog 77
Iron Bog Proper 77
Barony of Settmour Swamp 250
Canton of Gryphonwald 35
Canton of Marwick 35
Settmour Proper 180
Crown Lands 18
Crown Province of Ostgardr 547
Canton of Brokenbridge 52
Canton of Lions End 102
Canton of Northpass 100
Canton of Whyt Whey 147
Ostgardr Proper 146
Shire of Barren Sands 88
Shire of Blak Rose 87
Shire of Buckland Cross 64
Shire of Caer Adamant 98
Shire of Coill Tuar 1
Shire of Eisental 197
Shire of Hartshorn-dale 100
Shire of Montevale 78
Shire of Owlsherst 56
Shire of Rusted Woodlands 162
Shire of Silver Rylle 90
Grand Total 5,924
Kingdom of Atlantia
REGION PRIMARY SUBSIDIARY Count of ZipCode
Maryland 889
Barony of Bright Hills 152
Barony of Dun Carraig 66
Barony of Highland Foorde 85
Barony of Lochmere 70
Barony of Storvik 345
Shire of Roxbury Mill 56
Shire of Spiaggia Levantina 115
North Carolina 1,011
Barony of Hawkwood 206
Barony of Raven's Cove 13
Barony of Sacred Stone 436
Canton of Aire Faucon 38
Canton of Baelfire Dunn 76
Canton of Charlesbury Crossing 121
Canton of Crois Brigte 93
Canton of Middlegate 82
Canton of Salesberie Glen 15
(blank) 11
Barony of Windmasters' Hill 275
Canton of Attillium 54
Canton of Buckston-on-Eno 25
Canton of Elvegast 54
Canton of Kappellenberg 8
Canton of Nimenefeld 12
(blank) 122
Shire of Cathanar 28
Shire of Seareach 53
South Carolina 632
Barony of Hidden Mountain 278
Canton of Misty Marsh by the Sea 86
Canton of Tear-Sea's Shore 132
(blank) 60
Barony of Nottinghill Coill 354
Canton of Brockore Abbey 52
Canton of Cyddlain Downs 127
Canton of Falcon Cree 112
Canton of Ritterwald 6
(blank) 57
Virginia 810
Barony of Black Diamond 135
Barony of Caer Mear 147
Canton of Caer Gelynniog 11
Canton of Rivers Point 14
(blank) 122
Barony of Marinus 70
Barony of Ponte Alto 123
Barony of Stierbach 158
Canton of Abhainn Iarthair 20
Canton of Sudentorre 35
(blank) 103
Barony of Tir-y-Don 45
College of Rencester 5
(blank) 40
Shire of Berley Cort 26
Shire of Isenfir 106
(blank) 657
Crown Lands 657
(blank) 657
Grand Total 3,999


Kingdom of Meridies
REGION PRIMARY SECONDARY Count of ZIPCODE
Alabama 399
Barony of Iron Mountain 96
Barony of the Osprey 96
Incipient Shire of Ironstone Springs 3
Shire Des Forges 18
Shire of An Dun Theine 60
Shire of Glynn Rhe 50
Shire of Okebourne Keep 23
Shire of the Eagle 6
Shire of Thorngill 47
Florida 69
Barony of the Osprey 16
Incipient Shire of Ironstone Springs 4
Shire of Arenal 33
Shire of Phoenix Glade 16
Georgia 638
Barony of Bryn Madoc 33
Barony of South Downs 184
Incipient Shire of Depedale 11
Incipient Stronghold of Stags Gate 61
Shire of Beau Fort 32
Shire of Drakenmere 11
Shire of Flintmarsh 12
Shire of Forth Castle 46
Shire of Marion Glen 25
Shire of Owl's Nest 60
Shire of Sol Haven 28
Shire of Talmere 38
Shire of Tir Briste 50
Stronghold of Ravenwood 47
Kentucky 93
Shire of Loch an Fhraioch 18
Shire of Redwulfden 75
Tennessee 444
Barony of Thor's Mountain 125
Canton of Hochwald 7
(blank) 118
Shire of Crimson River 10
Shire of Easaraigh 53
Shire of Glaedenfeld 86
Shire of Loch Cairn 31
Shire of Nant y Derwyddon 65
Shire of Rising Stone 33
Shire of Vulpine Reach 41
(blank) 119
Crown Lands 119
Grand Total 1,762


Kingdom of Trimaris
PRIMARY SUBSIDIARY  Count of ZIPCODE
Barony of An Crosaire 108
Canton of Amurgorod 48
Canton of Mathom Trove 23
(blank) 37
Barony of Darkwater 157
Canton of Loch Gryphon 33
Canton of Peregrine Springs 26
Canton of Swampkeype 15
(blank) 83
Barony of Marcaster 151
Canton of Steadboro 77
(blank) 74
Barony of Oldenfeld 34
Barony of Wyvernwoode 93
Crown Lands 133
Shire of Bentonshire 34
Shire of Castlemere 59
Shire of Fiadh Ogain 35
Shire of Sangre del Sol 104
Shire of Sea March 60
Shire of Southkeep 136
Shire of Stag Ridge 11
Shire of Starhaven 47
Shire of Suderholt 8
Shire of the Ruins 60
Shire of Tri Os 31
Shire of Trystelle Tre 49
Shire of Vaca del Mar 54
Stronghold of Nan Crioch Tuatha 6
Grand Total 1,370

No comments:

Post a Comment

Thanks for your feedback. If you haven't already, please see the FAQ here: http://ottosturtle.blogspot.com/p/frequently-asked-questions.html
If requested, I'll try to get back to you as soon as I can. ;-) ~Otto