Databases, Spatial Databases, and PostGIS

1. Databases, Spatial Databases, and PostGIS Overview of relational database concepts and PostGIS

2. DBMS Perspective From Spatial Databases, A Tour, by Shekhar and Chawla

3. Role of DBMS • Typical small system architecture:

4. Role of DBMS • Larger spatial DBMS From “Introduction to Spatial Data Management with Postgis,” by Arnulf Christl http://www.ccgis.de, http://www.mapbender.org/

5. Definitions Database Management Systems (DBMS) – • “Software that controls the organization, storage, retrieval, security and integrity of data in a database. It accepts requests from the application and instructs the operating system to transfer the appropriate data.” (Computer Desktop Encyclopedia, 2007) • Key features: • Data Security • Data Integrity • Interactive Query • Interactive Data Manipulation • Data Independence • May be Network, Hierarchical, Object, Relational. Relational is by far the most commonly-used and well-established, and handles most data management problems very well.

6. Definitions Relational Database Management Systems (RDBMS)– • “A database that maintains a set of separate, related files (tables), but combines data elements from the files for queries and reports when required. The concept was developed in 1970 by Edgar Codd, whose objective was to accommodate a user's ad hoc request for selected data.” [See Codd Article for details.] (Computer Desktop Encyclopedia, 2007) • Data stored in separate tables, each containing tabular data like a spreadsheet, joined together as needed. • In the early days of RDBMS, many vendors claimed to offer relational databases when they did not – Codd came up with 12 rules defining the requirements for a database system to be truly relational.

7. Tables Relational tables have these properties: • Column Values Are of the Same Kind • Each Row is Unique • The Sequence of Columns is not significant • The Sequence of Rows is not significant • Each column must have a unique name (within the table) • Represents a single entity. Example:

8. Columns Table columns have these properties: • They have a data type (similar to variables, but slightly different) – char, varchar, number, float, text, BLOB (image), etc. • Column values should be independent from each other. • Values may be required (not null), or nullable. (Some databases differentiate between null and zero-length string). • Columns may be indexed to improve access speed. • A column may be used as the basis of the order of the physical data (clustered index). • Each represents an independent attribute.

9. Primary Keys • The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. • Every entity in the data model must have a primary key whose values uniquely identify instances of the entity. • To qualify as a primary key for an entity, an attribute must have the following properties: • it must have a non-null value for each instance of the entity. • the value must be unique for each instance of an entity. • the values must not change or become null during the life of each entity instance

10. Composite and Artificial Keys • Sometimes more than one attribute is required to uniquely identify an entity. A primary key that made up of more than one attribute is known as a composite key. • An artificial keyis one that has no intrinsic meaning. These can be very useful when no attribute has all the primary key properties, or the meaning of the primary key is otherwise complicated or conditional.

11. Foreign Keys • A foreign key is an attribute that defines a relationship between two tables. Foreign keys provide a method for maintaining integrity in the data (referential integrity). Every relationship between entities must be supported by a foreign key. • A foreign key is an attribute in one table and a primary key in another. • Typically, an RDBMS will let you specify whether dependent entities are deleted (cascade delete) when a parent is deleted, or disallowed. This ensures the referential integrity of the database. • Foreign keys make possible establishing relationships in an Entity-Relationship Diagram [see sample below].

12. Primary and Foreign Keys • Example: http://msdn.microsoft.com/en-us/library/ms171971.aspx

13. Indexes • Disk reads are expensive. • Finding a record in a large table can be slow, often requiring a full table scan, that is, checking every row in the table. • Indexes allow for much more efficient searching. • If the performance of a query is bad, most of the time it can be fixed with better indexes. • Indexes tend to decrease query time, but increase insert/delete time, since they need to be adjusted (automatically) when the data changes.

14. Indexes From Spatial Databases, A Tour, by Shekhar and Chawla

15. Indexes • Example of an index minimizing disk access:

16. Indexes • Spatial data doesn’t have a simple natural ordering like most traditional data types. • In PostGIS, interacting with spatial indexes is similar to other data types, but internally is handled differently using the Generalized Search Tree algorithm. • This allows flexible partitioning based on the kind of data being indexed, rather than on an existing alphanumeric sequence • PostGIS has implemented spatial partitioning algorithms for GIST indexes. • http://gist.cs.berkeley.edu/gist1.html

