3.7 Databases

Database Management Systems (DBMS) are software programs that can be considered the foundation that any organisation using modern computer systems builds upon; when compared with the file approach used in the early days of computers, they offer considerable advantages in managing information, by allowing simultaneous access in centrally stored and systematically organised data. Additional benefits from DBMS use include data independence, reduced data redundancies, quick data recovery and enforcing security policies. (Connolly & Begg, 2005).

Relational databases are the most widely used DBMS on which information is stored in data structures that can be visualised as two-dimensional tables, each table representing a distinct entity of a model of a system. A good database design which maintains referential and relational integrity among the data stored is fundamental for a successful implementation of an organisation’s system, and helps towards efficient data retrieval and scaling-up. Using structural query language (SQL) queries can be constructed which will return data based on filtering criteria, possibly by joining one or more related tables.(source: Butterworth, R., Lecture 07: Structuring and querying information stored in databases)

Modern databases (like MS SQL Server 2008) are now spatially-enabled and can be used in the field of GIS. Along with the native SQL data types, new spatial types allow geo-coded information to be stored and indexed, on which then spatial operations can be performed, with the results visualised on a map, allowing useful information which is hidden within the data to be extracted (Longley, P. Goodchild, M. Maguire, D, and Rhind, D. (2005)).

As an example using data describing property locations and property prices, and using post-codes to get longitude-latitude coordinates, a query can be constructed which will display on a map the properties which have beed sold with a price over a given amount:

SELECT p.PropertyID,  p.Address,  p.PostCode,  max(s.SalePrice) as MaxPrice, c.Latitude,    c.Longtidude
FROM dbo.Property p
   JOIN dbo.PropertySale s
     ON p.PropertyID = s.PropertyID
      JOIN dbo.Coordinates c
        ON p.PropertyID = c.PropertyID
GROUP BY p.PropertyID, p.FullAddress, p.PostCode, c.Longtidude, c.Latitude
HAVING max(s.SalePrice) > 220000

PropertyID	FullAddress	PostCode	MaxPrice	Latidude	Longtitude
1012	22 Mornington Road	E11 3BE	285000	51.5696	0.0143
1013	42 Abbot's Park Road	E10 6HX	222000	51.5725	-0.0039