Can I load a PostGIS View in QGIS, that contains joins?

[المشرف العام]

I have a geometry table (points) on PostGIS, containing several facilities:

Table facility:

• facility_id | bigint | not null
• facility_address | character varying(500) |
• facility_city | character varying(100) |
• facility_district | character varying(100) |
• ...

and so on...The facility categories are stored on this table:

Table category:

• category_id | bigint | not null
• category_code | character varying(200) | not null
• category_name | character varying(200) | not null

And there is a third-table that links the two:

Table category_facility:

• category_id | bigint | not null
• facility_id | bigint | not null

Now I want to create a view, to display the facilities (and their categories!) in QGIS. For that I use a sequence of inner joins:

create view v_facility_category as select facility.facility_id as id, facility_name, facility.geom, category.category_name from facility inner join category_facility on category_facility.facility_id=facility.facility_id inner join category on category_facility.category_id=category.category_id;Notice that the field id, contains a numerical id.

When I try to add the view in QGIS:

, I get an error message. See the log bellow:

(...) sslmode=allow key='id' srid=4326 type=POINT table="public"."v_facility_category" (geom) sql= is an invalid layer - not loadedI have to add that there are no problems, if I add a Table created exactly with the same query, so there should be nothing wrong with the geometry.

create table facility_category as select facility.facility_id, facility_name, facility.geom, category.category_name from facility inner join category_facility on category_facility.facility_id=facility.facility_id inner join category on category_facility.category_id=category.category_id;I cannot think of anything else that may create a problem, apart from the joins persisted in the view?



أكثر...