17. Other Features of RDBMS • Triggers • Transaction Support • Stored Procedures • Views • User-Defined Functions • User-Defined Data Types • Extended features: • Full-text search • Spatial data • Replication • Others…

18. Normalization • The most elementary structure for a database is a single table, which corresponds to a spreadsheet or attribute table. • In this model there is one kind of entity (consisting of rows) which has a uniform set of attributes (consisting of columns). • This is fast and simple, but very limited. • Example of un-normalized data – NYC PLUTO tax lot data:

19. Normalization • While this might adequately capture the data we need, it has some drawbacks: • There will be lots of redundant data if many parcels are added. The borough and owner names, for example, will all be replicated for each row where these values are the same. This makes maintaining the data difficult. • What happens if a single parcel is zoned for more uses than the number of columns allow? Or if one parcel has several owners? • How do you know for sure which parcel is which?

20. Normalization • The solution is to begin normalizing the data. You can look at this as a three-step process implementing each of Codd’s “Normal Forms”. • First normal form rules: • A row of data cannot contain repeating groups of similar data (atomicity); and • Each row of data must have a unique identifier or primary key. • Second normal form rules: • No attribute can be dependent on only a portion of the primary key. • Every column must depend only on the entire primary key; if it is dependent on one or more other columns, these should be moved into new tables. • Third normal form: • No dependencies within non-key attributes.

21. Normalization • Normalization usually involves creating multiple tables, each of which complies with the three normal forms. • A normalized version of the PLUTO data, for example, would likely have separate zoning and owner tables. • However, in practice it is often useful to de-normalize the database structure. The PLUTO data in its current form is extremely usable, and de-normalizing it for usability makes sense. • Typically reporting applications will use some form of de-normalization to improve query performance, while more transactional applications will use normalized database structures to minimize redundant updates.

22. SQL • Structured Query Language is the interface you use to communicate with an RDBMS. • It consists of Queries; DML – data manipulation language; and DDL – data definition language. • SQL is standardized, but different DB vendors have different flavors and extensions (Oracle Spatial, for example, adds spatial keywords to SQL). • SQL is not really a full featured language like C#, although most database vendors have SQL-based languages like PL/SQL or TSQL that let you embed SQL statements directly in procedural code. • Typically, a program will interact with a database by submitting SQL statements, one by one, to a database using a data access layer that sends the requests to the database and returns the results to the program.

23. SQL DDL statements - examples: • CREATE TABLE - creates a new database table • ALTER TABLE - changes a database table’s structure • DROP TABLE - deletes a database table • CREATE INDEX - creates an index • DROP INDEX - deletes an index

24. SQL Queries: • SELECT – extracts data from a database table DML statements: • UPDATE - updates data in a database table • DELETE - deletes data from a database table • INSERT - inserts new data into a database table

25. SQL Return all the rows in a table: SELECT * from TableName Order by a field: SELECT * from TableName ORDER BY ColumnName Add a where clause: SELECT * from TableName WHERE ColumnName < 5 ORDER BY ColumnName Joining two tables: SELECT Table1.Column1, Table2.Column1 FROM Table1 INNER JOIN Table2 ON Table1.Id = Table2.Id Others: SELECT DISTINCT Column1 from TableName SELECT COUNT(*) from TableName see this discussion for others. http://db.grussell.org/imp.html

