A little backstory. When I first started in my current position back in 2007, I was a wet behind the ears newly licensed surveyor taking over for a retiring surveyor who had been at it longer than I'd been alive. His system for tracking the physical files involved his elephant like memory of what was done and where and he conveniently walked out the door with it when he left. Realizing that this would be my first big challenge that first winter I set about going through each and every file to determine where and what work had been done and georeferenced the information in an Access spreadsheet so that I could plot the records when a new job came up and pick up where he left off in 1993. It's been continuously updated and is one of the secret ingredients to my special sauce.
Fast forward and the physical files containing copies of plats, recorded easements, plots, ect., have grown to the point where that single manila folder that started in 1993, is now an accordion file jammed with dozens of various jobs and keeping track of what is in what file and how they relate, has become rather tedious. Being that necessity is the mother of invention, I'm cobbling together a second Access spreadsheet to index those hard files. They're currently loosely organized by MTRS but with so much overlap in areas, it's becoming progressively more difficult to keep it all straight and know what accordion file contains what. With winter setting in and construction season in the rearview mirror, I'm tasking my young helper with going through the files and helping me build an index of all these files. How to best organize that index in a searchable spreadsheet is the question so we're not spending valuable time during our busy summer trying to find the equivalent of a needle in a hay stack. The key is it must be expandable to take into account whatever additional work and files might be added in the future.
I'm not an Access savant and I'm just looking for some suggestions, ideas or examples from my distinguished peers and of course, the rest of you. ?????ÿ
Cheers.?ÿ
Willy
Not using Access or database type of file search system, but for what it's worth, here is what I have done:
In the early 2000's my company developed the base parcel ownership layer for 6 Florida counties, including my home county (and, all adjoining counties), using high resolution georectified imagery as the underlying ??truth? for positioning. (If you have no access to the base parcel layer, inquire with the property appraiser office for a copy, or copy/paste on-line images into AutoCad, or print screen/copy into AutoCad, etc.). From the mid 1990's on, all of our boundary work is tied to state plane coordinate position. For nearly 20 years, as each boundary has been completed, the basic boundary, along with the job number, is drawn onto our GIS dwg. map (AutoCad/Carlson addon), in correct state plane position. Also, each job number is hyperlinked to an external (instantly viewable and printable) pdf file of the completed map.
In addition, jobs that were completed prior to using state plane coordinates have been translated, and placed onto the GIS dwg. As Florida is a PLSS state, each Section has its own .crd (Carlson) point file, and all job points within a Section are placed into its own (Section #-Township #-Range #) crd. point file.
We have already placed about 10,000 boundary surveys, in true state plane position, onto the GIS, along with the majority of all highways/streets/roads. Also, all survey monumentation set/recoverd are shown on the GIS, in true state plane position, along with point number and description. Several hundred Section breakdowns are also drawn showing recovered/set control corners.
This GIS dwg. is now our go-to source for research. From this one GIS drawing we are able to instantly view everything we have done in the area of need, and are able to select/print drawings, select/print street right of way data, view what monumentation we have previously set/found, and able to select/print point coordinates.
I have done the database thing using both ??Info-Retriever?, and ??Open Office? databases, but I cannot imagine any system that would be more useful than a headsup viewable active map. Also, I use 50? flat screens for AutoCad, photogrammetry (Pix4d & Agisoft Metashape), etc., but when I upgrade I'll be going bigger. The large screens are highly recommended with so much viewable info instantly available. I have used both monitors, and TV screens, for PC's, and cannot tell the difference. TV screens are so much cheaper. For 10 years all we have used are flat screen TV's. I have read all the blah blah about monitor specs, etc., and how they are so much better for blah blah type work. Monitor aficionados knock yourselves out.
That's a really big question.?ÿ In the last stage of my career I was pushed in to the "Survey Records Manager" position at a large outfit and given a steaming pile of dysfunctional Access databases,?ÿ out of date "Index Books", and "Project Folders" spread throughout the building in individual cubicles, the back room, sent to HQ,?ÿ grabbed by the legal team never to be returned, etc.?ÿ After a few weeks of spelunking I told my boss it would take literally years to straighten out and I didn't want to be involved but he said he's got the money and wasn't asking, he was directing me to take charge.?ÿ I'm the type to obey the boss so I said OK and he breathed a sigh of relief and told me he's asked half the LSs in the company to do it and they all said no, they'd pirouette laterally out of his branch or quit instead.?ÿ Lucky me ?????ÿ
Four years later there were 200,000 indexed documents on our Intranet dating back to 1905, viewable/downloadable/searchable by anyone with access to our Intranet.?ÿ
| Office Unit | Field Unit |
?ÿ There's a huge amount of stuff to think about when designing such a system.?ÿ Are you dealing with 500-10,000-100,000+ records??ÿ What are the roles of everybody that interacts with the database, view only, record creator, edit permissions, db manager??ÿ Every role should be presented a unique view of the database to prevent user created glitches. What are the domains of every field in the database, i.e., can a person enter "bloob-bloob" into a coordinate field or enter coordinates without also entering a valid datum, worst is allowing an indexable field to be blank and the record is still valid.?ÿ Key is how little keypunch work is required to?ÿ properly index a record, more is not better and redundancy should be impossible.?ÿ Creating a unique key for every record is critical, if a single field won't do it maybe a composite key will work, or, God forbid, a GUID.?ÿ Granularity is important, do you really want to index every document in a project folder, every page in a fieldbook, etc., or consider them as a single indexable bag that contains that fieldbook's pages, for example??ÿ I could go on but want to emphasise GIGO should be your guiding light.
Database software selection is critical and Access is woefully inadequate for anything more than a flat file database.?ÿ It's for folks who think you punch in a few structures and Access will figure out all the problems for you.?ÿ When dealing with 10s of thousands of records and dozens of users simultaneously it bogs down, referential integrity fails,?ÿ backups are incremental, I could go on.?ÿ I used Interbase, cheap (free for basic systems), transactional integrity, lightening fast, expandable, live mirroring to a different server(s), rollbackable to the last valid state and best of all, (almost) live updates of Webpages using functions as transactions occur.?ÿ It does require creation/maintenance of the database using DDL, but it's (mostly) ANSI standard code, so if you boss says we're switching to MacOS, Linux, Android, you're good to go. DDL is practically open source.?ÿ A beginner's book about database creation/maintenance will get you onboard fairly quickly and help you keep your eye on the ball as you develop your database.?ÿ I'm sure there are just as good inexpensive DDL based database software out there.
My final advice is immediately implement a scanning project to immortalize your paper records and insist all new fieldwork be scannable and all DC readouts, Starnet reductions, Record Map products, etc., be preserved.?ÿ Funny thing about paper originals/folders is they somehow disappear or burn up.?ÿ We spent $80,000 scanning every shred of paper records, indexed them on several remote servers, and can guarantee recovery.
OTOH if you only have a few file cabinets of records, don't bother, the "guygal" who knows where everything is sufficient.
I bought 2 Dell SE2719HR Low level gaming monitors for black Friday last year. Nothing super fancy except the 75MHz and 1000000:1 contrast.
Total cost was lest than 265 American.
Truth be told, I will need to buy a few VESA adapters for my next stand, but that only adds about fifty more dollars.
They are the best thing since sliced bread for me, and even had one of those ridonkulous curved 42in Dell Jons and didn't really like it.
?ÿ
?ÿ
The FME interoperability module is going to be anyone's friend when migration of large datasets is being planned.
Now that Autodesk and ESRI are playing with each other more friendly, it has really helped the integration of both platforms to each other recently.?ÿ Although there are people on both sides claiming it's a huge arduous task and will take years.....they just don't want to lose control of their cushy pension coaster jobs from what I've witnessed.
?ÿ
YMMVDOC
@mike-marks Brilliant. Thank you for your well thought out response to this. Needless to say the most important thing is the initial design and expectation of the functions it is to serve. The challenge that I have is just keeping track of where all of the data (both digital and hard copy) from our surveys is kept and avoiding duplications. What started as a single job in a subdivision has subsequently grown into a large accordion folder (or several) containing work in adjacent subdivisions and rights of way, with many newer jobs built off of and nested inside of the older job files, constantly being built out in a somewhat haphazard manner being driven by client demand. One new project I'm working on now, my party chief had created a new folder for a new job in the same subdivision where I'd already completed work leading to two separate files under different work order numbers, containing copies of the same plats. The database has to make it easier to find all the relevant files from past work containing the complete history of work done in the area, as opposed to just hitting the print (easy) button a generating a brand new copy of the same plat. I had one file dating back to my early days when my IM was doing much of this and rather than search through a file for the plat, he would print a new one. I found 7 copies of the same plat in that file because it was easier to him to hit print, than to actually look for it. Granted, he was a little lazy and file large, but a well designed database that can allow the user to run a query for a particular record and provide the exact location of the file, possibly nested inside an accordion nested in a file under a different job with a different name and work order that was used in the initial work. I think given the limitations of my needs, Access will provide sufficient functionality as it has worked out well as my main survey tracking database, which lacks the granularity to point the physical location of these nested files. It essentially just gives a plottable coordinate on our land base maps with tables identifying the work order number, year done, field book and pages, MTRS and a few salient comments. What it doesn't do is tell where that information is buried within ten large file cabinets that fill a room. While digitizing it all would reduce the amount of space used and provide a back up, on the flip side if I visit a particular subdivision ten times over the life of the file, I will be printing that plat ten times as opposed to once and reusing the same plat each time, complete with any original hand written notes on it.?ÿ
Viva la GIGO!
Just kidding.?ÿ
