4

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);
  • 1
    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
  • 1
    @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 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
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 .

  • 1. How can I check the index issue? i'm not so familiar with this... – michael Sep 29 '15 at 7:18
  • 2. 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 7:19
  • 1
    :-) the standard rekomendation from PostGIS people is ST_DWithin. But I have seen cases where buffer/intersects is faster. Esri-people tend to recommend buffer/intersectm I think that is the Esri approach. – Nicklas Avén Sep 29 '15 at 7:57
  • Do you know how can I debug the index issue? I'm using qgis and I see only the time... – michael Sep 29 '15 at 8:01
  • 1
    I recommend taking a look in pgAdmin or psql or something else that can show what you have in the database. Create a column in your table where you put your transformed geometries. create a spatial index on that column. run analyze table_name to make planner collect some data about the table and index. put keyword explain in front of your query and look in the output for "index scan" – Nicklas Avén Sep 29 '15 at 18:06
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...

1

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).

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.