0

I am new to SQL. I am trying to execute some queries on a database using PostgreSQL with the PostGIS extension. I cannot understand why the execution of the following query takes about 5 mins

select st_buffer(l.geom, 0.015), st_buffer(s.geom, 0.015)  
from   "road" as l,  "building" as s

while the execution of these queries

1) select st_buffer(l.geom, 0.015) from "road" as l

2) select st_buffer(s.geom, 0.015) from "building" as s

take about 300 ms for each query.

I have noticed that the execution of the query below takes a long time too.

select st_buffer(l.geom, 0.015)
from   "road" as l,  "building" as s

Moreover, I want to execute the ST_Intersection between the two buffer resulting from the queries. Something like this:

select st_intersection(st_buffer(l.geom, 0.015), st_buffer(s.geom, 0.015))
from   "road" as l,  "building" as s

What is the correct way to do that?

  • 4
    You've requested a Cartesian product between the two tables. Of course it will take a long time. The first rule of table joins is to use the JOIN keyword, instead of adding tables to the FROM list; this will force you to consider how you want the table to be joined (instead of relying on the WHERE clause to untangle the mess) -- As a bonus, it makes it more difficult to forget the join rule in the WHERE. Your buffer units are so tiny, I have to wonder if you're using degrees as units, in which case, you should be using the geography type (casting if necessary) – Vince Mar 30 at 19:04
  • Did you compare the number of features in your queries? You will notice that it's not a addition of road and building. – etrimaille Mar 30 at 19:05
  • 1
    If you want the addition of building and road in a single query, maybe you want to do a UNION: SELECT ST_BUFFER(l.geom, 0.015) FROM road AS l UNION SELECT ST_BUFFER(s.geom, 0.015) FROM building AS s ? – etrimaille Mar 30 at 19:07
3

The way you have it set up is repeating the process for all possible pairings of roads and buildings, i.e. a cartesian product. If you just want to get the buffers of the two tables into a single table then use UNION ALL

SELECT
    ST_Buffer(l.geom, 0.015)
FROM
    "road" as l
UNION ALL
SELECT
    ST_Buffer(s.geom, 0.015)  
FROM
    "building" as s

In response to your clarified question, the way to intersect it can be done like so:

SELECT
    ST_Intersection(rb.geom, bb.geom) AS geom
FROM
    (SELECT ST_Buffer(geom, 0.015) AS geom FROM "road") AS rb
    (SELECT ST_Buffer(geom, 0.015) AS geom FROM "building") AS bb

However I like to put the subqueries into common table expressions because I believe it improves readability, especially when the subqueries become more complex

WITH road_buffer AS (
    SELECT
        ST_Buffer(geom, 0.015) AS geom
    FROM
        "road"
),

building_buffer AS (
    SELECT
        ST_Buffer(geom, 0.015) AS geom
    FROM
        "building"
)

SELECT
    ST_Intersection(rb.geom, bb.geom)
FROM
    road_buffer AS rb,
    building_buffer as bb
  • In the case I want to execute an ST_Intersection between the result of the two queries, what is the right way? – Ricla Mar 31 at 12:36
  • @Ricla Please Edit the question to specify that you want the output to be an intersection, and clarify if you want a buffer of the intersection point, or the intersection of the buffers. – Vince Mar 31 at 12:48
  • @Ricla I have updated the answer. – wfgeo Mar 31 at 13:24

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.