{"action":"created","comment":{"url":"https://api.github.com/repos/agrc/ugs-db/pulls/comments/38882467","id":38882467,"diff_hunk":"@@ -87,28 +92,47 @@ def post_process(self, who):\n         print('joining to layer')\n         arcpy.AddJoin_management(stationsLyr, 'Id', stationsIdent, 'FID_' + stations_fc)\n \n-        print('calculating fields')\n+        print('calculating state')\n         arcpy.CalculateField_management(stationsLyr, 'StateCode', '!STATE_FIPS!', 'PYTHON')\n+        print('calculating county')\n         arcpy.CalculateField_management(stationsLyr, 'CountyCode', '!FIPS!', 'PYTHON')\n \n         print('removing join')\n         arcpy.RemoveJoin_management(stationsLyr, stations_identity)\n \n         #: Elevation\n-        print('selecting points with null values')\n-        arcpy.SelectLayerByAttribute_management(stationsLyr, where_clause='Elev IS NULL')\n-\n-        print('extracting values to points')\n-        arcpy.CheckOutExtension('Spatial')\n-        dem_points = arcpy.sa.ExtractValuesToPoints(stationsLyr, dem, r'in_memory\\DEMPoints', add_attributes='VALUE_ONLY')\n-\n-        print('joining to layer')\n-        arcpy.AddJoin_management(stationsLyr, 'StationId', dem_points, 'StationId')\n-\n-        print('calulcating fields')\n-        arcpy.CalculateField_management(stationsLyr, 'Elev', '!RASTERVALU!', 'PYTHON')\n-        arcpy.CalculateField_management(stationsLyr, 'ElevUnit', '\"meters\"')\n-        arcpy.CalculateField_management(stationsLyr, 'ElevMeth', '\"Other\"')\n+        print('looping through points with null elevation values')\n+        connection = pyodbc.connect(self._get_db(who)['connection_string'])\n+        cursor = connection.cursor()\n+        cursor.execute('''\n+            SELECT Lon_X, Lat_Y, Id\n+            FROM Stations\n+            WHERE Elev IS NULL OR Elev = 0 OR Elev > 20000\n+        ''')\n+        i = 0\n+        batch_size = 100\n+        rows = cursor.fetchall()\n+        total = len(rows)\n+        for row in rows:\n+            payload = {'x': row.Lon_X, 'y': row.Lat_Y, 'units': 'Meters', 'output': 'json'}\n+            r = requests.get(epqs_service_url, params=payload)\n+            try:\n+                elev = r.json()['USGS_Elevation_Point_Query_Service']['Elevation_Query']['Elevation']\n+            except:\n+                print('error retrieving elevation for Lon: {} & Lat: {}. Skipping'.format(row.Lon_X, row.Lat_Y))\n+                continue\n+\n+            unit = 'meters'\n+            method = 'Other'\n+            cursor.execute('''","path":"src/dbseeder/dbseeder.py","position":75,"original_position":75,"commit_id":"97873070ca432beed2b9cc5469ad86c0a2f378bb","original_commit_id":"97873070ca432beed2b9cc5469ad86c0a2f378bb","user":{"login":"steveoh","id":325813,"avatar_url":"https://avatars.githubusercontent.com/u/325813?v=3","gravatar_id":"","url":"https://api.github.com/users/steveoh","html_url":"https://github.com/steveoh","followers_url":"https://api.github.com/users/steveoh/followers","following_url":"https://api.github.com/users/steveoh/following{/other_user}","gists_url":"https://api.github.com/users/steveoh/gists{/gist_id}","starred_url":"https://api.github.com/users/steveoh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/steveoh/subscriptions","organizations_url":"https://api.github.com/users/steveoh/orgs","repos_url":"https://api.github.com/users/steveoh/repos","events_url":"https://api.github.com/users/steveoh/events{/privacy}","received_events_url":"https://api.github.com/users/steveoh/received_events","type":"User","site_admin":false},"body":"this should use the [pyodbc params](https://code.google.com/p/pyodbc/wiki/Cursor#execute) instead of string formatting. ","created_at":"2015-09-07T23:43:08Z","updated_at":"2015-09-07T23:43:08Z","html_url":"https://github.com/agrc/ugs-db/pull/28#discussion_r38882467","pull_request_url":"https://api.github.com/repos/agrc/ugs-db/pulls/28","_links":{"self":{"href":"https://api.github.com/repos/agrc/ugs-db/pulls/comments/38882467"},"html":{"href":"https://github.com/agrc/ugs-db/pull/28#discussion_r38882467"},"pull_request":{"href":"https://api.github.com/repos/agrc/ugs-db/pulls/28"}}},"pull_request":{"url":"https://api.github.com/repos/agrc/ugs-db/pulls/28","id":44162838,"html_url":"https://github.com/agrc/ugs-db/pull/28","diff_url":"https://github.com/agrc/ugs-db/pull/28.diff","patch_url":"https://github.com/agrc/ugs-db/pull/28.patch","issue_url":"https://api.github.com/repos/agrc/ugs-db/issues/28","number":28,"state":"open","locked":false,"title":"Switch to use the epqs elevation service and pyodbc","user":{"login":"stdavis","id":1326248,"avatar_url":"https://avatars.githubusercontent.com/u/1326248?v=3","gravatar_id":"","url":"https://api.github.com/users/stdavis","html_url":"https://github.com/stdavis","followers_url":"https://api.github.com/users/stdavis/followers","following_url":"https://api.github.com/users/stdavis/following{/other_user}","gists_url":"https://api.github.com/users/stdavis/gists{/gist_id}","starred_url":"https://api.github.com/users/stdavis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/stdavis/subscriptions","organizations_url":"https://api.github.com/users/stdavis/orgs","repos_url":"https://api.github.com/users/stdavis/repos","events_url":"https://api.github.com/users/stdavis/events{/privacy}","received_events_url":"https://api.github.com/users/stdavis/received_events","type":"User","site_admin":false},"body":"Closes #3\r\nRelated to #17","created_at":"2015-09-03T21:59:33Z","updated_at":"2015-09-07T23:43:08Z","closed_at":null,"merged_at":null,"merge_commit_sha":"6faa1f3e26d2248cb5ab2f7af19a71716aa2cd87","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/agrc/ugs-db/pulls/28/commits","review_comments_url":"https://api.github.com/repos/agrc/ugs-db/pulls/28/comments","review_comment_url":"https://api.github.com/repos/agrc/ugs-db/pulls/comments{/number}","comments_url":"https://api.github.com/repos/agrc/ugs-db/issues/28/comments","statuses_url":"https://api.github.com/repos/agrc/ugs-db/statuses/97873070ca432beed2b9cc5469ad86c0a2f378bb","head":{"label":"agrc:elevation-service","ref":"elevation-service","sha":"97873070ca432beed2b9cc5469ad86c0a2f378bb","user":{"login":"agrc","id":709415,"avatar_url":"https://avatars.githubusercontent.com/u/709415?v=3","gravatar_id":"","url":"https://api.github.com/users/agrc","html_url":"https://github.com/agrc","followers_url":"https://api.github.com/users/agrc/followers","following_url":"https://api.github.com/users/agrc/following{/other_user}","gists_url":"https://api.github.com/users/agrc/gists{/gist_id}","starred_url":"https://api.github.com/users/agrc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agrc/subscriptions","organizations_url":"https://api.github.com/users/agrc/orgs","repos_url":"https://api.github.com/users/agrc/repos","events_url":"https://api.github.com/users/agrc/events{/privacy}","received_events_url":"https://api.github.com/users/agrc/received_events","type":"Organization","site_admin":false},"repo":{"id":28191086,"name":"ugs-db","full_name":"agrc/ugs-db","owner":{"login":"agrc","id":709415,"avatar_url":"https://avatars.githubusercontent.com/u/709415?v=3","gravatar_id":"","url":"https://api.github.com/users/agrc","html_url":"https://github.com/agrc","followers_url":"https://api.github.com/users/agrc/followers","following_url":"https://api.github.com/users/agrc/following{/other_user}","gists_url":"https://api.github.com/users/agrc/gists{/gist_id}","starred_url":"https://api.github.com/users/agrc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agrc/subscriptions","organizations_url":"https://api.github.com/users/agrc/orgs","repos_url":"https://api.github.com/users/agrc/repos","events_url":"https://api.github.com/users/agrc/events{/privacy}","received_events_url":"https://api.github.com/users/agrc/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/agrc/ugs-db","description":"Database seeder for UGS data","fork":false,"url":"https://api.github.com/repos/agrc/ugs-db","forks_url":"https://api.github.com/repos/agrc/ugs-db/forks","keys_url":"https://api.github.com/repos/agrc/ugs-db/keys{/key_id}","collaborators_url":"https://api.github.com/repos/agrc/ugs-db/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/agrc/ugs-db/teams","hooks_url":"https://api.github.com/repos/agrc/ugs-db/hooks","issue_events_url":"https://api.github.com/repos/agrc/ugs-db/issues/events{/number}","events_url":"https://api.github.com/repos/agrc/ugs-db/events","assignees_url":"https://api.github.com/repos/agrc/ugs-db/assignees{/user}","branches_url":"https://api.github.com/repos/agrc/ugs-db/branches{/branch}","tags_url":"https://api.github.com/repos/agrc/ugs-db/tags","blobs_url":"https://api.github.com/repos/agrc/ugs-db/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/agrc/ugs-db/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/agrc/ugs-db/git/refs{/sha}","trees_url":"https://api.github.com/repos/agrc/ugs-db/git/trees{/sha}","statuses_url":"https://api.github.com/repos/agrc/ugs-db/statuses/{sha}","languages_url":"https://api.github.com/repos/agrc/ugs-db/languages","stargazers_url":"https://api.github.com/repos/agrc/ugs-db/stargazers","contributors_url":"https://api.github.com/repos/agrc/ugs-db/contributors","subscribers_url":"https://api.github.com/repos/agrc/ugs-db/subscribers","subscription_url":"https://api.github.com/repos/agrc/ugs-db/subscription","commits_url":"https://api.github.com/repos/agrc/ugs-db/commits{/sha}","git_commits_url":"https://api.github.com/repos/agrc/ugs-db/git/commits{/sha}","comments_url":"https://api.github.com/repos/agrc/ugs-db/comments{/number}","issue_comment_url":"https://api.github.com/repos/agrc/ugs-db/issues/comments{/number}","contents_url":"https://api.github.com/repos/agrc/ugs-db/contents/{+path}","compare_url":"https://api.github.com/repos/agrc/ugs-db/compare/{base}...{head}","merges_url":"https://api.github.com/repos/agrc/ugs-db/merges","archive_url":"https://api.github.com/repos/agrc/ugs-db/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/agrc/ugs-db/downloads","issues_url":"https://api.github.com/repos/agrc/ugs-db/issues{/number}","pulls_url":"https://api.github.com/repos/agrc/ugs-db/pulls{/number}","milestones_url":"https://api.github.com/repos/agrc/ugs-db/milestones{/number}","notifications_url":"https://api.github.com/repos/agrc/ugs-db/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/agrc/ugs-db/labels{/name}","releases_url":"https://api.github.com/repos/agrc/ugs-db/releases{/id}","created_at":"2014-12-18T16:19:38Z","updated_at":"2015-01-22T20:21:53Z","pushed_at":"2015-09-04T13:01:35Z","git_url":"git://github.com/agrc/ugs-db.git","ssh_url":"git@github.com:agrc/ugs-db.git","clone_url":"https://github.com/agrc/ugs-db.git","svn_url":"https://github.com/agrc/ugs-db","homepage":null,"size":55442,"stargazers_count":1,"watchers_count":1,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":10,"forks":1,"open_issues":10,"watchers":1,"default_branch":"master"}},"base":{"label":"agrc:version2","ref":"version2","sha":"1c8ea23fd9c17e40c4c67f8d8082a71befc036bc","user":{"login":"agrc","id":709415,"avatar_url":"https://avatars.githubusercontent.com/u/709415?v=3","gravatar_id":"","url":"https://api.github.com/users/agrc","html_url":"https://github.com/agrc","followers_url":"https://api.github.com/users/agrc/followers","following_url":"https://api.github.com/users/agrc/following{/other_user}","gists_url":"https://api.github.com/users/agrc/gists{/gist_id}","starred_url":"https://api.github.com/users/agrc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agrc/subscriptions","organizations_url":"https://api.github.com/users/agrc/orgs","repos_url":"https://api.github.com/users/agrc/repos","events_url":"https://api.github.com/users/agrc/events{/privacy}","received_events_url":"https://api.github.com/users/agrc/received_events","type":"Organization","site_admin":false},"repo":{"id":28191086,"name":"ugs-db","full_name":"agrc/ugs-db","owner":{"login":"agrc","id":709415,"avatar_url":"https://avatars.githubusercontent.com/u/709415?v=3","gravatar_id":"","url":"https://api.github.com/users/agrc","html_url":"https://github.com/agrc","followers_url":"https://api.github.com/users/agrc/followers","following_url":"https://api.github.com/users/agrc/following{/other_user}","gists_url":"https://api.github.com/users/agrc/gists{/gist_id}","starred_url":"https://api.github.com/users/agrc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agrc/subscriptions","organizations_url":"https://api.github.com/users/agrc/orgs","repos_url":"https://api.github.com/users/agrc/repos","events_url":"https://api.github.com/users/agrc/events{/privacy}","received_events_url":"https://api.github.com/users/agrc/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/agrc/ugs-db","description":"Database seeder for UGS data","fork":false,"url":"https://api.github.com/repos/agrc/ugs-db","forks_url":"https://api.github.com/repos/agrc/ugs-db/forks","keys_url":"https://api.github.com/repos/agrc/ugs-db/keys{/key_id}","collaborators_url":"https://api.github.com/repos/agrc/ugs-db/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/agrc/ugs-db/teams","hooks_url":"https://api.github.com/repos/agrc/ugs-db/hooks","issue_events_url":"https://api.github.com/repos/agrc/ugs-db/issues/events{/number}","events_url":"https://api.github.com/repos/agrc/ugs-db/events","assignees_url":"https://api.github.com/repos/agrc/ugs-db/assignees{/user}","branches_url":"https://api.github.com/repos/agrc/ugs-db/branches{/branch}","tags_url":"https://api.github.com/repos/agrc/ugs-db/tags","blobs_url":"https://api.github.com/repos/agrc/ugs-db/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/agrc/ugs-db/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/agrc/ugs-db/git/refs{/sha}","trees_url":"https://api.github.com/repos/agrc/ugs-db/git/trees{/sha}","statuses_url":"https://api.github.com/repos/agrc/ugs-db/statuses/{sha}","languages_url":"https://api.github.com/repos/agrc/ugs-db/languages","stargazers_url":"https://api.github.com/repos/agrc/ugs-db/stargazers","contributors_url":"https://api.github.com/repos/agrc/ugs-db/contributors","subscribers_url":"https://api.github.com/repos/agrc/ugs-db/subscribers","subscription_url":"https://api.github.com/repos/agrc/ugs-db/subscription","commits_url":"https://api.github.com/repos/agrc/ugs-db/commits{/sha}","git_commits_url":"https://api.github.com/repos/agrc/ugs-db/git/commits{/sha}","comments_url":"https://api.github.com/repos/agrc/ugs-db/comments{/number}","issue_comment_url":"https://api.github.com/repos/agrc/ugs-db/issues/comments{/number}","contents_url":"https://api.github.com/repos/agrc/ugs-db/contents/{+path}","compare_url":"https://api.github.com/repos/agrc/ugs-db/compare/{base}...{head}","merges_url":"https://api.github.com/repos/agrc/ugs-db/merges","archive_url":"https://api.github.com/repos/agrc/ugs-db/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/agrc/ugs-db/downloads","issues_url":"https://api.github.com/repos/agrc/ugs-db/issues{/number}","pulls_url":"https://api.github.com/repos/agrc/ugs-db/pulls{/number}","milestones_url":"https://api.github.com/repos/agrc/ugs-db/milestones{/number}","notifications_url":"https://api.github.com/repos/agrc/ugs-db/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/agrc/ugs-db/labels{/name}","releases_url":"https://api.github.com/repos/agrc/ugs-db/releases{/id}","created_at":"2014-12-18T16:19:38Z","updated_at":"2015-01-22T20:21:53Z","pushed_at":"2015-09-04T13:01:35Z","git_url":"git://github.com/agrc/ugs-db.git","ssh_url":"git@github.com:agrc/ugs-db.git","clone_url":"https://github.com/agrc/ugs-db.git","svn_url":"https://github.com/agrc/ugs-db","homepage":null,"size":55442,"stargazers_count":1,"watchers_count":1,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":10,"forks":1,"open_issues":10,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/agrc/ugs-db/pulls/28"},"html":{"href":"https://github.com/agrc/ugs-db/pull/28"},"issue":{"href":"https://api.github.com/repos/agrc/ugs-db/issues/28"},"comments":{"href":"https://api.github.com/repos/agrc/ugs-db/issues/28/comments"},"review_comments":{"href":"https://api.github.com/repos/agrc/ugs-db/pulls/28/comments"},"review_comment":{"href":"https://api.github.com/repos/agrc/ugs-db/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/agrc/ugs-db/pulls/28/commits"},"statuses":{"href":"https://api.github.com/repos/agrc/ugs-db/statuses/97873070ca432beed2b9cc5469ad86c0a2f378bb"}}}}