Retrieve lat, long for large group of PIDs

[ArtMan]

Perhaps one of our data gurus can help me with this.

 

I have a spreadsheet with several thousand PIDs, and sundry info related to each PID, but not the latitude or longitude.

 

I want to add the coordinates for each PID in the spreadsheet, preferably in decimal form, so I can teach myself some mapping of the data.

 

Although I am not a programmer, I probably could do this by retrieving the datasheets, capturing the coordinates with GREP, and parse it out This would seem to require downloading state archive files from the NGS FTP server (ftp://ftp.ngs.noaa.gov) for states with a lot of PIDs, and retrieving individual datasheets (http://www.ngs.noaa....-bin/ds_pid.prl) for the rest.

 

This seems tedious.

 

Alternatively, I could probably run WGET against the list of PIDs (e.g., http://www.ngs.noaa.gov/cgi-bin/get_image.prl?PROCESSING=list&PID=AA1234) in a BAT file (Windows) or script (LINUX) to retrieve the datasheets, then GREP the info.

 

This seems rather Rube Goldbergish to me. Any other thoughts?

 

Thanks for you help

ArtMan

[TillaMurphs]

ArtMan,

 

I have been trying to figure this out as well. I was able to take my list of PIDs and create a shapefile database via the NOAA (NGS) website. My current plan is to attempt to figure out how to convert that shapefile database to a CSV file so that I can have access to the latitude/longitude. If you find some other way to do this, please let me know.

 

Thanks,

[kayakbird]

ArtMan,

 

I have been trying to figure this out as well. I was able to take my list of PIDs and create a shapefile database via the NOAA (NGS) website. My current plan is to attempt to figure out how to convert that shapefile database to a CSV file so that I can have access to the latitude/longitude. If you find some other way to do this, please let me know.

 

Thanks,

 

ArtMan,

 

Did you get a zipped file; which, when opened, has a " _dbf Text Document" about have way down the list?

 

That text document can be copy/paste into Excel and converted via the 'DATA'> 'Text to Columns' feature.

 

kayakbird

[TillaMurphs]

 

Did you get a zipped file; which, when opened, has a " _dbf Text Document" about have way down the list?

 

That text document can be copy/paste into Excel and converted via the 'DATA'> 'Text to Columns' feature.

 

kayakbird

 

HEY! That looks like that will work (for me). THANKS kayaybird!

[ArtMan]

ArtMan,

 

I have been trying to figure this out as well. I was able to take my list of PIDs and create a shapefile database via the NOAA (NGS) website. My current plan is to attempt to figure out how to convert that shapefile database to a CSV file so that I can have access to the latitude/longitude. If you find some other way to do this, please let me know.

 

Thanks,

 

ArtMan,

 

Did you get a zipped file; which, when opened, has a " _dbf Text Document" about have way down the list?

 

That text document can be copy/paste into Excel and converted via the 'DATA'> 'Text to Columns' feature.

 

kayakbird

No - the zip files I've seen are just the text files of data sheets. Where did you get this one?

ArtMan

 

[kayakbird]

ArtMan,

 

I have been trying to figure this out as well. I was able to take my list of PIDs and create a shapefile database via the NOAA (NGS) website. My current plan is to attempt to figure out how to convert that shapefile database to a CSV file so that I can have access to the latitude/longitude. If you find some other way to do this, please let me know.

 

Thanks,

 

ArtMan,

 

Did you get a zipped file; which, when opened, has a " _dbf Text Document" about have way down the list?

 

That text document can be copy/paste into Excel and converted via the 'DATA'> 'Text to Columns' feature.

 

kayakbird

No - the zip files I've seen are just the text files of data sheets. Where did you get this one?

ArtMan

 

http://www.ngs.noaa.gov/cgi-bin/ds_pid_sf.prl

 

ArtMan,

 

Hopefully the above link will take you to the correct page - if not maybe this file path will:

 

Go to the NGS DATASHEET PAGE, click on the second box down [shapeFiles],then the first line [PIDS] (it will accept up to 1100), copy/paste your pids, scroll down to METADATA FORMAT and toggle HTML, toggle the line with 'ZIP FILE' in it, enter a file prefix in the box. Submit.

 

For my working Excel file I like to get my L/L from the regular DS download page by selection: {MOVE}the above station list to a File->Print, Window) because they come without spaces between DDMMSS and I can run a comparison column to see how far off my FOUNDS are. (because NGS uses periods for space holders, it is awkward to work with other than whole seconds) MEL

[AZcachemeister]

I use downloads from Jim's site (along with NGS>>GPX, and GSAK) to do everything I need to do with Benchmark data.

Once I have the data in GSAK I can slice it, dice it, filter it and export it twelve ways until Sunday.

[kayakbird]

 

(along with NGS>>GPX, and GSAK)

 

 

AZc,

 

Have you figured out a work around for GSAK's major flaw of ignoring 'UNK' in the Placed column? It uses the first history date instead. I'm afraid that I have missed a few 1908 PLN's for my target list working north of Lund UT today. That is one reason I like the county shape files in an Excel file - sort for date and change UNK's based on a SWAG by name. MEL

[foxtrot_xray]

 

(along with NGS>>GPX, and GSAK)

 

 

AZc,

 

Have you figured out a work around for GSAK's major flaw of ignoring 'UNK' in the Placed column? It uses the first history date instead. I'm afraid that I have missed a few 1908 PLN's for my target list working north of Lund UT today. That is one reason I like the county shape files in an Excel file - sort for date and change UNK's based on a SWAG by name. MEL

It's technically not a flaw - GSAK requires a date in that field - you can't enter a 'non' date. So, NGS»GPX will pick the first 'real' date it finds in the history to populate that field with. (In previous, older versions, it used to hard-code the date to something like 1/1/1800, or 1/1/1900, but that proved more.. inaccurate than having an actual history date.)

 

With GSAK 8's custom column feature (which, d@mn it, now I have to rewrite some screens in v3 to take advantage of those..) you can create a date field that will allow nulls I think - if not, a text field will do - where you can then have 'UNK' placed in there for those types of sheets.

 

--Mike.

[TillaMurphs]

ArtMan,

 

I have been trying to figure this out as well. I was able to take my list of PIDs and create a shapefile database via the NOAA (NGS) website. My current plan is to attempt to figure out how to convert that shapefile database to a CSV file so that I can have access to the latitude/longitude. If you find some other way to do this, please let me know.

 

Thanks,

 

ArtMan,

 

Did you get a zipped file; which, when opened, has a " _dbf Text Document" about have way down the list?

 

That text document can be copy/paste into Excel and converted via the 'DATA'> 'Text to Columns' feature.

 

kayakbird

No - the zip files I've seen are just the text files of data sheets. Where did you get this one?

ArtMan

Artman, I am late getting back to this thread. I found it in the location that kayakbird described above.

[ArtMan]

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 spreadhseet — 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. I have never done anything like this before, so I'd appreciate any feedback.

 

-ArtMan-

 

(edited to add file sizes)

Edited March 16, 2012 by ArtMan

[ArtMan]

Incidentally, the Geocaching.com snapshot (ca. 2002) of the NGS database has 736,425 PIDs, according to the homepage of the benchmark section.

 

My count of datasheets downloaded last month includes 751,984 marks, a difference of 15,559, or a 2.1 percent increase. However, since a certain number of stations included in the decade-old database are no longer publishable, the number of new marks added over the past decade is larger.

 

ArtMan