Question 113922 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use database index correctly when searching on geo field?

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.

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    As @fresser and @laurenz-albe helpfully suggested, the correct index is GIST index:

    from django.contrib.postgres.indexes import GistIndex

class Place:
    ...

    class Meta:
        indexes = [
            GistIndex(fields=["location"]),
        ]

  2. 0

    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);
    Login or Signup to reply.
  3. 0

    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);
    Login or Signup to reply.
  4. 0

    Your problem is that your data is in degrees (EPSG:4326) and you ask for everything with in 20 of a point. That 30 is in degrees and probably includes a large chunk (if not all) of your data so PostGIS is too smart to use your index.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search