Data Base ideas for surveys

> I have been in my solo practice for 5 years now. I made a big mistake by not creating some kind of data base to log my surveys so I know what I've done in areas that I may work in next. I guess I assumed I could remember? HAHA What a joke!
> Looking for ideas to create a simple database to either type in address or section, township and range. I am not a GIS guy and do not have software. Was thinking about maybe using google earth or MS Streets and trips. Would appreciate any ideas!

So you have no list at all of where you have worked?? If you have any kind of job number/address list I'd start with that. If you don't, I might sit down with the files over a weekend and start logging them into a spreadsheet. You can always get google earth pro and just drop them in. it will geocode on the fly based on address.

NOW is the time to start, (well, day 1 was really, but I'm not judging) I can tell you that when I bought my business one of the key points was a good database of previous work. without it, I might not have bought the firm.

It's old fashioned, but .....

I still like spotting jobs by job numbers on quad sheets in rural areas and tax maps in built up areas. Paper is not dependent on changing technology, computer crashes, software updates, etc.

Here are a few ideas:

1. Very simple - type everything into a ascii file as blocks of data. You could search using the "Find" in most any word processor. Very low tech and easy to do.

2. Depending on your drafting software and if your state has state wide aerial mapping available for purchase or download - place the aerial mapping into your CADD software and then rely on zooming into the area you will be working in to see if you have already placed a individual job block into the aerial background. The more CADD literate folks might even have a neat trick for assigning an address to the individual survey blocks and searching them.

3. http://www.bluemarblegeo.com/products/global-mapper.php - the best money you will spend on a product.
This is what I use. I have aerial mapping, topographic quads, county wide street line work and names, NGS Benchmarks, recorded plats, my jobs, etc, etc, etc.....
Using this, you can place your drawing in associate any data you want to with the drawing (client name, street name, address, etc) All of those values will be searchable.
It was not overly difficult to get used to - if I can do it, anyone can.

Steve,

I came up with a basic GIS type database to do exactly this when I started here about six years ago. My predecessor had a memory like an elephant and when a particular job came up, he'd walk over the file cabinets rubbing his chin, yank out some old job he'd done ten years ago, toss it to me and say, 'use this to get going'. When he retired, he took that elephant memory with him.

So what I did was this. Using our township maps in CAD, I spent a winter going through each old job and looking up their rough SP coordinates and plugged these into an Access spread sheet. First column was 'work order #', second the northing, third the easting, then the file's name, followed by the township,section, range, 'ie. 23,14N,2W' and last was a column titled 'comments' giving a brief account and history of the file along with key words like subdivision, road, landmark names and anything else that could jog my memory. After additional work I'll add a date and reference to the Fb# & Pg in the comments section.

I can search the database for anything using 'Find', but the really slick part is by using an ODBC connection in autocad, I can link to the database and plot it using the northings and easting on the CAD township maps. They appear as squares marking on the map the general location of the survey and extent, more entries for larger jobs bracketing the area. Then I can go into 'edit styles' and change the display to give me the work order #, file name, whatever record I want displayed.

I'll have an engineer come into my office and ask if we've done any work in a particular area and I can usually find the file in three minutes or less. There few things more aggravating than getting out to a job and realizing that you've already done work in the area, but now you have to drive an hour back to the office to grab the right files. It's gotten to the point where about 7 out of 10 of my jobs are just building out old ones.

Hope this helps.

Willy

I made the big mistake of numbering my surveys for the first 10 years or so 91-001 . . .91-002.

Now everything goes into a folder for the state(all Ohio), a three letter designator for the county, three letter designator for the township and a three number designator for the lot/section. if needed I also use designators for the Tract or city or village, then the name.

So a 10 acre survey in Trumbull County, Braceville Township, section 10 that was completed on March 15, 2012 at 2:20pm, would be TRU-BRA-010-SMITH-10A 031512 1420.

I have started putting all these files in an open directory so that I have everything and only need to scroll down to the county, township and section to begin looking.

You could actually add more data to the file name.

You can name photos, descriptions and everything else the same way and leave them in the same open directory, with the drawing files, DXF copies and PDF copies . . . the whole shootin match in one folder and you don't need no stinkin data base program.

These are some great ideas. I think someone should do a "how to" for dummies. I like the Google Earth idea. Been doing it on a simpler level but you guys got me thinking....and that could be dangerous.

I strongly second the Global Mapper idea. It is very affordable, easy to use and has great support if you need it.
I don't know if Jon has taken advantage of their webinars but they are very good.

When I started my own business in 1997 I created an access database (microsoft). It has fields for job number (which is year-xxx), client, county, state, status, comments, billed?, paid?, etc.

Of course the type of work I do is not property, so I don't have a lot of lot surveys, etc to reference. I do about 100 to 120 jobs per year.

I also have another database that links to the first one above that references CD (now DVD) number to job numbers. I have 178 numbered CD's/DVD's at this point with all project archives on them. For example, a particular DVD may have 20 different projects on it. I have an app that I type in something from the job name or pick the state (I have worked in all 50), and it will list all jobs matching that. I then click on a job number from that list and it will give me the DVD(s).

Of course, since this is all in a database I can easily create custom queries and reports.

This system has worked very well. I also have a very good memory, and can remember jobs from 10 years ago (but not what my wife told me yesterday).

Here's an option:

http://www.agtcad.com/products/retriever/index.html

Really a super little program and easy to use.

I use a simple open office spreadsheet. I drop my job numbers and control pts into google earth as well. Info-retriever looks really nice and have often thought of trying it out.

I'll send you a KML that I made out of part of my job files if you want to see it.

We print out Google images for most proposals, and when we do, we mark down the Lat & Long on the print out.

When the proposal turns into a job, the Lat & Long gets entered into our job sheet and into a specific Google KML file along with the Job Number.

With that layer turned on, it's easy to graphically see where you've done projects (well, approximately anyway) and what those Job Numbers are.

In addition, we have an Access Database that contains:

Proposal Number
Job Number
Date
Client Name
Sub-Client Name (Agency usually)
Project Description
Project Location (City & State)
Map Page Number
Type of Work
etc.

We are then able to search by any of these fields to find things that this old mind cannot remember!

If you want to keep it real simple. I took over an old timer's business that all of his drawings were done on mylar and the jobs indexed in a card file system by Sec. Town, Range. The card files were not convenient and I had no backup of the original drawings. So my daughter is scanning all the drawings and making a data base all in one step. She names the PDF's: Town-Range-Sec-1/4(or GL) Job Number Client Lot#-Plat(if it has one)

When I finish a new CAD drawing I save as PDF the same naming. Almost all my searching is by Sec. Town Range or Subdivision name, but occasionally someone calls and has a name. Windows lists them in order for me or I can do a search for the other info. It is working great, I can talk to a client on the phone and research at the same time and see the PDF drawings in their area.

Good Luck and you need some kind of system not only for your failing memory, but it will add to the selling value of your business.

Yeh...

what I like about the file naming system is that it's not dependent on any software whatsoever.

I tried many of the database softwares back in the mid 90's.

Specialty software that was good to go, as long as the software was available and would run on the ever changing computer hardwares.

I even made backups that would go into newer software, but it was software driven and that bothered me.

This is why I went to simply renaming the filenames themselves . . . the simplest of simplicity.

Filemaker Pro.

My system is not so much a database, but an AutoCAD based master or index map.

Once a job is finished I make a copy of the survey model and give it the job name then xref and bind it into a master model stored on a memory stick. Each survey appears in its proper place in absolute and relative terms like an incomplete jigsaw puzzle. It's easy then to input a new survey location to check for mutual boundaries etc.

Artie,

Doesn't that CAD file get awfully large? I'd be worried about crashing it.

I have a CAD drawing with every piece of fieldwork I've done going back to 2005 in state plane.
Much as above, the drawing serves to hold a bunch of XREFs to each individual job. I did not really think this up myself, I combined the tools at hand after hearing Robert Young talk about how he had 40+ years of fieldwork in state plane in one star*net project and was continuing to add to it with his own RTN.

I started using Carlson 2006, and early on I used its .SHP import to put the US Census Tiger line data for streets (and a few other layers) in the master drawing. From there I was able to simply XREF in the DXF file for each field job and, eureka! I have not only found it but I know where it is. Later I replaced the Tiger map data with King County GIS data that is much more accurate.

I don't have quite the same thing that Mr. Young talked about, in that it's not all in one Star*net project. I export each small field job as its own DXF that is in state plane and then XREF them into the main cadaster drawing.

The gotchas are in keeping it all on "true grid" in CAD. When it was just my own files, everything fit because I lucked into the default settings being "unitless". But after a few years I started trying to XREF in drawings from others, that were supposedly in state plane, but didn't quite register. I asked a lot of questions about this on the old board, and a few here lately. The basic inescapable fact is that in AutoCAD, feet are ALWAYS international feet. So if you are in a US Survey Foot state like I am, setting anything to "feet" will get you when you start working with large state plane coordinates. Unitless, always. And then you have to make sure that any scripts, utilities, third party menus, built in buttons, etc. don't try to change your units or apply their own scaling... I am getting less inclined to trust CAD for anything larger than a mile, because unless you are very careful about the above, non-obvious things happen and you got some 'splainin' to do. And while I am on my soap box, I will ponder out loud if this is one of the main causes for modern day pincushions -- people mixing GPS, grid, and CAD without knowing all the gotchas.

Fun as it is, pretty soon I will go the opposite direction, where I use QGIS or uDig (both free software) to import a DXF of each field job as a layer in a GIS. This will make all the coordinate transformation stuff a lot easier, puts the files under a database of sorts, easy to store and replicate and backup and re-export to CAD for a local project.

Originally I was aiming for a CAD-free workflow: to hack a GIS in such a way that it would take care of the field-to-finish, then export to CAD if anyone needed a CAD file, but be able to render a perfect record-of-survey drawing or an ALTA without any CAD at all... still working on it.

Soon I will get rid of AutoCAD and Microsoft in one fell swoop: I have been testing BRICSCAD's Linux version, and now I "get it": with about a dozen Lisp functions I can do everything I need to do for now. If I can replace the field-to-finish I am using now in Carlson with custom Lisp in BRICSCAD, I'm done with both MS and AC. So long, farewell, auf wiedersehen, goodbye! That's this summer's project.

Seems like the holy grail for most surveyors is to be that solo guy who makes $250K/year working half time. After reading clues from the too few such heroes we have, my current hypothesis is that the difference between me being one of Those Guys and just another bozo on the bus is in making a custom toolchain.