GIS stuff

helper_functions.sql

CREATE OR REPLACE FUNCTION f(geom geometry, variadic rec anyarray) RETURNS json AS
$$
BEGIN
    RETURN
        json_build_object(
                'type', 'Feature',
                'id',
                'null', -- the GeoJson spec includes an 'id' field, but it is optional. some services still cry if it's not in there, so we leave it empty just for now.
                'geometry', ST_AsGeoJSON(geom)::json,
                'properties', json_build_object(variadic rec)
            );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fc(features anyelement) RETURNS json AS
$$
BEGIN
    RETURN
        json_build_object(
                'type', 'FeatureCollection',
                'features', features);
END;
$$ LANGUAGE plpgsql;

with rasters as (select r.geometry as geom, r.id as raster_id
                 from districts d
                          join r1000_wg84 r on st_intersects(r.geometry, wkb_geometry)
                 where d.state_id = 'AT31'
                   and d.district_id = '401')
select fc(json_agg(f(geom, 'id', raster_id)))
from rasters

scripts.sql

-- convert a list of points to a geojson object
-- TODO: maybe we can convert this into a function?
SELECT json_build_object(
               'type', 'FeatureCollection',
               'crs', json_build_object(
                       'type', 'name',
                       'properties', json_build_object(
                               'name', 'EPSG:4326'
                           )
                   ),
               'features', json_agg(
                       json_build_object(
                               'type', 'Feature',
                               'id', 'null', -- the GeoJson spec includes an 'id' field, but it is optional. some services still cry if it's not in there, so we leave it empty just for now.
                               'geometry', ST_AsGeoJSON(geom.g)::json,
                               'properties', json_build_object(
--                 -- list of fields
--                 'field1', field1,
--                 'field2', field2
            )
                           )
                   )
           )
FROM (select st_transform(adr.geom, 4326) as g from adresses adr limit 50) geom;


-- create google maps links for geometry objects (function)
create or replace function gmaps(geom geometry) RETURNS varchar as
$$
select 'https://www.google.at/maps/place/' || st_y(geom) || ',' || st_x(geom) as result;
$$ LANGUAGE SQL;


-- find all points within a given radius (given in wkt)
select *
from triply.haltestellen
where st_dwithin(
              st_transform(st_setsrid(geom, 31287), 4326),
              st_wkttosql('SRID=4326;POINT(13.9916886021513 48.1494006079075)'),
              10000);
              
-- geodesic buffer function with wrappers for other data types
CREATE OR REPLACE FUNCTION geodesic_buffer(geom geometry, dist double precision,
                                           num_seg_quarter_circle integer)
    RETURNS geometry AS
$$
SELECT ST_Transform(
               ST_Buffer(ST_Point(0, 0), $2, $3),
               ('+proj=aeqd +x_0=0 +y_0=0 +lat_0='
                    || ST_Y(ST_Centroid($1))::text || ' +lon_0=' || ST_X(ST_Centroid($1))::text),
               ST_SRID($1))
$$ LANGUAGE sql IMMUTABLE
                STRICT
                COST 100;
CREATE OR REPLACE FUNCTION geodesic_buffer(geom geometry, dist double precision)
    RETURNS geometry AS
'SELECT geodesic_buffer($1, $2, 8)'
    LANGUAGE sql IMMUTABLE
                 STRICT
                 COST 100;
-- Optional wrappers for geography type
CREATE OR REPLACE FUNCTION geodesic_buffer(geog geography, dist double precision)
    RETURNS geography AS
'SELECT geodesic_buffer($1::geometry, $2)::geography'
    LANGUAGE sql IMMUTABLE
                 STRICT
                 COST 100;
CREATE OR REPLACE FUNCTION geodesic_buffer(geog geography, dist double precision,
                                           num_seg_quarter_circle integer)
    RETURNS geography AS
'SELECT geodesic_buffer($1::geometry, $2, $3)::geography'
    LANGUAGE sql IMMUTABLE
                 STRICT
                 COST 100;