{"action":"created","comment":{"url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/comments/29117722","id":29117722,"diff_hunk":"@@ -0,0 +1,410 @@\n+\n+SET SEARCH_PATH = marvl3, public;\n+\t\n+----AUV\n+\\echo 'AUV'\n+INSERT INTO marvl3.spatial_subset(\n+source_id,\n+origin_id,\n+\"LONGITUDE\",\n+\"LATITUDE\",\n+\"TIME\",\n+\"DEPTH\",\n+\"TEMP\",\n+\"PSAL\",\n+geom\n+)\n+SELECT \n+s.source_id,\n+d.file_id,\n+d.\"LONGITUDE\",\n+d.\"LATITUDE\",\n+d.\"TIME\",\n+d.\"DEPTH\",\n+d.\"TEMP\",\n+d.\"PSAL\",\n+d.geom\n+FROM auv.auv_trajectory_st_data d,  \"500m_isobath\" p,source s\n+WHERE ST_CONTAINS(p.geom, d.geom)\n+AND s.table_name= 'auv_trajectory_st_data' \n+AND d.\"TIME\" >= '1995-01-01' \n+AND d.\"TIME\" < '2015-01-01'\n+\t\n+---SOOP-CO2\t\n+\\echo 'SOOP-CO2'\n+INSERT INTO marvl3.spatial_subset(\n+source_id,\n+origin_id,\n+\"LONGITUDE\",\n+\"LONGITUDE_QC\",\n+\"LATITUDE\",\n+\"LATITUDE_QC\",\n+\"TIME\",\n+\"TIME_QC\",\n+\"DEPTH\",\n+\"TEMP\",\n+\"TEMP_QC\",\n+\"PSAL\",\n+\"PSAL_QC\",\n+geom\n+)\n+SELECT \n+s.source_id,\n+d.cruise_id,\n+d.\"LONGITUDE\",\n+d.\"LONGITUDE_quality_control\",\n+d.\"LATITUDE\",\n+d.\"LATITUDE_quality_control\",\n+d.\"TIME\",\n+d.\"TIME_quality_control\",\n+0,\n+d.\"TEMP_1\",\n+d.\"TEMP_1_quality_control\",\n+d.\"PSAL\",\n+d.\"PSAL_quality_control\",\n+d.geom\n+FROM soop_co2.soop_co2_trajectory_data d, \"500m_isobath\" p,source s\n+WHERE ST_CONTAINS(p.geom, d.geom) \n+AND s.table_name='soop_co2_trajectory_data' \n+AND d.\"TIME\" >= '1995-01-01' \n+AND d.\"TIME\" < '2015-01-01'\n+AND vessel_name IN('L''Astrolabe', 'Aurora Australis')\t   \n+\n+DELETE \"TEMP\" FROM marvl3.spatial_subset WHERE source_id=19 -- delete Temperature data from SOOP-CO2 will be extracted from other schemas\n+ \t\n+\n+\n+---SOOP-TRV\n+\\echo 'SOOP-TRV'\n+INSERT INTO marvl3.spatial_subset(\n+source_id,\n+origin_id,\n+\"LONGITUDE\",\n+\"LATITUDE\",\n+\"TIME\",\n+\"DEPTH\",\n+\"TEMP\",\n+\"PSAL\",\n+geom\n+) \n+SELECT \n+s.source_id,\n+d.trip_id,\n+d.\"LONGITUDE\",\n+d.\"LATITUDE\",\n+d.\"TIME\",\n+0,\n+d.\"Seawater_Intake_Temperature\",\n+d.\"PSAL\",\n+d.geom\n+FROM soop_trv.soop_trv_trajectory_data d, \"500m_isobath\" p,source s\n+WHERE ST_CONTAINS(p.geom, soop_trv.soop_trv_trajectory_data.geom) \n+AND s.table_name='soop_trv_trajectory_data' \n+AND d.\"TIME\" >= '1995-01-01' \n+AND d.\"TIME\" < '2015-01-01'\n+ \n+\t\n+---SOOP-SST DM\t\n+\\echo 'SOOP-SST DM'\n+INSERT INTO marvl3.spatial_subset(\n+source_id,\n+origin_id,\n+\"LONGITUDE\",\n+\"LONGITUDE_QC\",\n+\"LATITUDE\",\n+\"LATITUDE_QC\",\n+\"TIME\",\n+\"TIME_QC\",\n+\"DEPTH\",\n+\"TEMP\",\n+\"TEMP_QC\",\n+geom\n+)\t\n+SELECT \n+s.source_id,\n+d.trajectory_id,\n+d.\"LONGITUDE\",\n+d.\"LONGITUDE_quality_control\",\n+d.\"LATITUDE\",\n+d.\"LATITUDE_quality_control\",\n+d.\"TIME\",\n+d.\"TIME_quality_control\",\n+0,\n+d.\"TEMP\",\n+d.\"TEMP_quality_control\",\n+d.geom\n+FROM  soop_sst.soop_sst_dm_trajectory_data d, \"500m_isobath\" p,source s\n+WHERE ST_CONTAINS(p.geom, d.geom)\n+AND s.table_name='soop_sst_dm_trajectory_data'\n+AND d.\"TIME\" >= '1995-01-01' \n+AND d.\"TIME\" < '2015-01-01'\t\n+\t\n+---SOOP-SST NRT\n+\\echo 'SOOP-SST NRT'\n+INSERT INTO marvl3.spatial_subset(\n+source_id,\n+origin_id,\n+\"LONGITUDE\",\n+\"LONGITUDE_QC\",\n+\"LATITUDE\",\n+\"LATITUDE_QC\",\n+\"TIME\",\n+\"TIME_QC\",\n+\"DEPTH\",\n+\"TEMP\",\n+\"TEMP_QC\",\n+geom\n+)\n+SELECT source_id,\n+d.trajectory_id,\n+d.\"LONGITUDE\",\n+d.\"LONGITUDE_quality_control\",\n+d.\"LATITUDE\",\n+d.\"LATITUDE_quality_control\",\n+d.\"TIME\",\n+d.\"TIME_quality_control\",\n+0,\n+d.\"TEMP\",\n+d.\"TEMP_quality_control\",\n+d.geom\n+FROM soop_sst.soop_sst_nrt_trajectory_data d, \"500m_isobath\" p,source s\n+WHERE ST_CONTAINS(p.geom, d.geom) \n+AND s.table_name='soop_sst_nrt_trajectory_data'\n+AND d.\"TIME\" >= '1995-01-01' \n+AND d.\"TIME\" < '2015-01-01'\n+AND vessel_name NOT IN('Xutra Bhum', 'Wana Bhum','RV Cape Ferguson')\t--'Xutra Bhum', 'Wana Bhum' inSOOP-SST DM; 'RV Cape Ferguson' in SOOP-TRV\n+\n+DELETE from marvl3.spatial_subset m where \n+m.pkid IN (select trajectory_id \n+from soop_sst.soop_sst_nrt_trajectory_data d \n+join marvl3.spatial_subset m on d.trajectory_id::character =m.origin_id\n+where vessel_name IN ('L''Astrolabe') AND d.TIME <'2013-04-15- 00:00:00'\n+) -- Astrolabe SOOP-SST-NRT data processed to DM product only up to Apr 2013  \n+---SOOP_TMV\n+\\echo 'SOOP_TMV'\t\n+INSERT INTO marvl3.spatial_subset(","path":"sql_queries/PopulateSpatialSubset_TRAJECTORY.sql","position":185,"original_position":185,"commit_id":"6af2ed1cd3a8b00dbcf8be60082c34353a8d9375","original_commit_id":"6af2ed1cd3a8b00dbcf8be60082c34353a8d9375","user":{"login":"ggalibert","id":3498686,"avatar_url":"https://avatars.githubusercontent.com/u/3498686?v=3","gravatar_id":"","url":"https://api.github.com/users/ggalibert","html_url":"https://github.com/ggalibert","followers_url":"https://api.github.com/users/ggalibert/followers","following_url":"https://api.github.com/users/ggalibert/following{/other_user}","gists_url":"https://api.github.com/users/ggalibert/gists{/gist_id}","starred_url":"https://api.github.com/users/ggalibert/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ggalibert/subscriptions","organizations_url":"https://api.github.com/users/ggalibert/orgs","repos_url":"https://api.github.com/users/ggalibert/repos","events_url":"https://api.github.com/users/ggalibert/events{/privacy}","received_events_url":"https://api.github.com/users/ggalibert/received_events","type":"User","site_admin":false},"body":"Already addressed in master. Please compare and if all good delete this.","created_at":"2015-04-27T02:09:47Z","updated_at":"2015-04-27T02:09:47Z","html_url":"https://github.com/aodn/marvl-data-atlas/pull/4#discussion_r29117722","pull_request_url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4","_links":{"self":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/comments/29117722"},"html":{"href":"https://github.com/aodn/marvl-data-atlas/pull/4#discussion_r29117722"},"pull_request":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4"}}},"pull_request":{"url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4","id":31093838,"html_url":"https://github.com/aodn/marvl-data-atlas/pull/4","diff_url":"https://github.com/aodn/marvl-data-atlas/pull/4.diff","patch_url":"https://github.com/aodn/marvl-data-atlas/pull/4.patch","issue_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/4","number":4,"state":"open","locked":false,"title":"Add queries to populate spatial_subset table with IMOS and CSIRO traject...","user":{"login":"bpasquer","id":3733612,"avatar_url":"https://avatars.githubusercontent.com/u/3733612?v=3","gravatar_id":"","url":"https://api.github.com/users/bpasquer","html_url":"https://github.com/bpasquer","followers_url":"https://api.github.com/users/bpasquer/followers","following_url":"https://api.github.com/users/bpasquer/following{/other_user}","gists_url":"https://api.github.com/users/bpasquer/gists{/gist_id}","starred_url":"https://api.github.com/users/bpasquer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bpasquer/subscriptions","organizations_url":"https://api.github.com/users/bpasquer/orgs","repos_url":"https://api.github.com/users/bpasquer/repos","events_url":"https://api.github.com/users/bpasquer/events{/privacy}","received_events_url":"https://api.github.com/users/bpasquer/received_events","type":"User","site_admin":false},"body":"...ory data","created_at":"2015-03-13T01:38:45Z","updated_at":"2015-04-27T02:09:47Z","closed_at":null,"merged_at":null,"merge_commit_sha":"69c811a2fb1a67b738032ac3fa6036b3f83cfe14","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4/commits","review_comments_url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4/comments","review_comment_url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/comments{/number}","comments_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/4/comments","statuses_url":"https://api.github.com/repos/aodn/marvl-data-atlas/statuses/6af2ed1cd3a8b00dbcf8be60082c34353a8d9375","head":{"label":"aodn:benes_branch","ref":"benes_branch","sha":"6af2ed1cd3a8b00dbcf8be60082c34353a8d9375","user":{"login":"aodn","id":2802166,"avatar_url":"https://avatars.githubusercontent.com/u/2802166?v=3","gravatar_id":"","url":"https://api.github.com/users/aodn","html_url":"https://github.com/aodn","followers_url":"https://api.github.com/users/aodn/followers","following_url":"https://api.github.com/users/aodn/following{/other_user}","gists_url":"https://api.github.com/users/aodn/gists{/gist_id}","starred_url":"https://api.github.com/users/aodn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aodn/subscriptions","organizations_url":"https://api.github.com/users/aodn/orgs","repos_url":"https://api.github.com/users/aodn/repos","events_url":"https://api.github.com/users/aodn/events{/privacy}","received_events_url":"https://api.github.com/users/aodn/received_events","type":"Organization","site_admin":false},"repo":{"id":31052850,"name":"marvl-data-atlas","full_name":"aodn/marvl-data-atlas","owner":{"login":"aodn","id":2802166,"avatar_url":"https://avatars.githubusercontent.com/u/2802166?v=3","gravatar_id":"","url":"https://api.github.com/users/aodn","html_url":"https://github.com/aodn","followers_url":"https://api.github.com/users/aodn/followers","following_url":"https://api.github.com/users/aodn/following{/other_user}","gists_url":"https://api.github.com/users/aodn/gists{/gist_id}","starred_url":"https://api.github.com/users/aodn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aodn/subscriptions","organizations_url":"https://api.github.com/users/aodn/orgs","repos_url":"https://api.github.com/users/aodn/repos","events_url":"https://api.github.com/users/aodn/events{/privacy}","received_events_url":"https://api.github.com/users/aodn/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/aodn/marvl-data-atlas","description":"Source code which defines the \"Marvl Data Atlas\" database.","fork":false,"url":"https://api.github.com/repos/aodn/marvl-data-atlas","forks_url":"https://api.github.com/repos/aodn/marvl-data-atlas/forks","keys_url":"https://api.github.com/repos/aodn/marvl-data-atlas/keys{/key_id}","collaborators_url":"https://api.github.com/repos/aodn/marvl-data-atlas/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/aodn/marvl-data-atlas/teams","hooks_url":"https://api.github.com/repos/aodn/marvl-data-atlas/hooks","issue_events_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/events{/number}","events_url":"https://api.github.com/repos/aodn/marvl-data-atlas/events","assignees_url":"https://api.github.com/repos/aodn/marvl-data-atlas/assignees{/user}","branches_url":"https://api.github.com/repos/aodn/marvl-data-atlas/branches{/branch}","tags_url":"https://api.github.com/repos/aodn/marvl-data-atlas/tags","blobs_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/refs{/sha}","trees_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/trees{/sha}","statuses_url":"https://api.github.com/repos/aodn/marvl-data-atlas/statuses/{sha}","languages_url":"https://api.github.com/repos/aodn/marvl-data-atlas/languages","stargazers_url":"https://api.github.com/repos/aodn/marvl-data-atlas/stargazers","contributors_url":"https://api.github.com/repos/aodn/marvl-data-atlas/contributors","subscribers_url":"https://api.github.com/repos/aodn/marvl-data-atlas/subscribers","subscription_url":"https://api.github.com/repos/aodn/marvl-data-atlas/subscription","commits_url":"https://api.github.com/repos/aodn/marvl-data-atlas/commits{/sha}","git_commits_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/commits{/sha}","comments_url":"https://api.github.com/repos/aodn/marvl-data-atlas/comments{/number}","issue_comment_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/comments{/number}","contents_url":"https://api.github.com/repos/aodn/marvl-data-atlas/contents/{+path}","compare_url":"https://api.github.com/repos/aodn/marvl-data-atlas/compare/{base}...{head}","merges_url":"https://api.github.com/repos/aodn/marvl-data-atlas/merges","archive_url":"https://api.github.com/repos/aodn/marvl-data-atlas/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/aodn/marvl-data-atlas/downloads","issues_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues{/number}","pulls_url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls{/number}","milestones_url":"https://api.github.com/repos/aodn/marvl-data-atlas/milestones{/number}","notifications_url":"https://api.github.com/repos/aodn/marvl-data-atlas/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/aodn/marvl-data-atlas/labels{/name}","releases_url":"https://api.github.com/repos/aodn/marvl-data-atlas/releases{/id}","created_at":"2015-02-20T05:53:09Z","updated_at":"2015-04-26T23:25:36Z","pushed_at":"2015-04-26T23:25:36Z","git_url":"git://github.com/aodn/marvl-data-atlas.git","ssh_url":"git@github.com:aodn/marvl-data-atlas.git","clone_url":"https://github.com/aodn/marvl-data-atlas.git","svn_url":"https://github.com/aodn/marvl-data-atlas","homepage":null,"size":484,"stargazers_count":0,"watchers_count":0,"language":"PLpgSQL","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":"master"}},"base":{"label":"aodn:master","ref":"master","sha":"c21282a02afbfd59eaa9d2a0cb5828ee87a2745c","user":{"login":"aodn","id":2802166,"avatar_url":"https://avatars.githubusercontent.com/u/2802166?v=3","gravatar_id":"","url":"https://api.github.com/users/aodn","html_url":"https://github.com/aodn","followers_url":"https://api.github.com/users/aodn/followers","following_url":"https://api.github.com/users/aodn/following{/other_user}","gists_url":"https://api.github.com/users/aodn/gists{/gist_id}","starred_url":"https://api.github.com/users/aodn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aodn/subscriptions","organizations_url":"https://api.github.com/users/aodn/orgs","repos_url":"https://api.github.com/users/aodn/repos","events_url":"https://api.github.com/users/aodn/events{/privacy}","received_events_url":"https://api.github.com/users/aodn/received_events","type":"Organization","site_admin":false},"repo":{"id":31052850,"name":"marvl-data-atlas","full_name":"aodn/marvl-data-atlas","owner":{"login":"aodn","id":2802166,"avatar_url":"https://avatars.githubusercontent.com/u/2802166?v=3","gravatar_id":"","url":"https://api.github.com/users/aodn","html_url":"https://github.com/aodn","followers_url":"https://api.github.com/users/aodn/followers","following_url":"https://api.github.com/users/aodn/following{/other_user}","gists_url":"https://api.github.com/users/aodn/gists{/gist_id}","starred_url":"https://api.github.com/users/aodn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aodn/subscriptions","organizations_url":"https://api.github.com/users/aodn/orgs","repos_url":"https://api.github.com/users/aodn/repos","events_url":"https://api.github.com/users/aodn/events{/privacy}","received_events_url":"https://api.github.com/users/aodn/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/aodn/marvl-data-atlas","description":"Source code which defines the \"Marvl Data Atlas\" database.","fork":false,"url":"https://api.github.com/repos/aodn/marvl-data-atlas","forks_url":"https://api.github.com/repos/aodn/marvl-data-atlas/forks","keys_url":"https://api.github.com/repos/aodn/marvl-data-atlas/keys{/key_id}","collaborators_url":"https://api.github.com/repos/aodn/marvl-data-atlas/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/aodn/marvl-data-atlas/teams","hooks_url":"https://api.github.com/repos/aodn/marvl-data-atlas/hooks","issue_events_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/events{/number}","events_url":"https://api.github.com/repos/aodn/marvl-data-atlas/events","assignees_url":"https://api.github.com/repos/aodn/marvl-data-atlas/assignees{/user}","branches_url":"https://api.github.com/repos/aodn/marvl-data-atlas/branches{/branch}","tags_url":"https://api.github.com/repos/aodn/marvl-data-atlas/tags","blobs_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/refs{/sha}","trees_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/trees{/sha}","statuses_url":"https://api.github.com/repos/aodn/marvl-data-atlas/statuses/{sha}","languages_url":"https://api.github.com/repos/aodn/marvl-data-atlas/languages","stargazers_url":"https://api.github.com/repos/aodn/marvl-data-atlas/stargazers","contributors_url":"https://api.github.com/repos/aodn/marvl-data-atlas/contributors","subscribers_url":"https://api.github.com/repos/aodn/marvl-data-atlas/subscribers","subscription_url":"https://api.github.com/repos/aodn/marvl-data-atlas/subscription","commits_url":"https://api.github.com/repos/aodn/marvl-data-atlas/commits{/sha}","git_commits_url":"https://api.github.com/repos/aodn/marvl-data-atlas/git/commits{/sha}","comments_url":"https://api.github.com/repos/aodn/marvl-data-atlas/comments{/number}","issue_comment_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/comments{/number}","contents_url":"https://api.github.com/repos/aodn/marvl-data-atlas/contents/{+path}","compare_url":"https://api.github.com/repos/aodn/marvl-data-atlas/compare/{base}...{head}","merges_url":"https://api.github.com/repos/aodn/marvl-data-atlas/merges","archive_url":"https://api.github.com/repos/aodn/marvl-data-atlas/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/aodn/marvl-data-atlas/downloads","issues_url":"https://api.github.com/repos/aodn/marvl-data-atlas/issues{/number}","pulls_url":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls{/number}","milestones_url":"https://api.github.com/repos/aodn/marvl-data-atlas/milestones{/number}","notifications_url":"https://api.github.com/repos/aodn/marvl-data-atlas/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/aodn/marvl-data-atlas/labels{/name}","releases_url":"https://api.github.com/repos/aodn/marvl-data-atlas/releases{/id}","created_at":"2015-02-20T05:53:09Z","updated_at":"2015-04-26T23:25:36Z","pushed_at":"2015-04-26T23:25:36Z","git_url":"git://github.com/aodn/marvl-data-atlas.git","ssh_url":"git@github.com:aodn/marvl-data-atlas.git","clone_url":"https://github.com/aodn/marvl-data-atlas.git","svn_url":"https://github.com/aodn/marvl-data-atlas","homepage":null,"size":484,"stargazers_count":0,"watchers_count":0,"language":"PLpgSQL","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":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4"},"html":{"href":"https://github.com/aodn/marvl-data-atlas/pull/4"},"issue":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/4"},"comments":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/issues/4/comments"},"review_comments":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4/comments"},"review_comment":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/pulls/4/commits"},"statuses":{"href":"https://api.github.com/repos/aodn/marvl-data-atlas/statuses/6af2ed1cd3a8b00dbcf8be60082c34353a8d9375"}}}}