Spatial SQL: Code Reference

7.12 Measurements in spatial SQL

7.52

select
    st_area(geom) as area
from
    nyc_building_footprints
limit
    5

7.53

select
    st_area(geom :: geography) as area
from
    nyc_building_footprints
limit
    5

7.54

with one as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10009'
),
two as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10001'
)
select
    st_closestpoint(one.geom, two.geom) as point
from
    one,
    two

7.55

with one as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10009'
),
two as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10001'
)
select
    st_distance(one.geom, two.geom) as dist
from
    one,
    two

7.56

with one as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10009'
),
two as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10001'
)
select
    st_distance(one.geom, two.geom) / 5280 as dist
from
    one,
    two

7.57

with one as (
    select
        geom :: geography as geog
    from
        nyc_zips
    where
        zipcode = '10009'
),
two as (
    select
        geom :: geography as geog
    from
        nyc_zips
    where
        zipcode = '10001'
)
select
    st_distance(one.geog, two.geog) / 1609 as dist
from
    one,
    two

7.58

select
    st_srid(geom)
from
    nyc_zips
limit
    1   

7.59

with one as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10009'
),
two as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10001'
)
select
    st_transform(st_shortestline(one.geom, two.geom), 4326) as line
from
    one,
    two

7.60

select
    st_length(geom :: geography)
from
    nyc_bike_routes
limit
    1

7.61

select
    st_perimeter(geom)
from
    nyc_zips
where
    zipcode = '10009'

7.62

with one as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10009'
),
two as (
    select
        geom
    from
        nyc_zips
    where
        zipcode = '10001'
)
select
    st_transform(st_shortestline(one.geom, two.geom), 4326) as line
from
    one,
    two