def run(self): #the following code reads the configuration file which setups the plugin to search in the correct database, table and method plugin_path = os.path.dirname(os.path.realpath(__file__)) fname = os.path.join(plugin_path, "postgis.ini") if os.path.exists(fname): pass else: iface.messageBar().pushMessage("Error", "No config file found", level=QgsMessageBar.CRITICAL, duration=5) parser = SafeConfigParser() try: parser.read(fname) self.postgisdatabase = parser.get('postgis', 'postgisdatabase') self.postgisusername = parser.get('postgis', 'postgisusername') self.postgispassword = parser.get('postgis', 'postgispassword') self.postgishost = parser.get('postgis', 'postgishost') self.postgisport = parser.get('postgis', 'postgisport') self.postgisschema = parser.get('postgis', 'postgisschema') self.postgistable = parser.get('postgis', 'postgistable') self.postgissearchcolumn = parser.get('postgis', 'postgissearchcolumn') self.postgisdisplaycolumn = parser.get('postgis', 'postgisdisplaycolumn') self.postgisgeomname = parser.get('postgis', 'postgisgeomname') self.searchmethod = parser.get('postgis', 'searchmethod') except: iface.messageBar().pushMessage("Error", "Something wrong in the config file", level=QgsMessageBar.CRITICAL, duration=5) #declare the dlg self.dlg = PostGISSearchDialog() # show the dialog self.dlg.show() #Info box explaining the problems using SQL query. if self.searchmethod == "SQL": QMessageBox.warning(None, "Info", "Querying a large dataset by SQL search method may cause the search box to become unresponsive. So type slowly and wait for results to appear. If possible create a Full Text Search (FTS) column and change the search method.") #tie the line edit to the function self.dlg.ui.searchText.textChanged.connect(self.addPostGISLayer) # Run the dialog event loop result = self.dlg.exec_()
class PostGISSearch(): def __init__(self, iface): # Save reference to the QGIS interface self.iface = iface # initialize plugin directory self.plugin_dir = os.path.dirname(__file__) # initialize locale locale = QSettings().value("locale/userLocale")[0:2] localePath = os.path.join(self.plugin_dir, 'i18n', 'postgissearch_{}.qm'.format(locale)) if os.path.exists(localePath): self.translator = QTranslator() self.translator.load(localePath) if qVersion() > '4.3.3': QCoreApplication.installTranslator(self.translator) def initGui(self): # Create action that will start plugin configuration self.action = QAction( QIcon(":/plugins/postgissearch/postgissearch_logo.png"), u"PostGIS Search", self.iface.mainWindow()) # connect the action to the run method self.action.triggered.connect(self.run) # Add toolbar button and menu item self.iface.addToolBarIcon(self.action) self.iface.addPluginToMenu(u"&PostGIS Search", self.action) def unload(self): # Remove the plugin menu item and icon self.iface.removePluginMenu(u"&PostGIS Search", self.action) self.iface.removeToolBarIcon(self.action) # run method that performs all the real work def run(self): #the following code reads the configuration file which setups the plugin to search in the correct database, table and method plugin_path = os.path.dirname(os.path.realpath(__file__)) fname = os.path.join(plugin_path, "postgis.ini") if os.path.exists(fname): pass else: iface.messageBar().pushMessage("Error", "No config file found", level=QgsMessageBar.CRITICAL, duration=5) parser = SafeConfigParser() try: parser.read(fname) self.postgisdatabase = parser.get('postgis', 'postgisdatabase') self.postgisusername = parser.get('postgis', 'postgisusername') self.postgispassword = parser.get('postgis', 'postgispassword') self.postgishost = parser.get('postgis', 'postgishost') self.postgisport = parser.get('postgis', 'postgisport') self.postgisschema = parser.get('postgis', 'postgisschema') self.postgistable = parser.get('postgis', 'postgistable') self.postgissearchcolumn = parser.get('postgis', 'postgissearchcolumn') self.postgisdisplaycolumn = parser.get('postgis', 'postgisdisplaycolumn') self.postgisgeomname = parser.get('postgis', 'postgisgeomname') self.searchmethod = parser.get('postgis', 'searchmethod') except: iface.messageBar().pushMessage("Error", "Something wrong in the config file", level=QgsMessageBar.CRITICAL, duration=5) #declare the dlg self.dlg = PostGISSearchDialog() # show the dialog self.dlg.show() #Info box explaining the problems using SQL query. if self.searchmethod == "SQL": QMessageBox.warning(None, "Info", "Querying a large dataset by SQL search method may cause the search box to become unresponsive. So type slowly and wait for results to appear. If possible create a Full Text Search (FTS) column and change the search method.") #tie the line edit to the function self.dlg.ui.searchText.textChanged.connect(self.addPostGISLayer) # Run the dialog event loop result = self.dlg.exec_() #following function takes the contents of the line edit and uses it to query the declared database def addPostGISLayer(self, string): if (len(string) > 4) or (" " in string): uri = QgsDataSourceURI() # set host name, port, database name, username and password uri.setConnection(self.postgishost, self.postgisport, self.postgisdatabase, self.postgisusername, self.postgispassword) #need a different SQL query based on the search method declared in the postgis.ini file if self.searchmethod == 'SQL': querystring = (string + "%") sql = """SELECT %s, ST_X(%s) as x, ST_Y(%s) as y from %s.%s WHERE LOWER(%s) LIKE LOWER('%s') LIMIT 100"""%(self.postgisdisplaycolumn, self.postgisgeomname, self.postgisgeomname, self.postgisschema, self.postgistable, self.postgissearchcolumn, querystring) elif self.searchmethod == 'FTS': sql = """SELECT %s, ST_X(%s) as x, ST_Y(%s) as y FROM %s.%s WHERE %s @@ plainto_tsquery('english', '%s') LIMIT 100"""%(self.postgisdisplaycolumn, self.postgisgeomname, self.postgisgeomname, self.postgisschema, self.postgistable, self.postgissearchcolumn, string) else: iface.messageBar().pushMessage("Error", "Wrong search method declared in config file", level=QgsMessageBar.CRITICAL, duration=5) #specify the type of database to query - in principla this could be changed to other databases self.db = QSqlDatabase.addDatabase('QPSQL') # check to see if it is valid if self.db.isValid(): self.db.setHostName(uri.host()) self.db.setDatabaseName(uri.database()) self.db.setPort(int(uri.port())) self.db.setUserName(uri.username()) self.db.setPassword(uri.password()) # open (create) the connection if self.db.open(): #setup model and run the query and then set the model to the results self.projectModel = QSqlQueryModel() self.projectModel.setQuery(sql,self.db) self.dlg.ui.tableView.setModel(self.projectModel) #close connection to database self.db.close() self.db.removeDatabase('QPSQL') #tweak the table based on the results self.dlg.ui.tableView.resizeColumnsToContents() #tie table to function for adding point to map canvas self.dlg.ui.tableView.selectionModel().currentChanged.connect(self.cellClicked) #the following function runs when the user clicks a row of the search results def cellClicked(self): iface.mapCanvas().refresh() #get the selected row from table view if self.dlg.ui.tableView.currentIndex(): index = self.dlg.ui.tableView.currentIndex().row() #need just one attribute for the label, this will be the first attribute if more than one is returned by the SQL query if "," in self.postgisdisplaycolumn: labelcolumn = self.postgisdisplaycolumn.split(",")[0] else: labelcolumn = self.postgisdisplaycolumn #setup the x and y variables which are used to create a memory point on the map canvas self.x = self.projectModel.record(index).value("x") self.y = self.projectModel.record(index).value("y") self.label = str(self.projectModel.record(index).value(labelcolumn)) canvas = self.iface.mapCanvas() dest_crs = canvas.mapRenderer().destinationCrs() #create memory layer for the new point self.pinLayer = QgsVectorLayer("Point?&field=Description:string(120)&field=X_Coordinate:double&field=Y_Coordinate:double&index=yes", self.label, "memory") self.provider = self.pinLayer.dataProvider() QgsMapLayerRegistry.instance().addMapLayer(self.pinLayer) feature = QgsFeature() feature.setGeometry(QgsGeometry.fromPoint(QgsPoint(self.x,self.y))) #depending on the version of QGIS adding the feature to the memory layer is different if QGis.QGIS_VERSION_INT > 10800: feature.setAttributes([self.label, self.x, self.y]) self.pinLayer.startEditing() self.pinLayer.addFeature(feature, True) self.pinLayer.commitChanges() else: feature.setAttributeMap( {0 : QVariant(self.description), 1 : QVariant(self.x), 2 : QVariant(self.y)}) self.provider.addFeatures([feature]) self.pinLayer.updateExtents() #based on the feature move and scale the map accodingly scale = 1200 rect = QgsRectangle(float(self.x)-scale,float(self.y)-scale,float(self.x)+scale,float(self.y)+scale) canvas.setExtent(rect) canvas.refresh() #close the dialogue do user can see map automcatically self.dlg.close()