3

I have stored 2.5 million data points (addresses) using the geometry data type in column latlong with SRID 4326. The latlon - column has a GIST index defined. I would select all addresses within a distance given in metres.

I understand that SRID 4326 measures distance in degrees, so it doesn't make sense in my case. However the query

select *
from adresse
where st_distance(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography) < 200

is incredibly slow, I guess it is unable to use the index.

How can I speed up this query?

Improve this question
2
  • Have you tried to sava data as geography instead of using cast?
    – user30184
    Commented Jul 27, 2016 at 17:01
  • Did that and it already helped a lot. Casting prevents using the index, at least it seems to be in my case.
    – JohnDoe
    Commented Jul 27, 2016 at 19:24

1 Answer 1

Reset to default
6

You want to use ST_DWithin() which takes advantage of indexing

SELECT *
FROM adresse
WHERE ST_DWithin(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography, 200)
ORDER BY ST_Distance(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography);

See also http://postgis.net/2013/08/26/tip_ST_DWithin/

Improve this answer
1
  • ST_DWithin was already at least 10 times faster. After adding an additional column of type geography, defining and index and using ST_DWithin I am now in the area of milliseconds. Mission accomplished.
    – JohnDoe
    Commented Jul 27, 2016 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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