Using Python, how to get SQL Server queried points in the map?

Question

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.

Answer 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.