Postgis table design for creating and editing in QGIS

Question

I am testing a postgis table (and schema) design to capture wind farm project information, so GIS (and non-GIS) data can be created/updated in QGIS.

In the current table design I have a nullable POINT column to define location. But I would like to also have a nullable column to optionally record multiple POLYGONS to define the occupied area by each wind farm.

And if possible a nullable column to record multiple points to define the location of individual wind turbines for each wind farm. (Though I am pretty sure this would be better done via a separate table with a FK link)

Two issues I have encountered so far:

• the GEOMETRYCOLLECTION datatype does not appear to be supported in QGIS postgis-intro geometries
• after adding a separate nullable column for POLYGON datatype records in pgadmin, when importing (linking) to the table in QGIS, rows with NULL values do not appear in the QGIS Attribute Table and so can't be updated.

Answer 1

tl;dr

Define one primary geometry type per windfarm (like multipolygons). Store the other geometries (like points) as representations and update them, based on the original geoms. Avoid nullable and mixed geometries, as they are hard (impossible?) to handle in QGIS.

Long story

Let's take a look at traditional geodata-formats and how a GIS handels them: real world objects (rwo) are stored as features, which describe them using one geometry and a set of attributes. Usually, all features are described by the same type of geometry. In your case, every windfarm could be a polygon. A set of features is stored in a format like shapefile, geodatabase feature class or postgis table.

Usually these formats require one geometry type using one coordinate refrence system per feature class. It is unusual (and mostly impossible) to store different geometry types in one feature class. That's very important, because a lot of GIS functionality (symbology, analyses) depend on the geometry type. This also leads to the fact, that geometrycollections are hard to handle, as they could contain points, lines or polygons.

The geometry datatype in postgis stores all features as eWKT, which contains the srid, geometry type and geometry per feature (and not per table, if it would follow the "traditional" GIS approach). Thus it's possible to store a polygon in WGS84 and a point in UTM32 in the same column. As shown before, this is not a practical way, if you want to use this column in QGIS. Therefore one defines their geometry columns and limits the allowed geometry type and coordinate systems:

CREATE TABLE windfarm(
  id serial primary key,
  geom geometry(POINT, 4326),
  name text,
  installed_at date
)

As you mention, that you would like to have multiple polygons per windfarm, you could use a multipolygon. It describes one rwo using one or more polygons. (As an example think about countries: you need a lot of polygons to cover greece and its islands - but only one polygon to represent hungary.) If a road divides a windfarm into two pieces - store them as multipolygons.

In a GIS, geometry and attributes are tighly coupled and it is unusual and mostly impossible to store features without geometries. But it's possible to do in PostgreSQL.

Storing the windfarms as point or polygon can have two reasons:

One could be concerns about representation: In a large scale map (like 1:10.000) you could show the polygons and at smaler scales (like 1:75.000) only a point. If you want to show polygons and points, based on scale: always store the windfarms as polygons and create their centroids if needed.

Another reason: You receive the data from different sources, which store the windfarms as point or polygon. In this case store the windfarms as polygons. If you get a point, buffer it by 100 meter and store the buffered geometry.

If you don't like the buffered point approach, you could primarly store points and store the polygons as representations:

Another, unusual but possible, solution:

PostgreSQL allows, in some cases, UPDATE and INSERT on views. This could lead to the following approach:

CREATE TABLE mixedgeoms(
    id integer primary key,
    geom geometry(GEOMETRY, 4326)
);

INSERT INTO mixedgeoms (id, geom) 
VALUES 
(1, 'SRID=4326;POINT(8 50)'::geometry),
(2, st_Buffer('SRID=4326;POINT(8.05 50.05)'::geometry, 0.025));

CREATE VIEW polys AS 
SELECT id, geom
FROM mixedgeoms
WHERE  ST_GeometryType(geom) = 'ST_Polygon';

CREATE VIEW points AS 
SELECT id, geom
FROM mixedgeoms
WHERE  ST_GeometryType(geom) = 'ST_Point';

It is possible to load the views into QGIS, edit them separately and store them back in one column. IF you want to try this, uncheck and check the id column.

Not sure, if this helps :-)