4

I have a very large SQL query consisting of two similar but different blocks, connected by a UNION ALL:

select OBJECTID, PROJECT_NAME, BIOGEOCLIMATIC_LBL, SITE_SERIES_MAP_CDE_LBL_CPNT_1, GEOMETRY as geom

from (select * from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW tem inner join ( select distinct BUSINESS_AREA_PROJECT_ID from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW where PROJECT_NAME IN( 'Okanagan Amalgamation TEM' , 'BC Gas Pipeline TEM' , 'Osoyoos Okanagan Area A Mapping TEM' , 'Peachland TEM' , 'TFL15 OK Falls TEM' , 'Westbank First Nation Community Forest TEM' , 'Updated Central OK Valley Central OK South Slopes Kelowna Ellison Joe Rich' ) order by BUSINESS_AREA_PROJECT_ID ) bapid on tem.BUSINESS_AREA_PROJECT_ID = bapid.BUSINESS_AREA_PROJECT_ID where (SITE_SERIES_MAP_CDE_LBL_CPNT_1= 'PA' OR SITE_SERIES_MAP_CDE_LBL_CPNT_2 = 'PA' OR SITE_SERIES_MAP_CDE_LBL_CPNT_3 = 'PA') UNION ALL select * from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW tem inner join ( select distinct BUSINESS_AREA_PROJECT_ID from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW where PROJECT_NAME IN( 'Okanagan Amalgamation TEM' , 'BC Gas Pipeline TEM' , 'Osoyoos Okanagan Area A Mapping TEM' , 'Peachland TEM' , 'TFL15 OK Falls TEM' , 'Westbank First Nation Community Forest TEM' , 'Updated Central OK Valley Central OK South Slopes Kelowna Ellison Joe Rich' ) order by BUSINESS_AREA_PROJECT_ID ) bapid on tem.BUSINESS_AREA_PROJECT_ID = bapid.BUSINESS_AREA_PROJECT_ID where ( ( BIOGEOCLIMATIC_LBL = 'BGxh1' AND (SITE_SERIES_LBL_CPNT_1= '04' or SITE_SERIES_LBL_CPNT_2= '04' or SITE_SERIES_LBL_CPNT_3= '04') or (BIOGEOCLIMATIC_LBL = 'PPxh1' AND (SITE_SERIES_LBL_CPNT_1= '02' or SITE_SERIES_LBL_CPNT_2= '02' or SITE_SERIES_LBL_CPNT_3= '02' )))) 

The query works nicely in Oracle's SQL developer against the database I'm drawing results from. But it fails in QGIS 3.10, whether I'm using DB Manager or loading the layer via PyQGIS ( QgsDataSourceUri() , mainly).

When I run just the first half of the query (above the UNION ALL) , it works in my PyQGIS code (but still fails in DB manager).

@MBain confirmed that UNION ALL exists in QGIS' SQL dialect. Checking the logs, I'm getting this error message:

Geometry type and srid for empty column GEOM of (entire query here) undefined.

So I'm trying to figure out how to explicitly define the geometry type (shoudl be POLYGON or MULTIPOLYGON) and srid (should be 3005) within the query.

5
  • When it fails in the DB Manager does it show an error message? Could be just a copy paste artifact but there is an errant space in your first line: tem. BUSINESS_AREA_PROJECT_ID
    – M Bain
    Commented Mar 11, 2021 at 3:53
  • I get a a “Database Error: There was an error creating the SQL layer, please check the logs for further information.” From the logs (View > Panels > Log Messages), I get this message: WARNING Geometry type and srid for empty column GEOMETRY of (entire query here) undefined.
    – grego
    Commented Mar 11, 2021 at 20:37
  • 1
    If you change your edited query so GEOMETRY as geom reads GEOMETRY.Get_WKT() as geom does the query manage to run?
    – M Bain
    Commented Mar 11, 2021 at 22:55
  • Thank you @M Bain ! This didn't work yet, but it has shown me that there are a few MULTIPOLYGONs mixed in with the POLYGONs in my dataset. I suspect this is causing a problem. Now to find a way to force the returned geom type to be MULTIPOLYGON..
    – grego
    Commented Mar 12, 2021 at 0:28
  • If you work this out please post an answer here - I feel it will come in handy one day.
    – M Bain
    Commented Mar 12, 2021 at 1:56

3 Answers 3

5

The error message Geometry type and srid for empty column GEOM of (entire query here) undefined is caused by a failed unique ID, as it turns out.

The following query returns ~2500 rows, but a few are duplicates. Therefore , using OBJECTID as a unique key causes a problem:

select OBJECTID, PROJECT_NAME, BIOGEOCLIMATIC_LBL, SITE_SERIES_MAP_CDE_LBL_CPNT_1, GEOMETRY as geom
from 
(select * from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW tem
inner join
( select distinct BUSINESS_AREA_PROJECT_ID from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW
where PROJECT_NAME IN( 'Okanagan Amalgamation TEM' , 'BC Gas Pipeline TEM') ) bapid 
on tem.BUSINESS_AREA_PROJECT_ID = bapid.BUSINESS_AREA_PROJECT_ID
where (SITE_SERIES_MAP_CDE_LBL_CPNT_1= 'PA' OR SITE_SERIES_MAP_CDE_LBL_CPNT_2 = 'PA' OR 
SITE_SERIES_MAP_CDE_LBL_CPNT_3 = 'PA')
UNION ALL
select * from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW tem
inner join
(select distinct BUSINESS_AREA_PROJECT_ID from WHSE_TERRESTRIAL_ECOLOGY.STE_TEM_ATTRIBUTE_POLYS_SVW
 where PROJECT_NAME IN( 'Okanagan Amalgamation TEM' , 'BC Gas Pipeline TEM') ) bapid  
on tem.BUSINESS_AREA_PROJECT_ID = bapid.BUSINESS_AREA_PROJECT_ID
where ( ( BIOGEOCLIMATIC_LBL = 'BGxh1' AND (SITE_SERIES_LBL_CPNT_1= '04' or SITE_SERIES_LBL_CPNT_2= '04' or SITE_SERIES_LBL_CPNT_3= '04') 
or (BIOGEOCLIMATIC_LBL = 'PPxh1' AND (SITE_SERIES_LBL_CPNT_1= '02' or SITE_SERIES_LBL_CPNT_2= '02' or SITE_SERIES_LBL_CPNT_3= '02' )))) 

There's at least two possible solutions to this.

SOLUTION 1: add 'select rownum' to the query

If you can live with duplicate rows (and duplicate geometries stacked atop one another), just add rownum to the selection statement in the first line of your SQL:

select rownum, OBJECTID, PROJECT_NAME, BIOGEOCLIMATIC_LBL, SITE_SERIES_MAP_CDE_LBL_CPNT_1, GEOMETRY as geom

Then load the layer, choosing rownum as your column with unique values in DB Manager: enter image description here

You can deal with duplicate records by using dissolve in QGIS.

SOLUTION 2: modify query to ensure there's no duplicate rows When developing your query in SQL Developer (or another SQL editor), compare the number of rows returned by select count with select count distinct . If your select count distinct returns a lower count, then you have duplicate rows

select count(OBJECTID)
select count(distinct OBJECTID)

Once you're sure your query is not selecting the same records more than once, you can happily use OBJECTID as your column with unique values in DB Manager.

It is confusing as to why one would get the misleading Geometry type and srid for empty column GEOM of (entire query here) undefined. error in this situation, but that's how you solve it.

1
  • 1
    Who would've guessed that was the cause! Well done.
    – M Bain
    Commented Mar 17, 2021 at 19:46
3

Each UNION All query must have the same number of columns as the one below it. You should be able to see this in the log. This can be found in QGIS at the bottom rightenter image description here.

1
  • Thank you, but that's not the issue. I do have the same columns selected in each of the two clauses.
    – grego
    Commented Mar 11, 2021 at 20:38
3

I think @Omegon 's suggestion is worth a closer look. The first line of the first query ends with

SITE_SERIES_MAP_CDE_LBL_CPNT_1, as ha GEOMETRY

While the second query reads

SITE_SERIES_MAP_CDE_LBL_CPNT_1, GEOMETRY

The , as ha GEOMETRY doesn't look right.

Also I can confirm UNION ALL works in the DB Manager

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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