Surveying GIS: Designing a point database.
In several prior episodes of this column, we have talked extensively about the importance of data. We have also discussed the role surveyors play in the overall GIS landscape.
In the course of those discussions, we have addressed some of the core functions surveyors provide for a GIS. For sure, the parcel land base ranks high on that list. But even more fundamental than that is the all-important control layer.
But a coordinate system or datum is an abstract mathematical concept. Physical monuments are still valuable resources in the world of surveying and mapping. Today, much of the control work for mapping is done by GPS, but physical control points and monuments haven’t yet been rendered totally obsolete. Survey monuments are still used in the field for GPS base stations, and their positions are still valuable for historic purposes. So there are many good reasons to maintain a current and accurate survey monument database.
Selecting a Platform
Remember that software is easy--people are hard. Before selecting the product(s) that will be used to construct and maintain a point database, it is a good idea to consider not only what will be included in your database but who will be using it. It is important to acquire and develop this information before you actually begin working with the data.
Most databases are designed by programmers. This fact can create a development dilemma in some instances. Programmers are expert with the workings of software and programming languages, but they often fall short in their knowledge about the data. It falls to the data subject-matter experts, therefore, to determine what will reside in the database.
The best and most useful databases are constructed by interdisciplinary teams. A team that includes members who have expertise with both the data and the platform it resides on will usually be more successful.
Many data users prefer to keep point (and other) data in a spreadsheet format such as Microsoft Excel. But spreadsheets have a somewhat different functionality from database programs despite their apparent similarities.
The conceptual difference is the function of the cell in the different environments. The spreadsheet is a programming environment, so the cell function can be dynamic with enabled features like “intelligent cell re-computation.” Because spreadsheets like Excel are a programming environment, data sets can be subject to inadvertent alterations. So the spreadsheet tends to be more viable for short-term operations than for long-term storage.
Ideally, the database is a storage and retrieval environment. The basic structure is a table, field and record (cell). And that base structure remains static regardless of how the output of an operation is displayed. This type of environment tends to keep data more stable for storage over time. Each record or set of records requires a specific action by the user for editing.
Microsoft Access is a powerful database tool, but it does have a few limitations. In the off-the-shelf (OTS) version, Access is a single-session-editor database program. Because Access is a relational database, data can be shared by multiple concurrent users on a common network drive. But “out of the box,” only one user (typically the last) can perform edits that will be saved in that common session.
To support multiple concurrent editors in a common session, Access can be Web-enabled by using Microsoft SQL (MSDE) and creating ODBC (Open Database Connectivity) user accounts. This approach uses the Microsoft Server technology to replace the Jet Database Engine (see Figures 1 and 2).
There are many good reasons to maintain a historical control point database. But there are also more commonly used types of point databases that require management. Feature points, such as manholes, fire hydrants, culverts, water valves, and power, light and telephone poles, get collected by the hundreds and by varied means. It is not uncommon to have points for mapping and asset management purposes collected by a total station or GPS or some combination thereof (see Figure 3).
The challenge is often to merge like items from different sources and sessions (see Figure 4, p. 56). In that case, it is often best to create a file-based database consisting of individual jobs and sessions. The .csv (comma-separated value) or .txt formats both accommodate appending. Combined files can be created by merging desired point groups into separate files. But for storage, it is usually best to store the original data collection files separately.
CAD files have point database capability. The points typically are part of a project and can be assigned to individual drawing layers using devices such as “description keys.” But those points can be leveraged for other purposes in at least two ways: Users can maintain all data collection points in a separate file or files, or they can choose the export capability of the CAD software package to make use of those points in another environment.
The Shapefile Format
Many map creation software programs, including those for field data collection systems, export to the Shapefile format. Shapefiles are fine as GIS map layers. They provide a versatile distribution format that can be accessed by a variety of mapping and CAD products. But most of these products have a limited ability to edit Shapefiles, which minimizes the format’s usefulness as a database.
Many functions can be performed with a point database once it is constructed. If a point is in the national database, you don’t need to do all of the work yourself. One way to enhance the power of your database is to link it to Web data sources. In ESRI’s ArcGIS program, it’s easy--you simply need to add a hyperlink (see Figure 5).
To create a hyperlink, you need to add a field to your data table for the link and populate the field with the URL (Uniform Resource Locator) for the point in question (see Figure 6). Once the hyperlink is created, any updates performed by others are automatically added to your database.
Anyone who is serious about creating useful databases should do additional research. A useful Web site for MS Access users is office.microsoft.com/en-us/access/HA012242471033.aspx.
A point database in many cases is not simply a file. It can be a complex system of interfaces that serve a wide variety of users. To manage a point database, what we really need is an integrated and synchronized system that both stores and distributes point data. And it takes both planning and effort to construct a useful point database.