Spatial SQL: Code Reference

7.11 Outputs

7.40

select
    st_asgeojson(geom)
from
    nyc_building_footprints
limit
    3

7.41

select
    st_geomfromtext('POINT(-73.9772294 40.7527262)', 4326) as geom

7.42

select
    st_buffer(st_transform(geom, 4326) :: geography, -200)
from
    nyc_zips
limit
    10

7.43

select
    st_centroid(st_transform(geom, 4326) :: geography)
from
    nyc_zips
limit
    10

7.44

select
    st_transform(geom, 4326) as original,
    st_chaikinsmoothing(st_transform(geom, 4326), 1) as one,
    st_chaikinsmoothing(st_transform(geom, 4326), 5) as five
from
    nyc_zips
limit
    10

7.45

-- Find the first 50 trees in Fort Greene with the
-- Latitude and longitude columns
with a as (
    select
        latitude,
        longitude
    from
        nyc_2015_tree_census
    where
        nta_name = 'Fort Greene'
    limit
        50
), 

-- Turn the latitude/longitude columns into geometries
b as (
    select
        st_collect(
            buildpoint(longitude :: numeric, latitude :: numeric, 4326)
        ) as geom
    from
        a
)

-- Create multiple concave hulls with various concave-ness
-- and use UNION to turn them into a single table
select
    st_concavehull(geom, 0.1) as hull
from
    b
union
select
    st_concavehull(geom, 0.3) as hull
from
    b
union
select
    st_concavehull(geom, 0.7) as hull
from
    b

7.46

with a as (
    select
        latitude,
        longitude
    from
        nyc_2015_tree_census
    where
        nta_name = 'Fort Greene'
    limit
        50
), b as (
    select
        st_collect(
            buildpoint(longitude :: numeric, latitude :: numeric, 4326)
        ) as geom
    from
        a
)
select
    st_convexhull(geom) as hull
from
    b

7.47

select
    st_delaunaytriangles(st_transform(geom, 4326)) as triangles
from
    nyc_zips
where
    zipcode = '10009'

7.48

select
    st_generatepoints(st_transform(geom, 4326), 500) as points
from
    nyc_zips
where
    zipcode = '10009'

7.49

select
st_linemerge(geom) as geom from
nyc_bike_routes
where
street = '7 AV'
and fromstreet = '42 ST'
and tostreet = '65 ST'

7.50

-- Create multiple geometries with different simplification levels
-- and UNION them into one table
select
    st_transform(geom, 4326) as geom
from
    nyc_zips
where
    zipcode = '11693'
union
select
    st_transform(st_simplify(geom, 1), 4326) as geom
from
    nyc_zips
where
    zipcode = '11693'
union
select
    st_transform(st_simplify(geom, 10), 4326) as geom
from
    nyc_zips
where
    zipcode = '11693'
union
select
    st_transform(st_simplify(geom, 50), 4326) as geom
from
    nyc_zips
where
    zipcode = '11693'

7.51

-- Create multiple geometries that share borders with different 
-- simplification levels and UNION them into one table
select
    st_transform(geom, 4326) as geom
from
    nyc_zips
where
    zipcode = '11434'
    or st_touches(
        geom,
        (
            select
                geom
            from
                nyc_zips
            where
                zipcode = '11434'
        )
    )
union
select
    st_transform(st_simplifypreservetopology(geom, 1), 4326) as geom
from
    nyc_zips
where
    zipcode = '11434'
    or st_touches(
        geom,
        (
            select
                geom
            from
                nyc_zips
            where
                zipcode = '11434'
        )
    )
union
select
    st_transform(st_simplifypreservetopology(geom, 10), 4326) as geom
from
    nyc_zips
where
    zipcode = '11434'
    or st_touches(
        geom,
        (
            select
                geom
            from
                nyc_zips
            where
                zipcode = '11434'
        )
    )
union
select
    st_transform(st_simplifypreservetopology(geom, 50), 4326) as geom
from
    nyc_zips
where
    zipcode = '11434'
    or st_touches(
        geom,
        (
            select
                geom
            from
                nyc_zips
            where
                zipcode = '11434'
        )
    )