2

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?

  • Have you tried to sava data as geography instead of using cast? – user30184 Jul 27 '16 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 Jul 27 '16 at 19:24
5

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/

  • 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 Jul 27 '16 at 19:23

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.