Spatial SQL: Code Reference

6.6 Joins

6.38

select
    nyc_311.complaint_type,
    nyc_311.incident_zip,
    nyc_zips.population
from
    nyc_311
    join nyc_zips on nyc_311.incident_zip = nyc_zips.zipcode
limit
    5

6.39

select
    nyc_311.complaint_type,
    nyc_311.incident_zip,
    nyc_zips.population
from
    nyc_311
    join nyc_zips on nyc_311.incident_zip = nyc_zips.zipcode
where
    nyc_zips.population > 80000
limit
    5

6.40

select
    a.complaint_type,
    a.incident_zip,
    b.population
from
    nyc_311 a
    join nyc_zips b on a.incident_zip = b.zipcode
where
    b.population > 80000
limit
    5

6.41

with b as (select population, zipcode from nyc_zips limit 30)

select
    a.complaint_type,
    a.incident_zip,
    b.population
from
    nyc_311 a
    left join b on a.incident_zip = b.zipcode
limit
    5

6.42

with a as (
    select
        complaint_type,
        incident_zip
    from
        nyc_311
    limit
        30
)
select
    a.complaint_type,
    a.incident_zip,
    b.population
from
    a
    right join nyc_zips b on a.incident_zip = b.zipcode
limit
    5

6.43

with a as (
    select
        complaint_type,
        incident_zip
    from
        nyc_311
    limit
        30
), b as (
    select
        population,
        zipcode
    from
        nyc_zips
    limit
        30
)
select
    a.complaint_type,
    a.incident_zip,
    b.population
from
    a full
    outer join b on a.incident_zip = b.zipcode
limit
    100

6.44

with a as (
    select
        neighborhood
    from
        nyc_neighborhoods
    limit
        2
), b as (
    select
        population,
        zipcode
    from
        nyc_zips
    limit
        2
)
select
    a.neighborhood,
    b.population
from
    a
    cross join b

6.45

with a as (
    select
        neighborhood
    from
        nyc_neighborhoods
    limit
        2
), b as (
    select
        population,
        zipcode
    from
        nyc_zips
    limit
        2
)
select
    a.neighborhood,
    b.population
from
    a,
    b

6.46

with a as (
    select
        neighborhood
    from
        nyc_neighborhoods
    limit
        2
), b as (
    select
        population,
        zipcode
    from
        nyc_zips
    limit
        2
)
select
    a.neighborhood,
    b.population,
    b.population / 1000 as calculation
from
    a,
    b

6.47

with a as (
    select
        ogc_fid,
        zipcode :: text
    from
        nyc_mappluto
)
select
    count(a.ogc_fid),
    b.zipcode
from
    nyc_zips b
    join a using(zipcode)
group by
    b.zipcode
order by
    count(a.ogc_fid) desc

6.49

with a as (
    select
        ogc_fid,
        zipcode :: text
    from
        nyc_mappluto
    order by
        ogc_fid desc
    limit
        5000
), c as (
    select
        ogc_fid,
        zipcode
    from
        nyc_2015_tree_census
    order by
        ogc_fid desc
    limit
        5000
)
select
    count(a.ogc_fid) as buildings,
    -- count(c.ogc_fid) as trees,  
    b.zipcode
from
    nyc_zips b
    join a using(zipcode) 
    -- join c  
    -- using(zipcode)  
group by
    b.zipcode
order by
    count(a.ogc_fid) desc

6.50

with a as (
    select
        ogc_fid,
        zipcode :: text
    from
        nyc_mappluto
    order by
        ogc_fid desc
    limit
        5000
), c as (
    select
        ogc_fid,
        zipcode
    from
        nyc_2015_tree_census
    order by
        ogc_fid desc
    limit
        5000
)
select
    -- count(a.ogc_fid) as buildings, 
    count(c.ogc_fid) as trees,
    b.zipcode
from
    nyc_zips b 
    -- join a using(zipcode)  
    join c using(zipcode)
group by
    b.zipcode 
    -- order by count(a.ogc_fid) desc  
order by
    count(c.ogc_fid) desc

6.51

with a as (
    select
        count(ogc_fid) as buildings,
        zipcode :: text
    from
        nyc_mappluto
    group by
        zipcode
),
b as (
    select
        count(ogc_fid) as trees,
        zipcode
    from
        nyc_2015_tree_census
    group by
        zipcode
)
select
    a.buildings,
    b.trees,
    c.zipcode
from
    nyc_zips c
    join a using(zipcode)
    join b using(zipcode)
order by
    b.trees desc