databasealgorithmpostgresqlperformancepostgis

How to speed up query that use postgis extension?

I have the following query that checks whether is point (T.latitude, T.longitude) is inside a POLYGON

query = """
  SELECT id
  FROM T
  WHERE ST_Intersects(ST_Point(T.latitude, T.longitude), 'POLYGON(({points}))')
"""

But it works slow, how can I speed up it if I have the following index: (latitude, longitude)?

The query is slow because it must compute the formula for every possible pair of points. So it makes the postgress server do a lot of math, and it forces it to scan through your whole location table. How can we optimize this? Maybe we can eliminate the points that are too far north or too far south or too far east or west?

Solution

  • 1) Add a geometry column of type Geometry(Point) and fill it:

    ALTER TABLE T add COLUMN geom geometry(Point);
UPDATE T SET geom = ST_Point(T.latitude, T.longitude);

    2) Create a spatial index:

    CREATE INDEX t_gix ON t USING GIST (geom);

    3) Use ST_DWithin instead of ST_Intersect:

    WHERE ST_DWithin('POLYGON(({points}))', geom, 0)

    You want actually find the points which are within a polygon, so ST_DWithin() is what you need. From the documentation:

    This function call will automatically include a bounding box comparison that will make use of any indexes that are available

    PS:

    If you for some reason cannot make the points 1 and 2, so at least use ST_Dwithin instead of ST_Intersect:

    WHERE ST_DWithin('POLYGON(({points}))', ST_Point(T.latitude, T.longitude), 0)

    The last parameter is the tolerance.