https://www.sqlshack.com/getting-started-with-spatial-data-in-postgresql/

SQLShack

Skip to content

SQL Server training
Espanol  

[]

[                    ] []
Aveek Das
Lines of Latitude and Longitude across the surface of the earthLines
of Latitude and Longitude across the surface of the earth

Getting started with Spatial Data in PostgreSQL

January 13, 2022 by Aveek Das

In this article, we are going to discuss in detail what a spatial
database is and the data type, POINT that is supported by PostgreSQL
to perform geographic data analysis.

In my previous article, Install and upgrade PostgreSQL to support
Spatial Data, I have demonstrated how to install a PostgreSQL
database on a Mac and how to enable the PostGIS extension using which
spatial or geographical data can be inserted into the database and
spatial queries can be executed. You can also download and install
the PostGIS extension on a Windows machine by following similar
steps. It is highly recommended that you follow the tutorial to set
up an environment first and then continue with this article further
on. Once you have correctly installed the PostgreSQL database and
enabled the PostGIS extension, you can then follow this article to
understand more about the different spatial data types associated
with PostGIS.

PostGIS is an add-on library that can be installed to an existing
instance of a PostgreSQL database. The PostGIS will add specific
functions that allow us to work with geographical data within the
PostgreSQL database. If you want to have a descriptive overview of
the PostGIS extension and how it works, I recommend you have a look
at the official documentation for PostGIS.

Spatial Data Analysis and Why is it required?

Before diving deep into the technical stuff of working with Spatial
Data, let us first understand what spatial data analysis is all about
and why is it required. In simple terms, the study of patterns,
anomalies, and theories within spatial or geographical data is termed
Spatial Data Analysis. It is an extended version of data analysis in
which the focus is on geographical data rather than normal data. As
such, there are a specific set of tools and methodologies that
involve such kind of geographical data analysis. A few examples of
such Spatial Analysis would be as follows.

  * How far is Location A from Location B?
  * What are the bus stops within 500 meters from Location X?
  * What is the best route from Location X to Location Y?

In order to answer these questions, we need a special set of
functions within the databases. These functions are designed to work
with geographical data and give us the answers that we are actually
looking for. In PostgreSQL, these functions can be made available in
the form of an extension known as the PostGIS. This article is just
an introduction to the basic data type, POINT, in which the data is
stored in the database. Performing spatial analysis with such data is
beyond the scope of this article and will be addressed in the future.

Components of the Spatial Data Types in PostgreSQL

In order to work with geographical data, the databases use a special
data type. There are multiple data types that deal with geographical
data such as points, lines, polygon, etc. In this article, we will
specifically look into the POINT data type and understand how to
create and work with it. Before moving further, let us also brush our
knowledge on how to interpret geographical data in the real world.

If we consider the surface of the earth on a plane, lines of latitude
and longitude are used to describe the exact position of a location
on that surface.

Lines of Latitude and Longitude across the surface of the earthLines
of Latitude and Longitude across the surface of the earth

Figure 1 - Lines of Latitude and Longitude across the surface of the
earth - (Source)

As you can see in the figure above, the horizontal and the vertical
lines across the surface of the earth can be used to exactly locate
places on the surface of the earth. By knowing the latitude and
longitude coordinates we can give the exact location of any place in
the world. This is known as geolocation. There are various spatial
data types that can be used to perform spatial data analysis as
follows.

Geometry Data Types in PostgreSQL Geometry Data Types in PostgreSQL

Figure 2 - Geometry Data Types in PostgreSQL

The above figure shows all the available geometrical data types
available to work in PostgreSQL. In this article, we will look into
the following geographical data types.

  * POINT - It is used to define the exact location using latitude
    and longitude coordinates.

POINT Data Type

A point is an intersection of a latitude and a longitude coordinate.
With the combination of a latitude and a longitude coordinate, it is
possible to locate any location on the map. In PostgreSQL, a point is
a geometry data type that stores the data in the geometry data type.
For example, let us consider the following coordinates for London
where the latitude is 51.501220 and the longitude is -0.138702.

