Working with geospatial data in Postgres.
Tags: sql, postgres, database, beginners
Published: 2020-12-29 20:07:23

---
title: Working with geospatial data in Postgres.
published: true
date: 2020-12-28 19:17:12 UTC
tags: SQL, PostgreSQL, database, beginner
canonical_url: https://blog.arctype.com/working-with-geospatial-data-in-postgresql/
---

![Working with geospatial data in Postgres](https://dev-to-uploads.s3.amazonaws.com/i/fzlksrmjiw8jre1adtct.png)

PostgreSQL has several extensions that allow spatial and geometry data to be treated as first-class objects within a PostgreSQL database.

##Table of Contents
* [Working with geospatial data] (#postgres)
* [Introduction to PostGIS](#postgis)
* [Related extensions] (#extensions)

## Working with geospatial data <a name="postgres"></a>

There are a variety of scenarios in which you may want to work with geospatial data in Postgres for your application, including:

- Working with census data
- Storing addresses
- Calculating the distance between two paths
- Storing PointCloud data of the physical world
- Tracking shipping data
- Tracking cars and delivery vehicles
- Visualization of raster data   

PostgreSQL offers [extensions](https://www.postgresql.org/download/products/6-postgresql-extensions/) for working with geospatial data that allow you to treat that data as first-class objects in your database. Treating data as objects allows developers to create more powerful applications that can be built on top of data about the objects and relationships between them in the physical world.

##PostGIS <a name="postgis"></a>

The primary spatial-data extension is PostGIS. [PostGIS](https://postgis.net/) (Geographic Information Systems) is an open-source extension of the PostgreSQL database that lets you work with geographic objects that integrate directly with your database. With PostGIS, geographic and spatial data can be treated as first-class objects in your database.   
  
By adding the PostGIS extension to your PostgreSQL database, you can work seamlessly with geospatial data without having to convert that data from the format that the rest of your application is working with to use with your database. You can also determine relationships between that spatial data with the extension, such as the distance between two objects in your database. You can also use PostGIS to render visualizations of this data.   
  
Working with data such as cities and geometry data is as simple as something like:

> **SELECT** superhero.name

> **FROM** city, superhero

> **WHERE** ST\_Contains(city.geom, superhero.geom)

> **AND** city.name = 'Gotham';

**PostGIS includes:**

- Spatial Types
- Point
- Line
- Polygon
- Etc

The hierarchy of these spatial-focused type (from [Introduction to PostGIS](https://postgis.net/workshops/postgis-intro/introduction.html)) is below:

![Working with geospatial data in Postgres.](https://lh6.googleusercontent.com/wufnED6YRf_o4EpPb0igYM6jOt4gltS65BzTOhCNU4Ndi1-O_hLy_4FrWFYt8BhXdchWdZUamKcdixycdwRtJ0f2tvEgFNlsNLaDtHRAudaQtpjRyqM1r4ZU0epX1n4JXU8BbiWx)

- Spatial-Indexing
- Efficiently index spatial relationships
- Spatial-Functions
- For querying spatial properties, and the relationships between them
- Functions for analyzing geometric components, determining spatial relationships, and manipulating geometries  

In most databases, data is stored in rows and columns. With PostGIS, you can actually store data in a geometry column. This column stores data in a spatial coordinate system that’s defined by an SRID ([Spatial Reference Identifier](https://spatialreference.org/)). This allows your database structure to reflect the spatial data that’s stored in the database.

## Related Extensions <a name="extensions"></a>

There are other PostgreSQL extensions related to PostGIS for working with spatial data, too:

- [pgRouting](http://pgrouting.org/) - an extension of PostGIS itself; pgRouting enables geospatial routing information such as:

Shortest distance  
 Driving distance  
 Traveling salesman

- [ogrfdw](https://github.com/pramsey/pgsql-ogr-fdw) - a data wrapper for reading other spatial and non-spatial datasources as tables in PostgreSQL
- [pgpointcloud](https://github.com/pgpointcloud/pointcloud) A PostgreSQL extension and loader for storing Point Cloud data in PostgreSQL.
- PointCloud data about the physical environment that is gathered using 3D cameras, and used in application areas such as AR, VR, and robotics

PostgreSQL’s extensions for working with geospatial data allow you to work with data as first-class objects in your database. Check out [Arctype](https://arctype.com) to discover the modern SQL editor for working with databases.