2444 lines
125 KiB
MySQL
2444 lines
125 KiB
MySQL
|
--DO NOT MODIFY THIS FILE, IT IS GENERATED AUTOMATICALLY FROM SOURCES
|
||
|
|
-- Complain if script is sourced in psql, rather than via CREATE EXTENSION
|
||
|
|
\echo Use "CREATE EXTENSION observatory" to load this file. \quit
|
||
|
|
-- Version number of the extension release
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory_version()
|
||
|
|
RETURNS text AS $$
|
||
|
|
SELECT '1.7.0'::text;
|
||
|
|
$$ language 'sql' STABLE STRICT;
|
||
|
|
|
||
|
|
-- Internal identifier of the installed extension instence
|
||
|
|
-- e.g. 'dev' for current development version
|
||
|
|
CREATE OR REPLACE FUNCTION _cdb_observatory_internal_version()
|
||
|
|
RETURNS text AS $$
|
||
|
|
SELECT installed_version FROM pg_available_extensions where name='observatory' and pg_available_extensions IS NOT NULL;
|
||
|
|
$$ language 'sql' STABLE STRICT;
|
||
|
|
|
||
|
|
-- Returns the table name with geoms for the given geometry_id
|
||
|
|
-- TODO probably needs to take in the column_id array to get the relevant
|
||
|
|
-- table where there is multiple sources for a column from multiple
|
||
|
|
-- geometries.
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_GeomTable(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
geometry_id text,
|
||
|
|
time_span text DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS TEXT
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
result text;
|
||
|
|
BEGIN
|
||
|
|
EXECUTE '
|
||
|
|
SELECT tablename FROM observatory.OBS_table
|
||
|
|
WHERE id IN (
|
||
|
|
SELECT table_id
|
||
|
|
FROM observatory.OBS_table tab,
|
||
|
|
observatory.OBS_column_table coltable,
|
||
|
|
observatory.OBS_column col
|
||
|
|
WHERE type ILIKE ''geometry''
|
||
|
|
AND coltable.column_id = col.id
|
||
|
|
AND coltable.table_id = tab.id
|
||
|
|
AND col.id = $1
|
||
|
|
AND CASE WHEN $3::TEXT IS NOT NULL THEN timespan ILIKE $3::TEXT ELSE TRUE END
|
||
|
|
ORDER BY timespan DESC LIMIT 1
|
||
|
|
)
|
||
|
|
'
|
||
|
|
USING geometry_id, geom, time_span
|
||
|
|
INTO result;
|
||
|
|
|
||
|
|
return result;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
-- A function that gets the column data for multiple columns
|
||
|
|
-- Old: OBS_GetColumnData
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_GetColumnData(
|
||
|
|
geometry_id text,
|
||
|
|
column_ids text[],
|
||
|
|
timespan text
|
||
|
|
)
|
||
|
|
RETURNS SETOF JSON
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
-- figure out highest-weight geometry_id/timespan pair for the first data column
|
||
|
|
-- TODO this should be done for each data column separately
|
||
|
|
IF geometry_id IS NULL OR timespan IS NULL THEN
|
||
|
|
EXECUTE '
|
||
|
|
SELECT data_t.timespan timespan, geom_c.id boundary_id
|
||
|
|
FROM observatory.obs_table data_t,
|
||
|
|
observatory.obs_column_table data_ct,
|
||
|
|
observatory.obs_column data_c,
|
||
|
|
observatory.obs_column_table geoid_ct,
|
||
|
|
observatory.obs_column_to_column c2c,
|
||
|
|
observatory.obs_column geom_c
|
||
|
|
WHERE data_c.id = $2
|
||
|
|
AND data_ct.column_id = data_c.id
|
||
|
|
AND data_ct.table_id = data_t.id
|
||
|
|
AND geoid_ct.table_id = data_t.id
|
||
|
|
AND geoid_ct.column_id = c2c.source_id
|
||
|
|
AND c2c.reltype = ''geom_ref''
|
||
|
|
AND geom_c.id = c2c.target_id
|
||
|
|
AND CASE WHEN $3 IS NULL THEN True ELSE $3 = timespan END
|
||
|
|
AND CASE WHEN $1 IS NULL THEN True ELSE $1 = geom_c.id END
|
||
|
|
ORDER BY geom_c.weight DESC,
|
||
|
|
data_t.timespan DESC
|
||
|
|
LIMIT 1
|
||
|
|
' INTO timespan, geometry_id
|
||
|
|
USING geometry_id, (column_ids)[1], timespan;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE '
|
||
|
|
WITH geomref AS (
|
||
|
|
SELECT ct.table_id id
|
||
|
|
FROM observatory.OBS_column_to_column c2c,
|
||
|
|
observatory.OBS_column_table ct
|
||
|
|
WHERE c2c.reltype = ''geom_ref''
|
||
|
|
AND c2c.target_id = $1
|
||
|
|
AND c2c.source_id = ct.column_id
|
||
|
|
),
|
||
|
|
column_ids as (
|
||
|
|
select row_number() over () as no, a.column_id as column_id from (select unnest($2) as column_id) a
|
||
|
|
)
|
||
|
|
SELECT row_to_json(a) from (
|
||
|
|
select colname,
|
||
|
|
tablename,
|
||
|
|
aggregate,
|
||
|
|
name,
|
||
|
|
type,
|
||
|
|
c.description,
|
||
|
|
$1 AS boundary_id
|
||
|
|
FROM column_ids, observatory.OBS_column c, observatory.OBS_column_table ct, observatory.OBS_table t
|
||
|
|
WHERE column_ids.column_id = c.id
|
||
|
|
AND c.id = ct.column_id
|
||
|
|
AND t.id = ct.table_id
|
||
|
|
AND t.timespan = $3
|
||
|
|
AND t.id in (SELECT id FROM geomref)
|
||
|
|
order by column_ids.no
|
||
|
|
) a
|
||
|
|
'
|
||
|
|
USING geometry_id, column_ids, timespan
|
||
|
|
RETURN;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
--Test point cause Stuart always seems to make random points in the water
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._TestPoint()
|
||
|
|
RETURNS geometry(Point, 4326)
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
-- new york city
|
||
|
|
RETURN ST_SetSRID(ST_Point( -73.936669, 40.704512), 4326);
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
--Test polygon cause Stuart always seems to make random points in the water
|
||
|
|
-- TODO: remove as it's not used anywhere?
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._TestArea()
|
||
|
|
RETURNS geometry(Geometry, 4326)
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
-- Buffer NYC point by 500 meters
|
||
|
|
RETURN ST_Buffer(cdb_observatory._TestPoint()::geography, 500)::geometry;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
--Problematic test area that tends to cause errors
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._ProblemTestArea()
|
||
|
|
RETURNS geometry(Geometry, 4326)
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
RETURN ST_Translate('0106000020E610000001000000010300000004000000A3030000A09400CAD92D5AC088FA8054CBD04340C74F1462DA2D5AC080F9946B63D043400CBFCDA8DA2D5AC048A9756963D04340BCA4A5B0DA2D5AC010733FD05FD0434054923667DA2D5AC0986276D25FD043406038BC25DB2D5AC0600F50FBDCCF43400453A577DB2D5AC0E834A8F6DCCF4340AC97C1A6DB2D5AC0A06DFAF5BBCF43401C80158CDC2D5AC0D8139CE81DCF43407008A7FBDC2D5AC058865A08EFCE4340DCD519AFDE2D5AC0982B0911EFCE43400C8832AFDE2D5AC070AED4FFEECE43406471BEBFDE2D5AC0B7B6E3FFEECE4340C01E93F2DE2D5AC0077C7A16CBCE4340B4EA55F2DE2D5AC0E0147D15CBCE4340385DF195DF2D5AC0585D07A158CE4340649353D3DF2D5AC010AF6FBD2CCE4340A4981805E02D5AC0889B302709CE434077676710E02D5AC0D8ECC62A01CE4340E7F37D10E02D5AC04047191B01CE4340A0F8F5CCDD2D5AC0B89BFF1501CE43404475C582DE2D5AC0B075C01A8CCD4340A8F865B4DE2D5AC090BC3DEC6BCD4340D89E52EBDE2D5AC038A7AD0C48CD4340971F1E40E12D5AC048E3BA1048CD43404463BE00FE2D5AC0D04FFE7C48CD4340DC6B6347FE2D5AC0C0DB48881BCD4340203BDB48FE2D5AC0C815D7981ACD43404CF94B49FE2D5AC0185A9C521ACD43401421F24CFE2D5AC0A0D6345C15CD43402F82F7CFFF2D5AC028761C9514CD43409FD10571152E5AC028DC1FED14CD43404C31B28A152E5AC050929DDA00CD4340444C4399152E5AC0F0E2E5D200CD4340E87E1A99152E5AC0B00DD8EC00CD4340F8D55ACD372E5AC070C9CECEEECC4340185D1ECB382E5AC0C0309E50EECC43406CDA9A473A2E5AC07FCC3DADEDCC43400053E4483B2E5AC0D003AE51EDCC434094CDD14D3C2E5AC018E78B04EDCC4340A0CEA9523D2E5AC0D0FCC1C5ECCC43402F1D2A5B3E2E5AC0988E0594ECCC434050DF93633F2E5AC018C4A770ECCC434050CC31EE402E5AC0488FAC57ECCC4340046D20FA412E5AC080815A58ECCC4340CC0A7184432E5AC0D8609D74ECCC43402C1E4F12452E5AC0E8E297B0ECCC4340C0FB8A98462E5AC0E085250CEDCC434058D39A1E482E5AC080FBCA88EDCC4340849AA8D84A2E5AC03849F9A4EECC43401CB8D1CD4C2E5AC078B0FB84EFCC434090E02BBF4E2E5AC02055DC71F0CC43404472BCAC502E5AC00052176DF1CC4340489D389A522E5AC0E09C4E75F2CC4340AC1EEA83542E5AC0E84A668AF3CC43405009D269562E5AC0B09CDCADF4CC43409024ED4B582E5AC098F3C1DCF5CC434054833C2A5A2E5AC0B8A2011AF7CC4340345EC3045C2E5AC010EEB862F8CC434067D235DF5D2E5AC01874D7B9F9CC4340E76C28B25F2E5AC090425A1CFBCC434004384E81612E5AC0B856CA8BFCCC4340447FAB4C632E5AC048A81808FECC434040E43A14652E5AC058655691FFCC43408C6F4AD4662E5AC08890FA2501CD4340A8DF4994682E5AC0F88527C602CD43405350C74C6A2E5AC0D0212A7304CD43405B177A016C2E5AC0977FA62B06CD4340247384F06E2E5AC06885D04B09CD4340D03C6AF36F2E5AC0902F206F0ACD4340A45FD598712E5AC0D891646F0CCD4340708FA911732E5AC0B8C3F4640ECD434044DF9286742E5AC080A29A7B10CD4340F45CE2D2752E5AC0F75A958612CD4340D487612C782E5AC0FF8FFDAD16CD4340ECF38E3C782E5AC0B8D443CC16CD434078184049782E5AC070F20BE416CD43401C81A16F742E5AC0B0A384AF1BCD4340EF78AFC7752E5AC01057146A1ECD434090475AD1762E5AC058D8C8BF20CD4340803AE7D2782E5AC070CE07BF25CD434030725C147D2E5AC040A74A7530CD4340D3D9C772812E5AC0D8B06C743BCD4340F8ABBF0C832E5AC0883E577C3FCD43400456A793842E5AC0F830465443CD434048E65526872E5AC080902ECE49CD43406CEF40BB882E5AC0D08441944DCD4340F82791DA8A2E5AC018AA351652CD4340FCA1D5D38B2E5AC0500DF6F153CD4340080BA67B8C2E5AC05091453255CD43407CE0BCFB8D2E5AC07093A3D057CD4340204B852F8E2E5AC0D06A022B58CD4340AB56181B8D2E5AC078A969685ACD4340AF292487922E5AC0C05E3D6862CD434088A3DCF7942E5AC0F036546B65CD434058B1590A952E5AC09055998265CD434048856440972E5AC0B86AB6E367CD4340E4948B549F2E5AC0A771539670CD4340A491D52E9F2E5AC06868F4F170CD4340AB776367A82E5AC0FF703DC87ACD4340E402822CB02E5AC027239D0A83CD4340440C2339B02E5AC0F8670D1883CD4340109761DEAE2E5AC0804BD43386CD4340107679BCAE2E5AC0102E387E86CD43403B3663ADAE2E5AC0A04C53A686CD43402090A065AE2E5AC02849636387CD434030B50E3CAE2E5AC0D0C3F7D587CD4340E4128012AE2E5AC0C0469B4888CD4340C88DA5ECAD2E5AC0381543BB88CD4340C8F5C9C6AD2E5AC03054C93089CD4340C73F7DA8AD2E5AC0F8AC239589CD4340EB4AEDA0AD2E5AC020934FA689CD434064F7189DAD2E5AC0903D6DBA89CD4340DFF5C77EAD2E5AC06096C71E8ACD434053559E5CAD2E5AC060BF41978ACD4340ACB9A449AD2E5AC0D01D33D98ACD4340A8C7753AAD2E5AC0972BB10F8BCD434090B5061CAD2E5AC0203738888BCD4340F45793FDAC2E5AC0E04197038CCD4340B81C43EEAC2E5AC0204D1E518CCD4340D088DAE2AC2E5AC06809017F8CCD4340E8931FC8AC2E5AC0C0247EFA8CCD43400CAF04B5AC2E5AC070625D568DCD4340C02D5EADAC2E5AC047A8CA788DCD43408C02AEA5AC2E5AC060B8C0A68DCD43404CC75D96AC2E5AC0A0C3
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
--Used to expand a column based response to a table based one. Give it the desired
|
||
|
|
--columns and it will return a partial query for rolling them out to a table.
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_BuildSnapshotQuery(names text[])
|
||
|
|
RETURNS TEXT
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
q text;
|
||
|
|
i numeric;
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
q := 'SELECT ';
|
||
|
|
|
||
|
|
FOR i IN 1..array_upper(names,1)
|
||
|
|
LOOP
|
||
|
|
q = q || format(' vals[%s] As %I', i, names[i]);
|
||
|
|
IF i < array_upper(names, 1) THEN
|
||
|
|
q= q || ',';
|
||
|
|
END IF;
|
||
|
|
END LOOP;
|
||
|
|
RETURN q;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- Function that replaces all non digits or letters with _ trims and lowercases the
|
||
|
|
-- passed measure name
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_StandardizeMeasureName(measure_name text)
|
||
|
|
RETURNS text
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
result text;
|
||
|
|
BEGIN
|
||
|
|
-- Turn non letter or digits to _
|
||
|
|
result = regexp_replace(measure_name, '[^\dA-Za-z]+','_', 'g');
|
||
|
|
-- Remove duplicate _'s
|
||
|
|
result = regexp_replace(result,'_{2,}','_', 'g');
|
||
|
|
-- Trim _'s from beginning and end
|
||
|
|
result = trim(both '_' from result);
|
||
|
|
result = lower(result);
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- Function that returns the currently deployed obs_dump_version from the
|
||
|
|
-- remote table of the same name.
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_DumpVersion(
|
||
|
|
)
|
||
|
|
RETURNS TEXT
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
result text;
|
||
|
|
BEGIN
|
||
|
|
EXECUTE '
|
||
|
|
SELECT MAX(dump_id) FROM observatory.obs_dump_version
|
||
|
|
' INTO result;
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
-- Function we can call to raise an exception in the midst of a SQL statement
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_RaiseNotice(
|
||
|
|
message TEXT
|
||
|
|
) RETURNS TEXT
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
RAISE NOTICE '%', message;
|
||
|
|
RETURN NULL;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
-- Create a function that always returns the first non-NULL item
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.first_agg ( anyelement, anyelement )
|
||
|
|
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
|
||
|
|
SELECT $1;
|
||
|
|
$$;
|
||
|
|
|
||
|
|
DROP AGGREGATE IF EXISTS cdb_observatory.FIRST (anyelement);
|
||
|
|
|
||
|
|
-- And then wrap an aggregate around it
|
||
|
|
CREATE AGGREGATE cdb_observatory.FIRST (
|
||
|
|
sfunc = cdb_observatory.first_agg,
|
||
|
|
basetype = anyelement,
|
||
|
|
stype = anyelement
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.isnumeric (
|
||
|
|
typename varchar
|
||
|
|
)
|
||
|
|
RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE STRICT AS $$
|
||
|
|
SELECT LOWER(typename) IN (
|
||
|
|
'smallint',
|
||
|
|
'integer',
|
||
|
|
'bigint',
|
||
|
|
'decimal',
|
||
|
|
'numeric',
|
||
|
|
'real',
|
||
|
|
'double precision'
|
||
|
|
)
|
||
|
|
$$;
|
||
|
|
|
||
|
|
-- Attempt to perform intersection, if there's an exception then buffer
|
||
|
|
-- https://gis.stackexchange.com/questions/50399/how-best-to-fix-a-non-noded-intersection-problem-in-postgis
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.safe_intersection(
|
||
|
|
geom_a Geometry(Geometry, 4326),
|
||
|
|
geom_b Geometry(Geometry, 4326)
|
||
|
|
)
|
||
|
|
RETURNS Geometry(Geometry, 4326) AS
|
||
|
|
$$
|
||
|
|
BEGIN
|
||
|
|
RETURN ST_MakeValid(ST_Intersection(geom_a, geom_b));
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
BEGIN
|
||
|
|
RETURN ST_MakeValid(ST_Intersection(ST_Buffer(geom_a, 0.0000001), ST_Buffer(geom_b, 0.0000001)));
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN NULL;
|
||
|
|
END;
|
||
|
|
END
|
||
|
|
$$
|
||
|
|
LANGUAGE 'plpgsql' STABLE STRICT;
|
||
|
|
--Functions for augmenting specific tables
|
||
|
|
--------------------------------------------------------------------------------
|
||
|
|
|
||
|
|
-- Creates a table of demographic snapshot
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetDemographicSnapshot(geom geometry(Geometry, 4326),
|
||
|
|
timespan text DEFAULT NULL,
|
||
|
|
boundary_id text DEFAULT NULL
|
||
|
|
) RETURNS SETOF JSON
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
meta JSON;
|
||
|
|
BEGIN
|
||
|
|
boundary_id = COALESCE(boundary_id, 'us.census.tiger.census_tract');
|
||
|
|
|
||
|
|
EXECUTE $query$ SELECT cdb_observatory.OBS_GetMeta($1,
|
||
|
|
('[ ' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01003001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01001002", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01001026", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01002001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002003", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002004", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002006", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002012", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002005", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002008", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002009", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002002", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B11001001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003017", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003019", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003020", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003021", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003022", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003023", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19013001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19083001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19301001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25001001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25002003", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25004002", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25004004", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25058001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25071001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25075001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25075025", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25081002", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134002", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134003", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134004", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134005", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134006", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134007", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134008", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134009", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08134010", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08135001", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001002", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001003", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001004", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001005", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001006", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001007", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001008", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001009", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001010", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001011", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001012", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001013", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001014", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001015", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001016", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19001017", "numer_timespan": ' || $2 || ', "geom_id": ' || $3 || '}' ||
|
||
|
|
']')::JSON)
|
||
|
|
$query$
|
||
|
|
INTO meta
|
||
|
|
USING geom,
|
||
|
|
COALESCE('"' || timespan || '"', 'null'),
|
||
|
|
COALESCE('"' || boundary_id || '"', 'null');
|
||
|
|
|
||
|
|
RETURN QUERY EXECUTE $query$
|
||
|
|
WITH vals AS (SELECT JSON_Array_Elements(data)->'value' val,
|
||
|
|
JSON_Array_Elements($2) meta
|
||
|
|
FROM cdb_observatory.OBS_GetData( ARRAY[($1, 1)::geomval], $2))
|
||
|
|
SELECT JSON_Build_Object(
|
||
|
|
'value', val,
|
||
|
|
'id', meta->'numer_id',
|
||
|
|
'name', meta->'numer_name',
|
||
|
|
'type', meta->'numer_type',
|
||
|
|
'description', meta->'numer_description'
|
||
|
|
) FROM vals
|
||
|
|
$query$
|
||
|
|
USING geom, meta
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetMeta(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
params JSON,
|
||
|
|
num_timespan_options INTEGER DEFAULT NULL, -- how many timespan options to show
|
||
|
|
num_score_options INTEGER DEFAULT NULL, -- how many score options to show
|
||
|
|
target_geoms INTEGER DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS JSON
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
numer_filters TEXT[];
|
||
|
|
geom_filters TEXT[];
|
||
|
|
meta_filter_clause TEXT;
|
||
|
|
scores_clause TEXT;
|
||
|
|
result JSON;
|
||
|
|
BEGIN
|
||
|
|
IF num_timespan_options IS NULL THEN
|
||
|
|
num_timespan_options := 1;
|
||
|
|
END IF;
|
||
|
|
IF num_score_options IS NULL THEN
|
||
|
|
num_score_options := 1;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
numer_filters := (SELECT Array_Agg(val) FILTER (WHERE val IS NOT NULL) FROM (SELECT (JSON_Array_Elements(params))->>'numer_id' val) foo);
|
||
|
|
geom_filters := (SELECT Array_Agg(val) FILTER (WHERE val IS NOT NULL) FROM (SELECT (JSON_Array_Elements(params))->>'geom_id' val) bar);
|
||
|
|
meta_filter_clause := '(m.numer_id = ANY ($6) OR m.geom_id = ANY ($7))';
|
||
|
|
|
||
|
|
scores_clause := ' agg_geoms AS (
|
||
|
|
SELECT target_geoms, target_area, ARRAY_AGG(geom_id) geom_ids
|
||
|
|
FROM meta
|
||
|
|
GROUP BY target_geoms, target_area
|
||
|
|
), scores AS (
|
||
|
|
SELECT target_geoms, target_area,
|
||
|
|
CASE target_area
|
||
|
|
-- point-specific, just order by numgeoms instead of score
|
||
|
|
WHEN 0 THEN scores.numgeoms
|
||
|
|
-- has some area, use proper scoring
|
||
|
|
ELSE scores.score
|
||
|
|
END AS score,
|
||
|
|
scores.numgeoms, scores.table_id, scores.column_id
|
||
|
|
FROM agg_geoms,
|
||
|
|
LATERAL cdb_observatory._OBS_GetGeometryScores($1,
|
||
|
|
geom_ids, COALESCE(target_geoms, $2), target_area) scores
|
||
|
|
) ';
|
||
|
|
|
||
|
|
IF JSON_Array_Length(params) = 1 THEN
|
||
|
|
IF numer_filters IS NULL AND geom_filters IS NOT NULL THEN
|
||
|
|
meta_filter_clause := 'm.geom_id = ($7)[1]';
|
||
|
|
ELSIF geom_filters IS NULL AND numer_filters IS NOT NULL THEN
|
||
|
|
meta_filter_clause := 'm.numer_id = ($6)[1]';
|
||
|
|
ELSIF numer_filters IS NOT NULL AND geom_filters IS NOT NULL THEN
|
||
|
|
meta_filter_clause := 'm.numer_id = ($6)[1] AND m.geom_id = ($7)[1]';
|
||
|
|
ELSE
|
||
|
|
RAISE EXCEPTION 'Must pass either numer_id or geom_id to every key in GetMeta';
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF geom_filters IS NOT NULL AND numer_filters IS NOT NULL THEN
|
||
|
|
scores_clause := 'scores AS (
|
||
|
|
SELECT NULL::INTEGER target_geoms, NULL::Numeric target_area,
|
||
|
|
1 score, null, geom_tid table_id, geom_id column_id,
|
||
|
|
NULL::Integer numgeoms
|
||
|
|
FROM meta) ';
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXECUTE format($string$
|
||
|
|
WITH _filters AS (SELECT
|
||
|
|
row_number() over () id, *
|
||
|
|
FROM json_to_recordset($3)
|
||
|
|
AS x(numer_id TEXT, denom_id TEXT, geom_id TEXT, numer_timespan TEXT,
|
||
|
|
geom_timespan TEXT, normalization TEXT, max_timespan_rank TEXT,
|
||
|
|
max_score_rank TEXT, target_geoms INTEGER, target_area Numeric
|
||
|
|
)
|
||
|
|
), meta AS (SELECT
|
||
|
|
id,
|
||
|
|
f.numer_id,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_aggregate END numer_aggregate,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_colname END numer_colname,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_geomref_colname END numer_geomref_colname,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_tablename END numer_tablename,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_type END numer_type,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_name END numer_name,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_description END numer_description,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE numer_t_description END numer_t_description,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE m.numer_timespan END numer_timespan,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE m.denom_id END denom_id,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_aggregate END denom_aggregate,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_colname END denom_colname,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_geomref_colname END denom_geomref_colname,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_tablename END denom_tablename,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_name END denom_name,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_description END denom_description,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_t_description END denom_t_description,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_type END denom_type,
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN NULL ELSE denom_reltype END denom_reltype,
|
||
|
|
m.geom_id,
|
||
|
|
m.geom_timespan,
|
||
|
|
geom_colname,
|
||
|
|
geom_tid,
|
||
|
|
geom_geomref_colname,
|
||
|
|
geom_tablename,
|
||
|
|
geom_name,
|
||
|
|
geom_description,
|
||
|
|
geom_t_description,
|
||
|
|
geom_type,
|
||
|
|
Coalesce(normalization,
|
||
|
|
-- automatically assign normalization to numeric numerators
|
||
|
|
CASE WHEN cdb_observatory.isnumeric(numer_type) THEN
|
||
|
|
CASE WHEN denom_reltype ILIKE 'denominator' THEN 'denominated'
|
||
|
|
WHEN numer_aggregate ILIKE 'sum' THEN 'area'
|
||
|
|
WHEN numer_aggregate IN ('median', 'average') AND denom_reltype ILIKE 'universe'
|
||
|
|
THEN 'prenormalized'
|
||
|
|
ELSE 'prenormalized'
|
||
|
|
END ELSE NULL
|
||
|
|
END
|
||
|
|
) normalization,
|
||
|
|
max_timespan_rank,
|
||
|
|
max_score_rank,
|
||
|
|
target_geoms,
|
||
|
|
target_area
|
||
|
|
FROM observatory.obs_meta m JOIN _filters f
|
||
|
|
ON CASE WHEN f.numer_id IS NULL THEN m.geom_id ELSE m.numer_id END =
|
||
|
|
CASE WHEN f.numer_id IS NULL THEN f.geom_id ELSE f.numer_id END
|
||
|
|
WHERE
|
||
|
|
%s
|
||
|
|
AND (m.numer_id = f.numer_id OR COALESCE(f.numer_id, '') = '')
|
||
|
|
AND (m.denom_id = f.denom_id OR COALESCE(f.denom_id, '') = '')
|
||
|
|
AND (m.geom_id = f.geom_id OR COALESCE(f.geom_id, '') = '')
|
||
|
|
AND (m.geom_timespan = f.geom_timespan OR COALESCE(f.geom_timespan, '') = '')
|
||
|
|
AND (m.numer_timespan = f.numer_timespan OR COALESCE(f.numer_timespan, '') = '')
|
||
|
|
), %s
|
||
|
|
, groups AS (SELECT
|
||
|
|
id,
|
||
|
|
scores.score,
|
||
|
|
numer_timespan,
|
||
|
|
dense_rank() OVER (PARTITION BY id ORDER BY numer_timespan DESC) timespan_rank,
|
||
|
|
dense_rank() OVER (PARTITION BY id ORDER BY score DESC) score_rank,
|
||
|
|
json_build_object(
|
||
|
|
'id', id,
|
||
|
|
'numer_id', numer_id,
|
||
|
|
'timespan_rank', dense_rank() OVER (PARTITION BY id ORDER BY numer_timespan DESC),
|
||
|
|
'score_rank', dense_rank() OVER (PARTITION BY id ORDER BY score DESC),
|
||
|
|
'timespan_rownum', row_number() over
|
||
|
|
(PARTITION BY id, score ORDER BY numer_timespan DESC, Coalesce(denom_id, '')),
|
||
|
|
'score_rownum', row_number() over
|
||
|
|
(PARTITION BY id, numer_timespan ORDER BY score DESC, Coalesce(denom_id, '')),
|
||
|
|
'score', scores.score,
|
||
|
|
'suggested_name', cdb_observatory.FIRST(
|
||
|
|
LOWER(TRIM(BOTH '_' FROM regexp_replace(CASE WHEN numer_id IS NOT NULL
|
||
|
|
THEN CASE
|
||
|
|
WHEN normalization ILIKE 'area%%' THEN numer_colname || ' per sq km'
|
||
|
|
WHEN normalization ILIKE 'denom%%' THEN numer_colname || ' rate'
|
||
|
|
ELSE numer_colname
|
||
|
|
END || ' ' || numer_timespan
|
||
|
|
ELSE geom_name || ' ' || geom_timespan
|
||
|
|
END, '[^a-zA-Z0-9]+', '_', 'g')))
|
||
|
|
),
|
||
|
|
'numer_aggregate', cdb_observatory.FIRST(meta.numer_aggregate),
|
||
|
|
'numer_colname', cdb_observatory.FIRST(meta.numer_colname),
|
||
|
|
'numer_geomref_colname', cdb_observatory.FIRST(meta.numer_geomref_colname),
|
||
|
|
'numer_tablename', cdb_observatory.FIRST(meta.numer_tablename),
|
||
|
|
'numer_type', cdb_observatory.FIRST(meta.numer_type),
|
||
|
|
'numer_description', cdb_observatory.FIRST(meta.numer_description),
|
||
|
|
'numer_t_description', cdb_observatory.FIRST(meta.numer_t_description),
|
||
|
|
'denom_aggregate', cdb_observatory.FIRST(meta.denom_aggregate),
|
||
|
|
'denom_colname', cdb_observatory.FIRST(denom_colname),
|
||
|
|
'denom_geomref_colname', cdb_observatory.FIRST(denom_geomref_colname),
|
||
|
|
'denom_tablename', cdb_observatory.FIRST(denom_tablename),
|
||
|
|
'denom_type', cdb_observatory.FIRST(meta.denom_type),
|
||
|
|
'denom_reltype', cdb_observatory.FIRST(meta.denom_reltype),
|
||
|
|
'denom_description', cdb_observatory.FIRST(meta.denom_description),
|
||
|
|
'denom_t_description', cdb_observatory.FIRST(meta.denom_t_description),
|
||
|
|
'geom_colname', cdb_observatory.FIRST(geom_colname),
|
||
|
|
'geom_geomref_colname', cdb_observatory.FIRST(geom_geomref_colname),
|
||
|
|
'geom_tablename', cdb_observatory.FIRST(geom_tablename),
|
||
|
|
'geom_type', cdb_observatory.FIRST(meta.geom_type),
|
||
|
|
'geom_timespan', cdb_observatory.FIRST(meta.geom_timespan),
|
||
|
|
'geom_description', cdb_observatory.FIRST(meta.geom_description),
|
||
|
|
'geom_t_description', cdb_observatory.FIRST(meta.geom_t_description),
|
||
|
|
'numer_timespan', cdb_observatory.FIRST(numer_timespan),
|
||
|
|
'numer_name', cdb_observatory.FIRST(numer_name),
|
||
|
|
'denom_name', cdb_observatory.FIRST(denom_name),
|
||
|
|
'geom_name', cdb_observatory.FIRST(geom_name),
|
||
|
|
'normalization', cdb_observatory.FIRST(normalization),
|
||
|
|
'max_timespan_rank', cdb_observatory.FIRST(max_timespan_rank),
|
||
|
|
'max_score_rank', cdb_observatory.FIRST(max_score_rank),
|
||
|
|
'target_geoms', cdb_observatory.FIRST(scores.target_geoms),
|
||
|
|
'target_area', cdb_observatory.FIRST(scores.target_area),
|
||
|
|
'num_geoms', cdb_observatory.FIRST(scores.numgeoms),
|
||
|
|
'denom_id', denom_id,
|
||
|
|
'geom_id', meta.geom_id
|
||
|
|
) metadata
|
||
|
|
FROM meta, scores
|
||
|
|
WHERE meta.geom_id = scores.column_id
|
||
|
|
AND meta.geom_tid = scores.table_id
|
||
|
|
AND COALESCE(meta.target_geoms, 0) = COALESCE(scores.target_geoms, 0)
|
||
|
|
AND COALESCE(meta.target_area, 0) = COALESCE(scores.target_area, 0)
|
||
|
|
GROUP BY id, score, numer_id, denom_id, geom_id, numer_timespan
|
||
|
|
) SELECT JSON_AGG(metadata ORDER BY id)
|
||
|
|
FROM groups
|
||
|
|
WHERE timespan_rank <= Coalesce((metadata->>'max_timespan_rank')::INTEGER, 'infinity'::FLOAT)
|
||
|
|
AND score_rank <= Coalesce((metadata->>'max_score_rank')::INTEGER, 1)
|
||
|
|
AND (metadata->>'timespan_rownum')::INTEGER <= $4
|
||
|
|
AND (metadata->>'score_rownum')::INTEGER <= $5
|
||
|
|
$string$, meta_filter_clause, scores_clause)
|
||
|
|
INTO result
|
||
|
|
USING
|
||
|
|
CASE WHEN ST_GeometryType(geom) = 'ST_Point' THEN
|
||
|
|
ST_Buffer(geom::geography, 200)::geometry(geometry, 4326)
|
||
|
|
ELSE geom
|
||
|
|
END,
|
||
|
|
target_geoms,
|
||
|
|
params,
|
||
|
|
num_timespan_options,
|
||
|
|
num_score_options, numer_filters, geom_filters
|
||
|
|
;
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetMeasure(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
measure_id TEXT,
|
||
|
|
normalize TEXT DEFAULT NULL,
|
||
|
|
boundary_id TEXT DEFAULT NULL,
|
||
|
|
time_span TEXT DEFAULT NULL,
|
||
|
|
simplification NUMERIC DEFAULT 0.00001
|
||
|
|
)
|
||
|
|
RETURNS NUMERIC
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
geom_type TEXT;
|
||
|
|
params JSON;
|
||
|
|
map_type TEXT;
|
||
|
|
result Numeric;
|
||
|
|
numer_aggregate TEXT;
|
||
|
|
BEGIN
|
||
|
|
IF geom IS NULL THEN
|
||
|
|
RETURN NULL;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF simplification IS NOT NULL THEN
|
||
|
|
geom := ST_Simplify(geom, simplification);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF ST_GeometryType(geom) = 'ST_Point' THEN
|
||
|
|
geom_type := 'point';
|
||
|
|
ELSIF ST_GeometryType(geom) IN ('ST_Polygon', 'ST_MultiPolygon') THEN
|
||
|
|
geom_type := 'polygon';
|
||
|
|
geom := ST_CollectionExtract(ST_MakeValid(geom), 3);
|
||
|
|
ELSE
|
||
|
|
RAISE EXCEPTION 'Invalid geometry type (%), can only handle ''ST_Point'', ''ST_Polygon'', and ''ST_MultiPolygon''',
|
||
|
|
ST_GeometryType(geom);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
params := (SELECT cdb_observatory.OBS_GetMeta(
|
||
|
|
geom, JSON_Build_Array(JSON_Build_Object('numer_id', measure_id,
|
||
|
|
'geom_id', boundary_id,
|
||
|
|
'numer_timespan', time_span
|
||
|
|
)), 1, 1, 500));
|
||
|
|
numer_aggregate := params->0->>'numer_aggregate';
|
||
|
|
|
||
|
|
IF normalize ILIKE 'area%' AND numer_aggregate ILIKE 'sum' THEN
|
||
|
|
map_type := 'areaNormalized';
|
||
|
|
ELSIF normalize ILIKE 'denom%' THEN
|
||
|
|
map_type := 'denominated';
|
||
|
|
ELSIF normalize ILIKE 'pre%' THEN
|
||
|
|
map_type := 'predenominated';
|
||
|
|
ELSE
|
||
|
|
-- defaults: area normalization for point if it's possible and none for
|
||
|
|
-- polygon or non-summable point
|
||
|
|
IF geom_type = 'point' AND numer_aggregate ILIKE 'sum' THEN
|
||
|
|
map_type := 'areaNormalized';
|
||
|
|
ELSE
|
||
|
|
map_type := 'predenominated';
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
params := JSON_Build_Array(JSONB_Set((params::JSONB)->0, '{normalization}', to_jsonb(map_type))::JSON);
|
||
|
|
|
||
|
|
IF params->0->>'geom_id' IS NULL THEN
|
||
|
|
RAISE NOTICE 'No boundary found for geom';
|
||
|
|
RETURN NULL;
|
||
|
|
ELSE
|
||
|
|
RAISE NOTICE 'Using boundary %', params->0->>'geom_id';
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT (data->0->>'value')::Numeric FROM
|
||
|
|
cdb_observatory.OBS_GetData(ARRAY[($1, 1)::geomval], $2)
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING geom, params;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetMeasureById(
|
||
|
|
geom_ref TEXT,
|
||
|
|
measure_id TEXT,
|
||
|
|
boundary_id TEXT,
|
||
|
|
time_span TEXT DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS NUMERIC
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
result NUMERIC;
|
||
|
|
BEGIN
|
||
|
|
IF geom_ref IS NULL THEN
|
||
|
|
RETURN NULL;
|
||
|
|
ELSIF boundary_id IS NULL THEN
|
||
|
|
RETURN NULL;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT data->0->>'value'
|
||
|
|
FROM cdb_observatory.OBS_GetData(Array[$1],
|
||
|
|
cdb_observatory.OBS_GetMeta(ST_MakeEnvelope(-180, -90, 180, 90, 4326),
|
||
|
|
JSON_Build_Array(JSON_Build_Object(
|
||
|
|
'numer_id', $2,
|
||
|
|
'geom_id', $3,
|
||
|
|
'numer_timespan', $4,
|
||
|
|
'normalization', 'predenominated'
|
||
|
|
))))
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING geom_ref, measure_id, boundary_id, time_span;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
-- GetData that obtains data from array of geomrefs
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetData(
|
||
|
|
geomrefs text[],
|
||
|
|
params JSON
|
||
|
|
)
|
||
|
|
RETURNS TABLE (
|
||
|
|
id TEXT,
|
||
|
|
data JSON
|
||
|
|
)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
colspecs TEXT;
|
||
|
|
tables TEXT;
|
||
|
|
obs_wheres TEXT;
|
||
|
|
user_wheres TEXT;
|
||
|
|
|
||
|
|
q text;
|
||
|
|
BEGIN
|
||
|
|
IF params IS NULL OR JSON_ARRAY_LENGTH(params) = 0 THEN
|
||
|
|
RETURN QUERY EXECUTE $query$ SELECT NULL::TEXT, NULL::JSON LIMIT 0 $query$;
|
||
|
|
RETURN;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXECUTE
|
||
|
|
$query$
|
||
|
|
WITH _meta AS (SELECT
|
||
|
|
generate_series(1, array_length($1, 1)) colid,
|
||
|
|
(unnest($1))->>'id' id,
|
||
|
|
(unnest($1))->>'numer_id' numer_id,
|
||
|
|
(unnest($1))->>'numer_aggregate' numer_aggregate,
|
||
|
|
(unnest($1))->>'numer_colname' numer_colname,
|
||
|
|
(unnest($1))->>'numer_geomref_colname' numer_geomref_colname,
|
||
|
|
(unnest($1))->>'numer_tablename' numer_tablename,
|
||
|
|
(unnest($1))->>'numer_type' numer_type,
|
||
|
|
(unnest($1))->>'denom_id' denom_id,
|
||
|
|
(unnest($1))->>'denom_aggregate' denom_aggregate,
|
||
|
|
(unnest($1))->>'denom_colname' denom_colname,
|
||
|
|
(unnest($1))->>'denom_geomref_colname' denom_geomref_colname,
|
||
|
|
(unnest($1))->>'denom_tablename' denom_tablename,
|
||
|
|
(unnest($1))->>'denom_type' denom_type,
|
||
|
|
(unnest($1))->>'denom_reltype' denom_reltype,
|
||
|
|
(unnest($1))->>'geom_id' geom_id,
|
||
|
|
(unnest($1))->>'geom_colname' geom_colname,
|
||
|
|
(unnest($1))->>'geom_geomref_colname' geom_geomref_colname,
|
||
|
|
(unnest($1))->>'geom_tablename' geom_tablename,
|
||
|
|
(unnest($1))->>'geom_type' geom_type,
|
||
|
|
(unnest($1))->>'geom_timespan' geom_timespan,
|
||
|
|
(unnest($1))->>'numer_timespan' numer_timespan,
|
||
|
|
(unnest($1))->>'normalization' normalization,
|
||
|
|
(unnest($1))->>'api_method' api_method,
|
||
|
|
(unnest($1))->'api_args' api_args
|
||
|
|
)
|
||
|
|
SELECT String_Agg(
|
||
|
|
-- numeric
|
||
|
|
'JSON_Build_Object(' || CASE
|
||
|
|
WHEN api_method IS NOT NULL THEN
|
||
|
|
'''value'', ' ||
|
||
|
|
'ARRAY_AGG( ' ||
|
||
|
|
api_method || '.' || numer_colname || ')::' || numer_type || '[]'
|
||
|
|
-- numeric internal values
|
||
|
|
WHEN cdb_observatory.isnumeric(numer_type) THEN
|
||
|
|
'''value'', ' || CASE
|
||
|
|
-- denominated
|
||
|
|
WHEN LOWER(normalization) LIKE 'denom%' OR (normalization IS NULL AND denom_id IS NOT NULL)
|
||
|
|
THEN 'cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname ||
|
||
|
|
' / NullIf(' || denom_tablename || '.' || denom_colname || ', 0))'
|
||
|
|
-- areaNormalized
|
||
|
|
WHEN LOWER(normalization) LIKE 'area%' OR (normalization IS NULL AND numer_aggregate ILIKE 'sum')
|
||
|
|
THEN 'cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname ||
|
||
|
|
' / (ST_Area(' || geom_tablename || '.' || geom_colname || '::Geography)/1000000))'
|
||
|
|
-- prenormalized
|
||
|
|
ELSE 'cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname || ')'
|
||
|
|
END || ':: ' || numer_type
|
||
|
|
|
||
|
|
-- categorical/text
|
||
|
|
WHEN LOWER(numer_type) LIKE 'text' THEN
|
||
|
|
'''value'', ' || 'cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname || ') '
|
||
|
|
|
||
|
|
-- geometry
|
||
|
|
WHEN numer_id IS NULL THEN
|
||
|
|
'''geomref'', ' || 'cdb_observatory.FIRST(' || geom_tablename ||
|
||
|
|
'.' || geom_geomref_colname || '), ' ||
|
||
|
|
'''value'', ' || 'cdb_observatory.FIRST(' || geom_tablename ||
|
||
|
|
'.' || geom_colname || ')'
|
||
|
|
ELSE ''
|
||
|
|
END || ')', ', ')
|
||
|
|
AS colspecs,
|
||
|
|
|
||
|
|
(SELECT String_Agg(DISTINCT CASE
|
||
|
|
-- External API
|
||
|
|
WHEN tablename LIKE 'cdb_observatory.%' THEN
|
||
|
|
'LATERAL (SELECT * FROM ' || tablename || ') ' ||
|
||
|
|
REPLACE(split_part(tablename, '(', 1), 'cdb_observatory.', '')
|
||
|
|
-- Internal obs_ table
|
||
|
|
ELSE 'observatory.' || tablename
|
||
|
|
END, ', ') FROM (
|
||
|
|
SELECT DISTINCT UNNEST(tablenames_ary) tablename FROM (
|
||
|
|
SELECT ARRAY_AGG(numer_tablename) ||
|
||
|
|
ARRAY_AGG(denom_tablename) ||
|
||
|
|
ARRAY_AGG(geom_tablename) ||
|
||
|
|
ARRAY_AGG('cdb_observatory.' || api_method || '(_geomrefs.id' || COALESCE(', ' ||
|
||
|
|
(SELECT STRING_AGG(REPLACE(val::text, '"', ''''), ', ')
|
||
|
|
FROM (SELECT json_array_elements(api_args) as val) as vals),
|
||
|
|
'') || ')')
|
||
|
|
tablenames_ary
|
||
|
|
) tablenames_inner
|
||
|
|
) tablenames_outer) tablenames,
|
||
|
|
|
||
|
|
String_Agg(DISTINCT array_to_string(ARRAY[
|
||
|
|
CASE WHEN numer_tablename != geom_tablename
|
||
|
|
THEN numer_tablename || '.' || numer_geomref_colname || ' = ' ||
|
||
|
|
geom_tablename || '.' || geom_geomref_colname
|
||
|
|
ELSE NULL END,
|
||
|
|
CASE WHEN numer_tablename != denom_tablename
|
||
|
|
THEN numer_tablename || '.' || numer_geomref_colname || ' = ' ||
|
||
|
|
denom_tablename || '.' || denom_geomref_colname
|
||
|
|
ELSE NULL END
|
||
|
|
], ' AND '),
|
||
|
|
' AND ') AS obs_wheres,
|
||
|
|
|
||
|
|
String_Agg(geom_tablename || '.' || geom_geomref_colname || ' = ' ||
|
||
|
|
'_geomrefs.id', ' AND ')
|
||
|
|
AS user_wheres
|
||
|
|
FROM _meta
|
||
|
|
;
|
||
|
|
$query$
|
||
|
|
INTO colspecs, tables, obs_wheres, user_wheres
|
||
|
|
USING (SELECT ARRAY(SELECT json_array_elements_text(params))::json[]);
|
||
|
|
|
||
|
|
RETURN QUERY EXECUTE format($query$
|
||
|
|
WITH _geomrefs AS (SELECT UNNEST($1) as id)
|
||
|
|
SELECT _geomrefs.id, Array_to_JSON(ARRAY[%s]::JSON[])
|
||
|
|
FROM _geomrefs, %s
|
||
|
|
%s
|
||
|
|
GROUP BY _geomrefs.id
|
||
|
|
ORDER BY _geomrefs.id
|
||
|
|
$query$, colspecs, tables,
|
||
|
|
'WHERE ' || NULLIF(ARRAY_TO_STRING(ARRAY[
|
||
|
|
Nullif(obs_wheres, ''), Nullif(user_wheres, '')
|
||
|
|
], ' AND '), '')
|
||
|
|
)
|
||
|
|
USING geomrefs;
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
-- GetData that obtains data from array of (geom, id) geomvals.
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetData(
|
||
|
|
geomvals geomval[],
|
||
|
|
params JSON,
|
||
|
|
merge BOOLEAN DEFAULT True
|
||
|
|
)
|
||
|
|
RETURNS TABLE (
|
||
|
|
id INT,
|
||
|
|
data JSON
|
||
|
|
)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
procgeom_clauses TEXT;
|
||
|
|
val_clauses TEXT;
|
||
|
|
json_clause TEXT;
|
||
|
|
geomtype TEXT;
|
||
|
|
BEGIN
|
||
|
|
IF params IS NULL OR JSON_ARRAY_LENGTH(params) = 0 OR ARRAY_LENGTH(geomvals, 1) IS NULL THEN
|
||
|
|
RETURN QUERY EXECUTE $query$ SELECT NULL::INT, NULL::JSON LIMIT 0 $query$;
|
||
|
|
RETURN;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
geomtype := ST_GeometryType(geomvals[1].geom);
|
||
|
|
|
||
|
|
/* Read metadata to generate clauses for query */
|
||
|
|
EXECUTE $query$
|
||
|
|
WITH _meta AS (SELECT
|
||
|
|
row_number() over () colid, *
|
||
|
|
FROM json_to_recordset($1)
|
||
|
|
AS x(id TEXT, numer_id TEXT, numer_aggregate TEXT, numer_colname TEXT,
|
||
|
|
numer_geomref_colname TEXT, numer_tablename TEXT, numer_type TEXT,
|
||
|
|
denom_id TEXT, denom_aggregate TEXT, denom_colname TEXT,
|
||
|
|
denom_geomref_colname TEXT, denom_tablename TEXT, denom_type TEXT,
|
||
|
|
denom_reltype TEXT, geom_id TEXT, geom_colname TEXT,
|
||
|
|
geom_geomref_colname TEXT, geom_tablename TEXT, geom_type TEXT,
|
||
|
|
numer_timespan TEXT, geom_timespan TEXT, normalization TEXT,
|
||
|
|
api_method TEXT, api_args JSON)
|
||
|
|
),
|
||
|
|
|
||
|
|
-- Generate procgeom clauses.
|
||
|
|
-- These join the users' geoms to the relevant geometries for the
|
||
|
|
-- asked-for measures in the Observatory.
|
||
|
|
_procgeom_clauses AS (
|
||
|
|
SELECT
|
||
|
|
'_procgeoms_' || Coalesce(geom_tablename || '_' || geom_geomref_colname, api_method) || ' AS (' ||
|
||
|
|
CASE WHEN api_method IS NULL THEN
|
||
|
|
'SELECT _geoms.id, ' ||
|
||
|
|
CASE $3 WHEN True THEN '_geoms.geom'
|
||
|
|
ELSE geom_tablename || '.' || geom_colname
|
||
|
|
END || ' AS geom, ' ||
|
||
|
|
geom_tablename || '.' || geom_geomref_colname || ' AS geomref, ' ||
|
||
|
|
CASE
|
||
|
|
WHEN $2 = 'ST_Point' THEN
|
||
|
|
' Nullif(ST_Area(' || geom_tablename || '.' || geom_colname || '::Geography), 0)/1000000 ' ||
|
||
|
|
' AS area'
|
||
|
|
-- for numeric areas, include more complex calcs
|
||
|
|
ELSE
|
||
|
|
'CASE WHEN ST_Within(_geoms.geom, ' || geom_tablename || '.' || geom_colname || ')
|
||
|
|
THEN ST_Area(_geoms.geom) / Nullif(ST_Area(' || geom_tablename || '.' || geom_colname || '), 0)
|
||
|
|
WHEN ST_Within(' || geom_tablename || '.' || geom_colname || ', _geoms.geom)
|
||
|
|
THEN 1
|
||
|
|
ELSE ST_Area(cdb_observatory.safe_intersection(_geoms.geom, ' || geom_tablename || '.' || geom_colname || ')) /
|
||
|
|
Nullif(ST_Area(' || geom_tablename || '.' || geom_colname || '), 0)
|
||
|
|
END pct_obs'
|
||
|
|
END || '
|
||
|
|
FROM _geoms, observatory.' || geom_tablename || '
|
||
|
|
WHERE ST_Intersects(_geoms.geom, ' || geom_tablename || '.' || geom_colname || ')'
|
||
|
|
-- pass through input geometries for api_method
|
||
|
|
ELSE 'SELECT _geoms.id, _geoms.geom FROM _geoms'
|
||
|
|
END ||
|
||
|
|
') '
|
||
|
|
AS procgeom_clause
|
||
|
|
FROM _meta
|
||
|
|
GROUP BY api_method, geom_tablename, geom_geomref_colname, geom_colname
|
||
|
|
),
|
||
|
|
|
||
|
|
-- Generate val clauses.
|
||
|
|
-- These perform interpolations or other necessary calculations to
|
||
|
|
-- provide values according to users geometries.
|
||
|
|
_val_clauses AS (
|
||
|
|
SELECT
|
||
|
|
'_vals_' || Coalesce(geom_tablename || '_' || geom_geomref_colname, api_method) || ' AS (
|
||
|
|
SELECT _procgeoms.id, ' ||
|
||
|
|
String_Agg('json_build_object(' || CASE
|
||
|
|
-- api-delivered values
|
||
|
|
WHEN api_method IS NOT NULL THEN
|
||
|
|
'''value'', ' ||
|
||
|
|
'ARRAY_AGG( ' ||
|
||
|
|
api_method || '.' || numer_colname || ')::' || numer_type || '[]'
|
||
|
|
-- numeric internal values
|
||
|
|
WHEN cdb_observatory.isnumeric(numer_type) THEN
|
||
|
|
'''value'', ' || CASE
|
||
|
|
-- denominated
|
||
|
|
WHEN LOWER(normalization) LIKE 'denom%'
|
||
|
|
THEN CASE
|
||
|
|
WHEN denom_tablename IS NULL THEN ' NULL '
|
||
|
|
-- denominated point-in-poly
|
||
|
|
WHEN $2 = 'ST_Point' THEN
|
||
|
|
' cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname ||
|
||
|
|
' / NullIf(' || denom_tablename || '.' || denom_colname || ', 0))'
|
||
|
|
-- denominated polygon interpolation
|
||
|
|
-- SUM (numer * (% OBS geom in user geom)) / SUM (denom * (% OBS geom in user geom))
|
||
|
|
ELSE
|
||
|
|
' SUM(' || numer_tablename || '.' || numer_colname || ' ' ||
|
||
|
|
' * _procgeoms.pct_obs ' ||
|
||
|
|
' ) / NULLIF(SUM(' || denom_tablename || '.' || denom_colname || ' ' ||
|
||
|
|
' * _procgeoms.pct_obs), 0) '
|
||
|
|
END
|
||
|
|
-- areaNormalized
|
||
|
|
WHEN LOWER(normalization) LIKE 'area%'
|
||
|
|
THEN CASE
|
||
|
|
-- areaNormalized point-in-poly
|
||
|
|
WHEN $2 = 'ST_Point' THEN
|
||
|
|
' cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname ||
|
||
|
|
' / _procgeoms.area)'
|
||
|
|
-- areaNormalized polygon interpolation
|
||
|
|
-- SUM (numer * (% OBS geom in user geom)) / area of big geom
|
||
|
|
ELSE
|
||
|
|
--' NULL END '
|
||
|
|
' SUM(' || numer_tablename || '.' || numer_colname || ' ' ||
|
||
|
|
' * _procgeoms.pct_obs' ||
|
||
|
|
' ) / (Nullif(ST_Area(cdb_observatory.FIRST(_procgeoms.geom)::Geography), 0) / 1000000) '
|
||
|
|
END
|
||
|
|
-- median/average measures with universe
|
||
|
|
WHEN LOWER(numer_aggregate) IN ('median', 'average') AND
|
||
|
|
denom_reltype ILIKE 'universe' AND LOWER(normalization) LIKE 'pre%'
|
||
|
|
THEN CASE
|
||
|
|
-- predenominated point-in-poly
|
||
|
|
WHEN $2 = 'ST_Point' THEN
|
||
|
|
' cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname || ') '
|
||
|
|
ELSE
|
||
|
|
-- predenominated polygon interpolation weighted by universe
|
||
|
|
-- SUM (numer * denom * (% user geom in OBS geom)) / SUM (denom * (% user geom in OBS geom))
|
||
|
|
-- (10 * 1000 * 1) / (1000 * 1) = 10
|
||
|
|
-- (10 * 1000 * 1 + 50 * 10 * 1) / (1000 + 10) = 10500 / 10000 = 10.5
|
||
|
|
' SUM(' || numer_tablename || '.' || numer_colname ||
|
||
|
|
' * ' || denom_tablename || '.' || denom_colname ||
|
||
|
|
' * _procgeoms.pct_obs ' ||
|
||
|
|
' ) / Nullif(SUM(' || denom_tablename || '.' || denom_colname ||
|
||
|
|
' * _procgeoms.pct_obs ' || '), 0) '
|
||
|
|
END
|
||
|
|
-- prenormalized for summable measures. point or summable only!
|
||
|
|
WHEN numer_aggregate ILIKE 'sum' AND LOWER(normalization) LIKE 'pre%'
|
||
|
|
THEN CASE
|
||
|
|
-- predenominated point-in-poly
|
||
|
|
WHEN $2 = 'ST_Point' THEN
|
||
|
|
' cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname || ') '
|
||
|
|
ELSE
|
||
|
|
-- predenominated polygon interpolation
|
||
|
|
-- SUM (numer * (% user geom in OBS geom))
|
||
|
|
' SUM(' || numer_tablename || '.' || numer_colname || ' ' ||
|
||
|
|
' * _procgeoms.pct_obs) '
|
||
|
|
END
|
||
|
|
-- Everything else. Point only!
|
||
|
|
ELSE CASE
|
||
|
|
WHEN $2 = 'ST_Point' THEN
|
||
|
|
' cdb_observatory.FIRST(' || numer_tablename || '.' || numer_colname || ') '
|
||
|
|
ELSE
|
||
|
|
' cdb_observatory._OBS_RaiseNotice(''Cannot perform calculation over polygon for ' ||
|
||
|
|
numer_id || '/' || coalesce(denom_id, '') || '/' || geom_id || '/' || numer_timespan || ''')::Numeric '
|
||
|
|
END
|
||
|
|
END || '::' || numer_type
|
||
|
|
|
||
|
|
-- categorical/text
|
||
|
|
WHEN LOWER(numer_type) LIKE 'text' THEN
|
||
|
|
'''value'', ' || 'MODE() WITHIN GROUP (ORDER BY ' || numer_tablename || '.' || numer_colname || ') '
|
||
|
|
-- geometry
|
||
|
|
WHEN numer_id IS NULL THEN
|
||
|
|
'''geomref'', _procgeoms.geomref, ' ||
|
||
|
|
'''value'', ' || 'cdb_observatory.FIRST(_procgeoms.geom)::TEXT'
|
||
|
|
-- code below will return the intersection of the user's geom and the
|
||
|
|
-- OBS geom
|
||
|
|
--'''value'', ' || 'ST_Union(cdb_observatory.safe_intersection(_geoms.geom, ' || geom_tablename ||
|
||
|
|
-- '.' || geom_colname || '))::TEXT'
|
||
|
|
ELSE ''
|
||
|
|
END
|
||
|
|
|| ') val_' || colid, ', ')
|
||
|
|
|| '
|
||
|
|
FROM _procgeoms_' || Coalesce(geom_tablename || '_' || geom_geomref_colname, api_method) || ' _procgeoms ' ||
|
||
|
|
Coalesce(String_Agg(DISTINCT
|
||
|
|
Coalesce('LEFT JOIN observatory.' || numer_tablename || ' ON _procgeoms.geomref = observatory.' || numer_tablename || '.' || numer_geomref_colname,
|
||
|
|
', LATERAL (SELECT * FROM cdb_observatory.' || api_method || '(_procgeoms.geom' || Coalesce(', ' ||
|
||
|
|
(SELECT STRING_AGG(REPLACE(val::text, '"', ''''), ', ')
|
||
|
|
FROM (SELECT JSON_Array_Elements(api_args) as val) as vals),
|
||
|
|
'') || ')) AS ' || api_method)
|
||
|
|
, ' '), '') ||
|
||
|
|
CASE $3 WHEN True THEN E'\n GROUP BY _procgeoms.id ORDER BY _procgeoms.id '
|
||
|
|
ELSE E'\n GROUP BY _procgeoms.id, _procgeoms.geomref
|
||
|
|
ORDER BY _procgeoms.id, _procgeoms.geomref' END
|
||
|
|
|| ')'
|
||
|
|
AS val_clause,
|
||
|
|
'_vals_' || Coalesce(geom_tablename || '_' || geom_geomref_colname, api_method) AS cte_name
|
||
|
|
FROM _meta
|
||
|
|
GROUP BY geom_tablename, geom_geomref_colname, geom_colname, api_method
|
||
|
|
),
|
||
|
|
|
||
|
|
-- Generate clauses necessary to join together val_clauses
|
||
|
|
_val_joins AS (
|
||
|
|
SELECT String_Agg(a.cte_name || '.id = ' || b.cte_name || '.id ', ' AND ') val_joins
|
||
|
|
FROM _val_clauses a, _val_clauses b
|
||
|
|
WHERE a.cte_name != b.cte_name
|
||
|
|
AND a.cte_name < b.cte_name
|
||
|
|
),
|
||
|
|
|
||
|
|
-- Generate JSON clause. This puts together vals from val_clauses
|
||
|
|
_json_clause AS (SELECT
|
||
|
|
'SELECT ' || cdb_observatory.FIRST(cte_name) || '.id::INT,
|
||
|
|
Array_to_JSON(ARRAY[' || (SELECT String_Agg('val_' || colid, ', ') FROM _meta) || '])
|
||
|
|
FROM ' || String_Agg(cte_name, ', ') ||
|
||
|
|
Coalesce(' WHERE ' || val_joins, '')
|
||
|
|
AS json_clause
|
||
|
|
FROM _val_clauses, _val_joins
|
||
|
|
GROUP BY val_joins
|
||
|
|
)
|
||
|
|
|
||
|
|
SELECT (SELECT String_Agg(procgeom_clause, E',\n ') FROM _procgeom_clauses),
|
||
|
|
(SELECT String_Agg(val_clause, E',\n ') FROM _val_clauses),
|
||
|
|
json_clause
|
||
|
|
FROM _json_clause
|
||
|
|
$query$ INTO
|
||
|
|
procgeom_clauses,
|
||
|
|
val_clauses,
|
||
|
|
json_clause
|
||
|
|
USING params, geomtype, merge;
|
||
|
|
|
||
|
|
/* Execute query */
|
||
|
|
RETURN QUERY EXECUTE format($query$
|
||
|
|
WITH _raw_geoms AS (%s),
|
||
|
|
_geoms AS (SELECT id,
|
||
|
|
CASE WHEN (ST_NPoints(geom) > 1000)
|
||
|
|
THEN ST_CollectionExtract(ST_MakeValid(ST_SimplifyVW(geom, 0.00001)), 3)
|
||
|
|
ELSE geom END geom
|
||
|
|
FROM _raw_geoms),
|
||
|
|
-- procgeom_clauses
|
||
|
|
%s,
|
||
|
|
|
||
|
|
-- val_clauses
|
||
|
|
%s
|
||
|
|
|
||
|
|
-- json_clause
|
||
|
|
%s
|
||
|
|
$query$, CASE WHEN ARRAY_LENGTH(geomvals, 1) = 1
|
||
|
|
THEN ' SELECT $1[1].val as id, $1[1].geom as geom '
|
||
|
|
ELSE ' SELECT val as id, geom FROM UNNEST($1) '
|
||
|
|
END,
|
||
|
|
String_Agg(procgeom_clauses, E',\n '),
|
||
|
|
String_Agg(val_clauses, E',\n '),
|
||
|
|
json_clause)
|
||
|
|
USING geomvals;
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetCategory(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
category_id TEXT,
|
||
|
|
boundary_id TEXT DEFAULT NULL,
|
||
|
|
time_span TEXT DEFAULT NULL,
|
||
|
|
simplification NUMERIC DEFAULT 0.00001
|
||
|
|
)
|
||
|
|
RETURNS TEXT
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
geom_type TEXT;
|
||
|
|
params JSON;
|
||
|
|
map_type TEXT;
|
||
|
|
result TEXT;
|
||
|
|
BEGIN
|
||
|
|
IF geom IS NULL THEN
|
||
|
|
RETURN NULL;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF simplification IS NOT NULL THEN
|
||
|
|
geom := ST_Simplify(geom, simplification);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF ST_GeometryType(geom) = 'ST_Point' THEN
|
||
|
|
geom_type := 'point';
|
||
|
|
ELSIF ST_GeometryType(geom) IN ('ST_Polygon', 'ST_MultiPolygon') THEN
|
||
|
|
geom_type := 'polygon';
|
||
|
|
geom := ST_CollectionExtract(ST_MakeValid(geom), 3);
|
||
|
|
ELSE
|
||
|
|
RAISE EXCEPTION 'Invalid geometry type (%), can only handle ''ST_Point'', ''ST_Polygon'', and ''ST_MultiPolygon''',
|
||
|
|
ST_GeometryType(geom);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
params := (SELECT cdb_observatory.OBS_GetMeta(
|
||
|
|
geom, JSON_Build_Array(JSON_Build_Object('numer_id', category_id,
|
||
|
|
'geom_id', boundary_id,
|
||
|
|
'numer_timespan', time_span
|
||
|
|
)), 1, 1, 500));
|
||
|
|
|
||
|
|
IF params->0->>'geom_id' IS NULL THEN
|
||
|
|
RAISE NOTICE 'No boundary found for geom';
|
||
|
|
RETURN NULL;
|
||
|
|
ELSE
|
||
|
|
RAISE NOTICE 'Using boundary %', params->0->>'geom_id';
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT data->0->>'value' FROM
|
||
|
|
cdb_observatory.OBS_GetData(ARRAY[($1, 1)::geomval], $2)
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING geom, params;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetUSCensusMeasure(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
name TEXT,
|
||
|
|
normalize TEXT DEFAULT NULL,
|
||
|
|
boundary_id TEXT DEFAULT NULL,
|
||
|
|
time_span TEXT DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS NUMERIC AS $$
|
||
|
|
DECLARE
|
||
|
|
standardized_name text;
|
||
|
|
measure_id text;
|
||
|
|
result Numeric;
|
||
|
|
BEGIN
|
||
|
|
standardized_name = cdb_observatory._OBS_StandardizeMeasureName(name);
|
||
|
|
|
||
|
|
EXECUTE $string$
|
||
|
|
SELECT c.id
|
||
|
|
FROM observatory.obs_column c
|
||
|
|
JOIN observatory.obs_column_tag ct
|
||
|
|
ON c.id = ct.column_id
|
||
|
|
WHERE cdb_observatory._OBS_StandardizeMeasureName(c.name) = $1
|
||
|
|
AND ct.tag_id ILIKE 'us.census%'
|
||
|
|
$string$
|
||
|
|
INTO measure_id
|
||
|
|
USING standardized_name;
|
||
|
|
|
||
|
|
EXECUTE 'SELECT cdb_observatory.OBS_GetMeasure($1, $2, $3, $4, $5)'
|
||
|
|
INTO result
|
||
|
|
USING geom, measure_id, normalize, boundary_id, time_span;
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetUSCensusCategory(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
name TEXT,
|
||
|
|
boundary_id TEXT DEFAULT NULL,
|
||
|
|
time_span TEXT DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS TEXT AS $$
|
||
|
|
DECLARE
|
||
|
|
standardized_name TEXT;
|
||
|
|
category_id TEXT;
|
||
|
|
result TEXT;
|
||
|
|
BEGIN
|
||
|
|
standardized_name = cdb_observatory._OBS_StandardizeMeasureName(name);
|
||
|
|
|
||
|
|
EXECUTE $string$
|
||
|
|
SELECT c.id
|
||
|
|
FROM observatory.obs_column c
|
||
|
|
--JOIN observatory.obs_column_tag ct
|
||
|
|
-- ON c.id = ct.column_id
|
||
|
|
WHERE cdb_observatory._OBS_StandardizeMeasureName(c.name) = $1
|
||
|
|
AND c.type ILIKE 'TEXT'
|
||
|
|
AND c.id ILIKE 'us.census%' -- TODO this should be done by tag
|
||
|
|
--AND ct.tag_id = 'us.census.acs.demographics'
|
||
|
|
$string$
|
||
|
|
INTO category_id
|
||
|
|
USING standardized_name;
|
||
|
|
|
||
|
|
EXECUTE 'SELECT cdb_observatory.OBS_GetCategory($1, $2, $3, $4)'
|
||
|
|
INTO result
|
||
|
|
USING geom, category_id, boundary_id, time_span;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetPopulation(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
normalize TEXT DEFAULT NULL,
|
||
|
|
boundary_id TEXT DEFAULT NULL,
|
||
|
|
time_span TEXT DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS NUMERIC
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
population_measure_id TEXT;
|
||
|
|
result Numeric;
|
||
|
|
BEGIN
|
||
|
|
-- TODO use a super-column for global pop
|
||
|
|
population_measure_id := 'us.census.acs.B01003001';
|
||
|
|
|
||
|
|
EXECUTE $query$ SELECT cdb_observatory.OBS_GetMeasure(
|
||
|
|
$1, $2, $3, $4, $5
|
||
|
|
) LIMIT 1
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING geom, population_measure_id, normalize, boundary_id, time_span;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetSegmentSnapshot(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
boundary_id text DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS JSON
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
meta JSON;
|
||
|
|
data JSON;
|
||
|
|
result JSON;
|
||
|
|
BEGIN
|
||
|
|
boundary_id = COALESCE(boundary_id, 'us.census.tiger.census_tract');
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT cdb_observatory.OBS_GetMeta($1, ('[ ' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01003001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01001002_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01001026_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B01002001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002003_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002004_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002006_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B03002012_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B05001006_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08006001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08006002_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08301010_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08006009_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08006011_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08006015_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B08006017_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B09001001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B11001001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B14001001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B14001002_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B14001005_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B14001006_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B14001007_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B14001008_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003017_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003022_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B15003023_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B16001001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B16001002_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B16001003_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B17001001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B17001002_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19013001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19083001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B19301001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25001001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25002003_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25004002_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25004004_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25058001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25071001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25075001_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.acs.B25075025_quantile", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.spielman_singleton_segments.X10", "geom_id": ' || $2 || '},' ||
|
||
|
|
'{"numer_id": "us.census.spielman_singleton_segments.X55", "geom_id": ' || $2 || '}' ||
|
||
|
|
']')::JSON)
|
||
|
|
$query$
|
||
|
|
INTO meta
|
||
|
|
USING geom, COALESCE('"' || boundary_id || '"', 'null');
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT data FROM cdb_observatory.OBS_GetData(
|
||
|
|
ARRAY[($1, 1)::geomval], $2)
|
||
|
|
$query$
|
||
|
|
INTO data
|
||
|
|
USING geom, meta;
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
WITH els AS (SELECT
|
||
|
|
REPLACE(REPLACE(JSON_Array_Elements($1)->>'numer_id',
|
||
|
|
'us.census.spielman_singleton_segments.X55', 'x55_segment'),
|
||
|
|
'us.census.spielman_singleton_segments.X10', 'x10_segment') k,
|
||
|
|
JSON_Array_Elements($2)->>'value' v)
|
||
|
|
SELECT JSON_Object_Agg(k, v) FROM els
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING meta, data;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
-- MetadataValidation checks the metadata parameters and the geometry type
|
||
|
|
-- of the data in order to find possible wrong cases
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.obs_metadatavalidation(
|
||
|
|
geometry_extent geometry(Geometry, 4326),
|
||
|
|
geometry_type text,
|
||
|
|
params JSON,
|
||
|
|
target_geoms INTEGER DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS TABLE(valid boolean, errors text[]) AS $$
|
||
|
|
DECLARE
|
||
|
|
meta json;
|
||
|
|
errors text[];
|
||
|
|
BEGIN
|
||
|
|
errors := (ARRAY[])::TEXT[];
|
||
|
|
IF geometry_type IN ('ST_Polygon', 'ST_MultiPolygon') THEN
|
||
|
|
FOR meta IN EXECUTE 'SELECT json_array_elements(cdb_observatory.OBS_GetMeta($1, $2, 1, 1, $3))' USING geometry_extent, params, target_geoms
|
||
|
|
LOOP
|
||
|
|
IF (meta->>'normalization' = 'denominated' AND meta->>'denom_id' is NULL) THEN
|
||
|
|
errors := array_append(errors, 'Normalizated measure should have a numerator and a denominator. Please review the provided options.');
|
||
|
|
END IF;
|
||
|
|
IF (meta->>'numer_aggregate' IS NULL) THEN
|
||
|
|
errors := array_append(errors, 'For polygon geometries, aggregation is mandatory. Please review the provided options');
|
||
|
|
END IF;
|
||
|
|
IF (meta->>'numer_aggregate' IN ('median', 'average') AND meta->>'denom_id' IS NULL) THEN
|
||
|
|
errors := array_append(errors, 'Median or average aggregation for polygons requires a denominator to provide weights. Please review the provided options');
|
||
|
|
END IF;
|
||
|
|
IF (meta->>'numer_aggregate' IN ('median', 'average') AND meta->>'normalization' NOT LIKE 'pre%') THEN
|
||
|
|
errors := array_append(errors, format('Median or average aggregation only supports prenormalized normalization, %s passed. Please review the provided options', meta->>'normalization'));
|
||
|
|
END IF;
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
IF CARDINALITY(errors) > 0 THEN
|
||
|
|
RETURN QUERY EXECUTE 'SELECT FALSE, $1' USING errors;
|
||
|
|
ELSE
|
||
|
|
RETURN QUERY SELECT TRUE, ARRAY[]::TEXT[];
|
||
|
|
END IF;
|
||
|
|
ELSE
|
||
|
|
RETURN QUERY SELECT TRUE, ARRAY[]::TEXT[];
|
||
|
|
END IF;
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql STABLE;
|
||
|
|
|
||
|
|
-- TODO: implement search for timespan
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_SearchTables(
|
||
|
|
search_term text,
|
||
|
|
time_span text DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS table(tablename text, timespan text)
|
||
|
|
As $$
|
||
|
|
DECLARE
|
||
|
|
out_var text[];
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
IF time_span IS NULL
|
||
|
|
THEN
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
'SELECT tablename::text, timespan::text
|
||
|
|
FROM observatory.obs_table t
|
||
|
|
JOIN observatory.obs_column_table ct
|
||
|
|
ON ct.table_id = t.id
|
||
|
|
JOIN observatory.obs_column c
|
||
|
|
ON ct.column_id = c.id
|
||
|
|
WHERE c.type ILIKE ''geometry''
|
||
|
|
AND c.id = $1'
|
||
|
|
USING search_term;
|
||
|
|
RETURN;
|
||
|
|
ELSE
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
'SELECT tablename::text, timespan::text
|
||
|
|
FROM observatory.obs_table t
|
||
|
|
JOIN observatory.obs_column_table ct
|
||
|
|
ON ct.table_id = t.id
|
||
|
|
JOIN observatory.obs_column c
|
||
|
|
ON ct.column_id = c.id
|
||
|
|
WHERE c.type ILIKE ''geometry''
|
||
|
|
AND c.id = $1
|
||
|
|
AND t.timespan = $2'
|
||
|
|
USING search_term, time_span;
|
||
|
|
RETURN;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
||
|
|
|
||
|
|
-- Functions used to search the observatory for measures
|
||
|
|
--------------------------------------------------------------------------------
|
||
|
|
-- TODO allow the user to specify the boundary to search for measures
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_Search(
|
||
|
|
search_term text,
|
||
|
|
relevant_boundary text DEFAULT null
|
||
|
|
)
|
||
|
|
RETURNS TABLE(id text, description text, name text, aggregate text, source text) as $$
|
||
|
|
DECLARE
|
||
|
|
boundary_term text;
|
||
|
|
BEGIN
|
||
|
|
IF relevant_boundary then
|
||
|
|
boundary_term = '';
|
||
|
|
else
|
||
|
|
boundary_term = '';
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE format($string$
|
||
|
|
SELECT id::text, description::text,
|
||
|
|
name::text,
|
||
|
|
aggregate::text,
|
||
|
|
NULL::TEXT source -- TODO use tags
|
||
|
|
FROM observatory.OBS_column
|
||
|
|
where name ilike '%%' || %L || '%%'
|
||
|
|
or description ilike '%%' || %L || '%%'
|
||
|
|
%s
|
||
|
|
$string$, search_term, search_term,boundary_term);
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
-- Functions to return the geometry levels that a point is part of
|
||
|
|
--------------------------------------------------------------------------------
|
||
|
|
-- TODO add test response
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetAvailableBoundaries(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
timespan text DEFAULT null)
|
||
|
|
RETURNS TABLE(boundary_id text, description text, time_span text, tablename text) as $$
|
||
|
|
DECLARE
|
||
|
|
timespan_query TEXT DEFAULT '';
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
IF timespan != NULL
|
||
|
|
THEN
|
||
|
|
timespan_query = format('AND timespan = %L', timespan);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
$string$
|
||
|
|
SELECT
|
||
|
|
column_id::text As column_id,
|
||
|
|
obs_column.description::text As description,
|
||
|
|
timespan::text As timespan,
|
||
|
|
tablename::text As tablename
|
||
|
|
FROM
|
||
|
|
observatory.OBS_table,
|
||
|
|
observatory.OBS_column_table,
|
||
|
|
observatory.OBS_column
|
||
|
|
WHERE
|
||
|
|
observatory.OBS_column_table.column_id = observatory.obs_column.id AND
|
||
|
|
observatory.OBS_column_table.table_id = observatory.obs_table.id
|
||
|
|
AND
|
||
|
|
observatory.OBS_column.type = 'Geometry'
|
||
|
|
AND
|
||
|
|
ST_Intersects($1, st_setsrid(observatory.obs_table.the_geom, 4326))
|
||
|
|
$string$ || timespan_query
|
||
|
|
USING geom;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- Functions the interface works from to identify available numerators,
|
||
|
|
-- denominators, geometries, and timespans
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetAvailableNumerators(
|
||
|
|
bounds GEOMETRY DEFAULT NULL,
|
||
|
|
filter_tags TEXT[] DEFAULT NULL,
|
||
|
|
denom_id TEXT DEFAULT NULL,
|
||
|
|
geom_id TEXT DEFAULT NULL,
|
||
|
|
timespan TEXT DEFAULT NULL
|
||
|
|
) RETURNS TABLE (
|
||
|
|
numer_id TEXT,
|
||
|
|
numer_name TEXT,
|
||
|
|
numer_description TEXT,
|
||
|
|
numer_weight NUMERIC,
|
||
|
|
numer_license TEXT,
|
||
|
|
numer_source TEXT,
|
||
|
|
numer_type TEXT,
|
||
|
|
numer_aggregate TEXT,
|
||
|
|
numer_extra JSONB,
|
||
|
|
numer_tags JSONB,
|
||
|
|
valid_denom BOOLEAN,
|
||
|
|
valid_geom BOOLEAN,
|
||
|
|
valid_timespan BOOLEAN
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
geom_clause TEXT;
|
||
|
|
BEGIN
|
||
|
|
filter_tags := COALESCE(filter_tags, (ARRAY[])::TEXT[]);
|
||
|
|
denom_id := COALESCE(denom_id, '');
|
||
|
|
geom_id := COALESCE(geom_id, '');
|
||
|
|
timespan := COALESCE(timespan, '');
|
||
|
|
IF bounds IS NULL THEN
|
||
|
|
geom_clause := '';
|
||
|
|
ELSE
|
||
|
|
geom_clause := 'ST_Intersects(the_geom, $5) AND';
|
||
|
|
END IF;
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
format($string$
|
||
|
|
SELECT numer_id::TEXT,
|
||
|
|
numer_name::TEXT,
|
||
|
|
numer_description::TEXT,
|
||
|
|
numer_weight::NUMERIC,
|
||
|
|
NULL::TEXT license,
|
||
|
|
NULL::TEXT source,
|
||
|
|
numer_type numer_type,
|
||
|
|
numer_aggregate numer_aggregate,
|
||
|
|
numer_extra::JSONB numer_extra,
|
||
|
|
numer_tags numer_tags,
|
||
|
|
$1 = ANY(denoms) valid_denom,
|
||
|
|
$2 = ANY(geoms) valid_geom,
|
||
|
|
$3 = ANY(timespans) valid_timespan
|
||
|
|
FROM observatory.obs_meta_numer
|
||
|
|
WHERE %s (numer_tags ?& $4 OR CARDINALITY($4) = 0)
|
||
|
|
$string$, geom_clause)
|
||
|
|
USING denom_id, geom_id, timespan, filter_tags, bounds;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_GetNumerators(
|
||
|
|
bounds GEOMETRY DEFAULT NULL,
|
||
|
|
section_tags TEXT[] DEFAULT ARRAY[]::TEXT[],
|
||
|
|
subsection_tags TEXT[] DEFAULT ARRAY[]::TEXT[],
|
||
|
|
other_tags TEXT[] DEFAULT ARRAY[]::TEXT[],
|
||
|
|
ids TEXT[] DEFAULT ARRAY[]::TEXT[],
|
||
|
|
name TEXT DEFAULT NULL,
|
||
|
|
denom_id TEXT DEFAULT '',
|
||
|
|
geom_id TEXT DEFAULT '',
|
||
|
|
timespan TEXT DEFAULT ''
|
||
|
|
) RETURNS TABLE (
|
||
|
|
numer_id TEXT,
|
||
|
|
numer_name TEXT,
|
||
|
|
numer_description TEXT,
|
||
|
|
numer_weight NUMERIC,
|
||
|
|
numer_license TEXT,
|
||
|
|
numer_source TEXT,
|
||
|
|
numer_type TEXT,
|
||
|
|
numer_aggregate TEXT,
|
||
|
|
numer_extra JSONB,
|
||
|
|
numer_tags JSONB,
|
||
|
|
valid_denom BOOLEAN,
|
||
|
|
valid_geom BOOLEAN,
|
||
|
|
valid_timespan BOOLEAN
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
where_clause_elements TEXT[];
|
||
|
|
geom_clause TEXT;
|
||
|
|
where_clause TEXT;
|
||
|
|
BEGIN
|
||
|
|
where_clause_elements := (ARRAY[])::TEXT[];
|
||
|
|
where_clause := '';
|
||
|
|
|
||
|
|
IF bounds IS NOT NULL THEN
|
||
|
|
where_clause_elements := array_append(where_clause_elements, format($data$ST_Intersects(the_geom, '%s'::geometry)$data$, bounds));
|
||
|
|
END IF;
|
||
|
|
IF cardinality(section_tags) > 0 THEN
|
||
|
|
where_clause_elements := array_append(where_clause_elements, format($data$numer_tags ?| '%s'$data$, section_tags));
|
||
|
|
END IF;
|
||
|
|
IF cardinality(subsection_tags) > 0 THEN
|
||
|
|
where_clause_elements := array_append(where_clause_elements, format($data$numer_tags ?| '%s'$data$, subsection_tags));
|
||
|
|
END IF;
|
||
|
|
IF cardinality(other_tags) > 0 THEN
|
||
|
|
where_clause_elements := array_append(where_clause_elements, format($data$numer_tags ?| '%s'$data$, other_tags));
|
||
|
|
END IF;
|
||
|
|
IF cardinality(ids) > 0 THEN
|
||
|
|
where_clause_elements := array_append(where_clause_elements, format($data$numer_id IN (array_to_string('%s'::text[], ','))$data$, ids));
|
||
|
|
END IF;
|
||
|
|
IF name IS NOT NULL AND name != '' THEN
|
||
|
|
where_clause_elements := array_append(where_clause_elements, format($data$numer_name ilike '%%%s%%'$data$, name));
|
||
|
|
END IF;
|
||
|
|
IF cardinality(where_clause_elements) > 0 THEN
|
||
|
|
where_clause := format($clause$WHERE %s$clause$, array_to_string(where_clause_elements, ' AND '));
|
||
|
|
END IF;
|
||
|
|
RAISE DEBUG '%', array_to_string(where_clause_elements, ' AND ');
|
||
|
|
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
format($string$
|
||
|
|
SELECT numer_id::TEXT,
|
||
|
|
numer_name::TEXT,
|
||
|
|
numer_description::TEXT,
|
||
|
|
numer_weight::NUMERIC,
|
||
|
|
NULL::TEXT license,
|
||
|
|
NULL::TEXT source,
|
||
|
|
numer_type numer_type,
|
||
|
|
numer_aggregate numer_aggregate,
|
||
|
|
numer_extra::JSONB numer_extra,
|
||
|
|
numer_tags numer_tags,
|
||
|
|
$1 = ANY(denoms) valid_denom,
|
||
|
|
$2 = ANY(geoms) valid_geom,
|
||
|
|
$3 = ANY(timespans) valid_timespan
|
||
|
|
FROM observatory.obs_meta_numer
|
||
|
|
%s
|
||
|
|
$string$, where_clause)
|
||
|
|
USING denom_id, geom_id, timespan;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetAvailableDenominators(
|
||
|
|
bounds GEOMETRY DEFAULT NULL,
|
||
|
|
filter_tags TEXT[] DEFAULT NULL,
|
||
|
|
numer_id TEXT DEFAULT NULL,
|
||
|
|
geom_id TEXT DEFAULT NULL,
|
||
|
|
timespan TEXT DEFAULT NULL
|
||
|
|
) RETURNS TABLE (
|
||
|
|
denom_id TEXT,
|
||
|
|
denom_name TEXT,
|
||
|
|
denom_description TEXT,
|
||
|
|
denom_weight NUMERIC,
|
||
|
|
denom_license TEXT,
|
||
|
|
denom_source TEXT,
|
||
|
|
denom_type TEXT,
|
||
|
|
denom_aggregate TEXT,
|
||
|
|
denom_extra JSONB,
|
||
|
|
denom_tags JSONB,
|
||
|
|
valid_numer BOOLEAN,
|
||
|
|
valid_geom BOOLEAN,
|
||
|
|
valid_timespan BOOLEAN
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
geom_clause TEXT;
|
||
|
|
BEGIN
|
||
|
|
filter_tags := COALESCE(filter_tags, (ARRAY[])::TEXT[]);
|
||
|
|
numer_id := COALESCE(numer_id, '');
|
||
|
|
geom_id := COALESCE(geom_id, '');
|
||
|
|
timespan := COALESCE(timespan, '');
|
||
|
|
IF bounds IS NULL THEN
|
||
|
|
geom_clause := '';
|
||
|
|
ELSE
|
||
|
|
geom_clause := 'ST_Intersects(the_geom, $5) AND';
|
||
|
|
END IF;
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
format($string$
|
||
|
|
SELECT denom_id::TEXT,
|
||
|
|
denom_name::TEXT,
|
||
|
|
denom_description::TEXT,
|
||
|
|
denom_weight::NUMERIC,
|
||
|
|
NULL::TEXT license,
|
||
|
|
NULL::TEXT source,
|
||
|
|
denom_type::TEXT,
|
||
|
|
denom_aggregate::TEXT,
|
||
|
|
denom_extra::JSONB,
|
||
|
|
denom_tags::JSONB,
|
||
|
|
$1 = ANY(numers) valid_numer,
|
||
|
|
$2 = ANY(geoms) valid_geom,
|
||
|
|
$3 = ANY(timespans) valid_timespan
|
||
|
|
FROM observatory.obs_meta_denom
|
||
|
|
WHERE %s (denom_tags ?& $4 OR CARDINALITY($4) = 0)
|
||
|
|
$string$, geom_clause)
|
||
|
|
USING numer_id, geom_id, timespan, filter_tags, bounds;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetAvailableGeometries(
|
||
|
|
bounds GEOMETRY DEFAULT NULL,
|
||
|
|
filter_tags TEXT[] DEFAULT NULL,
|
||
|
|
numer_id TEXT DEFAULT NULL,
|
||
|
|
denom_id TEXT DEFAULT NULL,
|
||
|
|
timespan TEXT DEFAULT NULL
|
||
|
|
) RETURNS TABLE (
|
||
|
|
geom_id TEXT,
|
||
|
|
geom_name TEXT,
|
||
|
|
geom_description TEXT,
|
||
|
|
geom_weight NUMERIC,
|
||
|
|
geom_aggregate TEXT,
|
||
|
|
geom_license TEXT,
|
||
|
|
geom_source TEXT,
|
||
|
|
geom_type TEXT,
|
||
|
|
geom_extra JSONB,
|
||
|
|
geom_tags JSONB,
|
||
|
|
valid_numer BOOLEAN,
|
||
|
|
valid_denom BOOLEAN,
|
||
|
|
valid_timespan BOOLEAN,
|
||
|
|
score NUMERIC,
|
||
|
|
numtiles BIGINT,
|
||
|
|
notnull_percent NUMERIC,
|
||
|
|
numgeoms NUMERIC,
|
||
|
|
percentfill NUMERIC,
|
||
|
|
estnumgeoms NUMERIC,
|
||
|
|
meanmediansize NUMERIC
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
geom_clause TEXT;
|
||
|
|
BEGIN
|
||
|
|
filter_tags := COALESCE(filter_tags, (ARRAY[])::TEXT[]);
|
||
|
|
numer_id := COALESCE(numer_id, '');
|
||
|
|
denom_id := COALESCE(denom_id, '');
|
||
|
|
timespan := COALESCE(timespan, '');
|
||
|
|
IF bounds IS NULL THEN
|
||
|
|
geom_clause := '';
|
||
|
|
ELSE
|
||
|
|
geom_clause := 'ST_Intersects(the_geom, $5) AND';
|
||
|
|
END IF;
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
format($string$
|
||
|
|
WITH available_geoms AS (
|
||
|
|
SELECT geom_id::TEXT,
|
||
|
|
geom_name::TEXT,
|
||
|
|
geom_description::TEXT,
|
||
|
|
geom_weight::NUMERIC,
|
||
|
|
NULL::TEXT geom_aggregate,
|
||
|
|
NULL::TEXT license,
|
||
|
|
NULL::TEXT source,
|
||
|
|
geom_type::TEXT,
|
||
|
|
geom_extra::JSONB,
|
||
|
|
geom_tags::JSONB,
|
||
|
|
$1 = ANY(numers) valid_numer,
|
||
|
|
$2 = ANY(denoms) valid_denom,
|
||
|
|
CASE WHEN $3 IS NOT NULL AND $3 != '' THEN
|
||
|
|
-- Here we are looking for geometries with: a) geometry timespan or b) numerators linked to that geometries that fit in the
|
||
|
|
-- timespan passed. For example it look for geometries with timespan '2015 - 2015' or numerators linked to that geometry that has
|
||
|
|
-- '2015 - 2015' as one of the valid timespans.
|
||
|
|
-- If we pass a numerator_id, we filter by that numerator
|
||
|
|
CASE WHEN $1 IS NOT NULL AND $1 != '' THEN
|
||
|
|
EXISTS (SELECT 1 FROM observatory.obs_meta_geom_numer_timespan onu WHERE o.geom_id = onu.geom_id AND onu.numer_id = $1 AND ($3 = ANY(onu.timespans) OR $3 IN (select(unnest(o.timespans)))))
|
||
|
|
ELSE
|
||
|
|
EXISTS (SELECT 1 FROM observatory.obs_meta_geom_numer_timespan onu WHERE o.geom_id = onu.geom_id AND ($3 = ANY(onu.timespans) OR $3 IN (select(unnest(o.timespans)))))
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
false
|
||
|
|
END as valid_timespan
|
||
|
|
FROM observatory.obs_meta_geom o
|
||
|
|
WHERE %s (geom_tags ?& $4 OR CARDINALITY($4) = 0)
|
||
|
|
), scores AS (
|
||
|
|
SELECT * FROM cdb_observatory._OBS_GetGeometryScores($5,
|
||
|
|
(SELECT ARRAY_AGG(geom_id) FROM available_geoms)
|
||
|
|
)
|
||
|
|
) SELECT DISTINCT ON (geom_id) available_geoms.*, score, numtiles, notnull_percent, numgeoms,
|
||
|
|
percentfill, estnumgeoms, meanmediansize
|
||
|
|
FROM available_geoms, scores
|
||
|
|
WHERE available_geoms.geom_id = scores.column_id
|
||
|
|
$string$, geom_clause)
|
||
|
|
USING numer_id, denom_id, timespan, filter_tags, bounds;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetAvailableTimespans(
|
||
|
|
bounds GEOMETRY DEFAULT NULL,
|
||
|
|
filter_tags TEXT[] DEFAULT NULL,
|
||
|
|
numer_id TEXT DEFAULT NULL,
|
||
|
|
denom_id TEXT DEFAULT NULL,
|
||
|
|
geom_id TEXT DEFAULT NULL
|
||
|
|
) RETURNS TABLE (
|
||
|
|
timespan_id TEXT,
|
||
|
|
timespan_name TEXT,
|
||
|
|
timespan_description TEXT,
|
||
|
|
timespan_weight NUMERIC,
|
||
|
|
timespan_aggregate TEXT,
|
||
|
|
timespan_license TEXT,
|
||
|
|
timespan_source TEXT,
|
||
|
|
timespan_type TEXT,
|
||
|
|
timespan_extra JSONB,
|
||
|
|
timespan_tags JSONB,
|
||
|
|
valid_numer BOOLEAN,
|
||
|
|
valid_denom BOOLEAN,
|
||
|
|
valid_geom BOOLEAN
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
geom_clause TEXT;
|
||
|
|
BEGIN
|
||
|
|
filter_tags := COALESCE(filter_tags, (ARRAY[])::TEXT[]);
|
||
|
|
numer_id := COALESCE(numer_id, '');
|
||
|
|
denom_id := COALESCE(denom_id, '');
|
||
|
|
geom_id := COALESCE(geom_id, '');
|
||
|
|
IF bounds IS NULL THEN
|
||
|
|
geom_clause := '';
|
||
|
|
ELSE
|
||
|
|
geom_clause := 'ST_Intersects(the_geom, $5) AND';
|
||
|
|
END IF;
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE
|
||
|
|
format($string$
|
||
|
|
SELECT timespan_id::TEXT,
|
||
|
|
timespan_name::TEXT,
|
||
|
|
timespan_description::TEXT,
|
||
|
|
timespan_weight::NUMERIC,
|
||
|
|
NULL::TEXT timespan_aggregate,
|
||
|
|
NULL::TEXT timespan_license,
|
||
|
|
NULL::TEXT timespan_source,
|
||
|
|
NULL::TEXT timespan_type,
|
||
|
|
NULL::JSONB timespan_extra,
|
||
|
|
NULL::JSONB timespan_tags,
|
||
|
|
$1 = ANY(numers) valid_numer,
|
||
|
|
$2 = ANY(denoms) valid_denom,
|
||
|
|
$3 = ANY(geoms) valid_geom_id
|
||
|
|
FROM observatory.obs_meta_timespan
|
||
|
|
WHERE %s (timespan_tags ?& $4 OR CARDINALITY($4) = 0)
|
||
|
|
$string$, geom_clause)
|
||
|
|
USING numer_id, denom_id, geom_id, filter_tags, bounds;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
-- Function below should replace SQL in
|
||
|
|
-- https://github.com/CartoDB/cartodb/blob/ab465cb2918c917940e955963b0cd8a050c06600/lib/assets/javascripts/cartodb3/editor/layers/layer-content-views/analyses/data-observatory-metadata.js
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_LegacyBuilderMetadata(
|
||
|
|
aggregate_type TEXT DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS TABLE (
|
||
|
|
name TEXT,
|
||
|
|
subsection JSONB
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
aggregate_condition TEXT DEFAULT '';
|
||
|
|
BEGIN
|
||
|
|
IF LOWER(aggregate_type) ILIKE 'sum' THEN
|
||
|
|
aggregate_condition := ' AND numer_aggregate IN (''sum'', ''median'', ''average'') ';
|
||
|
|
ELSIF aggregate_type IS NOT NULL THEN
|
||
|
|
aggregate_condition := format(' AND numer_aggregate ILIKE %L ', aggregate_type);
|
||
|
|
END IF;
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE format($string$
|
||
|
|
WITH expanded AS (
|
||
|
|
SELECT JSONB_Build_Object('id', numer_id, 'name', numer_name) "column",
|
||
|
|
SUBSTR((sections).key, 9) section_id, (sections).value section_name,
|
||
|
|
SUBSTR((subsections).key, 12) subsection_id, (subsections).value subsection_name
|
||
|
|
FROM (
|
||
|
|
SELECT numer_id, numer_name,
|
||
|
|
jsonb_each_text(numer_tags) as sections,
|
||
|
|
jsonb_each_text as subsections
|
||
|
|
FROM (SELECT numer_id, numer_name, numer_tags,
|
||
|
|
jsonb_each_text(numer_tags)
|
||
|
|
FROM cdb_observatory.obs_getavailablenumerators()
|
||
|
|
WHERE numer_weight > 0 %s
|
||
|
|
) foo
|
||
|
|
) bar
|
||
|
|
WHERE (sections).key LIKE 'section/%%'
|
||
|
|
AND (subsections).key LIKE 'subsection/%%'
|
||
|
|
), grouped_by_subsections AS (
|
||
|
|
SELECT JSONB_Agg(JSONB_Build_Object('f1', "column")) AS columns,
|
||
|
|
section_id, section_name, subsection_id, subsection_name
|
||
|
|
FROM expanded
|
||
|
|
GROUP BY section_id, section_name, subsection_id, subsection_name
|
||
|
|
)
|
||
|
|
SELECT section_name as name, JSONB_Agg(
|
||
|
|
JSONB_Build_Object(
|
||
|
|
'f1', JSONB_Build_Object(
|
||
|
|
'name', subsection_name,
|
||
|
|
'id', subsection_id,
|
||
|
|
'columns', columns
|
||
|
|
)
|
||
|
|
)
|
||
|
|
) as subsection
|
||
|
|
FROM grouped_by_subsections
|
||
|
|
GROUP BY section_name
|
||
|
|
$string$, aggregate_condition);
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_GetGeometryScores(
|
||
|
|
bounds Geometry(Geometry, 4326) DEFAULT NULL,
|
||
|
|
filter_geom_ids TEXT[] DEFAULT NULL,
|
||
|
|
desired_num_geoms INTEGER DEFAULT NULL,
|
||
|
|
desired_area NUMERIC DEFAULT NULL
|
||
|
|
) RETURNS TABLE (
|
||
|
|
score NUMERIC,
|
||
|
|
numtiles BIGINT,
|
||
|
|
table_id TEXT,
|
||
|
|
column_id TEXT,
|
||
|
|
notnull_percent NUMERIC,
|
||
|
|
numgeoms NUMERIC,
|
||
|
|
percentfill NUMERIC,
|
||
|
|
estnumgeoms NUMERIC,
|
||
|
|
meanmediansize NUMERIC
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
num_geoms_multiplier Numeric;
|
||
|
|
BEGIN
|
||
|
|
IF desired_num_geoms IS NULL THEN
|
||
|
|
desired_num_geoms := 3000;
|
||
|
|
END IF;
|
||
|
|
filter_geom_ids := COALESCE(filter_geom_ids, (ARRAY[])::TEXT[]);
|
||
|
|
-- Very complex geometries simply fail. For a boundary check, we can
|
||
|
|
-- comfortably get away with the simplicity of an envelope
|
||
|
|
IF ST_Npoints(bounds) > 10000 THEN
|
||
|
|
bounds := ST_Envelope(bounds);
|
||
|
|
END IF;
|
||
|
|
IF desired_area IS NULL THEN
|
||
|
|
desired_area := ST_Area(bounds);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- In case of points, desired_area will be 0. We still want an accurate
|
||
|
|
-- estimate of numgeoms in that case.
|
||
|
|
IF desired_area = 0 THEN
|
||
|
|
num_geoms_multiplier := 1;
|
||
|
|
ELSE
|
||
|
|
num_geoms_multiplier := Coalesce(desired_area / Nullif(ST_Area(bounds), 0), 1);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN QUERY
|
||
|
|
EXECUTE $string$
|
||
|
|
WITH clipped_geom AS (
|
||
|
|
SELECT column_id, table_id
|
||
|
|
, CASE WHEN $1 IS NOT NULL THEN ST_Clip(tile, $1, True) -- -20
|
||
|
|
ELSE tile END clipped_tile
|
||
|
|
, tile
|
||
|
|
FROM observatory.obs_column_table_tile_simple
|
||
|
|
WHERE ($1 IS NULL OR ST_Intersects($1, tile))
|
||
|
|
AND (column_id = ANY($2) OR cardinality($2) = 0)
|
||
|
|
), clipped_geom_countagg AS (
|
||
|
|
SELECT column_id, table_id
|
||
|
|
, BOOL_AND(ST_BandIsNoData(clipped_tile, 1)) nodata
|
||
|
|
FROM clipped_geom
|
||
|
|
GROUP BY column_id, table_id
|
||
|
|
), clipped_geom_reagg AS (
|
||
|
|
SELECT COUNT(*)::BIGINT cnt, a.column_id, a.table_id,
|
||
|
|
cdb_observatory.FIRST(nodata) first_nodata,
|
||
|
|
cdb_observatory.FIRST(tile) first_tile,
|
||
|
|
(ST_SummaryStatsAgg(clipped_tile, 1, False)).sum::Numeric sum_geoms, -- ND
|
||
|
|
(ST_SummaryStatsAgg(clipped_tile, 2, False)).mean::Numeric / 255 mean_fill --ND
|
||
|
|
FROM clipped_geom_countagg a, clipped_geom b
|
||
|
|
WHERE a.table_id = b.table_id
|
||
|
|
AND a.column_id = b.column_id
|
||
|
|
GROUP BY a.column_id, a.table_id
|
||
|
|
), final AS (
|
||
|
|
SELECT
|
||
|
|
cnt, table_id, column_id
|
||
|
|
, NULL::Numeric AS notnull_percent
|
||
|
|
, (CASE WHEN first_nodata IS FALSE
|
||
|
|
THEN sum_geoms
|
||
|
|
ELSE COALESCE(ST_Value(first_tile, 1, ST_PointOnSurface($1)), 0)
|
||
|
|
* (ST_Area($1) / ST_Area(ST_PixelAsPolygon(first_tile, 0, 0)))
|
||
|
|
END)::Numeric * $4
|
||
|
|
AS numgeoms
|
||
|
|
, (CASE WHEN first_nodata IS FALSE
|
||
|
|
THEN mean_fill
|
||
|
|
ELSE COALESCE(ST_Value(first_tile, 2, ST_PointOnSurface($1))::Numeric / 255, 0) -- -2
|
||
|
|
END)::Numeric
|
||
|
|
AS percentfill
|
||
|
|
, null::numeric estnumgeoms
|
||
|
|
, null::numeric meanmediansize
|
||
|
|
FROM clipped_geom_reagg
|
||
|
|
) SELECT
|
||
|
|
((100.0 / (1+abs(log(0.0001 + $3) - log(0.0001 + numgeoms::Numeric)))) * percentfill)::Numeric
|
||
|
|
AS score, *
|
||
|
|
FROM final
|
||
|
|
$string$ USING bounds, filter_geom_ids, desired_num_geoms, num_geoms_multiplier;
|
||
|
|
RETURN;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
||
|
|
-- Data Observatory -- Welcome to the Future
|
||
|
|
-- These Data Observatory functions provide access to boundary polyons (and
|
||
|
|
-- their ids) such as those available through the US Census Tiger, Who's on
|
||
|
|
-- First, the Spanish Census, and so on
|
||
|
|
|
||
|
|
|
||
|
|
-- OBS_GetBoundary
|
||
|
|
--
|
||
|
|
-- Returns the boundary polygon(s) that overlap with the input point geometry.
|
||
|
|
-- From an input point geometry, find the boundary which intersects with the
|
||
|
|
-- centroid of the input geometry
|
||
|
|
-- Inputs:
|
||
|
|
-- geom geometry: input point geometry
|
||
|
|
-- boundary_id text: source id of boundaries
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- boundary geometry: geometry boundary that intersects with geom, is at the
|
||
|
|
-- resolution requested with boundary_id, and time_span
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetBoundary(
|
||
|
|
geom geometry(Point, 4326),
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL)
|
||
|
|
RETURNS geometry(Geometry, 4326)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
boundary geometry(Geometry, 4326);
|
||
|
|
target_table text;
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
-- TODO: Check if SRID = 4326, if not transform?
|
||
|
|
|
||
|
|
-- if not a point, raise error
|
||
|
|
IF ST_GeometryType(geom) != 'ST_Point'
|
||
|
|
THEN
|
||
|
|
RAISE EXCEPTION 'Invalid geometry type (%), expecting ''ST_Point''', ST_GeometryType(geom);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- return the first boundary in intersections
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT * FROM cdb_observatory._OBS_GetBoundariesByGeometry($1, $2, $3) LIMIT 1
|
||
|
|
$query$ INTO boundary
|
||
|
|
USING geom, boundary_id, time_span;
|
||
|
|
|
||
|
|
RETURN boundary;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- OBS_GetBoundaryId
|
||
|
|
--
|
||
|
|
-- retrieves the boundary identifier (e.g., '36047' = Kings County/Brooklyn, NY)
|
||
|
|
-- corresponding to the location geom and boundary types (e.g.,
|
||
|
|
-- us.census.tiger.county)
|
||
|
|
|
||
|
|
-- Inputs:
|
||
|
|
-- geom geometry: location where the boundary is requested to overlap with
|
||
|
|
-- boundary_id text: source id of boundaries (e.g., us.census.tiger.county)
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- geometry_id text: identifier of the geometry which overlaps with the input
|
||
|
|
-- point geom in the table corresponding to boundary_id and
|
||
|
|
-- time_span
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetBoundaryId(
|
||
|
|
geom geometry(Point, 4326),
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL
|
||
|
|
)
|
||
|
|
RETURNS text
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
result TEXT;
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT geom_refs FROM cdb_observatory._OBS_GetBoundariesByGeometry(
|
||
|
|
$1, $2, $3) LIMIT 1
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING geom, boundary_id, time_span;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
-- OBS_GetBoundaryById
|
||
|
|
--
|
||
|
|
-- Given a geometry reference (e.g., geoid for US Census), and it's geometry
|
||
|
|
-- level (see OBS_ListGeomColumns() for all available boundary ids), give back
|
||
|
|
-- the boundary that corresponds to that geometry_id, boundary_id, and
|
||
|
|
-- time_span
|
||
|
|
|
||
|
|
-- Inputs:
|
||
|
|
-- geometry_id text: geometry id of the requested boundary
|
||
|
|
-- boundary_id text: source id of boundaries (e.g., us.census.tiger.county)
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- boundary geometry: geometry boundary that matches geometry_id, is at the
|
||
|
|
-- resolution requested with boundary_id, and time_span
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetBoundaryById(
|
||
|
|
geometry_id text, -- ex: '36047'
|
||
|
|
boundary_id text, -- ex: 'us.census.tiger.county'
|
||
|
|
time_span text DEFAULT NULL -- ex: '2009'
|
||
|
|
)
|
||
|
|
RETURNS geometry(geometry, 4326)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
result GEOMETRY;
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT (data->0->>'value')::Geometry
|
||
|
|
FROM cdb_observatory.OBS_GetData(
|
||
|
|
ARRAY[$1],
|
||
|
|
cdb_observatory.OBS_GetMeta(
|
||
|
|
ST_MakeEnvelope(-180, -90, 180, 90, 4326),
|
||
|
|
('[{"geom_id": "' || $2 || '"}]')::JSON))
|
||
|
|
$query$
|
||
|
|
INTO result
|
||
|
|
USING geometry_id, boundary_id;
|
||
|
|
|
||
|
|
RETURN result;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- _OBS_GetBoundariesByGeometry
|
||
|
|
-- internal function for retrieving geometries based on an input geometry
|
||
|
|
-- see OBS_GetBoundariesByGeometry or OBS_GetBoundariesByPointAndRadius for
|
||
|
|
-- more information
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_GetBoundariesByGeometry(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL,
|
||
|
|
overlap_type text DEFAULT NULL)
|
||
|
|
RETURNS TABLE (
|
||
|
|
the_geom geometry,
|
||
|
|
geom_refs text
|
||
|
|
) AS $$
|
||
|
|
DECLARE
|
||
|
|
meta JSON;
|
||
|
|
BEGIN
|
||
|
|
overlap_type := COALESCE(overlap_type, 'intersects');
|
||
|
|
-- check inputs
|
||
|
|
IF lower(overlap_type) NOT IN ('contains', 'intersects', 'within')
|
||
|
|
THEN
|
||
|
|
-- recognized overlap type (map to ST_Contains, ST_Intersects, and ST_Within)
|
||
|
|
RAISE EXCEPTION 'Overlap type ''%'' is not an accepted type (choose intersects, within, or contains)', overlap_type;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXECUTE $query$
|
||
|
|
SELECT cdb_observatory.OBS_GetMeta($1, JSON_Build_Array(JSON_Build_Object(
|
||
|
|
'geom_id', $2, 'geom_timespan', $3)))
|
||
|
|
$query$
|
||
|
|
INTO meta
|
||
|
|
USING geom, boundary_id, time_span;
|
||
|
|
|
||
|
|
IF meta->0->>'geom_id' IS NULL THEN
|
||
|
|
RETURN QUERY EXECUTE 'SELECT NULL::Geometry, NULL::Text LIMIT 0';
|
||
|
|
RETURN;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- return first boundary in intersections
|
||
|
|
RETURN QUERY EXECUTE $query$
|
||
|
|
SELECT (data->0->>'value')::Geometry the_geom, data->0->>'geomref' geom_refs
|
||
|
|
FROM cdb_observatory.OBS_GetData(
|
||
|
|
ARRAY[($1, 1)::geomval], $2, False
|
||
|
|
)
|
||
|
|
$query$ USING geom, meta;
|
||
|
|
RETURN;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- OBS_GetBoundariesByGeometry
|
||
|
|
--
|
||
|
|
-- Given a bounding box (or a polygon), and it's geometry level (see
|
||
|
|
-- OBS_ListGeomColumns() for all available boundary ids), give back the
|
||
|
|
-- boundaries that are contained within the bounding box polygon and the
|
||
|
|
-- associated geometry ids
|
||
|
|
|
||
|
|
-- Inputs:
|
||
|
|
-- geom geometry: bounding box (or polygon) of the region of interest
|
||
|
|
-- boundary_id text: source id of boundaries (e.g., us.census.tiger.county)
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- table with the following columns
|
||
|
|
-- boundary geometry: geometry boundary that is contained within the input
|
||
|
|
-- bounding box at the requested geometry level
|
||
|
|
-- with boundary_id, and time_span
|
||
|
|
-- geom_refs text: geometry identifiers (e.g., geoid for the US Census)
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetBoundariesByGeometry(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL,
|
||
|
|
overlap_type text DEFAULT NULL)
|
||
|
|
RETURNS TABLE(the_geom geometry, geom_refs text)
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
RETURN QUERY SELECT *
|
||
|
|
FROM cdb_observatory._OBS_GetBoundariesByGeometry(
|
||
|
|
geom,
|
||
|
|
boundary_id,
|
||
|
|
time_span,
|
||
|
|
overlap_type
|
||
|
|
);
|
||
|
|
RETURN;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- OBS_GetBoundariesByPointAndRadius
|
||
|
|
--
|
||
|
|
-- Given a point and radius, and it's geometry level (see
|
||
|
|
-- OBS_ListGeomColumns() for all available boundary ids), give back the
|
||
|
|
-- boundaries that are contained within the point buffered by radius meters and
|
||
|
|
-- the associated geometry ids
|
||
|
|
|
||
|
|
-- Inputs:
|
||
|
|
-- geom geometry: point geometry centered on area of interest
|
||
|
|
-- radius numeric: radius (in meters) of a circle centered on geom for
|
||
|
|
-- selecting polygons
|
||
|
|
-- boundary_id text: source id of boundaries (e.g., us.census.tiger.county)
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- table with the following columns
|
||
|
|
-- boundary geometry: geometry boundary that is contained within the input
|
||
|
|
-- bounding box at the requested geometry level
|
||
|
|
-- with boundary_id, and time_span
|
||
|
|
-- geom_refs text: geometry identifiers (e.g., geoid for the US Census)
|
||
|
|
--
|
||
|
|
-- TODO: move to ST_DWithin instead of buffer + intersects?
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetBoundariesByPointAndRadius(
|
||
|
|
geom geometry(Point, 4326), -- point
|
||
|
|
radius numeric, -- radius in meters
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL,
|
||
|
|
overlap_type text DEFAULT NULL)
|
||
|
|
RETURNS TABLE(the_geom geometry, geom_refs text)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
circle_boundary geometry(Geometry, 4326);
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
IF ST_GeometryType(geom) != 'ST_Point'
|
||
|
|
THEN
|
||
|
|
RAISE EXCEPTION 'Input geometry ''%'' is not a point', ST_AsText(geom);
|
||
|
|
ELSE
|
||
|
|
circle_boundary := ST_Buffer(geom::geography, radius)::geometry;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN QUERY SELECT *
|
||
|
|
FROM cdb_observatory._OBS_GetBoundariesByGeometry(
|
||
|
|
circle_boundary,
|
||
|
|
boundary_id,
|
||
|
|
time_span,
|
||
|
|
overlap_type);
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- _OBS_GetPointsByGeometry
|
||
|
|
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory._OBS_GetPointsByGeometry(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL,
|
||
|
|
overlap_type text DEFAULT NULL)
|
||
|
|
RETURNS TABLE(the_geom geometry, geom_refs text)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
boundary geometry(Geometry, 4326);
|
||
|
|
geom_colname text;
|
||
|
|
geoid_colname text;
|
||
|
|
target_table text;
|
||
|
|
BEGIN
|
||
|
|
overlap_type := COALESCE(overlap_type, 'intersects');
|
||
|
|
|
||
|
|
IF lower(overlap_type) NOT IN ('contains', 'within', 'intersects')
|
||
|
|
THEN
|
||
|
|
RAISE EXCEPTION 'Overlap type ''%'' is not an accepted type (choose intersects, within, or contains)', overlap_type;
|
||
|
|
ELSIF ST_GeometryType(geom) NOT IN ('ST_Polygon', 'ST_MultiPolygon')
|
||
|
|
THEN
|
||
|
|
RAISE EXCEPTION 'Invalid geometry type (%), expecting ''ST_MultiPolygon'' or ''ST_Polygon''', ST_GeometryType(geom);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- return first boundary in intersections
|
||
|
|
RETURN QUERY EXECUTE $query$
|
||
|
|
SELECT ST_PointOnSurface(the_geom), geom_refs
|
||
|
|
FROM cdb_observatory._OBS_GetBoundariesByGeometry($1, $2)
|
||
|
|
$query$ USING geom, boundary_id;
|
||
|
|
RETURN;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- OBS_GetPointsByGeometry
|
||
|
|
--
|
||
|
|
-- Given a polygon, and it's geometry level (see
|
||
|
|
-- OBS_ListGeomColumns() for all available boundary ids), give back a point
|
||
|
|
-- which lies in a boundary from the requested geometry level that is contained
|
||
|
|
-- within the bounding box polygon and the associated geometry ids
|
||
|
|
--
|
||
|
|
-- Inputs:
|
||
|
|
-- geom geometry: bounding box (or polygon) of the region of interest
|
||
|
|
-- boundary_id text: source id of boundaries (e.g., us.census.tiger.county)
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- table with the following columns
|
||
|
|
-- boundary geometry: point that lies on a boundary that is contained within
|
||
|
|
-- the input bounding box at the requested geometry
|
||
|
|
-- level with boundary_id, and time_span
|
||
|
|
-- geom_refs text: geometry identifiers (e.g., geoid for the US Census)
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetPointsByGeometry(
|
||
|
|
geom geometry(Geometry, 4326),
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL,
|
||
|
|
overlap_type text DEFAULT NULL)
|
||
|
|
RETURNS TABLE(the_geom geometry, geom_refs text)
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
RETURN QUERY SELECT *
|
||
|
|
FROM cdb_observatory._OBS_GetPointsByGeometry(
|
||
|
|
geom,
|
||
|
|
boundary_id,
|
||
|
|
time_span,
|
||
|
|
overlap_type);
|
||
|
|
RETURN;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- OBS_GetBoundariesByPointAndRadius
|
||
|
|
--
|
||
|
|
-- Given a point and radius, and it's geometry level (see
|
||
|
|
-- OBS_ListGeomColumns() for all available boundary ids), give back the
|
||
|
|
-- boundaries that are contained within the point buffered by radius meters and
|
||
|
|
-- the associated geometry ids
|
||
|
|
|
||
|
|
-- Inputs:
|
||
|
|
-- geom geometry: point geometry centered on area of interest
|
||
|
|
-- radius numeric: radius (in meters) of a circle centered on geom for
|
||
|
|
-- selecting polygons
|
||
|
|
-- boundary_id text: source id of boundaries (e.g., us.census.tiger.county)
|
||
|
|
-- see function OBS_ListGeomColumns for all avaiable
|
||
|
|
-- boundary ids
|
||
|
|
-- time_span text: time span that the geometries were collected (optional)
|
||
|
|
--
|
||
|
|
-- Output:
|
||
|
|
-- table with the following columns
|
||
|
|
-- boundary geometry: geometry boundary that is contained within the input
|
||
|
|
-- bounding box at the requested geometry level
|
||
|
|
-- with boundary_id, and time_span
|
||
|
|
-- geom_refs text: geometry identifiers (e.g., geoid for the US Census)
|
||
|
|
--
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION cdb_observatory.OBS_GetPointsByPointAndRadius(
|
||
|
|
geom geometry(Point, 4326), -- point
|
||
|
|
radius numeric, -- radius in meters
|
||
|
|
boundary_id text,
|
||
|
|
time_span text DEFAULT NULL,
|
||
|
|
overlap_type text DEFAULT NULL)
|
||
|
|
RETURNS TABLE(the_geom geometry, geom_refs text)
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
circle_boundary geometry(Geometry, 4326);
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
IF ST_GeometryType(geom) != 'ST_Point'
|
||
|
|
THEN
|
||
|
|
RAISE EXCEPTION 'Input geometry ''%'' is not a point', ST_AsText(geom);
|
||
|
|
ELSE
|
||
|
|
circle_boundary := ST_Buffer(geom::geography, radius)::geometry;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN QUERY SELECT *
|
||
|
|
FROM cdb_observatory._OBS_GetPointsByGeometry(
|
||
|
|
ST_Buffer(geom::geography, radius)::geometry,
|
||
|
|
boundary_id,
|
||
|
|
time_span,
|
||
|
|
overlap_type);
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
-- Placeholder for permission tweaks at creation time.
|
||
|
|
-- Make sure by default there are no permissions for publicuser
|
||
|
|
-- NOTE: this happens at extension creation time, as part of an implicit transaction.
|
||
|
|
-- REVOKE ALL PRIVILEGES ON SCHEMA cdb_observatory FROM PUBLIC, publicuser CASCADE;
|
||
|
|
|
||
|
|
-- Grant permissions on the schema to publicuser (but just the schema)
|
||
|
|
-- GRANT USAGE ON SCHEMA cdb_crankshaft TO publicuser;
|
||
|
|
|
||
|
|
-- Revoke execute permissions on all functions in the schema by default
|
||
|
|
-- REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA cdb_observatory FROM PUBLIC, publicuser;
|