-
Notifications
You must be signed in to change notification settings - Fork 2
/
query_db_catid.py
executable file
·202 lines (154 loc) · 8.79 KB
/
query_db_catid.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
#!/usr/bin/env python
#
# Query nga inventory on ADAPT by catalog ID
#
import psycopg2
import csv
import argparse
import os, errno
import shutil
import os
def force_symlink(file1, file2):
try:
os.symlink(file1, file2)
except OSError as e:
if e.errno == errno.EEXIST:
os.remove(file2)
os.symlink(file1, file2)
def query_db_catid_test(catID, prod_code='M1BS', out_dir='/explore/nobackup/people/pmontesa', db_table='nga_footprint_master_V2'):
'''Query and select scenes from latest database
'''
with psycopg2.connect(database="arcgis", user="pmontesa", password=os.environ['NGADBPASS'], host="arcdb04", port="5432") as dbConnect:
cur = dbConnect.cursor() # setup the cursor
selquery = "SELECT S_FILEPATH, SENSOR, CATALOG_ID, ACQ_TIME FROM %s WHERE CATALOG_ID = '%s' AND PROD_CODE = '%s'" %(db_table, catID, prod_code)
#selquery = "SELECT * FROM %s WHERE CATALOG_ID = '%s' AND PROD_CODE = '%s'" %(db_table, catID, prod_code)
cur.execute(selquery)
selected=cur.fetchall()
return selected
def query_db_catid_v2(catID: str, prod_code: str, out_dir: str, out_csv_fn: str, username: str, password: str, db_table='nga_footprint_master_V2', symlink=True):
"""
Returns to stdout the ADAPT dir that has the imagery associated with the input catID
If out_dir specified, copies all images and xmls associated with catid into out_dir as symbolic links
"""
if out_csv_fn is not None:
f = open( out_csv_fn, 'w')
# Search ADAPT's NGA database for catID
imglist=[]
#with psycopg2.connect(database="ngadb01", user="anon", host="ngadb01", port="5432") as dbConnect:
with psycopg2.connect(database="arcgis", user=username, password=password, host="arcdb04", port="5432") as dbConnect:
cur = dbConnect.cursor() # setup the cursor
#selquery = "SELECT s_filepath, sensor, acq_time, cent_lat, cent_long FROM %s WHERE catalog_id = '%s' AND prod_code = '%s'" %(db_table, catID, prod_code)
selquery = "SELECT S_FILEPATH, SENSOR, ACQ_TIME FROM %s WHERE CATALOG_ID = '%s' AND PROD_CODE = '%s'" %(db_table, catID, prod_code)
print( "\n\t Now executing database query on catID '%s' ..."%catID)
cur.execute(selquery)
selected=cur.fetchall()
print( "\n\t Found '%s' scenes for catID '%s' \n"%(len(selected),catID))
if len(selected) == 0:
print('Exiting.')
os._exit(1)
if not os.path.exists(out_dir):
os.makedirs(out_dir)
# This will only get the data that match the first prod_id, preventing replicated data from being copied. This should prevent mosaics from failing
prod_id_list = list(set([ selected[i][0].split('-')[-1].split('_')[0] for i in range(0,len(selected)) ]))
print( "\t List of all prod_id '%s'" %(prod_id_list))
prod_id = prod_id_list[0]
print( "\t Selecting data associated with prod_id '%s'" %(prod_id))
if symlink:
print(f'\t Making symlinks in output dir: {out_dir}')
for i in range(0,len(selected)):
if prod_id in selected[i][0]:
imglist.extend(selected[i][0])
# Copy selection ntf and xml to out_dir as symlinks
filename = os.path.split(selected[i][0])[1]
print(selected[i][0])
# Write selection to CSV
if out_csv_fn is not None:
f.write(selected[i][0]+'\n')
if symlink:
force_symlink( selected[i][0], os.path.join(out_dir, filename) )
try:
# shutil.Error: ... are the same file
# Just copy over the xmls, instead of creating a symlink to them
shutil.copy2(os.path.splitext(selected[i][0])[0]+'.xml', out_dir)
except Exception as e:
force_symlink( os.path.splitext(selected[i][0])[0]+'.xml', os.path.join(out_dir, os.path.splitext(filename)[0]+'.xml') )
# Print to stdout the dir from first record selected
#print(os.path.split(selected[0][0])[0])
if out_csv_fn is not None:
f.close()
def query_db_catid(catID: str, prod_code: str, out_dir: str, out_csv_fn: str, username: str, password: str, db_table='nga_footprint_master_V2', symlink=True):
"""
Returns to stdout the ADAPT dir that has the imagery associated with the input catID
If out_dir specified, copies all images and xmls associated with catid into out_dir as symbolic links
"""
if out_csv_fn is not None:
f = open( out_csv_fn, 'w')
# Search ADAPT's NGA database for catID
imglist=[]
#with psycopg2.connect(database="ngadb01", user="anon", host="ngadb01", port="5432") as dbConnect:
with psycopg2.connect(database="arcgis", user=username, password=password, host="arcdb04", port="5432") as dbConnect:
cur = dbConnect.cursor() # setup the cursor
#selquery = "SELECT s_filepath, sensor, acq_time, cent_lat, cent_long FROM %s WHERE catalog_id = '%s' AND prod_code = '%s'" %(db_table, catID, prod_code)
selquery = "SELECT S_FILEPATH, SENSOR, ACQ_TIME FROM %s WHERE CATALOG_ID = '%s' AND PROD_CODE = '%s'" %(db_table, catID, prod_code)
print( "\n\t Now executing database query on catID '%s' ..."%catID)
cur.execute(selquery)
selected=cur.fetchall()
print( "\n\t Found '%s' scenes for catID '%s' \n"%(len(selected),catID))
if len(selected) == 0:
print('Exiting.')
os._exit(1)
if symlink:
if not os.path.exists(out_dir):
os.makedirs(out_dir)
# This will only get the data that match the first prod_id, preventing replicated data from being copied. This should prevent mosaics from failing
print(f'\t Making symlinks in output dir: {out_dir}')
prod_id = selected[0][0].split('-')[-1].split('_')[0]
print( "\t Creating symlinks for data associated with prod_id '%s'" %(prod_id))
for i in range(0,len(selected)):
if prod_id in selected[i][0]:
imglist.extend(selected[i][0])
#Copy ntf and xml to out_dir as symlinks
filename = os.path.split(selected[i][0])[1]
print(selected[i][0])
force_symlink( selected[i][0], os.path.join(out_dir, filename) )
try:
# shutil.Error: ... are the same file
# Just copy over the xmls, instead of creating a symlink to them
shutil.copy2(os.path.splitext(selected[i][0])[0]+'.xml', out_dir)
except Exception as e:
force_symlink( os.path.splitext(selected[i][0])[0]+'.xml', os.path.join(out_dir, os.path.splitext(filename)[0]+'.xml') )
#return(imglist)
# Print to stdout the dir from first record selected
print(os.path.split(selected[0][0])[0])
# Write CSV
if out_csv_fn is not None:
for i in range(0,len(selected)):
f.write(selected[i][0]+'\n')
if out_csv_fn is not None:
f.close()
def getparser():
parser = argparse.ArgumentParser(description="Query NGAdb with a catalog id")
parser.add_argument('catID', default=None, type=str, help='Input catid')
parser.add_argument('-NGA_DB_USER', default=os.environ['USER'], type=str, help='Username for db arcgis on on host arcdb04')
parser.add_argument('-NGA_DB_PASS', default=os.environ['NGADBPASS'], type=str, help='Password for db arcgis on on host arcdb04')
parser.add_argument('-prod_code', default='P1BS', type=str, help='Image production code: P1BS or M1BS')
parser.add_argument('-out_dir', default='/explore/nobackup/people/pmontesa', type=str, help='Output pairname dir for symlinks')
parser.add_argument('-db_table', default='nga_footprint_master_v2', type=str, help='Specify the db table name in the database')
parser.add_argument('-out_csv_fn', default=None, help='Output CSV of paths')
parser.add_argument('--no-symlink', dest='symlink', action='store_false', help='Turn off symlinking into output dir')
parser.set_defaults(symlink=True)
return parser
def main():
parser = getparser()
args = parser.parse_args()
catID = args.catID
prod_code = args.prod_code
out_dir = args.out_dir
db_table = args.db_table
out_csv_fn = args.out_csv_fn
if args.NGA_DB_PASS is None:
print('Needs password. Exiting.')
os._exit(1)
query_db_catid(catID, prod_code, out_dir, out_csv_fn, args.NGA_DB_USER, args.NGA_DB_PASS, db_table, args.symlink)
if __name__ == "__main__":
main()