1

For a birthday present for my father-in-law, I want to get historical shoreline data viewable in Google Earth into a format I can read into R and use with the sf package, specifically table T-00267, that I will incorporate into a map of his neighborhood.

The KMZ file can be downloaded here, https://shoreline.noaa.gov/data/datasheets/t-sheets.html

I download shoreline extract for table T-00267, and get a Microsoft database file called ec10a01.mdb

I'm using Ubuntu, so I downloaded mdbtools. I run in the location the file is saved...

mdb-tables ec10a01.mdb

and see there are files t00267line_SHAPE_Index t00267line in the database. I then try to save the file like

mdb-export  ec10a01.mdb t00267line > t00267line.csv

And I get a corrupted file that seems like it has some shape data in it that I'm not accessing right. If there are other ways to get this data besides through Google Earth, I'm open to alternative solutions.


The historical data for MD was also used in the creation of these shoreline erosion datasets found here http://www.mgs.md.gov/coastal_geology/shoreline%20change.html


Final Product, shoreline change over time Tyaskin, MD

3
  • 3
    If the MDB has geometry, it's not Access but Esri personal geodatabase, which is a proprietary geometry format. You can't use Linux Access tools to copy that data.
    – Vince
    Commented Jan 22, 2023 at 15:29
  • 1
    This is (I think) a direct link to the zip file containing the .mdb file to save people having to fire up Google Earth and chase down the link from the KMZ: nosimagery.noaa.gov/images/shoreline_surveys/projects_zips/…
    – Spacedman
    Commented Jan 22, 2023 at 22:48
  • 1
    You can use OGR drivers in Linux with some configuration...
    – Spacedman
    Commented Jan 22, 2023 at 23:02

1 Answer 1

2

As mentioned in comments, the .mdb file is an ESRI Personal GeoDatabase file. There is a driver for GDAL/OGR so it can be read by open source tools, but there is some configuration required. You need to install ODBC tools and drivers.

The process is detailed here:

https://gdal.org/drivers/vector/pgeo.html

With that complete I can for example query the .mdb for layers:

$ ogrinfo PGeo:ec10a01
INFO: Open of `PGeo:ec10a01'
      using driver `PGeo' successful.
1: t00250bis1line (Multi Line String)
2: t00250bis2line (Multi Line String)
3: t00255line (Multi Line String)
4: t00266line (Multi Line String)
5: t00265line (Multi Line String)
[etc - it contains 123 layers]

and I could use ogr2ogr to convert these to something less troublesome, like a GeoPackage (which could be read simply into R using the sf package).

I can read the PGDB into R with all this config and setup, eg:

 library(sf)
 s = st_read("PGeo:ec10a01","ec10a01polys_83")
 plot(s$Shape)

enter image description here

(note if this is the only .mdb file you want then I don't mind doing the conversion for you and putting the output somewhere accessible, but if you have a lot to do I think its better you configure your system so you can do it...)

5
  • Thanks so much for the help. I've managed to download the data from another source but in principle I would like to be able to do this. I am currently stuck with the error "Unable to open datasource `PGeo:ec10a01.mdb' with the following drivers." ... "-> PGeo". I get the same error whether or not I update the odbcinst.ini file. I've tried exactly what it has in the instructions, and fiddled with it so the file paths match up with where things are on my computer. Any idea where I might be messing up?
    – Cannon
    Commented Jan 24, 2023 at 10:20
  • Stuck with what error?
    – Spacedman
    Commented Jan 24, 2023 at 10:23
  • "ERROR 1: Unable to initialize ODBC connection to DSN for ec10a01.mdb," "FAILURE: Unable to open datasource `PGeo:ec10a01.mdb' with the following drivers." ..."-> PGeo"
    – Cannon
    Commented Jan 24, 2023 at 10:28
  • Sounds like time for a new question, because we need to know how you've set up your odbc configuration files etc. Its non-trivial and we don't want to chase it in the comments here.
    – Spacedman
    Commented Jan 24, 2023 at 11:08
  • Thanks, I have posted that question here for anyone with a similar issue gis.stackexchange.com/questions/450319/…
    – Cannon
    Commented Jan 24, 2023 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.