[postgis-users] faster st_dwithin

Darafei "Komяpa" Praliaskouski me at komzpa.net
Fri Feb 22 03:06:26 PST 2019


Simplest way would be to read definition of ST_DWithin and use index
lookups for smaller box, but rechecks for bigger. Don't ST_Simplify, it's
just wasting cycles.

select ... where geom && ST_Expand(other_geom, 100) and _ST_DWithin(geom,
other_geom, 110)

It will exit loop in ST_DWithin faster as threshold is higher.

Alternatively this is a case (lookup of indexed points closest to
linestring) where building ST_SimplifyPreserveTopology(ST_Buffer(geom,
(110+100)/2), (110-100)/2) in advance and then looking ST_Intersects of
that once-calculated buffer and points will be faster. Also note how your
condition is defined: you buffer on half of your tolerance more, and then
simplify by half of your tolerance, so that result shape is covering your
"non-simplified" buffer for sure.

On Fri, Feb 22, 2019 at 1:57 PM Michal Palenik <michal.palenik at oma.sk>

> hi all,
> I use postgis to query nearby points to a linestring using st_dwithin()
> however, in my usecase, the linestrings are too detailed and I do not
> need very exact results. I would like function like:
> st_dwithin(g1,g2, 100, 110) which:
> return true if distance is less than 100
> return false if distance is more than 110
> return whatever you want if the distance is between 100-110 (but make it
> fast)
> similarly, query points that are within a polygon (or "close" to it)
> currently, I use triggers to store simplified version of the linestring,
> using trial-and-error paremeters for st_simplify and st_expand (or
> larger distance parameter for st_dwithin), with an index on this
> simplified line.
> apart from problems resulting from two versions of the same line, I do
> not know how to control the max-error parameters (110 in the above
> example), due to st_simplify working only on geometries, etc.
> how would you approach this?
> thanks, Michal
> --
> michal palenik
> www.freemap.sk
> www.oma.sk
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

Darafei Praliaskouski
Support me: http://patreon.com/komzpa
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190222/c7e7cb28/attachment.html>

More information about the postgis-users mailing list