Jump to content

State Spreadsheets

Followers 2

Recommended Posts

Greetings, all.

I've downloaded all of the NGS county archive datasheets (after the November archive updates). I then merged them state by state, so I have a *really* big text file for each state including all the data sheets one after another. This is for text searching by state.

More usefully, I made Excel spreadsheets for each state. The data included for each PID for each state is:



County (sort by county)



Monumentation (pertinent to the current contest, but useful in general)

Mark_Logo (quick check for discs)

All_Rec_Date (look for old marks and for the recent recovery date)


Each file averages 2 MB. The spreadsheets are useful for me for some plans for web work over Christmas (going to take some time off).

I had planned to zip each file (30 MB over the 50 files) and put them on a webpage. Would this be useful to ya'll?

Link to comment



So Ah... What do you do in your spare time? :-)


Yeah, Post them on your website. You never know what may happen to this website.


Being able to download a state might be cool. You may even be able to broker a deal to use the updated data as a bone to toss Geocachng so their Data becomes a more current copy.


I like the way your thinkin...



Link to comment

Absolutely! I would use CA immediately! I have 5 CA counties in Cachemate for my Palm, but having the whole state in a searchable xls spreadsheet will be very handy! Thanks loads, BuckBrooke!


BTW: Did you download & combine the NGS Datasheets in their format (forgot the file extension type... is it .DAT?), or did you combine them in actual text (.TXT extension) files. The reason I ask: The NGS file format can be used by BMGPX, then GSAK to Cachemate. File size for all CA must get really ugly, though.....


Thanks either way!

Link to comment
File size for all CA must get really ugly, though.....

I had the same thought! I have San Mateo and Santa Clara counties in my Palm, and that's hundreds and hundreds of benchmarks. California has 58 counties, so do the math! Okay, yeah, most of them won't have as many marks as these two heavily populated counties, but still...



Link to comment

Ok. I've posted the files on this page.


The grand total is 3GBs of combined PID files (not surprising to holograph), 100MB of spreadsheets. Klemmer, the .DAT vs. .TXT file extension doesn't matter; it's the same format.


The below discussion doesn't come directly from the files, as it's fairly easy to get the state PID #s, from the archived state file from NGS that lists the PID#/designation.


There are a few surprises in the PID heavy states. The top handful of states by # of PIDs are:


1) CA - 62861 (8.7MB spreadsheet, 256MB combined datasheet)

2) TX - 48037 (6.7MB, 196MB)

3) FL - 43619 (6.4MB, 202MB)

4) NC - 36546 (5.2MB, 195MB)

5) MN - 25947 (3.6MB, 110MB)

6) NY - 22479 (3MB, 92.7MB)


