Official Mafiree Blogs

Keeping you informed about Databases

Scaling Databases Horizontally: A Solution for High-Concurrency Systems
By Sukan   |   September 20, 2023   |   Posted in : TiDB
Blog post on how TiDB handles the OLAP requests without compromising OLTP

6 Interesting Features In MongoDB 6.0
By Thivakar   |   June 19, 2023   |   Posted in : MongoDB
Upgrade to MongoDB 6.0 for improved index management, error handling, powerful aggregation, replicaset enhancements, time series collection improvements, and embedded field exclusion. Experience efficient database management.

Access Control & Account Management In MySQL 8
By Sukan   |   June 08, 2023   |   Posted in : MySQL
Discover how MySQL 8 enhances security through features like password expiry, password validation, account lock/unlock, privilege management, and dual passwords. Safeguard your valuable data and streamline access control in your MySQL environment.

Significance of using Invisible Primary key (GIPK) with MySQL 8.0
By Sukan   |   February 21, 2023   |   Posted in : MySQL
How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

 Home  /  PostgreSQL  
Geospatial Database :: PostGIS Basics and Beyond
By Sujith   |   August 02, 2019   |   Posted in : PostgreSQL

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

  1. Spatial data types - refer to shapes such as point, line, and polygon;

  2. Multi-dimensional spatial indexing is used for efficient processing of spatial operations;

  3. 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




0 Comments


Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree