The postgis extension
Work with geospatial data in Postgres using PostGIS
The postgis
extension provides support for spatial data - coordinates, maps and polygons, encompassing geographical and location-based information. It introduces new data types, functions, and operators to manage and analyze spatial data effectively.
This guide introduces you to the postgis
extension - how to enable it, store and query spatial data, and perform geospatial analysis with real-world examples. Geospatial data is crucial in fields like urban planning, environmental science, and logistics.
note
PostGIS is an open-source extension for Postgres that can be installed on any Neon Project using the instructions below. Detailed installation instructions and compatibility information can be found at PostGIS Documentation.
For information about PostGIS-related extensions, including pgrouting
, H3_PostGIS, PostGIS SFCGAL, and PostGIS Tiger Geocoder, see PostGIG-related extensions.
Version availability:
Please refer to the list of all extensions available in Neon for up-to-date information.
Currently, Neon uses version 3.3.3
of the postgis
extension for all Postgres versions.
postgis
extension
Enable the You can enable the extension by running the following CREATE EXTENSION
statement in the Neon SQL Editor or from a client such as psql
that is connected to Neon.
For information about using the Neon SQL Editor, see Query with Neon's SQL Editor. For information about using the psql
client with Neon, see Connect with psql.
Example usage
Create a table with spatial data
Suppose you're managing a city's public transportation system. You can create a table to store the locations of bus stops.
Here, the location column is of type GEOGRAPHY(Point)
, which is a spatial data type provided by the postgis
extension and used to store points on the Earth's surface.
Inserting data
Data can be inserted into the table using regular INSERT
statements.
The ST_Point
function is used to create a point from the specified latitude and longitude.
Querying spatial data
Now, we can perform spatial queries using the built-in functions provided by PostGIS
. For example, below we try to find points within a certain distance from a reference.
Query:
This query returns the following:
The ST_DWithin
function returns true if the distance between two points is less than or equal to the specified distance (when used with the GEOGRAPHY
type, the unit is meters).
Spatial data types
PostGIS extends Postgres data types to handle spatial data. The primary spatial types are:
- GEOMETRY: A flexible type for spatial data, supporting various shapes. It models shapes in the cartesian coordinate plane. Each
GEOMETRY
value is also associated with a spatial reference system (SRS), which defines the coordinate system and units of measurement. - GEOGRAPHY: Specifically designed for large-scale spatial operations on the Earth's surface, factoring in the Earth's curvature. The coordinates for a
GEOGRAPHY
shape are specified in degrees of latitude and longitude.
The actual shapes are stored as a set of coordinates. For example, a point is stored as a pair of coordinates, a line as a set of points, and a polygon as a set of lines.
Longer example
PostGIS provides a number of other functions for spatial analysis - area, distance, intersection, and more. To illustrate, we'll create dataset representing a small set of landmarks and roads in a fictional city and run spatial queries on it.
Creating the test dataset
Nearest landmark to a given point
Finding the nearest places to a given point is a common spatial query. We can use the ST_Distance
function to find the distance between two points and order the results by distance.
This query returns the following:
Intersection of Roads
We can use the ST_Intersects
function to find if two roads intersect. To ensure we don't get duplicate pairs of roads, we filter out pairs where the first road has a higher id
than the second road.
This query returns the following:
Buffer zone around a landmark
Say, the municipal council wants to create a buffer zone of 50 units around landmarks and check which roads intersect these zones. ST_Buffer
computes an area around the given point with the specified radius.
This query returns the following:
Line of Sight Between Landmarks
To check if there's a direct line of sight (no roads intersecting) between two landmarks, we can combine two postgis
functions.
This query returns the following:
This tells us there's no direct line of sight between the Park and the Library.
Performance considerations
When working with PostGIS, thinking about performance is crucial, especially when dealing with large datasets or complex spatial queries.
Indexing
GIST (Generalized Search Tree) is the default spatial index in PostGIS. GiST indexes are well-suited for multidimensional data, like points, lines, and polygons. It can significantly improve query performance, especially for spatial search operations and joins.
Query optimization
- Unnecessary Casting:
GEOMETRY
andGEOGRAPHY
are the two primary data types inpostgis
, and a lot of functions are overloaded to work with both. However, casting between the two types can be expensive, so it's best to store data in the more frequently used type. - Use Appropriate Precision: Reducing the precision of coordinates can often improve performance without significantly impacting the results.
Conclusion
These examples provide a quick introduction to handling and analyzing spatial data in PostgresQL. We saw how to create tables with spatial data, insert data, and perform spatial queries using the postgis
extension. It offers a powerful set of tools, with functions for calculating distances, identifying spatial relationships, and aggregating spatial data.
Resources
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.