Hot answers tagged

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