def prepare(self, query_string):
query = QSqlQuery(self.db)
query.setForwardOnly(True)
# weird; QSqlQuery instances don't appear to be GC'ed by the time the DB is closed - so I capture them in a strong-ref'd
# set and finish them up myself manually.
self.__refQuery(query)
if query.prepare(query_string):
return query
raise SyntaxError("failed to prepare query, sql was: '{}', db error: {}".format(query_string, query.lastError().text()))
def get_info(self, image_date):
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.prepare('SELECT copyright_info FROM copyright WHERE image_date = ?;')
query.addBindValue(image_date)
if not query.exec_():
self.error.emit('Error getting copyright info', query.lastError().text())
return ''
query.first()
copyright_info = query.value(0).toString()
return copyright_info
def get_all_info(self):
info = {}
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.prepare('SELECT image_date, copyright_info FROM copyright;')
if not query.exec_():
self.error.emit('Error getting all copyright info', query.lastError().text())
return info
while query.next():
image_date = str(query.value(0).toString())
copyright_info = unicode(query.value(1).toString())
info[image_date] = copyright_info
return info
def export_esu_line(self):
"""
Export ESUs
:return:
"""
canvas = self.iface.mapCanvas()
clayer = canvas.currentLayer()
# will return 0 if none selected
count = clayer.selectedFeatureCount()
feature_count = clayer.featureCount()
# Get list of selected features
selected_esu_ids = list()
if count > 0:
selectedfeats = clayer.selectedFeatures()
for feat in selectedfeats:
selected_esu_ids.append(int(feat.attribute('esu_id')))
feature_count = clayer.selectedFeatureCount()
self.warn_about_selected_features(feature_count)
# Prepare sql query
if self.unassigned:
nsgexportsql = self.sql_queries['export_all']
else:
nsgexportsql = self.sql_queries['export_assigned_only']
# SQL to filter out selected records
if count > 0:
nsgexportsql += " WHERE esu.esu_id IN ({})".format(', '.join(
map(str, selected_esu_ids)))
# Setup database temporary tables
for table in ['qryType12', 'qryType3', 'qryType4']:
# Drop tables if left behind from last export
args = {'table': table}
query = self.run_sql('drop_table', args)
query = self.run_sql('create_qryType12')
query = self.run_sql('create_qryType3')
query = self.run_sql('create_qryType4')
# Run the main query
if config.DEBUG_MODE:
print(nsgexportsql)
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.exec_(nsgexportsql)
if query.isActive() is False:
raise StandardError('Database query problem: {}'.format(
query.lastError().text()))
# create layer
vlayer = QgsVectorLayer("multilinestring?crs=EPSG:27700", "temp",
"memory")
vlayer.setCrs(
QgsCoordinateReferenceSystem(
27700, QgsCoordinateReferenceSystem.EpsgCrsId))
provider = vlayer.dataProvider()
# add fields
self.fields = [
QgsField("esu_id", QVariant.String),
QgsField("USRN", QVariant.LongLong),
QgsField("Rec_type", QVariant.Int),
QgsField("DescTxt", QVariant.String),
QgsField("Locality", QVariant.String),
QgsField("Town", QVariant.String),
QgsField("Entry_date", QVariant.Date),
QgsField("Type_3_USRN", QVariant.LongLong),
QgsField("Type_3_Desc", QVariant.String),
QgsField("Type_4_USRN", QVariant.LongLong),
QgsField("Type_4_Desc", QVariant.String)
]
provider.addAttributes(self.fields)
vlayer.updateFields()
# Exit if output file path is invalid
if len(str(self.export_path)) < 0:
return False
if self.check_if_export_file_in_use():
return False
# Run through SQL results creating features from rows
self.progresswin.show()
i = 0
while query.next():
if self.progresswin.wasCanceled():
self.kill_export()
break
record = query.record()
new_feature = self.create_feature_from_record(record)
provider.addFeatures([new_feature])
# Update progress bar
i += 1
diff = feature_count + (
i - feature_count) if i > feature_count else feature_count
percent_complete = (i / float(diff)) * 100
self.progresswin.setValue(percent_complete)
if self.killed:
# Show message and exit if killed
export_error_msg_box = QMessageBox(
QMessageBox.Warning, " ",
"An error occurred while exporting shapefile", QMessageBox.Ok,
None)
export_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint
| Qt.WindowTitleHint)
export_error_msg_box.exec_()
return False
vlayer.updateExtents()
result = QgsVectorFileWriter.writeAsVectorFormat(
vlayer, self.export_path, "utf-8", None, "ESRI Shapefile")
# checks for completed export
if result == 0:
self.progresswin.close()
if config.DEBUG_MODE:
print('DEBUG_MODE: {} features exported'.format(
vlayer.featureCount()))
return True
def get_esu_feature(self):
# Run Query and get the matching features
shape_tbl = 'SELECT AsBinary(E.geometry) AS geom, E.esu_id AS esuid, E.PK_UID AS FID, T.* ' \
'FROM esu E, tblESU T ' \
'WHERE E.esu_id = T.esu_id and T.currency_flag = 0'
query = QSqlQuery(self.db)
query.setForwardOnly(True)
query.exec_(shape_tbl)
rec = query.record()
feat_list = list()
while query.next():
esu_id = query.value(rec.indexOf("esuid"))
geom = query.value(rec.indexOf("geom"))
# write a temp geom to extract the geom to be written
g = QgsGeometry()
g.fromWkb(geom)
feat = QgsFeature()
feat.setGeometry(g)
geom = feat.geometry()
count = len(geom.asMultiPolyline()[0])
start = geom.vertexAt(0)
end = geom.vertexAt(count - 1)
# only write in a mid point if the line has a midpoint vert
mids = None
if count >= 2:
geom.deleteVertex(count - 1)
geom.deleteVertex(0)
mids = geom.asMultiPolyline()
esuy = query.value(rec.indexOf("yref"))
esux = query.value(rec.indexOf("xref"))
if esuy <= 999999:
esuy = "0" + str(query.value(rec.indexOf("yref")))
if esux <= 999999:
esux = "0" + str(query.value(rec.indexOf("xref")))
afields = dict(esu_id=query.value(rec.indexOf("esu_id")),
xref=query.value(rec.indexOf("xref")),
yref=query.value(rec.indexOf("yref")),
ESUXYID=str(esux) + str(esuy),
version_no=query.value(rec.indexOf("version_no")),
entry_date=query.value(rec.indexOf("entry_date")),
closure_date=query.value(rec.indexOf("closure_date")),
start_date=query.value(rec.indexOf("start_date")),
Update_Date=query.value(rec.indexOf("Update_Date")),
start_xref=query.value(rec.indexOf("start_xref")),
start_yref=query.value(rec.indexOf("start_yref")),
end_xref=query.value(rec.indexOf("end_xref")),
end_yref=query.value(rec.indexOf("end_yref")),
tolerance=query.value(rec.indexOf("tolerance")),
)
if geom is not None:
self.xref_esu.append(esu_id)
item = dict(result=0, esu_id=esu_id, start={
'x': start[0],
'y': start[1]
}, end={
'x': end[0],
'y': end[1]
}, length=count, attributes=afields, midpoints=mids)
else:
item = dict(result=1, esu_id=esu_id, ESUXYID=int(str(esux) + str(esuy)))
feat_list.append(item)
return feat_list
