Skip to main content
SereneDBSereneDB
961Contact Us

Geospatial Search

The inverted index indexes geographic shapes for fast spatial predicates — finding which points fall inside a region, which regions contain a location and so on. Geometries can be stored either as GeoJSON in a JSON column or as values of the native GEOMETRY type, and both are indexed with a geospatial text search dictionary.

Creating a geospatial index​

Use the encode_geojson dictionary template on a JSON column holding GeoJSON. The column type must be JSON or GEOMETRY — a plain VARCHAR is rejected for geo analyzers:

SQL
CREATE TABLE geo_data (id INTEGER PRIMARY KEY, geo JSON);
CREATE TEXT SEARCH DICTIONARY geojson_shape AS encode_geojson();
CREATE INDEX geo_idx ON geo_data USING inverted (id, geo geojson_shape);
INSERT INTO geo_data VALUES    (1, '{"type":"Point","coordinates":[37.615895,55.703900]}'),    (2, '{"type":"Point","coordinates":[37.616297,55.704831]}'),    (3, '{"type":"Point","coordinates":[37.617053,55.704610]}'),    (4, '{"type":"Point","coordinates":[37.559509,55.715895]}'),    (5, '{"type":"Point","coordinates":[37.701645,55.832144]}'),    (6, '{"type":"Polygon","coordinates":[[[37.614323,55.705898],[37.615825,55.705898],[37.615825,55.706520],[37.614323,55.706520],[37.614323,55.705898]]]}');
VACUUM (REFRESH_TABLE) geo_data;
SELECT id FROM geo_idxWHERE ST_Intersects(geo, '{"type":"Polygon","coordinates":[[[37.610,55.700],[37.620,55.700],[37.620,55.710],[37.610,55.710],[37.610,55.700]]]}')ORDER BY id;
 id
----
  1
  2
  3
  6

The encode_geojson template indexes Point, LineString, Polygon, MultiPoint, MultiLineString and MultiPolygon geometries. A row holding a GeometryCollection, or a value that is not valid GeoJSON, is stored without an error but gets no geo terms, so the spatial predicates below never find it. GeoJSON coordinates are [longitude, latitude]. An encode_geopoint template is also available for extracting a point from latitude/longitude fields of a JSON object, and the encode_geojson template accepts coding := 's2point' to store a compact S2 encoding of each geometry instead of the source value.

Indexing a GEOMETRY column​

The native GEOMETRY type is indexed the same way, with two requirements. The column must be declared with the CRS84 coordinate reference system, as GEOMETRY('OGC:CRS84'): without one, or with another, CREATE INDEX fails. And it takes only the encode_geojson template, with the default source coding or with coding := 's2point'; encode_geopoint reads JSON fields and needs a JSON column. Values are written from WKT, which uses the same longitude latitude axis order as GeoJSON:

SQL
CREATE TABLE geo_shapes (id INTEGER PRIMARY KEY, geo GEOMETRY('OGC:CRS84'));
CREATE TEXT SEARCH DICTIONARY geojson_s2 AS encode_geojson(coding := 's2point');
CREATE INDEX geo_shapes_idx ON geo_shapes USING inverted (id, geo geojson_s2);
INSERT INTO geo_shapes VALUES    (1, 'POINT(37.615895 55.703900)'::GEOMETRY('OGC:CRS84')),    (2, 'POINT(37.616297 55.704831)'::GEOMETRY('OGC:CRS84')),    (3, 'POINT(37.617053 55.704610)'::GEOMETRY('OGC:CRS84')),    (4, 'POINT(37.559509 55.715895)'::GEOMETRY('OGC:CRS84')),    (6, 'POLYGON((37.614323 55.705898, 37.615825 55.705898, 37.615825 55.706520, 37.614323 55.706520, 37.614323 55.705898))'::GEOMETRY('OGC:CRS84'));
VACUUM (REFRESH_TABLE) geo_shapes;
SELECT id FROM geo_shapes_idxWHERE ST_Intersects(geo, '{"type":"Polygon","coordinates":[[[37.610,55.700],[37.620,55.700],[37.620,55.710],[37.610,55.710],[37.610,55.700]]]}')ORDER BY id;
 id
