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
Related Tags
postgis × 9043postgresql × 2906
qgis × 1363
sql × 527
geoserver × 522
pgrouting × 442
raster × 389
openstreetmap × 371
python × 325
polygon × 299
coordinate-system × 289
geometry × 271
shapefile × 254
postgis-2.0 × 198
intersection × 197
gdal × 189
ogr2ogr × 181
geojson × 170
linestring × 170
openlayers-2 × 168
point × 159
query × 152
distance × 141
osm2pgsql × 134
srid × 127