3

I have the following query that finds all id inside POLYGON(({points}))

SELECT id
FROM t
WHERE ST_DWithin('POLYGON(({points}))', ST_Point(latitude, longitude), 0);

The query is slow because it doesn't use the (latitude, longitude) index and must compute the formula for every possible pair of points.

How can I change my query to force Postgres to use the (latitude, longitude) index (I need to change the query because I cannot add some other index or create/change the table)?

I have the following index:

"index_latitude_longitude" btree (latitude, longitude)

latitude, longitude has double precision type.

I think that Postgres will use the index if we add something like: latitude <= ... and longitude <= .... How can we do this?

  • show index definition – Craig Ringer Jul 18 '17 at 10:44
4

You should be able to create an index on ST_Point(latitude, longitude):

CREATE INDEX index_latitude_longitude
ON t
USING GIST (ST_Point(latitude, longitude))

Update: (Updated again after suggestion from @JGH)

As you commented, you have no write access to the database, there is another possibility to use the existing Indexes to filter the possible points using the Minimum Bounding Rectangle (MBR). Then use ST_DWithin on the rest (no indexes are used).

SELECT id
FROM t, ( SELECT ST_GeomFromText('POLYGON(({points}))') as geom ) as polygon
WHERE longitude >= ST_XMIN(polygon.geom)
AND longitude <= ST_XMAX(polygon.geom)
AND latitude >= ST_YMIN(polygon.geom)
AND latitude <= ST_YMAX(polygon.geom)
AND ST_DWithin(polygon.geom, ST_Point(latitude, longitude), 0);

I would (still) suggest not doing this but rather asking the database owner to add the index from above.

  • Yes, but I don't have a write permission to the db – ipetr Jul 18 '17 at 11:16
  • 2
    Keep both solutions to avoid the shape restriction: filter roughly using the envelope and refine using the st_dwithin function. – JGH Jul 18 '17 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.