#--------------------------------------------------------------------- # # mac_noise - A QGIS plugin set to work with flight noise data # for the Metropolitan Airports Commission of Minneapolis, MN. # # Copyright (C) 2008 Aaron Racicot, Z-Pulley Inc. # Copyright (C) 2008 Metropolitan Airports Commission of Minneapolis # # EMAIL: aaronr (at) z-pulley.com # WEB : www.z-pulley.com # www.reprojected.com # #--------------------------------------------------------------------- # # licensed under the terms of GNU GPL 2 # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License along # with this program; if not, write to the Free Software Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. # #--------------------------------------------------------------------- from PyQt4.QtCore import * from PyQt4.QtGui import * from qgis.core import * from qgis.gui import * # TODO wrap this with check for errors on import and notify the user gracefully if # they need to add psycopg2 import psycopg2 import psycopg2.extras import pdb from mac_querybuilder_ui import Ui_QueryBuilderWindow class QueryBuilderWindow(QDialog, Ui_QueryBuilderWindow): def __init__(self, iface, fl, plugin): QDialog.__init__(self, iface.mainWindow(), fl) self.setupUi(self) self.iface = iface self.plugin = plugin self.macLayer = None self.macLayerInReg = False self.connect(self.configConnectionButton, SIGNAL("clicked()"), self.confConn) self.connect(self.btnExecute, SIGNAL("clicked()"), self.executeSql) self.connect(self.btnClear, SIGNAL("clicked()"), self.clearSql) #self.connect(self.btnEdit, SIGNAL("clicked()"), self.edit) self.editEnabled = False self.connect(self.btnClose, SIGNAL("clicked()"), self.hide) self.defaultFields = True #macLayerIsValidForMAC = False #if macLayer and (macLayer.type() == macLayer.VectorLayer): # pass #self.setupMyUI() self.attributeTables = ['mac_airports','mac_runways','mac_adflags','mac_actypes','mac_runwaytimes'] def setupMyUI(self,macLayer = None): if macLayer != None: self.macLayer = macLayer if self.macLayer == None: # pop up a warning about selecting a layer uri = self.plugin.createURI() macLayer = QgsVectorLayer(uri.uri(), self.plugin.name, "postgres") if macLayer.isValid(): self.macLayer = macLayer self.plugin.macLayer = macLayer self.plugin.connect(QgsMapLayerRegistry.instance(), SIGNAL("layerWillBeRemoved(QString)"), self.plugin.layerWillBeRemoved) vprovider = self.macLayer.dataProvider() dataUri = vprovider.dataSourceUri() # print str(dataUri) # dbname='test_db' host=localhost port=5432 user='aaronr' table="mac_flights2" (the_geom) sql= dataUriList = dataUri.split(QString(" ")) validDB = False dbname = None host = None port = None user = None table = None sql = None schema = None for uri in dataUriList: if uri.contains(QString("dbname=")): validDB = True # We have a database... so lets build the psycopg2 URI splitUri = uri.split(QString("=")) # print "uri=" + uri if len(splitUri)>1: # print "splitUri=" + splitUri[1] dbname = splitUri[1] if uri.contains(QString("host=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: host = splitUri[1] if uri.contains(QString("port=")) and not uri.contains(QString("air")): splitUri = uri.split(QString("=")) if len(splitUri)>1: port = splitUri[1] if uri.contains(QString("user=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: user = splitUri[1] if uri.contains(QString("table=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: if splitUri[1].contains(QString(".")): splitTable = splitUri[1].split(QString(".")) if len(splitTable)>1: schema = splitTable[0].remove(QChar("\"")) table = splitTable[1].remove(QChar("\"")) else: table = splitUri[1].remove(QChar("\"")) if uri.contains(QString("sql=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: sql = splitUri[1] psyUri = "" if dbname is not None: psyUri = psyUri + "dbname=" + str(dbname) + " " if host: psyUri = psyUri + "host='" + str(host) + "' " if port: psyUri = psyUri + "port='" + str(port) + "' " if user: psyUri = psyUri + "user=" + str(user) + " " # connection = psycopg2.connect("dbname='tisec_dss' user='aaronr'") # print "psyUri=" + psyUri # print "table=" + table # print "sql=" + sql connection = psycopg2.connect(psyUri) mark = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) self.connectionString.setText(dataUri) if schema != None and schema != "public": # print "Setting schema to " + str(schema) mark.execute("SET search_path TO %s,public;" % (schema)) elif schema != None and schema == "public": # print "Setting schema to " + str(schema) mark.execute("SET search_path TO %s;" % (schema)) if self.defaultFields == True: self.airportList.clear() mark.execute("select distinct(airport) from mac_airports") airports = mark.fetchall() for airport in airports: # print airport[0] if airport[0] != "": QListWidgetItem(QString(str(airport[0])), self.airportList) # self.editSql.append(airport.toString()) self.airportList.setCurrentRow(0) self.airportList.selectAll() if self.defaultFields == True: self.runwayList.clear() mark.execute("select distinct(runway) from mac_runways") runways = mark.fetchall() for runway in runways: # print runway[0] if runway[0] != "": QListWidgetItem(QString(str(runway[0])), self.runwayList) # self.editSql.append(runway.toString()) self.runwayList.setCurrentRow(0) self.runwayList.selectAll() if self.defaultFields == True: self.adflagList.clear() mark.execute("select distinct(adflag) from mac_adflags") adflags = mark.fetchall() for adflag in adflags: # print adflag[0] if adflag[0] != "": QListWidgetItem(QString(str(adflag[0])), self.adflagList) # self.editSql.append(adflag.toString()) self.adflagList.setCurrentRow(0) self.adflagList.selectAll() if self.defaultFields == True: self.actypeList.clear() mark.execute("select distinct(actype) from mac_actypes") actypes = mark.fetchall() for actype in actypes: # print actype[0] if actype[0] != "": QListWidgetItem(QString(str(actype[0])), self.actypeList) # self.editSql.append(actype.toString()) self.actypeList.setCurrentRow(0) self.actypeList.selectAll() if self.defaultFields == True: # Do the STIME # index = vprovider.fieldNameIndex(QString("stime")) startDate = QDate(3000,1,1) # print "Initial start time = " + str(startDate.toString()) # stimes = self.getUniqueValues(vprovider, index) mark.execute("select runwaytime from mac_runwaytimes") stimes = mark.fetchall() for stime in stimes: if stime[0] != "": date = QDate() stime_short = str(stime[0]).split(" ")[0] # print "Test stime_short = " + stime_short if stime_short != 'None': date = date.fromString(QString(stime_short),QString("yyyy'-'MM'-'dd")) # print "Test date = " + str(date.toString()) if date < startDate: # New time for start startDate = date # print "New start time = " + str(startDate.toString()) self.startDate.setSelectedDate(startDate) self.editSql.append(startDate.toString()) # print "Start time = " + str(startDate.toString()) if self.defaultFields == True: # Do the ETIME # index = vprovider.fieldNameIndex(QString("etime")) endDate = QDate(1000,1,1) # etimes = self.getUniqueValues(vprovider, index) mark.execute("select runwaytime from mac_runwaytimes") etimes = mark.fetchall() for etime in etimes: if etime[0] != "": date = QDate() etime_short = str(etime[0]).split(" ")[0] if etime_short != 'None': date = date.fromString(QString(etime_short),QString("yyyy'-'MM'-'dd")) if date > endDate: # New time for end endDate = date self.endDate.setSelectedDate(endDate) self.editSql.append(endDate.toString()) # print "End time = " + str(endDate.toString()) if self.defaultFields == True: self.startTime.setTime(QTime(0,0,0)) self.endTime.setTime(QTime(23,59,59)) def getUniqueValues(self, provider, index): allAttrs = provider.attributeIndexes() provider.select(allAttrs) feat = QgsFeature() values = [] check = [] provider.rewind() while provider.nextFeature(feat): if not feat.attributeMap()[index].toString() in check: values.append( feat.attributeMap()[index] ) check.append( feat.attributeMap()[index].toString() ) return values def getFeatureIds(self, vLayer): # print "getFeatureIds called" provider = vLayer.dataProvider() allAttrs = provider.attributeIndexes() provider.select(allAttrs) feat = QgsFeature() values = [] check = [] provider.rewind() # All posible IDs while provider.nextFeature(feat): # print "Looping features ID = " + str(feat.id()) if not feat.id() in check: values.append( feat.id() ) check.append( feat.id() ) feat2 = QgsFeature() values2 = [] for i,featureId in enumerate(values): # while vLayer.nextFeature(feat): # print "Looping2 features ID = " + str(featureId) if vLayer.featureAtId(featureId,feat2): values2.append( feat2.id() ) # DEBUG pyqtRemoveInputHook() pdb.set_trace() # DEBUG return values2 def getFeatureIdsThatChanged(self, vLayer, origIds): # print "getFeatureIdsThatChanged called" provider = vLayer.dataProvider() allAttrs = provider.attributeIndexes() provider.select(allAttrs) feat = QgsFeature() feat2 = QgsFeature() values = [] # All posible IDs # DEBUG pyqtRemoveInputHook() pdb.set_trace() # DEBUG for origId in origIds: originalFeature = provider.featureAtId(origId,feat) newFeature = vLayer.featureAtId(origId,feat2,True,allAttrs) if originalFeature and not newFeature: # We have a removed ID pass if newFeature and not originalFeature: # We have an added ID pass if newFeature and originalFeature: # We have a feature that was there before... now see if it has the same geom #featureDiff = feat.geometry().difference(feat2.geometry()) # print "ID = " + str(origId) featWkt = feat.geometry().exportToWkt() feat2Wkt = feat2.geometry().exportToWkt() if origId == 171: pass # print "Feature1 as WKT = " + str(feat.geometry().exportToWkt()) # print "Feature2 as WKT = " + str(feat2.geometry().exportToWkt()) #print "wkbType = " + str(featureDiff.wkbType()) if featWkt.compare(feat2Wkt) != 0: # print "We have a feature that was modified = " + str(origId) values.append( origId ) return values def fillInDefaultLayout(self): pass def executeSql(self): # print "executeSql Pressed" self.defaultFields = False mapLayers = QgsMapLayerRegistry.instance().mapLayers() found = False for mapLayer in mapLayers: # DEBUG #pyqtRemoveInputHook() #pdb.set_trace() # DEBUG if mapLayer == self.macLayer.getLayerID(): # We are already in the reg... mark found found = True if not found: QgsMapLayerRegistry.instance().addMapLayer(self.macLayer) self.macLayerInReg = True # Try to constrain with the current params # print "Original Feature Count = " + str(self.macLayer.featureCount()) runwayString = None runwayItems = self.runwayList.selectedItems() if len(runwayItems)>0: runwayString = QString("(") for i,runwayItem in enumerate(runwayItems): if i == 0: runwayString = runwayString.append(QString("runway=\'" + str(runwayItem.text()) + "\'")) else: runwayString = runwayString.append(QString(" OR runway=\'" + str(runwayItem.text()) + "\'")) runwayString = runwayString.append(QString(")")) airportString = None airportItems = self.airportList.selectedItems() if len(airportItems)>0: airportString = QString("(") for i,airportItem in enumerate(airportItems): if i == 0: airportString = airportString.append(QString("airport=\'" + str(airportItem.text()) + "\'")) else: airportString = airportString.append(QString(" OR airport=\'" + str(airportItem.text()) + "\'")) airportString = airportString.append(QString(")")) # airportString = QString("airport=\'" + str(self.airportList.currentItem().text()) + "\'") adflagString = None adflagItems = self.adflagList.selectedItems() if len(adflagItems)>0: adflagString = QString("(") for i,adflagItem in enumerate(adflagItems): if i == 0: adflagString = adflagString.append(QString("adflag=\'" + str(adflagItem.text()) + "\'")) else: adflagString = adflagString.append(QString(" OR adflag=\'" + str(adflagItem.text()) + "\'")) adflagString = adflagString.append(QString(")")) actypeString = None actypeItems = self.actypeList.selectedItems() if len(actypeItems)>0: actypeString = QString("(") for i,actypeItem in enumerate(actypeItems): if i == 0: actypeString = actypeString.append(QString("actype=\'" + str(actypeItem.text()) + "\'")) else: actypeString = actypeString.append(QString(" OR actype=\'" + str(actypeItem.text()) + "\'")) actypeString = actypeString.append(QString(")")) stimeString = QString("runwaytime>=to_timestamp(\'" + str(self.startDate.selectedDate().toString(QString("yyyy'-'MM'-'dd"))) + " " + str(self.startTime.time().toString(QString("hh':'mm':'ss"))) + "\',\'YYYY-MM-DD HH24:MI:SS\')") etimeString = QString("runwaytime<=to_timestamp(\'" + str(self.endDate.selectedDate().toString(QString("yyyy'-'MM'-'dd"))) + " " + str(self.endTime.time().toString(QString("hh':'mm':'ss"))) + "\',\'YYYY-MM-DD HH24:MI:SS\')") subString = QString("") if runwayString is not None: subString.append(runwayString) subString.append(QString(" and ")) if airportString is not None: subString.append(airportString) subString.append(QString(" and ")) if adflagString is not None: subString.append(adflagString) subString.append(QString(" and ")) if actypeString is not None: subString.append(actypeString) subString.append(QString(" and ")) subString.append(stimeString) subString.append(QString(" and ")) subString.append(etimeString) # print subString self.macLayer.setSubsetString(subString) #self.macLayer.setSubsetString(runwayString.append(QString(" and ").append(airportString))) # print self.macLayer.subsetString() # print "New Feature Count = " + str(self.macLayer.featureCount()) # Refresh the URI in the GUI vprovider = self.macLayer.dataProvider() dataUri = vprovider.dataSourceUri() self.connectionString.setText(dataUri) self.iface.mapCanvas().refresh() self.editSql.append(QString("New Count = ").append(QString(str(self.macLayer.featureCount())))) ## As a test, loop through and find selected layer and show the attribute names #macLayer = self.iface.macLayer() #if macLayer and (macLayer.type() == macLayer.VectorLayer): # fieldList = self.getFieldList(macLayer) # for field in fieldList: # self.editSql.append(QString(fieldList[field].name())) def getFieldList(self, vlayer): fProvider = vlayer.dataProvider() feat = QgsFeature() allAttrs = fProvider.attributeIndexes() fProvider.select(allAttrs) myFields = fProvider.fields() return myFields def layerWillBeRemoved(self,layerID): # print "querybuilder layerWillBeRemoved Pressed" self.macLayer = None self.macLayerInReg = False self.defaultFields = True def clearSql(self): # print "clearSql Pressed" self.macLayer.setSubsetString(QString("")) # We will drop the old layer... then add a new one QgsMapLayerRegistry.instance().removeMapLayer(self.macLayer.getLayerID()) self.macLayer = None self.defaultFields = True self.setupMyUI() self.iface.mapCanvas().refresh() def edit(self): # print "edit Pressed" if self.editEnabled == False: # Enable editing and connect signals #self.btnEdit.setText(QString("Stop Editing")) self.iface.actionToggleEditing().trigger() self.iface.actionToggleEditing().setDisabled(True) self.iface.actionCopyFeatures().setDisabled(True) self.iface.actionPasteFeatures().setDisabled(True) self.iface.actionCapturePoint().setDisabled(True) self.iface.actionCaptureLine().setDisabled(True) self.iface.actionCapturePologon().setDisabled(True) self.iface.actionDeleteSelected().setDisabled(True) self.iface.actionMoveFeature().setDisabled(True) self.iface.actionSplitFeatures().setDisabled(True) self.iface.actionAddVertex().setDisabled(True) self.iface.actionDelerteVertex().setEnabled(True) self.iface.actioMoveVertex().setEnabled(True) self.iface.actionAddRing().setDisabled(True) self.iface.actionAddIsland().setDisabled(True) self.vLayerFeatureIds = self.getFeatureIds(self.macLayer) self.editEnabled = True self.connect(self.macLayer, SIGNAL("layerModified(bool)"), self.layerModified) else: # Disable editing and manually snag the features that need update and do that # via the psycopg2 # Before tuning off editing and rolling out the edits, ask if we want to finish the edits # and if so capture the diffs and get them ready to update in postGIS # print "Feature Count = " + QString(str(self.macLayer.featureCount())) # vLayerFeatureIds = self.getFeatureIds(self.macLayer) output = "" self.vLayerFeaturesThatChanged = self.getFeatureIdsThatChanged(self.macLayer,self.vLayerFeatureIds) for featureId in self.vLayerFeaturesThatChanged: output = output + " + " + str(featureId) # print output #self.btnEdit.setText(QString("Start Editing")) self.macLayer.rollBack() #self.iface.actionToggleEditing().trigger() self.iface.actionToggleEditing().setEnabled(True) self.iface.actionDelerteVertex().setDisabled(True) self.iface.actioMoveVertex().setDisabled(True) self.editEnabled = False def layerModified(self,onlyGeometry): # print "layerModified recieved" pass def testConnection(self): success = False qgis_valid = True psycopg2_valid = True # First test that we can connect via QGIS uri = self.plugin.createURI() macLayer = QgsVectorLayer(uri.uri(), self.plugin.name, "postgres") if macLayer.isValid() == False: # print "Could not create vector layer in testing..." qgis_valid = False try: # The main data layer is valid, now check for all the others... vprovider = macLayer.dataProvider() dataUri = vprovider.dataSourceUri() # print str(dataUri) # dbname='test_db' host=localhost port=5432 user='aaronr' table="mac_flights2" (the_geom) sql= dataUriList = dataUri.split(QString(" ")) validDB = False dbname = None host = None port = None user = None table = None sql = None schema = "public" for uri in dataUriList: if uri.contains(QString("dbname=")): validDB = True # We have a database... so lets build the psycopg2 URI splitUri = uri.split(QString("=")) # print "uri=" + uri if len(splitUri)>1: # print "splitUri=" + splitUri[1] dbname = splitUri[1] if uri.contains(QString("host=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: host = splitUri[1] if uri.contains(QString("port=")) and not uri.contains(QString("air")): splitUri = uri.split(QString("=")) if len(splitUri)>1: port = splitUri[1] if uri.contains(QString("user=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: user = splitUri[1] if uri.contains(QString("table=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: if splitUri[1].contains(QString(".")): splitTable = splitUri[1].split(QString(".")) if len(splitTable)>1: schema = splitTable[0].remove(QChar("\"")) table = splitTable[1].remove(QChar("\"")) else: table = splitUri[1].remove(QChar("\"")) if uri.contains(QString("sql=")): splitUri = uri.split(QString("=")) if len(splitUri)>1: sql = splitUri[1] psyUri = "" if dbname is not None: psyUri = psyUri + "dbname=" + str(dbname) + " " if host: psyUri = psyUri + "host='" + str(host) + "' " if port: psyUri = psyUri + "port='" + str(port) + "' " if user: psyUri = psyUri + "user=" + str(user) + " " # connection = psycopg2.connect("dbname='tisec_dss' user='aaronr'") # print "psyUri=" + psyUri # print "table=" + table # print "sql=" + sql connection = psycopg2.connect(psyUri) mark = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) if schema != None and schema != "public": # print "Setting schema to " + str(schema) mark.execute("SET search_path TO %s,public;" % (schema)) elif schema != None and schema == "public": # print "Setting schema to " + str(schema) mark.execute("SET search_path TO %s;" % (schema)) errorTables = "" for thisTable in self.attributeTables: # print "select relname from pg_class where relname='"+thisTable+"' AND pg_table_is_visible(pg_class.oid)" mark.execute("select relname from pg_class where relname='"+thisTable+"' AND pg_table_is_visible(pg_class.oid)") thisTableRes = mark.fetchone() # print "length = " + str(thisTableRes) if thisTableRes == None: # We have a missing table... errorTables = errorTables + thisTable + " " # print "Table missing!" if errorTables != "": psycopg2_valid = False QMessageBox.warning(self.iface.mainWindow(), "Warning", "The following needed tables are missing: " + errorTables) except: psycopg2_valid = False return (qgis_valid and psycopg2_valid) def updateConnection(self): success = False # First try the test connection if self.testConnection() == True: # Now add the new one... uri = self.plugin.createURI() macLayer = QgsVectorLayer(uri.uri(), self.plugin.name, "postgres") if macLayer.isValid() == True: # We will drop the old layer... then add a new one if self.macLayer != None: QgsMapLayerRegistry.instance().removeMapLayer(self.macLayer.getLayerID()) # print "Layer Valid..." self.plugin.macLayer = macLayer self.setupMyUI(self.plugin.macLayer) success = True else: # pop up a warning about selecting a layer QMessageBox.warning(self.iface.mainWindow(), "Warning", "Data layer does not seem to be valid... please verify data layer") # print "Failed to load layer!" # Set the title to an error with connection... self.plugin.dbconnection_gui.setWindowTitle(QString("Error connecting layer... please verify valid data")) self.plugin.dbconnection_gui.show() else: # pop up a warning about selecting a layer QMessageBox.warning(self.iface.mainWindow(), "Warning", "Database connection failed... please verify connection params") # print "Failed to test layer!" # Set the title to an error with connection... self.plugin.dbconnection_gui.setWindowTitle(QString("Error testing layer... please verify/modify connection params")) self.plugin.dbconnection_gui.show() return success def confConn(self): # print "confConn Pressed" # Set the title to an error with connection... self.plugin.dbconnection_gui.setWindowTitle(QString("Modify connection params")) self.plugin.dbconnection_gui.show()