----
  1
  2
  3
  6

Query shapes may be supplied as GeoJSON text (as above) or as GEOMETRY literals. For example, ST_Distance_Centroid accepts a GEOMETRY centroid:

SQL
SELECT id FROM geo_shapes_idxWHERE ST_Distance_Centroid(geo, 'POINT(37.616 55.704)'::GEOMETRY('OGC:CRS84')) < 500.0ORDER BY id;
 id
----
  1
  2
  3
  6

Indexing latitude/longitude with encode_geopoint​

When your data already stores coordinates as separate latitude and longitude fields of a JSON object, the encode_geopoint template builds a point from them directly — no GeoJSON assembly needed. Name the two fields with the latitude and longitude options:

SQL
CREATE TABLE places (id INTEGER PRIMARY KEY, loc JSON);
CREATE TEXT SEARCH DICTIONARY geopoint_dict AS    encode_geopoint('lat', 'lng');
CREATE INDEX places_idx ON places USING inverted (id, loc geopoint_dict);
INSERT INTO places VALUES    (1, '{"lat":55.703900,"lng":37.615895}'),    (2, '{"lat":55.704831,"lng":37.616297}'),    (3, '{"lat":55.704610,"lng":37.617053}'),    (4, '{"lat":55.715895,"lng":37.559509}');
VACUUM (REFRESH_TABLE) places;
SELECT idFROM places_idxWHERE ST_Intersects(        loc,        '{"type":"Polygon","coordinates":[[[37.610,55.700],[37.620,55.700],[37.620,55.710],[37.610,55.710],[37.610,55.700]]]}'     )ORDER BY id;
 id
----
  1
  2
  3

Spatial predicates​

ST_Intersects​

ST_Intersects(field, shape) matches rows whose indexed geometry shares any space with the query shape — the bounding-box query at the top of this page finds every point and polygon intersecting that region. Intersection with a Point matches the polygons that cover it:

SQL
SELECT id FROM geo_idxWHERE ST_Intersects(geo, '{"type":"Point","coordinates":[37.615,55.706]}')ORDER BY id;
 id
----
  6

ST_Intersects is commutative — the field and shape arguments may be swapped.

ST_Contains​

ST_Contains is directional, so argument order matters:

  • ST_Contains(query_shape, field) — rows whose indexed geometry is fully contained within query_shape:

    SQL
    SELECT id FROM geo_idxWHERE ST_Contains('{"type":"Polygon","coordinates":[[[37.610,55.700],[37.620,55.700],[37.620,55.710],[37.610,55.710],[37.610,55.700]]]}', geo)ORDER BY id;
     id
----
  1
  2
  3
  6

  • ST_Contains(field, query_shape) — rows whose indexed geometry fully contains query_shape (e.g. which polygon contains a point):

    SQL
    SELECT id FROM geo_idxWHERE ST_Contains(geo, '{"type":"Point","coordinates":[37.615,55.706]}')ORDER BY id;
     id
----
  6

Distance predicates​

ST_Distance_Between(field, centroid, min, max [, incl_min [, incl_max]]) matches rows within a geodesic distance range of a centroid. ST_Distance_Centroid(field, centroid) compared with a constant in WHERE, as in ST_Distance_Centroid(geo, c) < 500.0, keeps the rows within that many metres of the centroid. It cannot order or return rows: in ORDER BY or a select list it fails with Inverted index function called outside inverted index context. Use in WHERE clause on a table with an inverted index. See the function reference.

How rows are checked​

Geo predicates are exact: a row matches only when its stored geometry satisfies the predicate. The function reference lists the pairs of shapes a predicate rejects or always answers false for. When the predicate is a required condition of the query, it is checked with the query's other filters: EXPLAIN lists it under Table Filter with Verify: table filter, and the Index Filter shows the index lookup with Verify: false. Under OR, under NOT, next to ts_offsets(), in ts_dict_agg and with a vector score, it is checked as the index finds the rows, shown as Verify: inline.

See also​

Edit this page ↗