Displaying buildings where tallest building on block is above certain height?

Question

I'm trying to figure out how to use the WHERE function to display only polygons (NYC properties, I'm using the PLUTO land use database) where the height (numfloors) of the tallest building on the block (there's a block column with a unique identifier for each block) is six stories or higher. I assume this is achieved using some nested combination of max, joins, etc., but I can't quite figure it out.

Any help?

SELECT * FROM qnmappluto
WHERE ...???

I'm using MapPLUTO, not straight PLUTO. My problem isn't getting the polygons to display.

I'm not trying to display all buildings where NumFloors >= 6. Rather, I'm trying to display all buildings where the tallest building on the same block is six stories or greater – as in, where the max NumFloors value of all buildings that share the same entry in the Block column is greater than or equal to six.

Answer 1

Based on this clarification:

I'm not trying to display all buildings where NumFloors >= 6. Rather, I'm trying to display all buildings where the tallest building on the same block is six stories or greater – as in, where the max NumFloors value of all buildings that share the same entry in the Block column is greater than or equal to six.

You will need something like:

WITH highest_in_each_block AS (
    SELECT block_id, max(storeys) as max_storey
    FROM the_table
    GROUP BY block_id
)
SELECT * FROM the_table
JOIN highest_in_each_block
ON highest_in_each_block.block_id = the_table.block_id
WHERE highest_in_each_block.max_storey >= 6

I don't know what PLUTO and MapPLUTO are so I can't give you the exact SQL expression you need, but hopefully that should get you most of the way there.

To read more about this kind of SQL expression, the term you need is "common table expression" (CTE). I believe they should be acceptable within the constraints of CartoDB.