3.7 Databases

Before the advent of the database approach in the early 1970s, data users had no means by which to centrally store and share information; leading to duplication, inaccuracies and program data dependence. Database Management Systems (DBMS) are a suite of software programs which allow information to be stored, organised and accessed in a systematic and consistent way and in a central location, allowing numerous users to access the same data. This increases efficiency by removing duplication and the inaccuracies of maintaining multiple tables of the same information.

In GIS the development of spatial databases and spatial database engines has enabled geographic data to be stored alongside non-spatial database tables within a single DBMS, thus driving the integration of spatial information. Using SQL information can be retrieved from both spatial and non spatial data simultaneously. For example, say we wanted to view a database table of customer addresses on a map, the table could be joined to a spatial table of addresses. As long as the spatial attribute field(s) are included in the output table (either the numeric coordinates or a proprietary geometry field) the table can be imported into a GIS and the customers' addresses viewed spatially. The following SQL query will retrieve the customer number field from the Customer_table and the coordinates from the Address_table and write them to an output table called customer_location.

create table customer_location as
select customer_number, xcoord, ycoord
from customer_table join address_table
on customer_table.address = address_table.address

Customer_table

Customer_number	Address	Postcode
NR173974	45 Laurel Avenue	HP1584
TM184903	7 High Street	E45GE
HA194829	Mill Cottage	IP76CD
MX960417	11 Vincent Street	HP114YE

Address_table

ID	Address	Postcode	Xcoord	Ycoord
ODFD197843	45 Laurel Avenue	HP1584	816304	497628
BNBV497553	7 High Street	E45GE	794382	201975
ASTT796962	Mill Cottage	IP76CD	794682	412876
PEKD969710	11 Vincent Street	HP114YE	994685	325874

Customer_location

Customer_number	Xcoord	Ycoord
NR173974	816304	497628
TM184903	794382	201975
HA194829	794682	412876
MX960417	994685	325874