Spatial data analysis using PostGIS - PostgreSQL Tutorial

As we collect and work with different kinds of data, the features and functionality offered by traditional relational database management systems aren't enough, and that's why all of the RDBMS that are in use today offer extensions for different kinds of data, including spatial data. Spatial data has to do with geographic locations, streets, area and so on. Spatial data analysis is the study of patterns, anomalies and theories in spatial data. Data analysis extended to geographic data such as locations, paths, areas, et cetera. Spatial data analysis requires databases to understand location coordinates, the idea of polygons that cover area, the idea of lines that stretch from one point to another. Let's discuss some of the things that you can do with spatial data analysis. Let's say you have two points, A and B. With spatial data analysis, you can compute the distance between these two locations. Let's say you are in a particular neighborhood and you're really looking out for coffee. With spatial data analysis, you can find coffee shops within one kilometer of location X, whatever location X happens to be. Spatial data analysis can work with streets and roads as well. For example, you can find all streets that intersect with a certain neighborhood, or you can find the best route between city X and city Y. When you have geographic data such as location coordinate data, street data, the ability to perform spatial data analysis on this data is very powerful indeed, and that's what PostgreSQL enables using the PostGIS extension. PostgreSQL gives you many interesting bits of functionality in the form of extensions and PostGIS is one such extension. It's the spatial database extender for PostgreSQL that supports geographic objects and allows location queries to be run using SQL. Here, GIS or G-I-S stands for Geographic Information Systems. Before we talk about the PostGIS extension, let me set some context. We need to discuss the Open Geospatial Consortium or OGC. This consortium developed the standard for geospatial data and this is what database systems around the world use. This standard is called the Simple Features Access or the SFA Standard. OGC SFA Standard defines geometry as a fundamental spatial type. Now remember that geometry is an abstract type, and there are many concrete implementations of geometry such as points, lines, polygons and so on. In addition to the geometry type, the standard also defines operations that can be used to manipulate and transform different kinds of geometry values. And this is the standard that PostGIS implements. PostGIS implements the OGC model using two different PostgreSQL types, geometry and geography. We'll see how they're different in just a bit. When you use PostGIS, you can define a column in your database table to be of type geometry. Geometry is an abstract type with many concrete subtypes, and these concrete subtypes may be atomic types or collection types. A collection is simply a collection of atomic values. Here are some of the atomic types that PostGIS supports. The first is of course, a point. A point is a zero dimensional geometry that represents a single location in coordinate space. A point on a plane can be defined using two coordinates that is X and Y values. A point with an elevation is defined using three coordinates, X, Y, and Z values and a point can have a fourth dimension that represents some measure such as time or distance. A line string is a one dimensional line formed by a contiguous sequence of line segments. So you can think of a line string as different line segments connected in sequence. A linear ring is an atomic type that is just a line string that is both closed and simple. The first and last points in a linear ring must be equal and the line must not self intersect. A polygon is a two dimensional planar region delimited by an external boundary, the shell and has zero or more interior boundaries holes. In addition, PostGIS supports many different collection types such as MultiPoint, which is just a collection of points or a MultiLineString, which is a collection of line strings. You have a MultiPolygon, which is a collection of non-overlapping non-adjacent polygons. You have a GeometryCollection which is a heterogeneous or mixed collection of geometries. You also have a PolyhedralSurface which is a contiguous collection of patches or facets which share some edges. And then you have a Triangle which is just a polygon defined by three distinct non collinear vertices. And finally, you have a TIN, which is a collection of non-overlapping triangles representing a triangulated, irregular network. In addition to these atomic types and collections that we've discussed, there are additional standards which extend the OGC standard to define subtypes containing curves with circular arcs.