SELECT
	COUNT(members.id) AS count,
	members.name AS member_name
FROM
	requests
	RIGHT JOIN members ON requests.member_id = members.id
GROUP BY
	members.id
ORDER BY
	count DESC;

SELECT
	SUBSTRING(buildings.address, 1, 64) AS building_address,
	COUNT(DISTINCT requests.member_id) AS request_count,
	COUNT(DISTINCT nodes.member_id) AS node_count,
	JSON_AGG(DISTINCT nodes.id) AS node_ids,
	JSON_AGG(DISTINCT members.email) AS request_emails
FROM
	buildings
	JOIN requests ON buildings.id = requests.building_id
	JOIN members ON members.id = requests.member_id
	JOIN nodes ON buildings.id = nodes.building_id
WHERE
	nodes.status = 'active'
GROUP BY
	buildings.id
HAVING
	COUNT(DISTINCT requests.member_id) > COUNT(DISTINCT nodes.member_id)
ORDER BY
	request_count DESC

SELECT
buildings.alt,
COUNT(DISTINCT requests.id) as request_count,
SUBSTRING(buildings.address, 1, 64) as building_address,
ARRAY_AGG(DISTINCT nodes.id) as node_ids,
ARRAY_AGG(DISTINCT panoramas.url) as pano_ids
FROM buildings
JOIN requests
ON buildings.id = requests.building_id
FULL JOIN nodes
ON buildings.id = nodes.building_id
JOIN panoramas
ON requests.id = panoramas.request_id
WHERE requests.roof_access IS TRUE
GROUP BY buildings.id
ORDER BY buildings.alt DESC;

SELECT
SUBSTRING(buildings.address, 1, 64) as building_address,
COUNT(buildings.id) as count
FROM requests
RIGHT JOIN buildings
ON requests.building_id = buildings.id
GROUP BY buildings.id
ORDER BY count DESC;

SELECT
buildings.alt as building_height,
-- COUNT(requests.id) as request_count,
COUNT(buildings.id) as node_count,
SUBSTRING (buildings.address, 1, 64) as building_address
FROM nodes
RIGHT JOIN buildings
ON nodes.building_id = buildings.id
RIGHT JOIN requests
ON nodes.building_id = requests.building_id
GROUP BY buildings.id
ORDER BY node_count DESC;

SELECT array_agg(id) FROM nodes WHERE nodes.building_id = \$1;

SELECT
buildings.alt as building_height,
COUNT(buildings.id) as node_count,
SUBSTRING (buildings.address, 1, 64) as building_address
FROM nodes
RIGHT JOIN buildings
ON nodes.building_id = buildings.id
GROUP BY buildings.id
ORDER BY node_count DESC;

SELECT
buildings.id,
COUNT(DISTINCT requests.id) as request_count,
COUNT(DISTINCT nodes.id) as node_count,
ARRAY_AGG(DISTINCT nodes.id) as node_ids,
SUBSTRING(buildings.address, 1, 64) as building_address
FROM buildings
JOIN requests
ON buildings.id = requests.building_id
JOIN nodes
ON buildings.id = nodes.building_id
GROUP BY buildings.id
ORDER BY request_count DESC;

SELECT
buildings.alt as building_height,
COUNT(nodes.id) as node_count,
SUBSTRING(buildings.address, 1, 64) as building_address
FROM nodes
RIGHT JOIN buildings
ON nodes.building_id = buildings.id
GROUP BY buildings.id
ORDER BY building_height DESC;

SELECT
buildings.id as building_id,
buildings.alt as building_height,
COUNT(nodes.id) as node_count,
array_agg(nodes.id) as node_ids,
SUBSTRING(buildings.address, 1, 64) as building_address
FROM buildings
LEFT JOIN nodes
ON buildings.id = nodes.building_id
GROUP BY buildings.id
-- HAVING COUNT(nodes.id) > 0 -- Toggle this line to hide/show nodeless buildings
ORDER BY building_height DESC;

SELECT
buildings.id as building_id,
buildings.alt as building_height,
COUNT(requests.id) as request_count,
array_agg(requests.id) as request_ids,
SUBSTRING(buildings.address, 1, 64) as building_address
FROM buildings
LEFT JOIN requests
ON buildings.id = requests.building_id
GROUP BY buildings.id
-- HAVING COUNT(nodes.id) > 0 -- Toggle this line to hide/show nodeless buildings
ORDER BY building_height DESC;

SELECT ST_AsText(ST_Centroid((SELECT geom FROM ny WHERE bldg_bin = '1001389'))) as a,
ST_AsText(ST_Centroid((SELECT geom FROM ny WHERE bldg_bin = '1006184'))) as b;
#                     a                     |                    b
# ------------------------------------------+------------------------------------------
#  POINT(987642.232749068 203357.276907034) | POINT(983915.956115596 198271.837494287)
# (1 row)

SELECT ST_ZMax((SELECT geom FROM ny WHERE bldg_bin = '1001389')) as a,
ST_ZMax((SELECT geom FROM ny WHERE bldg_bin = '1006184')) as b;
#         a         |        b
# ------------------+------------------
#  582.247499999998 | 120.199699999997
# (1 row)

SELECT a.bldg_bin
FROM ny AS a
WHERE ST_3DIntersects(a.geom, ST_SetSRID('LINESTRINGZ (983915 198271 582, 987642 203357 120)'::geometry, 2263));
#  bldg_bin
# ----------
# (0 rows)