Spartial database stores and retrieves spatial objects in the database like other database objects.
Evolution of spatial database:
First generation:
In first generation GIS systems, all spatial data, indexes and attributes are stored in flate files. GIS tools would access the data through proprietary APIs.
Second generation:
In second genration GIS systems, data and indexes were stored in files and the corresponding attributes stored in database tables. Proprietary API is involed to read the data from files and SQL queries from database
Third generation:
Third generation GIS system involves the RDBMS which stores spatial data that can be accessed by designated SQL queries by GIS tools. PostGIS is designed based on third generation GIS model.
Postgis extension enable the postgres database as a geospatial database. Postgis gives the following features to the PostgreSQL server
-
Spatial data types - refer to shapes such as point, line, and polygon;
-
Multi-dimensional spatial indexing is used for efficient processing of spatial operations;
-
Spatial functions, are for querying of spatial properties and relationships.
Postgis is a postgres extension that can be downloaded from yum repos. Once the postgis installation has completed, we need to create the extension per database.
Data types:
-
Geometry
-
Geography
Metadata views:
PostGIS provides views and tables to track and report on the geometry types available in a particular database.
-
geography_columns - This view, provides a listing of all geography attributes, and the basic details of those attributes.
-
geometry_columns - This view, provides a listing of all geometric attributes, and the basic details of those attributes.
-
spatial_ref_sys - This table defines all the spatial reference systems known to the database
Geometry:
geometry is a fundamental PostGIS spatial data type used to represent a feature in the Euclidean coordinate system.
When digging into the geometry_columns view we can notice the column that we have created.
Points:
A spatial point represents a single location on the Earth. This point is represented by a single coordinate (including either 2-, 3- or 4-dimensions). Points are used to represent objects when the exact details, such as shape and size, are not important at the target scale. For example, cities on a map of the world can be described as points, while a map of a single state might represent cities as polygons.
ST_AsText - This function returns the human readable representation of the spatial data.
The below are the special function used for points.
-
ST_X returns the X ordinate
-
ST_Y returns the Y ordinate
Linestrings:
A linestring is a path between points. Linestrings will be formed by an ordered series of two or more points. Roads, railway tracks and rivers are typically represented as linestrings. A linestring is said to be closed if it starts and ends on the same point. It is said to be simple if it does not cross or touch itself.
The below are the some special function used for Linestrings.
-
ST_Length returns the length of the linestring
-
ST_StartPoint returns the first coordinate as a point
-
ST_EndPoint returns the last coordinate as a point
-
ST_NPoints returns the number of coordinates in the linestring
Polygons:
A polygon is a representation of an area. The outline of the polygon is defined by a ring.
Polygons are used if size and shape of the objects are important. City limits, buildings, water catchments are all commonly represented as polygons. Roads and rivers can sometimes be represented as polygons.
Some of the specific spatial functions for working with polygons are:
-
ST_Area returns the area of the polygons
-
ST_NRings returns the number of rings (usually 1, more of there are holes)
-
ST_ExteriorRing returns the outer ring as a linestring
-
ST_InteriorRingN returns a specified interior ring as a linestring
-
ST_Perimeter returns the length of all the rings
Polygons with holes:
Holes within the polygon are also represented by rings.
Collections:
A collection is a group of Points, LineStrings and polygons or the mixing of the mentioned groups.
The below collections are possible in PostGIS.
-
MultiPoint - a collection of points
-
MultiLineString - a collection of linestrings
-
MultiPolygon - a collection of polygons
-
GeometryCollection - a heterogeneous collection of any geometry
Some of the specific spatial functions for working with collections are:
-
ST_NumGeometries returns the number of parts in the collection
-
ST_GeometryN returns the specified part
-
ST_Area returns the total area of all polygonal parts
-
ST_Length returns the total length of all linear parts
Geography:
The geography type provides native support for spatial features represented on "geographic" coordinates(latitude, longitude). Geographic coordinates are spherical coordinates expressed in degrees.
The SQL for creating a new table with a geography column is similar to how to you create a geometry table. However, geography includes the ability to specify the object type directly at the time of table creation.
The table got entry in the geography_columns view.
We can mention the shape when we create the table itself. If we need to mention the particular SRID there is a provision for that also. Default is 4326
Also, we can mention the shape while inserting the data with SRID if needed.
Casting to Geometry:
It is possible to read the coordinates as a point, LineStrings, Polygons from a geographical data,
For example, the ST_X(point) function only supports the geometry type, but we can read the X coordinate from our geographies using the below method.
Spatial indexes:
A spatial index is a type of extended index that allows you to index a spatial column. A spatial column is a table column that contains data of a spatial data type, such as geometry or geography.
The below example illustrates the creation of spatial index in Postgres using GiST.
When compared to MySQL, PostGIS having the following advantages.
- No limit on column sizes to support big GIS objcts.
- PostGIS will support 3dm, 3dz and 4d shapes.
- Generic index structure(GiST) to allow R-Tree index.
- PostgreSQL will store any data in spatial index, For example we can store location name and geography in a same index. PostGIS also supports BRIN indexes for large geometry tables
- Stored Procedures can be written in many languages, such as Python and R.
- MySQL does not support geography data type to store coordinates.
- PostGIS offers enarmous functions to handle the spatial data.
For more Postgis functions please refer the following link https://postgis.net/docs/reference.html
