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
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.
Top comments (0)