Creating a Multi-user Enterprise Geodatabase for Fisheries Samples in George Washington Birthplace National Monument
PROBLEM
The National Park Service needs to answer spatial questions about the park’s resources. A multi-user enterprise geodatabase is needed to house tabular and spatial data to support spatial queries and allow editing of features.
ANALYSIS
The ArcSDE Enterprise Geodatabase system requires a relational database management system (RDMS) for storage (e.g., PostgreSQL), and in order to serve the data publicly to the internet while allowing any number of users to access the data I will install an ArcGIS Server on a virtual machine. I created an Entity Relationship (ER) diagram to visualize how the database would be organized and I used Dia to recreate the ER diagram using Unified Modeling Language (UML) to depict each table and the relationships among them (Fig. 1). I converted the file of the diagram from Dia to SQL. Using pgAdmin to access the PostgreSQL database, a new user is created and connected to the database to ensure that newly created tables do not interfere with the database’s schema. A new database and new schema are created, and a query tool is used to run the SQL code from Dia and then SQL is used to copy the tabular data that was provided into the database. Using ArcCatalog, spatial datasets can be added directly to feature datasets housed within the database. Now, SQL statements can be used to create a view of the tabular data, which will be brought into ArcMap and made into a shapefile (feature class) that will address the questions about samples of fish.
RESULTS
The Enterprise Geodatabase that was created houses both spatial and tabular data in a relational database management system; these data can be queried in pgAdmin or accessed through ArcMap/ArcCatalog (Fig. 2). Tabular data that were brought into ArcMap in the form of a view and saved as a feature class can be symbolized and displayed in the map. I show gamefish and non-gamefish sampling locations in the GEWA park (Fig. 3).
Fig. 2. The database structure as viewed from ArcCatalog (left) and pgAdmin (right). Click thumbnails to see larger images.
REFLECTION
I learned how to create and use a RDMS by incorporating SQL statements to create and populate tables and query these data. This is an important skill because SQL can handle complex queries more easily than having to relate or join multiple tables. It is important to plan and organize the data and tables before you create the database to make querying the data as straightforward as possible, but the beauty of a RDMS is that very complex datasets, with many tables, can be easily be traversed to pull data from multiple tables into one. The real power comes from publishing web services with these data, because then anyone can access and manipulate the data as they update in real-time. Furthermore, it provides the ability to create users or restrict access to control the level of security as seen fit. Refer to ‘Creating a Web-mapping Application for Fisheries Samples in George Washington Birthplace National Monument’ on my Web Services page for details on the web-mapping application created with this database.
Creating an Enterprise Geodatabase to Determine the Impact of a New Power Line in the New River Gorge National River
PROBLEM
Ultimately, a web-mapping application is needed to determine the impact of a proposed power line to the natural and cultural history of the New River Gorge National River. Data are provided in tabular format (Access database) and spatial format (shapefiles), so an enterprise geodatabase needs to be built to support the web-mapping application.
ANALYSIS
Files in the Access database were inspected, and the data were cleaned and then exported. I used Dia to create a Unified Modeling Language (UML) diagram detailing the tables and relationships among them and converted this to Structured Query Language (SQL) so I could create the schema and populate the tables in the database (Fig. 1). An enterprise geodatabase was created, and roles were defined, and a user was created so I could import the tables and import spatial files into the database. I used SQL to query the tables and create a view for use with web-mapping services.
RESULTS
The database contains tabular and spatial data and can be accessed through pgAdmin or ArcCatalog (Fig. 2). The results of the query can me mapped and then published for use with web-mapping services (Fig. 3).
Fig. 2. The files as seen through ArcCatalog (left) and pgAdmin (right) depict the same data. Click on thumbnails for larger image.
REFLECTION
This is another example of the importance of understanding how to build relationships among tables for use in a database. These relationships allow queries to be performed spanning multiple tables. An enterprise geodatabase allows services to be published granting the public access to the data, query, and results. Refer to ‘Creating a Web-mapping Application to Determine the Impact of a New Power Line in the New River Gorge National River’ on my Web Services page for details on the web-mapping application created with this database.