Geek Answers Handbook

Using an alias from a JOIN statement in FROM

I am trying to build a PostGIS request that uses the JOIN operator alias in the FROM statement, but that is not possible. Is there any other way to do this?

SELECT DISTINCT 
ST_Buffer(
    ST_Centroid(geom),
    ST_Area(geom))
FROM building AS b, ST_Transform(sg.geometry, 31467) AS geom 
LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id WHERE ST_Area(geom) < 100
+5
source share
2 answers

You can enter an additional subquery level.

So now you have:

SELECT DISTINCT ST_Buffer( ST_Centroid(geom), ST_Area(geom) )
FROM building AS b,
     ST_Transform(sg.geometry, 31467) AS geom 
     LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id
WHERE ST_Area(geom) < 100

(I changed the indentation to reflect on this a bit, although I still don't understand it: you don't seem to use the values ​​from b anywhere). You need something like:

SELECT DISTINCT ST_Buffer( ST_Centroid(geom), ST_Area(geom) )
FROM (
    SELECT ST_Transform(sg.geometry, 31467) AS geom 
    FROM building AS b
         LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id
) x
WHERE ST_Area(geom) < 100
+3
source

Why don't you try:

SELECT DISTINCT 
    ST_Buffer(ST_Centroid(ST_Transform(sg.geometry, 31467),
              ST_Area(ST_Transform(sg.geometry, 31467)))
FROM building b LEFT JOIN surface_geometry sg ON (b.id = sg.cityobject_id)
WHERE ST_Area(ST_Transform(sg.geometry, 31467)) < 100;

It is also possible that this might work:

SELECT DISTINCT 
    ST_Buffer(ST_Centroid(geom), ST_Area(geom))
FROM building b LEFT JOIN surface_geometry sg ON (b.id = sg.cityobject_id) INNER JOIN
     ST_Transform(surface_geometry.geometry, 31467) geom ON (ST_Area(geom) < 100);

You might want to check the placement (), because at the moment I have nowhere to check it.

+1
source

More articles:


All Articles