with a as (
select
geom,
bldg_heigh,
ground_ele,
gid
from
denver_buildings
order by
random()
limit
2
)
select
-- This will create an array for the two buildings above.
-- One below for the centroid
array_agg(st_centroid(st_transform(geom, 4326))) as geom,
-- One for the building height + ground height
array_agg(bldg_heigh + ground_ele) as height,
-- One for the building IDs
array_agg(gid) as id
from
a
with a as (
select
geom,
bldg_heigh,
ground_ele,
gid
from
denver_buildings
order by
random()
limit
2
), bldgs as (
select
array_agg(st_centroid(st_transform(geom, 4326))) as geom,
array_agg(bldg_heigh + ground_ele) as height,
array_agg(gid) as id
from
a
),
line as (
select
-- Here we create a line between the two centroids
-- using both the geometries in individual subqueries
st_makeline(
(
select
geom [1]
from
bldgs
),
(
select
geom [2]
from
bldgs
)
)
)
select
*
from
line
with a as (
select
geom,
bldg_heigh,
ground_ele,
gid
from
denver_buildings
order by
random()
limit
2
), bldgs as (
select
array_agg(st_centroid(st_transform(geom, 4326))) as geom,
array_agg(bldg_heigh + ground_ele) as height,
array_agg(gid) as id
from
a
),
line as (
-- Here we use a simple select statement rather tha subqueries
-- like the previous step to grab the height column too
select
st_makeline(geom [1], geom [2]) as geom,
height
from
bldgs
)
select
-- This will return all the buildings higher than the line
b.gid,
b.bldg_heigh + b.ground_ele as height,
st_transform(b.geom, 4326),
line.height
from
denver_buildings b
join line on st_intersects(line.geom, st_transform(b.geom, 4326))
where
-- This finds any building taller than either of our two buildings
b.bldg_heigh + b.ground_ele < line.height [1]
or b.bldg_heigh + b.ground_ele < line.height [2]