{"action":"created","comment":{"url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/comments/59236323","id":59236323,"diff_hunk":"@@ -0,0 +1,237 @@\n+\n+-- Returns a list of avaliable geometry columns\n+CREATE OR REPLACE FUNCTION OBS_ListGeomColumns()\n+  RETURNS TABLE(column_id text)\n+AS $$\n+  SELECT id FROM observatory.OBS_column WHERE type ILIKE 'geometry';\n+$$ LANGUAGE SQL IMMUTABLE;\n+\n+-- Returns the table name with geoms for the given geometry_id\n+-- TODO probably needs to take in the column_id array to get the relevant\n+-- table where there is multiple sources for a column from multiple\n+-- geometries.\n+CREATE OR REPLACE FUNCTION OBS_GeomTable(\n+  geom geometry,\n+  geometry_id text\n+)\n+  RETURNS TEXT\n+AS $$\n+DECLARE\n+  result text;\n+BEGIN\n+  EXECUTE '\n+    SELECT tablename FROM observatory.OBS_table\n+    WHERE id IN (\n+      SELECT table_id\n+      FROM observatory.OBS_table tab,\n+           observatory.OBS_column_table coltable,\n+           observatory.OBS_column col\n+      WHERE type ILIKE ''geometry''\n+        AND coltable.column_id = col.id\n+        AND coltable.table_id = tab.id\n+        AND col.id = $1\n+        AND ST_Intersects($2, ST_SetSRID(bounds::box2d::geometry, 4326))\n+    )\n+    '\n+  USING geometry_id, geom\n+  INTO result;\n+\n+  return result;\n+\n+END;\n+$$ LANGUAGE plpgsql;\n+\n+-- A type for use with the OBS_GetColumnData function\n+CREATE TYPE OBS_ColumnData AS (colname text, tablename text, aggregate text);\n+\n+-- A function that gets teh column data for a column_id, geometry_id and timespan.\n+-- Old: OBS_GetColumnData\n+CREATE OR REPLACE FUNCTION OBS_GetColumnData(\n+  geometry_id text,\n+  column_id text,\n+  timespan text\n+)\n+RETURNS OBS_ColumnData\n+AS $$\n+DECLARE\n+  result OBS_ColumnData;\n+BEGIN\n+  EXECUTE '\n+  WITH geomref AS (\n+    SELECT t.table_id id\n+    FROM observatory.OBS_column_to_column c2c, observatory.OBS_column_table t\n+    WHERE c2c.reltype = ''geom_ref''\n+      AND c2c.target_id = $1\n+      AND c2c.source_id = t.column_id\n+    )\n+ SELECT colname, tablename, aggregate\n+ FROM observatory.OBS_column c, observatory.OBS_column_table ct, observatory.OBS_table t\n+ WHERE c.id = ct.column_id\n+   AND t.id = ct.table_id\n+   AND c.id = $2\n+   AND t.timespan = $3\n+   AND t.id in (SELECT id FROM geomref)\n+ '\n+ USING geometry_id, column_id, timespan\n+ INTO result;\n+ RETURN result;\n+\n+END;\n+$$ LANGUAGE plpgsql;\n+\n+\n+-- A function that gets the column data for multiple columns\n+-- Old: OBS_GetColumnData\n+CREATE OR REPLACE FUNCTION OBS_GetColumnData(\n+  geometry_id text,\n+  column_ids text[],\n+  timespan text\n+)\n+RETURNS OBS_ColumnData[]\n+AS $$\n+DECLARE\n+  result OBS_ColumnData[];\n+BEGIN\n+\n+  EXECUTE '\n+  WITH geomref AS (\n+    SELECT t.table_id id\n+    FROM observatory.OBS_column_to_column c2c, observatory.OBS_column_table t\n+    WHERE c2c.reltype = ''geom_ref''\n+      AND c2c.target_id = $1\n+      AND c2c.source_id = t.column_id\n+    ),\n+  column_ids as (\n+    select row_number() over () as no, a.column_id as column_id from (select unnest($2) as column_id) a\n+  )\n+ SELECT array_agg(ROW(colname, tablename, aggregate)::OBS_ColumnData order by column_ids.no)\n+ FROM column_ids, observatory.OBS_column c, observatory.OBS_column_table ct, observatory.OBS_table t\n+ WHERE column_ids.column_id  = c.id\n+   AND c.id = ct.column_id\n+   AND t.id = ct.table_id\n+   AND t.timespan = $3\n+   AND t.id in (SELECT id FROM geomref)\n+ '\n+ USING geometry_id, column_ids, timespan\n+ INTO result;\n+ RETURN result;\n+\n+END;\n+$$ LANGUAGE plpgsql;\n+\n+--Gets the column id for a census variable given a human readable version of it\n+-- Old: OBS_LOOKUP_CENSUS_HUMAN\n+CREATE OR REPLACE FUNCTION OBS_LookupCensusHuman(\n+  column_name text,\n+  table_name text DEFAULT '\"us.census.acs\".extract_block_group_5yr_2013_69b156927c'","path":"src/pg/sql/40_observatory_utility.sql","position":126,"original_position":126,"commit_id":"94169e6874f2a79f2b6ed4d58ad0fd542a4ec64e","original_commit_id":"94169e6874f2a79f2b6ed4d58ad0fd542a4ec64e","user":{"login":"rafatower","id":1914276,"avatar_url":"https://avatars.githubusercontent.com/u/1914276?v=3","gravatar_id":"","url":"https://api.github.com/users/rafatower","html_url":"https://github.com/rafatower","followers_url":"https://api.github.com/users/rafatower/followers","following_url":"https://api.github.com/users/rafatower/following{/other_user}","gists_url":"https://api.github.com/users/rafatower/gists{/gist_id}","starred_url":"https://api.github.com/users/rafatower/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rafatower/subscriptions","organizations_url":"https://api.github.com/users/rafatower/orgs","repos_url":"https://api.github.com/users/rafatower/repos","events_url":"https://api.github.com/users/rafatower/events{/privacy}","received_events_url":"https://api.github.com/users/rafatower/received_events","type":"User","site_admin":false},"body":"Just curious: any special meaning hidden in the `69b156927c` portion of the name?\r\n\r\ncc/ @talos ","created_at":"2016-04-11T16:32:40Z","updated_at":"2016-04-11T16:32:40Z","html_url":"https://github.com/CartoDB/observatory-extension/pull/2#discussion_r59236323","pull_request_url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2","_links":{"self":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/comments/59236323"},"html":{"href":"https://github.com/CartoDB/observatory-extension/pull/2#discussion_r59236323"},"pull_request":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2"}}},"pull_request":{"url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2","id":66010827,"html_url":"https://github.com/CartoDB/observatory-extension/pull/2","diff_url":"https://github.com/CartoDB/observatory-extension/pull/2.diff","patch_url":"https://github.com/CartoDB/observatory-extension/pull/2.patch","issue_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues/2","number":2,"state":"open","locked":false,"title":"Data observatory functions","user":{"login":"rafatower","id":1914276,"avatar_url":"https://avatars.githubusercontent.com/u/1914276?v=3","gravatar_id":"","url":"https://api.github.com/users/rafatower","html_url":"https://github.com/rafatower","followers_url":"https://api.github.com/users/rafatower/followers","following_url":"https://api.github.com/users/rafatower/following{/other_user}","gists_url":"https://api.github.com/users/rafatower/gists{/gist_id}","starred_url":"https://api.github.com/users/rafatower/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rafatower/subscriptions","organizations_url":"https://api.github.com/users/rafatower/orgs","repos_url":"https://api.github.com/users/rafatower/repos","events_url":"https://api.github.com/users/rafatower/events{/privacy}","received_events_url":"https://api.github.com/users/rafatower/received_events","type":"User","site_admin":false},"body":"Just created this PR to give early feedback on the work being done in the branch and to make sure we're kind of aligned.","created_at":"2016-04-11T15:13:43Z","updated_at":"2016-04-11T16:32:40Z","closed_at":null,"merged_at":null,"merge_commit_sha":"7246f9144eef7de8ddbff51cb93d91377c08cd09","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2/commits","review_comments_url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2/comments","review_comment_url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/comments{/number}","comments_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues/2/comments","statuses_url":"https://api.github.com/repos/CartoDB/observatory-extension/statuses/94169e6874f2a79f2b6ed4d58ad0fd542a4ec64e","head":{"label":"CartoDB:data-observatory-functions","ref":"data-observatory-functions","sha":"94169e6874f2a79f2b6ed4d58ad0fd542a4ec64e","user":{"login":"CartoDB","id":1799254,"avatar_url":"https://avatars.githubusercontent.com/u/1799254?v=3","gravatar_id":"","url":"https://api.github.com/users/CartoDB","html_url":"https://github.com/CartoDB","followers_url":"https://api.github.com/users/CartoDB/followers","following_url":"https://api.github.com/users/CartoDB/following{/other_user}","gists_url":"https://api.github.com/users/CartoDB/gists{/gist_id}","starred_url":"https://api.github.com/users/CartoDB/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/CartoDB/subscriptions","organizations_url":"https://api.github.com/users/CartoDB/orgs","repos_url":"https://api.github.com/users/CartoDB/repos","events_url":"https://api.github.com/users/CartoDB/events{/privacy}","received_events_url":"https://api.github.com/users/CartoDB/received_events","type":"Organization","site_admin":false},"repo":{"id":55629943,"name":"observatory-extension","full_name":"CartoDB/observatory-extension","owner":{"login":"CartoDB","id":1799254,"avatar_url":"https://avatars.githubusercontent.com/u/1799254?v=3","gravatar_id":"","url":"https://api.github.com/users/CartoDB","html_url":"https://github.com/CartoDB","followers_url":"https://api.github.com/users/CartoDB/followers","following_url":"https://api.github.com/users/CartoDB/following{/other_user}","gists_url":"https://api.github.com/users/CartoDB/gists{/gist_id}","starred_url":"https://api.github.com/users/CartoDB/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/CartoDB/subscriptions","organizations_url":"https://api.github.com/users/CartoDB/orgs","repos_url":"https://api.github.com/users/CartoDB/repos","events_url":"https://api.github.com/users/CartoDB/events{/privacy}","received_events_url":"https://api.github.com/users/CartoDB/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/CartoDB/observatory-extension","description":"","fork":false,"url":"https://api.github.com/repos/CartoDB/observatory-extension","forks_url":"https://api.github.com/repos/CartoDB/observatory-extension/forks","keys_url":"https://api.github.com/repos/CartoDB/observatory-extension/keys{/key_id}","collaborators_url":"https://api.github.com/repos/CartoDB/observatory-extension/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/CartoDB/observatory-extension/teams","hooks_url":"https://api.github.com/repos/CartoDB/observatory-extension/hooks","issue_events_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues/events{/number}","events_url":"https://api.github.com/repos/CartoDB/observatory-extension/events","assignees_url":"https://api.github.com/repos/CartoDB/observatory-extension/assignees{/user}","branches_url":"https://api.github.com/repos/CartoDB/observatory-extension/branches{/branch}","tags_url":"https://api.github.com/repos/CartoDB/observatory-extension/tags","blobs_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/refs{/sha}","trees_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/trees{/sha}","statuses_url":"https://api.github.com/repos/CartoDB/observatory-extension/statuses/{sha}","languages_url":"https://api.github.com/repos/CartoDB/observatory-extension/languages","stargazers_url":"https://api.github.com/repos/CartoDB/observatory-extension/stargazers","contributors_url":"https://api.github.com/repos/CartoDB/observatory-extension/contributors","subscribers_url":"https://api.github.com/repos/CartoDB/observatory-extension/subscribers","subscription_url":"https://api.github.com/repos/CartoDB/observatory-extension/subscription","commits_url":"https://api.github.com/repos/CartoDB/observatory-extension/commits{/sha}","git_commits_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/commits{/sha}","comments_url":"https://api.github.com/repos/CartoDB/observatory-extension/comments{/number}","issue_comment_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues/comments{/number}","contents_url":"https://api.github.com/repos/CartoDB/observatory-extension/contents/{+path}","compare_url":"https://api.github.com/repos/CartoDB/observatory-extension/compare/{base}...{head}","merges_url":"https://api.github.com/repos/CartoDB/observatory-extension/merges","archive_url":"https://api.github.com/repos/CartoDB/observatory-extension/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/CartoDB/observatory-extension/downloads","issues_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues{/number}","pulls_url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls{/number}","milestones_url":"https://api.github.com/repos/CartoDB/observatory-extension/milestones{/number}","notifications_url":"https://api.github.com/repos/CartoDB/observatory-extension/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/CartoDB/observatory-extension/labels{/name}","releases_url":"https://api.github.com/repos/CartoDB/observatory-extension/releases{/id}","deployments_url":"https://api.github.com/repos/CartoDB/observatory-extension/deployments","created_at":"2016-04-06T18:15:39Z","updated_at":"2016-04-06T18:16:32Z","pushed_at":"2016-04-11T15:13:44Z","git_url":"git://github.com/CartoDB/observatory-extension.git","ssh_url":"git@github.com:CartoDB/observatory-extension.git","clone_url":"https://github.com/CartoDB/observatory-extension.git","svn_url":"https://github.com/CartoDB/observatory-extension","homepage":null,"size":38,"stargazers_count":0,"watchers_count":0,"language":"Makefile","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":2,"forks":0,"open_issues":2,"watchers":0,"default_branch":"develop"}},"base":{"label":"CartoDB:develop","ref":"develop","sha":"fba43cf3b50b88b7debad0f4d510df691de842ff","user":{"login":"CartoDB","id":1799254,"avatar_url":"https://avatars.githubusercontent.com/u/1799254?v=3","gravatar_id":"","url":"https://api.github.com/users/CartoDB","html_url":"https://github.com/CartoDB","followers_url":"https://api.github.com/users/CartoDB/followers","following_url":"https://api.github.com/users/CartoDB/following{/other_user}","gists_url":"https://api.github.com/users/CartoDB/gists{/gist_id}","starred_url":"https://api.github.com/users/CartoDB/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/CartoDB/subscriptions","organizations_url":"https://api.github.com/users/CartoDB/orgs","repos_url":"https://api.github.com/users/CartoDB/repos","events_url":"https://api.github.com/users/CartoDB/events{/privacy}","received_events_url":"https://api.github.com/users/CartoDB/received_events","type":"Organization","site_admin":false},"repo":{"id":55629943,"name":"observatory-extension","full_name":"CartoDB/observatory-extension","owner":{"login":"CartoDB","id":1799254,"avatar_url":"https://avatars.githubusercontent.com/u/1799254?v=3","gravatar_id":"","url":"https://api.github.com/users/CartoDB","html_url":"https://github.com/CartoDB","followers_url":"https://api.github.com/users/CartoDB/followers","following_url":"https://api.github.com/users/CartoDB/following{/other_user}","gists_url":"https://api.github.com/users/CartoDB/gists{/gist_id}","starred_url":"https://api.github.com/users/CartoDB/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/CartoDB/subscriptions","organizations_url":"https://api.github.com/users/CartoDB/orgs","repos_url":"https://api.github.com/users/CartoDB/repos","events_url":"https://api.github.com/users/CartoDB/events{/privacy}","received_events_url":"https://api.github.com/users/CartoDB/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/CartoDB/observatory-extension","description":"","fork":false,"url":"https://api.github.com/repos/CartoDB/observatory-extension","forks_url":"https://api.github.com/repos/CartoDB/observatory-extension/forks","keys_url":"https://api.github.com/repos/CartoDB/observatory-extension/keys{/key_id}","collaborators_url":"https://api.github.com/repos/CartoDB/observatory-extension/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/CartoDB/observatory-extension/teams","hooks_url":"https://api.github.com/repos/CartoDB/observatory-extension/hooks","issue_events_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues/events{/number}","events_url":"https://api.github.com/repos/CartoDB/observatory-extension/events","assignees_url":"https://api.github.com/repos/CartoDB/observatory-extension/assignees{/user}","branches_url":"https://api.github.com/repos/CartoDB/observatory-extension/branches{/branch}","tags_url":"https://api.github.com/repos/CartoDB/observatory-extension/tags","blobs_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/refs{/sha}","trees_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/trees{/sha}","statuses_url":"https://api.github.com/repos/CartoDB/observatory-extension/statuses/{sha}","languages_url":"https://api.github.com/repos/CartoDB/observatory-extension/languages","stargazers_url":"https://api.github.com/repos/CartoDB/observatory-extension/stargazers","contributors_url":"https://api.github.com/repos/CartoDB/observatory-extension/contributors","subscribers_url":"https://api.github.com/repos/CartoDB/observatory-extension/subscribers","subscription_url":"https://api.github.com/repos/CartoDB/observatory-extension/subscription","commits_url":"https://api.github.com/repos/CartoDB/observatory-extension/commits{/sha}","git_commits_url":"https://api.github.com/repos/CartoDB/observatory-extension/git/commits{/sha}","comments_url":"https://api.github.com/repos/CartoDB/observatory-extension/comments{/number}","issue_comment_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues/comments{/number}","contents_url":"https://api.github.com/repos/CartoDB/observatory-extension/contents/{+path}","compare_url":"https://api.github.com/repos/CartoDB/observatory-extension/compare/{base}...{head}","merges_url":"https://api.github.com/repos/CartoDB/observatory-extension/merges","archive_url":"https://api.github.com/repos/CartoDB/observatory-extension/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/CartoDB/observatory-extension/downloads","issues_url":"https://api.github.com/repos/CartoDB/observatory-extension/issues{/number}","pulls_url":"https://api.github.com/repos/CartoDB/observatory-extension/pulls{/number}","milestones_url":"https://api.github.com/repos/CartoDB/observatory-extension/milestones{/number}","notifications_url":"https://api.github.com/repos/CartoDB/observatory-extension/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/CartoDB/observatory-extension/labels{/name}","releases_url":"https://api.github.com/repos/CartoDB/observatory-extension/releases{/id}","deployments_url":"https://api.github.com/repos/CartoDB/observatory-extension/deployments","created_at":"2016-04-06T18:15:39Z","updated_at":"2016-04-06T18:16:32Z","pushed_at":"2016-04-11T15:13:44Z","git_url":"git://github.com/CartoDB/observatory-extension.git","ssh_url":"git@github.com:CartoDB/observatory-extension.git","clone_url":"https://github.com/CartoDB/observatory-extension.git","svn_url":"https://github.com/CartoDB/observatory-extension","homepage":null,"size":38,"stargazers_count":0,"watchers_count":0,"language":"Makefile","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":2,"forks":0,"open_issues":2,"watchers":0,"default_branch":"develop"}},"_links":{"self":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2"},"html":{"href":"https://github.com/CartoDB/observatory-extension/pull/2"},"issue":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/issues/2"},"comments":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/issues/2/comments"},"review_comments":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2/comments"},"review_comment":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/pulls/2/commits"},"statuses":{"href":"https://api.github.com/repos/CartoDB/observatory-extension/statuses/94169e6874f2a79f2b6ed4d58ad0fd542a4ec64e"}}}}