8
votes
Accepted
Unmatched records missing from spatial left join
Try moving the where clause to a join condition:
select plg.id,p lg.geom, count(pnt.id) as num_pnt
from polygons plg
left join points pnt
on st_contains(plg.geom,st_transform(pnt.geom,3003)) and pnt....
- 60.7k
6
votes
Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?
Try this. I have a connection called localhost data so I search for the word data in all the connections:
from PyQt5.QtCore import QSettings
qs = QSettings()
L = []
for k in sorted(qs.allKeys()): #For ...
- 60.7k
4
votes
Is there a way to access named PostgreSQL connections via PyQGIS in order to access and load spatial layers?
Ah I think I actually figured it out from this page:
Getting schema names from PostGIS database in PyQGIS
For connections that exist, you can use:
md = QgsProviderRegistry.instance().providerMetadata('...
- 69
4
votes
Accepted
Intersection of Linestring with Polygon causes column mismatch in PostGIS
In the first part of union the order of attributes is:
p.lic_li_no, p.orig_licli, l.ra, l.descriptor
In the second part order is reversed:
p.lic_li_no, p.orig_licli, l.descriptor, l.ra
- 865
2
votes
Unmatched records missing from spatial left join
The issue come from :
where pnt.fieldx = 'xx'
If no point are matching, pnt.fieldx is NULL, so the WHERE is not true.
Just replace the where clause:
where pnt.fieldx = 'xx' or pnt.fieldx is NULL
Or ...
- 458
Only top scored, non community-wiki answers of a minimum length are eligible