1

Background: I am running QGIS 3.10.1, PostgreSQL 12.1 and PostGIS 3.0.0 on Windows 10. Using this environment, I am trying to run a simple query in DB Manager:

SELECT polygon('((10,20),(25,30),(30, 30),(10,20))')::geometry
UNION ALL
SELECT path('(1,21), (5,15), (9,20), (12,5)')::geometry
UNION ALL
SELECT box('(10, 21)'::point, '(16,10)'::point)::polygon::geometry
UNION ALL
SELECT circle('(20,10)'::point, 3)::polygon::geometry;

When I execute this query in DB Manager, it gives me the expected results:

Execution results

I want to visualize the result of this query, so I try to save it as a layer, like so:

Save as layer

However, when I click the Load button, it gives me the following error:

Error panel

When I look at the logs in QGIS:

Log Messages

Something is Missing

It is immediately apparent that the generated query is missing something inside the highlighted double quotes. What is the missing element? Is it the unique identifier, such as the _UID_ that I see in related posts on this same or similar topic?

QGIS Setting?

If so, is there a setting somewhere within QGIS where I can set this variable? Where is it?

I have seen multiple posts around this same issue, but none have pointed me to where I can set this variable correctly. It could be that version 3.10 is just too new for this.

An Experiment

As a little experiment, I created a table in the database with the following code:

CREATE TABLE ch02.geom_shapes AS (
  SELECT polygon('((10,20),(25,30),(30, 30),(10,20))')::geometry
  UNION ALL
  SELECT path('(1,21), (5,15), (9,20), (12,5)')::geometry
  UNION ALL
  SELECT box('(10, 21)'::point, '(16,10)'::point)::polygon::geometry
  UNION ALL
  SELECT circle('(20,10)'::point, 3)::polygon::geometry
);

When I open this table directly in QGIS, I get the following:

Table with shapes

No errors!

This is precisely the result that I was expecting to see. I do hope that someone knows how to get around the error that occurs when trying to create the layer from DB Manager.

  • 1
    You haven't named your column ("as geom") in the SELECT statements, and the geometry classes are not uniform (both polygon and linestring), and you haven't set SRIDs, in addition to the lack of a unique ID. – Vince Dec 27 '19 at 16:36
  • If this were the case, then the query itself would not have run successfully; the polygon column was populated correctly with the geometry data. additionally, if you drop the same query code into OpenJUMP, it will show the visual representation of the shapes correctly. – Michael Sheaver Dec 27 '19 at 17:02
2

Actually,

  • the query syntax is not wrong (that's why the query executes successfully)
  • QGIS doesn't mind mind if you don't have a uid column (it simply creates one, as you can see)
  • it's not necessary to alias the geometry column (you can simply choose the appropriate identifier)

The issue is the mixed geometry types; QGIS is not designed to handle those within the same layer!

Edit:
Creating a mixed geometry table will result in QGIS listing each geometry type as a separate View, to load only a distinct geometry type from the base table!

Each of your geometries will load successfully when loaded individually, e.g.:

SELECT polygon('((10,20),(25,30),(30, 30),(10,20))')::GEOMETRY;

or, simply if you leave out the LineString (path())!


However, it is unusual that you are using the PostgreSQL geometric types rather than the PostGIS geometry constructors; I'd suggest to use the appropriate ST functions, and set the CRS;

SELECT ST_GeometryFromText('POLYGON((10 20, 25 30, 30 30, 10 20))', <SRID>) AS geom;
SELECT ST_SetSRID('POLYGON((10 20, 25 30, 30 30, 10 20))'::GEOMETRY, <SRID>) AS geom;

or

SELECT ST_GeometryFromText('LINESTRING(1 21, 5 15, 9 20, 12 5)', <SRID>) AS geom; 
SELECT ST_SetSRID('LINESTRING(1 21, 5 15, 9 20, 12 5)'::GEOMETRY, <SRID>) AS geom;

or

SELECT ST_SetSRID(ST_Buffer(ST_MakePoint(20 10), 3), <SRID>) AS geom;



If you want to stick to using the PostgreSQL types as constructors, at least set the CRS appropriately:

SELECT ST_SetSRID(Polygon('((10,20),(25,30),(30, 30),(10,20))')::GEOMETRY, <SRID>) AS geom;
| improve this answer | |
  • No, that is not the problem, as you can see in the section 'An Experiment' above. The same exact query works correctly if I drop the results into a table and bring that table directly into QGIS. – Michael Sheaver Dec 27 '19 at 17:47
  • 1
    @MichaelSheaver that is precisely the problem; creating a mixed geometry table enables QGIS to prefetch geometry stats and prepare a View for each geometry type. In your experiment, the path geometry is not present, but has to be loaded separately as a layer! – geozelot Dec 28 '19 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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