Data Querying in GIS

A database system is an essential component of a geographic information system (GIS). GIS are driven off databases. Almost all entities in the world are tied to some geographical location, expressed by x (longitude) and y (latitude) coordinates. These entities are stored in GIS as a series of points, lines, and polygons.

The power of a GIS is its ability to store the coordinate information; however, most GIS have the ability to connect to other information stored in a database management system (DBMS). For example, usually one land parcel within a city is more than likely owned by one person; however, if that land parcel is located in Manhattan, with the dollar value of it being 20 million, more often than not that land parcel is likely owned by more than just one person. We can store information about this land parcel, such as acreage, asset value, and ownership information, in a single database table, but some of the information would be duplicated. To help prevent this duplication, a good database management practice is to create a unique key that distinguishes each land parcel from other parcels. To build a database dealing with land parcels, a good practice is to assign a parcel identification number to each land parcel. This step ensures that the land parcel can be distinguished from all other land parcels.

Instead of storing all the information in one table, where duplication can be a problem, relational database techniques can be applied and tables broken up into two or more tables. Certain unique fields within the tables are assigned a key that uniquely identifies them. Once unique key fields are established in the database tables, most DBMS have functions called joins and links that allow many tables to act as one table, thus preventing duplication of data. Most GIS have join and link functions built within the software. For example, in most GIS one can select a single land parcel and link it to the ownership table and return all the current owners for that land parcel.

Figure 1 Database query interface in ArcGIS software

Source: The Select by Attributes dialogue box is the intellectual property of ESRI and is provided herein by permission. Copyright © 2009 ESRI. All rights reserved.

Relational database standards have facilitated querying and retrieving data from database tables. In particular, one such standard called the SQL language has been one of the major reasons for the success of relational databases. So what is SQL? The acronym SQL stands for structured query language. Originally SQL was called SEQUEL (for structured English query language). Over the years several versions of the SQL standards have evolved, and several database vendors have created their own version of SQL or optioned a current standard.

The power of SQL is its ability to extract information from the database based on some userdefined criteria. For example, if you wanted to get all land parcels with acre size greater than 5, you would create an SQL query such as “Acres” > 5. In the early days, before today's easy-to-use Application Programming Interfaces were created to construct SQL queries, querying databases was time-consuming and challenging. Today, several advanced SQL tools are in use that have a user friendly interface. Figure 1 is an illustration of a database query interface created by the Environmental Systems Research Institute, the leader in GIS technology.

...