Efficiency in PostGIS: objects near point- ST_Intersects around buffer Vs ST_DWithin

Question

I need to find all buildings in radius of 25m from given location. I thought about 2 PostGis quires that can handle this, when I run it they both give same time execution. But I want to know which one of them is the more efficient way?

SELECT *
FROM buildings
WHERE  
ST_Intersects(ST_Buffer(ST_Transform(ST_GeomFromText('POINT(lon lat)', 4326), 2163), 25), ST_Transform(buildings.geom, 2163));
-----------
SELECT *
FROM buildings
WHERE  
ST_DWithin(ST_Transform(ST_GeomFromText('POINT(lon lat)', 4326), 2163), ST_Transform(buildings.geom, 2163), 25);

What is the native coordinate system of buildings.geom? Any query which requires a transform of the base table is likely to be much slower than a query that uses the native SRID. I suspect the answer will be "neither of these two". — Vince, Sep 28 '15 at 20:00
@Vince The original SRID of OSM (WGS84-4326), I performing the transformation only to use meters because it's more easy to think in meters... But it's not mandatory and you can assume ain't transformation. The question is more about the basics behind the spatial algorithms of intersection, buffer and dwithin... Anyway good to know that the transformation is 'computational heavy' — michael, Sep 28 '15 at 20:22
@Vince does it matter if he has to transform the geometry? If it's part of his workflow, which it sounds like it is, it is still a valid question. — DPSSpatial_BoycottingGISSE, Sep 28 '15 at 20:34
Far better to transform the query shape twice (from degrees to meters, then buffer, and back to degrees), to use the native spatial index in WGS84. That will be the most efficient query. — Vince, Sep 28 '15 at 20:50
Generally stated, "buffer" is best left as a concept only. You almost never really need to create a buffer. So just use Dwithin — Martin F, Sep 29 '15 at 19:04

Nicklas Avén · Answer 1 · 2015-09-29T07:57:57.723

3

Beside what is already said:

It is not the computional cost of st_transform that is the problem, but that it will kill the index.

About st_buffer approach: it is not ST_Intersects that is costly (as long as you have a working index), it is the buffering.

So the best way is to use ST_DWithin without transformation, and make sure that the index really kicks in. Check with Explain .

edited Sep 29 '15 at 07:57

answered Sep 29 '15 at 06:26

Nicklas Avén

13,241
1
39
48

How can I check the index issue? i'm not so familiar with this...

michael

Sep 29 '15 at 07:18

You actually say that the within method is better then buffer while vince say otherwise. Actually i'm little bit confused cause I trust you both...

– michael Sep 29 '15 at 07:19

score 1 · Answer 2 · edited Apr 13 '17 at 12:33

1

There is this thread on this very subject...

And an aside, but from Leo Hsu's presentation at FOSS4G 2014:

ST_Distance doesn't take advantage of the spatial index
ST_DWithin does...

...just in case you wanted to determine a third possible way to find features a given distance from each other...

edited Apr 13 '17 at 12:33

Community

1

answered Sep 28 '15 at 20:37

DPSSpatial_BoycottingGISSE

18,790
4
66
110

score 1 · Answer 3 · answered Sep 28 '15 at 22:08

PostGIS may be smart enough to reposition the transformation on optimization, but I always recommend tossing softballs at optimizers, which in this case would be to use the 25 meter "circle" (which will be non-circular in decimal degrees) as a search shape against the base buildings layer. It's also probably faster to use ST_Point than assemble well-known text on the fly (and then parse it), so the resulting query would look like:

SELECT *
FROM buildings
WHERE 
    ST_Intersects(
        ST_Transform(
            ST_Buffer(
                ST_Transform(
                    ST_SetSRID(
                        ST_Point(lon,lat),
                        4326
                    ),
                    2163
                ),
                25
            ),
            4326
        ),
        buildings.geom
    );

If the buildings layer is large, then this should run significantly faster than the other two (if it isn't large, then the first query will fill the cache and most queries will likely appear to have the same performance).

Efficiency in PostGIS: objects near point- ST_Intersects around buffer Vs ST_DWithin

3 Answers3