I bet you guys know how to do this?

[TillaMurphs]

I would like to see the locations of all of the TillaMurph's finds on a map of the US (probably on Google Earth).

 

We have all of our benchmark searches/recoveries in an Excel spreadsheet. Each row starts with the PID number in the first column and then has miscellaneous info like the disk type, date monumented, date found, etc. in subsequent columns. (We don’t have coordinates in the spreadsheet though).

 

Is there an easy way to take the first column of 500-plus PIDs and somehow generate a kml, kmz or whatever type file that I could import into Google Earth (or similar) to see the distribution of our finds on a map?

Just to clarify, I don’t care about date found, etc. I just want to see a spot/icon on the map at the location of each find (and maybe the PID number, but even that is not an absolute requirement).

 

Thanks for any help.

[foxtrot_xray]

Hmm..

 

Well, you would need to get the coordinates for the PIDs first, before you could need to get them into a format suitable for GE. (DSWorld may do this, but others would need to comment..) There's nothing 'built-in' to GE to look up a PID and get it's coords, unfortunately. You could do a PID lookup for a shape-file from the NGS sits, and then you'd get the locations (lat/lon).

 

Once you got that far, you'd need to get it into a KML/KMZ format that GE can read. Luckily, someone made an online tool for you:

http://kmltools.nobletech.com/csv2kml

 

So, if you can export the PIDs to a text file, one PID per line, then go here and select a shapefile, click PID', and then use the file to select your txt file. Once you get the shapefile, go to that website to get your KML.

 

Now, if you stored your stuff in GSAK, it's be super-simple, as GSAK already has the lat/lon and can export KML files.

 

