How to use database index correctly when searching on geo field?-CodePudding

Suppose i have the following model:

from django.db import models
from django.contrib.gis.db import models as gis_models

class Place(models.Model):
    location = gis_models.PointField(geography=True, srid=4326)

Later i am performing the search on those Places; my query is "fetch all places no further N meters from me":

from django.contrib.gis.db.models.functions import Distance
from django.contrib.gis.geos import Point


location = Point(1.0, 2.0)
distance = 20.0

queryset = queryset.annotate(distance=Distance("location", location)).filter(
        distance__lte=distance
    )

Is there any way using PostGIS to optimize those queries? For example, using indexes or something related.

CodePudding user response：

Your code does not look like SQL, and you did not tag a programming language, framework or ORM, so I'll give you an SQL answer.

To search for all geometrys that are less than 30 from a certain point, you would use

... WHERE ST_DWithin(geom, 'POINT(1 2)', 30)

The index to support that is

CREATE INDEX ON tab USING gist (geom);

CodePudding user response：

The Postgis funciton ST_DWithin should use relevant bounding box indexes if existing.

To create such a spatial index, you can for example do the following:

CREATE INDEX unique_index_name ON table_name USING gist (geometry_column);