In order to convert the above into PostgreSQL and represent London,
we can write it as

POINT(-0.138702 51.501220)

Notice how the longitude value is placed in the front and the
latitude is placed next to it with just a space as a separation
between the two. This is known as Well Known Text or WKT in PostGIS.
However, this is just textual data and in order to convert it into a
geographical object and load it into the database, we are going to
use the spatial function ST_GeomFromText(text WKT, integer SRID). In
this format, we are going to provide the coordinates as the text and
a Spatial Reference ID as the second parameter. The SRID is a spatial
reference ID that tells the PostGIS engine in what format is our
data, however, there can be many values for SRID which are beyond the
scope of this article. For this example, we will consider the SRID as
4326. So, the final representation of the point data type can be
written as follows.

ST_GeomFromText('POINT(-0.138702 51.501220)', 4326)

When the above statement is executed in PostgreSQL, the PostGIS
engine converts the Well Known Text string value into a valid
geometry data type and stores it within the database in a binary
format.

Now let us create a table in PostgreSQL and insert geometry data into
it. You can use the following SQL query from the gist to create and
insert data into your table.

https://gist.github.com/aveek22/daf288a18480ab91be7a17cf33ad7cfc

Once you run the query, you will see that a table has been created in
PostgreSQL that contains data for London.

Storing Geometry data into PostgreSQLStoring Geometry data into
PostgreSQL

Figure 3 - Storing Geometry data into PostgreSQL

As you can see in the above figure, the column sensorLocation which
was provided coordinates with a Well Known Text format has been
converted into a geometry data type and the data is stored in a
binary format. We can also view the location on the map by clicking
the small eye button on the right of the column header.

Viewing Geometry data on a map in PostgreSQLViewing Geometry data on
a map in PostgreSQL

Figure 4 - Viewing Geometry data on a map in PostgreSQL

As you can see in the figure above, when we select the Geometry
Viewer in PGAdmin, the point is being viewed as a blue dot on top of
the London city on the map. Since we have only one record in the
dataset, only one dot is visible on the map. All such points from the
dataset will be displayed on the map that corresponds to its location
coordinates.

Alternatively, you can also view the data type of an existing
geometry data type by using the following SQL script.

SELECT ST_GeometryType(sensorLocation) FROM SQLShackGeomTest;

Checking the Geometry Data TypeChecking the Geometry Data Type

Figure 5 - Checking the Geometry Data Type

Conclusion

In this article, we have understood what spatial data is all about
and how it can be stored in a PostgreSQL database. We have also
learnt the spatial data type POINT associated with PostGIS and its
significance. Points are the basic block of working with any spatial
data. A combination of points can be used to make other geographical
objects such as lines or polygons etc. We will learn more about these
in the upcoming articles.

  * Author
  * Recent Posts

Aveek DasAveek Das
 
Aveek Das
Aveek is an experienced Data and Analytics Engineer, currently
working in Dublin, Ireland. His main areas of technical interest
include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like
Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and
Azure.

He is a prolific author, with over 100 articles published on various
technical blogs, including his own blog, and a frequent contributor
to different technical forums.

In his leisure time, he enjoys amateur photography mostly street
imagery and still life. Some glimpses of his work can be found on
Instagram. You can also find him on LinkedIn

View all posts by Aveek Das
Aveek DasAveek Das
Latest posts by Aveek Das (see all)

  * Getting started with Spatial Data in PostgreSQL - January 13,
    2022
  * An overview of Power BI Incremental Refresh - December 6, 2021
  * Working with Date and Time Functions in PostgreSQL - November 26,
    2021

Related posts:

 1. Install and upgrade PostgreSQL to support Spatial Data
 2. Spatial SQL data types in SQL Server
 3. How to create geographic maps in Power BI using ArcGIS
 4. How to install PostgreSQL on Windows
 5. Access PostgreSQL databases using the Azure Data Studio extension

