intersect with ST_Buffer in PostGIS

Question

I have a table called "gebaeude" with all types of buildings in the city. In the following I want to use a Buffer around the central train station that has the id="2" and a radius of 1000m. Then I want to select the building types "kinergarten" and "school" that are within this radius by intersecting them with the buffer. What I did looks like this:

WITH center AS (
     SELECT ST_Buffer(geom, 1000) FROM gebaeude 
     WHERE id='2')
SELECT gebaeude.name AS "Name",
        ST_Distance(center.geom, gebaeude) AS Distanz
FROM gebaeude, center
WHERE (gebaeude='school' OR gebaeude.type='kindergarten')
AND ST_Intersects(gebaeude.geom,center.geom);

I get the following error:

ERROR: column center.geom does not exist LINE 5: ST_Distance(center.geom, gebaeude) AS Distanz ^

What am I doing wrong?

Answer 1

If you don't specify column names in the CTE, they will have the same names as in the inner select statement, so here the column would be named st_buffer instead of geom.

You can set the name at the CTE level

WITH center(geom) AS (
     SELECT ST_Buffer(geom, 1000) ...
)

or within the inner select:

WITH center AS (
     SELECT ST_Buffer(geom, 1000) as geom ...
)

But really, don't use a buffer here, it is not the way to go with your issue. Use ST_DWithin instead.