By population, it's CA, NY, FL, TX, but NY's fairly small, FL has extended highways (9300 PIDs by FL agencies might be a higher # than expected?) and TX is very large, so that shifts their PID ranking around. These 6 represent 1/3 of the NGS database.


I was surprised that NC and MN made the top 5, but NC has its well oiled state GIS machine (>18300 PIDs by NC agencies), as does MN (>10400 PIDs by MN agencies). NC's place isn't too surprising, given how much PFF talks about them, but...the total PID file for NC is almost as large as TX, with 3/4 the PIDs, so there's a higher average of reporting per PID in NC, longer descriptions in the NC PID logs or both.


The next 20 or so state PID ranks generally correlate with decreasing population, with a few states moving higher by area/highways or overachieving state agencies. After that it's a mix.


As a note, I've changed my website around a bit (restructured the folders, as I put in a new menu system), so if you've linked to a page it *may* not work.

Edited by BuckBrooke
Link to comment

Way cool !


A minor suggestion for those who obtain this data:


Some of the columns should probably be changed to text formatting; B, E, and G (and possibly the ones beyond G).


The resons for each:


B. The designations that are just numbers format oddly (you could reformat but it's better to change the whole column to text.


E. The Monumentation code - if you want to use vlookup to insert the text from the NGS Monumentation code table by importing it into a spreadsheet, this column should be changed to text mode too.


G. The date columns, G and ones beyond G, won't sort quite right unless you either truncate them or change them to text. Changing them to text is good.


To change a column to text in Excel (probably similar in others), do this:

1. Click the column's head letter.

2. Click "Data" at the top of the window

3. Select "Text to Columns"

4. Click the "NEXT" button twice

5. Select the "Text" radio button

6. Click "Finish".

Edited by Black Dog Trackers
Link to comment
I was surprised that NC and MN made the top 5, but NC has its well oiled state GIS machine (>18300 PIDs by NC agencies), as does MN (>10400 PIDs by MN agencies). NC's place isn't too surprising, given how much PFF talks about them....


And I see that I need to get busy.....So many marks, so little TIME! :laughing:


Thanks for this excellent piece of work!



Link to comment

I analyzed by monumentation code and found 3 types with zero occurrences in the files:


Code Description

0 Other mark or intersection station

23 VOR antenna

24 REN antenna


There was one occurrence of:

69 Regulatory sign


and 2 each of:

DK Gravity reference mark disk

J Earthenware jug

Y Drill hole in brick


There were also 1,715 code ST which is apparently CORS station. The ST code is not in the NGS monumentation code list.


A graph of the monumentation year (first REC year) shows a huge peak at 1934 and 1935.

Link to comment

OK, you radio experts. What the heck is a REN antenna?

The section of the marks is "Aeronautical and Electronic Aids to Navigation". I'm familiar all the other ones (ex-USAF pilot, USCG safety course grad and longtime boater). Some are seriously obsolete (like airway beacons) and would be fun to find.


The only REN I found a reference to on Google was this, and I doubt it qualifies as a mark type:





Link to comment
The only REN I found a reference to on Google was this, and I doubt it qualifies as a mark type:




:P  :P  :P  :D

Gosh Klem, I thought you knew... I guess the benchmark gig has you looking at the ground all the time.


While you were looking down, NGS enacted a pilot program where GPS satellite data is cross compared between CORS stations using REN antennas in the 177 MHz Range. These are a full wave loop antenna, allowed to swing in the wind, yet used for a more stable match to the transmitter while bi-directional tropospheric ducting processes are underway. The unregulated Beat tone derived from Lobe to null is sub audible at 2.5 Hz, .27 Microvolts. Thus Resulting in a 5/9 sig report almost anywhere you ask, but only when WX is windy. The info is then downloaded via STIMPY conversion techniques so it can then be collated further. :D


C'mon Klem, You gotta keep up! :D:P Glad I could help get you caught up here...



Edited by evenfall
Link to comment

I think a large factor might be the rise of state GIS agencies/DOTs taking over placement of marks and stations. Those aren't getting reported to the NGS. It looks to me like there's two slopes; one from the 1940s to early 1990s, and then a steeper slope from the 1990's on, which I hypothesize is the GPS effect.


Also, it takes the state agencies a while to get projects together; I know that Albuquerque plans to put in 2,000 or so stations in a project soon, but it's taken them a few years. There might be a reporting lag, that would have the last few years look lower than they will be.

Edited by BuckBrooke
Link to comment

Sorting a state by Mark_Logo (column F) is interesting. In CA, many are the County ID's (as used in NGS data base county files, e.g. CA-059). I assume those are the actual "County Surveyor" (local names vary). Percentage respectable (eyebal 30+% of statewide marks. Also many are Metroplolitan Water District (MWD..) or equivalent.


Interstingly, the more metropolitan counties are predominantly as above (CA-XXX or MWD or similar), whereas the more rural counties (in the Sierras & northern CA) are much more represented by CGS, NGS & USGS. Unfortunately, many (50% or so) are blank as well. Does this reflect more added new ones / less destruction of older ones? More active County GIS programs? Did CGS / NGS support more rural operations, less metro areas? Are mountains more "important" than flatlands? My guess is some of all the above. Interesting demographics.

Link to comment

I added a Prefix table.


The largest population is in my own area: HV with 8,430 occurrences.


The least common prefix is SZ in Northeastern Minnesota and Michigan's Isle Royale with only 9 occurrences.


I didn't add a Designation table because it's too big; 371,239 records.

The top few Designations are:

































































Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Followers 2
  • Create New...