(By the way, my map - by 'county' is here. Warning. It's huge.)

 

--Mike.

[+LSUFan]

As Mike said, You could generate what you need thru GSAK. I was able to do some filtering and global replacements, to show benchmarks I have logged thru the NGS (then I marked them as found). I did a filter off of my initials in the datasheets.

 

It can be done, but may require a little keyboard effort.

 

Some quick questions?

 

1. Do you log with the NGS?

 

2. How many different states and benchmarks have you logged (either GC.com or NGS). I see where you mentioned 500 or so.

 

Holograph may have the easy answer for this one, as he is able to keep up with who has logged on both sites.

Edited February 1, 2012 by LSUFan

[kayakbird]

DSWorld (Stations > Published > By PIDs) will accept an Excel column of PID's - a bit slow and I don't know how many - you might want to break it down by state or years in your GE 'My Places' file. MEL

[+LSUFan]

DSWorld (Stations > Published > By PIDs) will accept an Excel column of PID's - a bit slow and I don't know how many - you might want to break it down by state or years in your GE 'My Places' file. MEL

 

Hadn't even thought of that. Just copy and paste the PID column of your spreadsheet. Yes, that would be real easy, especially if you just wanted to see these on google earth.

 

Way to go Kayakbird.

Edited February 1, 2012 by LSUFan

[kayakbird]

Just built a file in order of logging - except I forgot to mention a feature I do not like, nor see any reason for, but DSWORLD gives you a station file and a benchmark file. Since I don't have that many Tri's for the year I will drag/drop them into order. Took about five minutes to do 114.

 

I will use this file to edit photo direction and scroll through it as I submit the recoveries. MEL

Edited February 1, 2012 by kayakbird

[2oldfarts (the rockhounders)]

I use GSAK and mark each benchmark as found after finding it. I then move all the found/destroyed and not found (that I will not be looking for again) to a special database "All Recoveries". I can then export a GPX file and drag & drop it on Google Earth and it will open and show the proper location for each mark in that database. I also drag and drop it on Topo 9 so when we are in an area where we have benchmarked before, we can see which ones we have already done.

 

When you mark a benchmark as found in GSAK it will automatically set the date found as the current date, so you might need to edit the date, to the date it was actually recovered.

 

I find it best to move the benchmarks to the "All Recoveries" right after I get done logging them on GC.

 

John

[AZcachemeister]

I use GSAK and mark each benchmark as found after finding it. I then move all the found/destroyed and not found (that I will not be looking for again) to a special database "All Recoveries". I can then export a GPX file and drag & drop it on Google Earth and it will open and show the proper location for each mark in that database. I also drag and drop it on Topo 9 so when we are in an area where we have benchmarked before, we can see which ones we have already done.

 

When you mark a benchmark as found in GSAK it will automatically set the date found as the current date, so you might need to edit the date, to the date it was actually recovered.

 

I find it best to move the benchmarks to the "All Recoveries" right after I get done logging them on GC.

 

John

 

Also my basic protocol. (although I see no need to clutter my map with marks I am already done with )

It does look like kayakbird has the best answer to fit TillaMurphs specific situation.

[+billwallace]

Export the name,desc,latitude,longitude columns to a csv file and copy and paste them into the Or paste your data here: box at GPSVisualizer

and the tutorial for that box showing an Excel example Tutorial: Building Plain-Text Waypoint Files by Hand

Edited February 2, 2012 by billwallace

[TillaMurphs]

See – I knew you guys would know what to do. !

 

I took some advice from all of you and came up with two solutions (so far).

 

1. 
    
   
2. I followed Mike’s idea and used a text file of the PIDs to create a shapefile database via the NGS website. It didn’t take very long. Then I downloaded a free program called ArcGIS Explorer. This works pretty darn well. It reads the shapefile and lets me look at all the points superimposed on any of 13 choices of basemaps. I like it.
    
   
3. I took kayakbird’s advice and used DSWorld as he suggested. It was VERY easy. It took a while to do all 530 PIDs but it was no big deal. I just started it, went and did something else, and came back and it was all done. Piece of cake.
    
   

 

For a long term solution it seems like I should work on developing a GSAK database.

 

A question:

 

Is there a way to go from a shapefile to a KML or a GPX? (Mike I saw the link you listed but it seemed to want a CSV file to start with – or maybe I am missing something?)

 

THANKS Mike, LSUFan, kayakbird, 2oldfarts, AZcachemeister and billwallace.

[kayakbird]

See – I knew you guys would know what to do. !

 

---- It took a while to do all 530 PIDs but it was no big deal. ----

TillaMurphs,

 

Have you happened to check if all your points were pinned on GE? Scrolling through my Jan list while doing NGS recoveries I have found several that are not there.

 

Somewhat related: does anyone know how to edit in a FOUND here pin or error off measurement line at the point in question? It is very tedious to drag/drop up several dozen from the bottom of the file.

 

Thanks, kayakbird

[+EdrickV]

A question:

 

Is there a way to go from a shapefile to a KML or a GPX? (Mike I saw the link you listed but it seemed to want a CSV file to start with – or maybe I am missing something?)

 

THANKS Mike, LSUFan, kayakbird, 2oldfarts, AZcachemeister and billwallace.

 

The text file included in the shapefile archive is a CSV file with the format described in the first line. And GPSBabel has an input option for that. (Listed as "Universal csv with field structure in first line" in the drop down box.) However GPSBabel can't handle the date format used. It wants yyyy/mm/dd rather then the yyyymmdd exported by the NGS site. Don't know if there are any other potential issues since the unparsable date is a showstopper for GPSBabel. Don't know about any other programs.

 

If you were to get the datasheets instead of using a shapefile you could save those as text files and run them through NGS->GPX. (Can only do 200 datasheets at a time if getting via PIDs, so if you've done a ton of benchmarks you'd have multiple files, which could either be loaded individually or merged via GPSBabel.) I personally would recommend converting the GPX to KML via GPSBabel before loading it into Google Earth, Google Earth would treat the GPX file as a Geocache Pocket Query but if you use a KML file instead you can bring up datasheets in your browser from within Google Earth. Note that with GPX/KML files the date slider will be active, so you can show/hide benchmarks by year.

[kayakbird]

Pegging FOUND/NOT FOUND onto Google Earth --- Is anyone doing much (or any) of this?

 

Original Question from TillaMurphs: 'I would like to see the locations of all of the TillaMurph's finds on a map of the US (probably on Google Earth).'

 

DSWorld (Stations > Published > By PIDs) will accept an Excel column of PID's - ---- MEL

 

I have been building GE 'My Places' files of a few or dozens in date visited order which I then scroll through as I get around to doing NGS recoveries - works great as a memory jog for those that need a DSWorld>Submit>Single Hand Held Position correction (and a great virtual vacation through the keyboard!).

 

After a good MORC Hunting Trip last week to the Missouri Breaks above Judith Landing I decided to put all my MORC activity into one file sorted more or less in downstream order from Three Forks (already had a many column Excel file of all 500 plus Montana MORC's numbered that way). All the PID's loaded up fine (except for that irritating glitch of being broken into separate files for TRI's & BM's, which means doubles on some).

 

But the real aggravation!!

I want to stub in cutsie stuff like kayak accessed trail heads, camp sites, critter notations ([saw a Momma Bobcat and two half grown kittens swim from RR to RL at RM 75.45 Tuesday morning], Horsehair Worm in Mud Camp on Fort Peck Reservoir, Western Hognose Snake (Heterodon nacicus) and, on and, on and, on).

 

Is there anyway to have GE automatically place these add in's in the proper order within the tabulated point file on the left side of the screen?

 

Some times they end up at the bottom of the list, other times in the Temporary Places file. I can live with dragging and dropping either the horizontals or verticals into one file; but it seems that if I do too much of that too fast GE goes on coffee break and closes, so you better SAVE after every three our four manipulations. I have resorted to adding a 'CARGO' folder, stuffing a few geographically related add-ins into it, dragging it to associated place in the tabulated column, carefully dragging out of it, SAVING, and back for another load.

 

Tedious!! Hope I get it done before I head to Moab in about three weeks.

 

Thanks for any (not too technical, please) help, kayakbird

 

Eye candy, but NOT A MORC

 

Don't know head from tail! MEL

[ArtMan]

I posted this earlier this year, but it might be useful info on this topic. (My files will be slightly out-of-date as they are based on NGS data from February.)

 

Partly to answer my own question, and partly to learn some Linux tools, I extracted key data from 776,099 benchmark datasheets for 50 states plus DC.

 

In a delimited file — easily imported into your favorite spreadsheet — it's one line per station, like this:

 

HV4442;DC;DISTRICT OF COLUMBIA;WASHINGTON MONUMENT 1913;NAD 83(2007);38.88946741;-77.03524049;ADJUSTED;

 

with the following fields:

PID

State

County

Designation

Datum

Latitude

Longitude

ADJUSTED, HD_HELD2, SCALED, etc.

 

There is one file that covers the entire U.S. (10 mb), plus the following regional files

 

North Central (2.2 mb)

IA, IL, IN, KS, MI, MN, MO, MT, ND, NE, SD, WI, WY

 

North East (1.9 mb)

CT, DC, DE, KY, MA, MD, ME, NH, NJ, NY, OH, PA, RI, VA, VT, WV

 

South Central (1.8 mb)

AR, AZ, CO, LA, NM, NV, OK, TX, UT

 

South East (2.1 mb)

AL, FL, GA, MS, NC, SC, TN

 

West (1.9 mb)

AK, CA, HI, ID, OR, WA

 

The links will take you to Google Docs, where you can download the files in 7z format. There is a readme file in each archive.

 

(If you don't use 7-zip, you should. It's noticeably faster than winzip, and compresses significantly better. Download it from http://www.7-zip.org/)

 

Feel free to use, mash up, or add to your website.

 

-ArtMan-