Spatial SQL: Code Reference

7.6 Constructors

7.16

with a as (
    select
        geom
    from
        nyc_zips
    limit
        5
)
select
    st_collect(geom)
from
    a

7.17

with a as (
    select
        geom,
        population,
        zipcode
    from
        nyc_zips
    limit
        5
)
select
    string_agg(zipcode, ',') as zips,
    sum(population) as total_pop,
    st_collect(geom)
from
    a

7.18

select
    st_makeenvelope(-66.125091, 18.296531, -65.99142, 18.471986) as geom

7.19

select
    st_makeenvelope(
        -66.125091,
        18.296531,
        -65.99142,
        18.471986,
        4326
    ) as geom

7.20

select
    st_makepoint(pickup_longitude, pickup_latitude) as geom
from
    nyc_yellow_taxi_0601_0615_2016
limit
    100

7.21

select
    ogc_fid,
    st_setsrid(
        st_makepoint(pickup_longitude, pickup_latitude),
        4326
    ) as geom
from
    nyc_yellow_taxi_0601_0615_2016
limit
    100

7.22

create or replace function BuildPoint(x numeric, y numeric, srid int) 
    returns geometry 
    language plpgsql 
    as $$ 
        begin 
        return st_setsrid(st_makepoint(x, y), srid);
    end;
$$;

7.23

select
    ogc_fid,
    buildpoint(
        pickup_longitude :: numeric,
        pickup_latitude :: numeric,
        4326
    ) as geom
from
    nyc_yellow_taxi_0601_0615_2016
limit
    100

7.24

SELECT
    st_setsrid(
        ST_Translate(ST_Scale(ST_Letters('Spatial SQL'), 1, 1), 0, 0),
        4326
    );