Working with geospatial data in Postgres.

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
• Introduction to PostGIS
• Related extensions

Working with geospatial data

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

The primary spatial-data extension is PostGIS. PostGIS (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) is below:

• 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). This allows your database structure to reflect the spatial data that’s stored in the database.

Related Extensions

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

• pgRouting - an extension of PostGIS itself; pgRouting enables geospatial routing information such as:

Shortest distance

Driving distance

Traveling salesman

• ogrfdw - a data wrapper for reading other spatial and non-spatial datasources as tables in PostgreSQL
• pgpointcloud 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 to discover the modern SQL editor for working with databases.