1

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.

  • SELECT * FROM qnmappluto WHERE storeys >= 6 ? – alphabetasoup Jun 21 '15 at 1:17
  • The field you want is NumFloors (from the data dictionary). However, as far as I can tell from what I've read, PLUTO is a csv database of text and numeric fields. It does not contain the polygons. I think what you want is the MapPLUTO database, which contains the geometry and the field data from the PLUTO database. Perhaps this is what you're referencing in your start of a query when you write "qnmappluto" for the database name? – Llaves Jun 21 '15 at 1:50
  • Polygons aren't my problem (I'm using MapPLUTO), and I'm not looking simply for NumFloors...see edited question where I tried to clarify. The "on the block" part is where I'm getting stuck. – Stephen Smith Jun 21 '15 at 19:24
2

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.

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.