26. Data Access Layer • Databases come with interactive SQL query interfaces. • Programmatic interfaces are provided by the DB vendors as well as third party vendors. • There are many different standards and conventions such as ODBC, ADO, ADO.NET, JDBC, OLE DB (to see a list of Microsoft’s for example, see http://msdn2.microsoft.com/en-us/library/ms810810.aspx.

27. Data Access Layer • Typical application sequence: • Connect to database • Requires editable string with database connection info; usually stored in configuration file. • Requires exception handling in case connection is unsuccessful! • Execute SQL • Iterate through results (if query) • Display/output data • Close connection

28. Data Access Layers • ADO.NET is commonly used in C#: SQLServerConnection Conn = new SQLServerConnection("host=nc-star;port=1433; User ID=test01;Password=test01; Database Name=Test"); try { Conn.Open(); } catch (SQLServerException ex) { Console.WriteLine(ex.Message); return; } try { string strSQL = "SELECT ename FROM emp WHERE sal>50000"; SQLServerCommand DBCmd = new SQLServerCommand(strSQL, Conn); SQLServerDataReader myDataReader; myDataReader = DBCmd.ExecuteReader(); while (myDataReader.Read()) { Console.WriteLine("High salaries: " + myDataReader["ename"].ToString()); } myDataReader.Close(); Conn.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); return; }

29. Spatial Data • Spatial data can be represented using a relational database structure. • But this is complex and inefficient, requiring multiple tables to capture topology. • Usually spatial data can best be captured by a binary column that contains all of the geometry associated with a record. • A table in a spatial database will usually have a single geometry column of a single type, although a generic Geometry column can support multiple geometry types. From Spatial Databases, A Tour, by Shekhar and Chawla

30. Spatial Databases • Spatial databases are usually built on top of RDBMS. • Spatial requirements are more involved, including referential integrity based on topological geometrical relationships, not just foreign-key constraints. • OGC has a SQL spec for simple features storage very similar to the simple features spec used for geo tools: http://www.opengeospatial.org/standards/sfs • PostGIS complies with OGC spec.

31. Spatial Databases Sample join with spatial columns: From Spatial Databases, A Tour, by Shekhar and Chawla

32. Spatial Databases Mechanics of a spatial query: From Spatial Databases, A Tour, by Shekhar and Chawla

33. PostGIS • PostGIS is the spatial add-on for PostgreSQL • Compares well to others but is free! (www.postgresql.org). • Based on relational structure with support for spatial data types and spatial functions. • Can be used to serve up map layers. • Can also be used for typical database transactional functions. • No intrinsic support for raster data, unlike ArcSDE or Oracle Spatial.

34. PostGIS • RDBMS use database tables and columns to represent the database structure itself. • PostGIS supports GIS data by adding additional metadata tables: • spatial_ref_sys – contains a definition for each available coordinate system and projection. These can be added or edited as needed. • geometry_columns – contains a definition of each geometry column in the current database. • http://www.opengis.org/techno/interop/EPSG2WKT.TXT • http://www.opengis.org/techno/specs.htm

35. PostGIS • PostGIS geometry is based on the Open Geospatial Consortium simple features specification: http://www.opengeospatial.org/standards/sfs

36. PostGIS • Uses human-readable well-known text format to represent geometry (converts to binary internally): From “Introduction to Spatial Data Management with Postgis,” by Arnulf Christl http://www.ccgis.de, http://www.mapbender.org/

37. PostGIS • Sample PostGIS SQL - note special role of AddGeometryColumn, which manages geometry_columns table. From “Introduction to Spatial Data Management with Postgis,” by Arnulf Christl http://www.ccgis.de, http://www.mapbender.org/

38. PostGIS • Examples of supported functions – Comparisons: • ST_Equals, ST_Disjoint, ST_Intersects, ST_Touches, ST_Crosses, ST_Within, ST_Contains, and ST_Overlaps. • Set operations: • ST_Intersection, ST_Difference, ST_Union, ST_SymDifference, ST_Buffer, and ST_ConvexHull • Others: • AsText, GeometryFromText, Transform (re-project).

39. PostGIS • Non-spatial data can be loaded from the SQL interface using the Copy command. • Spatial data can be loaded using a command-line utility that comes with the database, or a GUI shape file loader. From “Introduction to Spatial Data Management with Postgis,” by Arnulf Christl http://www.ccgis.de, http://www.mapbender.org/

40. PostGIS • Data can be exported from PostGIS using the query interface. • Spatial data can be exported to a shape file using the pgsql2shp command-line utility. The basic syntax is: psql2shp [<options>] <database> <SQL query> • See http://www.bostongis.com/postgis_quickguide_1_4.bqg

41. PostGIS • PGAdmin is easy-to-use admin interface for Postgresql database. It includes a pane for DDL and an interactive SQL query tool.

42. PostGIS • Sample application: NYC Solar Map • Vector data and raster tile metadata stored in PostGIS database • Raster tiles stored on file system • Data accessed managed by C# web services • Web services communicate with web application via JSON (Javascript object notation), with GeoJSON extensions. • Client application uses Javascript to display map layers and query C# web service