Postgres: Filter Using Geospatial Data
PostGIS spatial relationship operators
The _st_contains, _st_crosses, _st_equals, _st_intersects, _st_3d_intersects, _st_overlaps, _st_touches,
_st_within, _st_d_within, and _st_3d_d_within operators are used to filter based on geometry like columns.
_st_d_within and _st_intersects can be used on geography columns also (but their 3D variations are for geometry
only).
For more details on spatial relationship operators and Postgres equivalents, refer to the API reference.
Use JSON representation (see GeoJSON) of geometry and geography values in
variables as shown in the following examples:
_st_contains
_st_contains is a spatial operator that checks whether one geometry contains another. It returns values if the first
geometry completely contains the second geometry.
_st_crosses
_st_crosses is a spatial operator that checks whether two geometries cross each other. It returns values if the two
geometries cross one another.
_st_equals
_st_equals is a spatial operator that checks whether two geometries are equal. It returns values if the two geometries
have the same shape and size.
_st_intersects
In this example, the getPointsWithinPolygon query takes in a polygon argument that defines a rectangular area on a
map. The location field returns the latitude and longitude of points that intersect with the given polygon using the
_st_intersects operator.
_st_within
_st_within is a spatial operator that checks whether one geometry is completely contained within another geometry. It
returns values if the first geometry is completely contained within the second geometry.
_st_d_within
Fetch a list of geometry values which are within a 3-unit buffer from a given point value:
_st_3d_d_within
This is completely analogous to the _st_d_within example above, the only difference being that our coordinates now
contain a z-value.
_st_3d_intersects
Fetch a list of (3D) geometry values which intersect a given polygon value:
_st_overlaps
_st_overlaps is a spatial operator that checks whether two geometries overlap each other and that their two compared
geometries are of the same dimension. It returns values if the two geometries share some, but not all, points.
_st_touches
_st_touches is a spatial operator that checks whether two geometries share a boundary, but do not overlap. It returns
values if the two geometries share one or more points on their boundaries.
Intersect operators on RASTER columns
Intersect operators on columns with raster type are supported. Please submit a feature request via
GitHub if you want support for more functions.
For more details on intersect operators on raster columns and Postgres equivalents, refer to the API reference.
_st_intersects_rast
Filter the raster values which intersect the input raster value.
Executes the following SQL function:
boolean ST_Intersects( raster <raster-col> , raster <raster-value> );
_st_intersects_geom_nband
Filter the raster values which intersect the input geometry value and optional band number.
Executes the following SQL function:
boolean ST_Intersects( raster <raster-col> , geometry geommin , integer nband=NULL );
_st_intersects_nband_geom
Filter the raster values (with specified band number) which intersect the input geometry value.
Executes the following SQL function:
boolean ST_Intersects( raster <raster-col> , integer nband , geometry geommin );