Database development, PostgreSQL, Uncategorized
[gif][fc1459d3]

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently
working in Dublin, Ireland. His main areas of technical interest
include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like
Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and
Azure. He is a prolific author, with over 100 articles published on
various technical blogs, including his own blog, and a frequent
contributor to different technical forums. In his leisure time, he
enjoys amateur photography mostly street imagery and still life. Some
glimpses of his work can be found on Instagram. You can also find him
on LinkedIn View all posts by Aveek Das

View all posts by Aveek Das -
66 Views

Follow us!

   

ApexSQL Audit Lab

Popular

  * Different ways to SQL delete duplicate rows from a SQL Table
  * SQL Convert Date functions and formats
  * SQL PARTITION BY Clause overview
  * How to UPDATE from a SELECT statement in SQL Server
  * SQL WHILE loop with simple examples
  * Learn SQL: Join multiple tables
  * SQL Variables: Basics and usage
  * SQL Server table hints - WITH (NOLOCK) best practices
  * How to backup and restore MySQL databases using the mysqldump
    command
  * CASE statement in SQL
  * SQL multiple joins for beginners with examples
  * SQL Server functions for converting a String to a Date
  * What is the difference between Clustered and Non-Clustered
    Indexes in SQL Server?
  * SQL Not Equal Operator introduction and examples
  * The Table Variable in SQL Server
  * DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key
  * Multiple options to transposing rows into columns
  * SQL Server Transaction Log Backup, Truncate and Shrink Operations
  * How to implement error handling in SQL Server
  * INSERT INTO SELECT statement overview and examples

Bundles are better

Trending

  * SQL Server Transaction Log Backup, Truncate and Shrink Operations
  * Six different methods to copy tables between databases in SQL
    Server
  * How to implement error handling in SQL Server
  * Working with the SQL Server command line (sqlcmd)
  * Methods to avoid the SQL divide by zero error
  * Query optimization techniques in SQL Server: tips and tricks
  * How to create and configure a linked server in SQL Server
    Management Studio
  * SQL replace: How to replace ASCII special characters in SQL
    Server
  * How to identify slow running queries in SQL Server
  * SQL varchar data type deep dive
  * How to implement array-like functionality in SQL Server
  * All about locking in SQL Server
  * SQL Server stored procedures for beginners
  * Database table partitioning in SQL Server
  * How to drop temp tables in SQL Server
  * How to determine free space and file size for SQL Server
    databases
  * Using PowerShell to split a string into an array
  * KILL SPID command in SQL Server
  * How to install SQL Server Express edition
  * SQL Union overview, usage and examples

ApexSQL Audit Lab

Solutions

  * Read a SQL Server transaction log
  * SQL Server database auditing techniques
  * How to recover SQL Server data from accidental UPDATE and DELETE
    operations
  * How to quickly search for SQL database data and objects
  * Synchronize SQL Server databases in different remote sources
  * Recover SQL data from a dropped table without backups
  * How to restore specific table(s) from a SQL Server database
    backup
  * Recover deleted SQL data from transaction logs
  * How to recover SQL Server data from accidental updates without
    backups
  * Automatically compare and synchronize SQL Server data
  * Open LDF file and view LDF file content
  * Quickly convert SQL code to language-specific client code
  * How to recover a single table from a SQL Server database backup
  * Recover data lost due to a TRUNCATE operation without backups
  * How to recover SQL Server data from accidental DELETE, TRUNCATE
    and DROP operations
  * Reverting your SQL Server database back to a specific point in
    time
  * How to create SSIS package documentation
  * Migrate a SQL Server database to a newer version of SQL Server
  * How to restore a SQL Server database backup to an older version
    of SQL Server

SQL auditing made easy

