State Spreadsheets

[+BuckBrooke]

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:

 

PID

County (sort by county)

Designation

POS_SRC (SCALED or ADJUSTED)

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?

[+GEO*Trailblazer 1]

I do not know.

But I think it would.

 

Since I have not used it yet,I can not add the appropriate answer.

 

But the Excel spreadsheet from the National Map Corps works well.

I am sure it we be just about the same,with lots a data.

[+Black Dog Trackers]

Do it BuckBrooke!

[evenfall]

Buck,

 

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...

 

Rob

[+BuckBrooke]

I think holograph has a much more robust system for showing updates to Geocaching. Nonetheless...

[+Klemmer]

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!

[Wintertime]

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...

 

Patty

[+BuckBrooke]

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 December 9, 2005 by BuckBrooke

[Wintertime]

Great work, BuckBrooke. Thank you!

 

Patty

[+Black Dog Trackers]

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 December 8, 2005 by Black Dog Trackers

[+Klemmer]

Yes, like BDT said - way cool!

And thanks BDT for the sort tips. Works much better that way.

 

Thanks LOTS BuckBrooke.

[+GEO*Trailblazer 1]

That is awesome.

I will have to get busy and update it now for MO,ARK

[+PFF]

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!

 

Thanks for this excellent piece of work!

 

-Paul-

[+Black Dog Trackers]

BuckBrooke -

 

I can't download AL and ME.

[+BuckBrooke]

AL was a quick fix; I'll fix ME tomorrow, as I don't have the files at hand. Blah.

 

Added Friday 12/09/2005 8:11 AM: ME is fixed.

Edited December 9, 2005 by BuckBrooke

[+Black Dog Trackers]

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.

[+Klemmer]

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:

 

 

[evenfall]

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

 

 

     

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.

 

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

 

Rob

Edited December 12, 2005 by evenfall

[+Harry Dolphin]

So, where do I need to go to look for a 69=Regulatory Sign?

[+Klemmer]

Thanks, Rob. I figured you'd know what it was all about! It ties into the WAAS and LAAS systems after the STIMPY conversions, right?

[+Black Dog Trackers]

Harry Dolphin -

 

Near Sacramento California is the one and only regulatory sign. Hey, according to the description it is only 53.3 feet from a whybothermarking candidate just for you to eschew! Heh heh.

[+Black Dog Trackers]

Using the files BuckBrooke provided, I wrote a program and gathered statistics from them. I put some results here. The data includes the count of each monumentation type and the graph of monumentation date count by year.

[+Klemmer]

Nice work, BDT. Doesn't look good for the continued creation of monuments. I guess survey grade GPSr's are just killing monumentation business, huh?

[+BuckBrooke]

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 December 13, 2005 by BuckBrooke

[+Klemmer]

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.

[+Black Dog Trackers]

I added the nationwide table of Logo field contents.

Unfortuntely the vast majority of PIDs have a blank in this field.

Here are the top few and their counts.

552,163|blank

55,775|NGS

48,339|CGS

17,477|NCGS

8,692|MNDT

8,478|USGS

5,748|SCGS

4,798|NOS

Edited December 14, 2005 by Black Dog Trackers

[+Black Dog Trackers]

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:

 

313|BM

223|ROCK

205|WHITE

205|1

199|SBM

185|BLACK

179|GREEN

178|SMITH

174|CEDAR

170|PARK

170|2

165|BROWN

163|RED

161|LONG

159|SPRING

155|PINE

149|MILL

149|LAKE

148|SAND

148|MILLER

148|DAVIS

147|WILSON

146|HILL

144|FLAT

142|3

141|CHISELED SQUARE

139|BEAR

134|JOHNSON

134|INDIAN

134|BRIDGE

131|KING

130|OAK

129|WEST

125|POINT

125|BLUFF

123|CAMP

121|RIDGE

118|UNION

118|JONES

118|CLARK

118|CENTER

115|WOOD

115|BLUE

115|4

113|BEAVER

111|SANDY

109|TAYLOR

109|LEE

109|EAGLE

109|BUCK

107|CROSS

105|HIGH

104|JACK

104|HORSE

104|CHURCH

103|LITTLE

103|FOX

102|AIRPORT

101|ROSE

101|POLE

101|LINE

101|CREEK

101|5

[+southcarolinahwypatrol]

Where can I find the spreadsheets?

 

Thanks

[+Black Dog Trackers]

They are here on BuckBrooke's site.

[+Black Dog Trackers]

The top few counties:

 

8189|CA,LOS ANGELES

4528|CA,SAN BERNARDINO

3586|CA,KERN

3479|AZ,MARICOPA

3123|FL,BREVARD

2893|CA,RIVERSIDE

2837|CA,SAN DIEGO

2720|WA,KING

2658|TX,HARRIS

2443|FL,PINELLAS

2279|MN,ST LOUIS

2115|CA,FRESNO

2114|AZ,PIMA

2067|NY,SUFFOLK

2051|FL,HILLSBOROUGH

2004|CA,IMPERIAL