0

Since days I'm struggling with this problem, did a lot of searching - also in this forum - but did not find the answer I need.

I have a layer 'Project' in QGIS that is connected to a spatial table in SQL Server with the same name. The table contains more than 30000 projects (done since 2009) with their position. If we want to do a new project at a new address with let's say coordinates (lon=8.002, lat=35.453) in WGS84, we want to know which projects nearby we have done already. So, select the projects around this address within a distance of say 0.1°. I prefer to select these projects using SQL in the database itself, I think that's the quickest way.

In Python, I make a connection with the SQL Server database and run the spatial query that selects the projects.

The code up to now that runs without error messages in Python is:

pr = QgsProject.instance().mapLayersByName('Project')[0]
self.iface.setActiveLayer(pr)
server = 'MyServer'  
database = 'MyDatabase'
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER="+server+";DATABASE="+database+";Trusted_Connection=yes")
cursor = conn.cursor()
cursor.execute("SELECT * FROM Project WHERE Position.STDistance(GEOGRAPHY::Point(1.002, 51.453, 4326)) <= 0.1")
row=cursor.fetchall

I'm looking for the correct Python code to show the points in layer 'Project' that meet the query result.

1

You can solve it using virtual layers:

I update my answer:

To create the virtual layers the supported query language is SQLite and SpatiaLite.

You can check the supported functions here.

e.g:

from qgis.core import QgsVectorLayer, QgsProject
vlayer = QgsVectorLayer( "?query=SELECT * FROM Project WHERE st_distance(geometry, PointFromText('POINT (1.002 51.453)'), 4326) <= 0.1", "vlayer_name", "virtual" )
QgsProject.instance().addMapLayer(vlayer)

Note: Project is your layer loaded in QGIS.

| improve this answer | |
  • Thanks. But is it possible to filter my layer Project using the query result instead of creating a virtual layer? – SWP_IB May 9 at 9:45
  • Virtual layers can be filtered. I recommend this video: youtu.be/coTSHnWvZXA?t=168 – Leo Cardona May 9 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.