Categories and tips

  * >Auditing and compliance (50)
      + Auditing (40)
      + Data classification (1)
      + Data masking (9)
  * Azure (248)
  * Azure Data Studio (40)
  * Backup and restore (107)
  * >Business Intelligence (462)
      + Analysis Services (SSAS) (47)
      + Biml (10)
      + Data Mining (14)
      + Data Quality Services (4)
      + Data Tools (SSDT) (13)
      + Data Warehouse (14)
      + Excel (20)
      + General (39)
      + Integration Services (SSIS) (125)
      + Master Data Services (6)
      + OLAP cube (15)
      + PowerBI (84)
      + Reporting Services (SSRS) (64)
  * Data science (21)
  * >Database design (217)
      + Clustering (16)
      + Common Table Expressions (CTE) (11)
      + Concurrency (1)
      + Constraints (8)
      + Data types (11)
      + FILESTREAM (20)
      + General database design (96)
      + Partitioning (13)
      + Relationships and dependencies (12)
      + Temporal tables (12)
      + Views (16)
  * VDatabase development (400)
      + Comparison (4)
      + Continuous delivery (CD) (5)
      + Continuous integration (CI) (11)
      + Development (138)
      + Functions (103)
      + Hyper-V (1)
      + Search (10)
      + Source Control (15)
      + SQL unit testing (23)
      + Stored procedures (32)
      + String Concatenation (2)
      + Synonyms (1)
      + Team Explorer (2)
      + Testing (34)
      + Visual Studio (14)
  * DBAtools (35)
  * DevOps (23)
  * DevSecOps (2)
  * Documentation (20)
  * ETL (70)
  * >Features (212)
      + Adaptive query processing (11)
      + Bulk insert (16)
      + Database mail (10)
      + DBCC (7)
      + Experimentation Assistant (DEA) (3)
      + High Availability (35)
      + Query store (10)
      + Replication (40)
      + Transaction log (59)
      + Transparent Data Encryption (TDE) (21)
  * Importing, exporting (48)
  * Installation, setup and configuration (113)
  * Jobs (42)
  * >Languages and coding (675)
      + Cursors (9)
      + DDL (9)
      + DML (5)
      + JSON (17)
      + PowerShell (77)
      + Python (35)
      + R (16)
      + SQL commands (194)
      + SQLCMD (7)
      + String functions (21)
      + T-SQL (270)
      + XML (15)
  * Lists (12)
  * Machine learning (37)
  * Maintenance (97)
  * Migration (50)
  * Miscellaneous (1)
  * >Performance tuning (830)
      + Alerting (8)
      + Always On Availability Groups (81)
      + Buffer Pool Extension (BPE) (9)
      + Columnstore index (9)
      + Deadlocks (16)
      + Execution plans (116)
      + In-Memory OLTP (22)
      + Indexes (79)
      + Latches (5)
      + Locking (10)
      + Monitoring (92)
      + Performance (186)
      + Performance counters (28)
      + Performance Testing (9)
      + Query analysis (111)
      + Reports (20)
      + SSAS monitoring (3)
      + SSIS monitoring (10)
      + SSRS monitoring (4)
      + Wait types (11)
  * >Professional development (66)
      + Professional development (26)
      + Project management (9)
      + SQL interview questions (31)
  * Recovery (32)
  * Security (83)
  * Server management (21)
  * SQL Azure (255)
  * SQL Server Management Studio (SSMS) (87)
  * SQL Server on Linux (14)
  * >SQL Server versions (174)
      + SQL Server 2012 (6)
      + SQL Server 2016 (63)
      + SQL Server 2017 (49)
      + SQL Server 2019 (54)
      + SQL Server 2022 (2)
  * VTechnologies (308)
      + AWS (45)
      + AWS RDS (56)
      + Azure Cosmos DB (24)
      + Containers (11)
      + Docker (8)
      + Graph database (13)
      + Kerberos (2)
      + Kubernetes (1)
      + Linux (40)
      + LocalDB (2)
      + MySQL (49)
      + Oracle (10)
      + PolyBase (10)
      + PostgreSQL (21)
      + SharePoint (4)
      + Ubuntu (12)
  * Uncategorized (4)
  * Utilities (21)
  * 

Helpers and best practices

  * BI performance counters
  * SQL code smells rules
  * SQL Server wait types

(c) 2022 Quest Software Inc. ALL RIGHTS RESERVED.   |   GDPR   |  
Terms of Use   |   Privacy

[35]
*