QGIS – Adding a GIS Feature to Existing Database Entry with QGIS

databasepostgispostgresqlqgisshapefile

I've built a database with postgreSQL with tables, which include geometry columns.
When I add some data to my non-geometry columns, I can see them in QGIS when I connect to my database using postgis. Now, I want to add polygons to the existing entries with the editing tool in QGIS. That means, filling up the geometry columns in QGIS.

How can I do this? I've only heard about adding whole shapefiles to the database but not to add single features to a single column.

An example… I have the following table (municipality):

id smallint NOT NULL,
name varchar(50) NOT NULL,
mun_geom geometry(Polygon, 31468)

I can fill the columns id and name in postgresql when I'm connected to my database (like (1,'municipality1')). After adding municipality as a postgis layer I want to complement the entry with adding a polygon feature.

Best Answer

Figured this out! (also asked here)

A record in a spatial table (Shapefile, PostGIS, Spatialite) that has no geometry can have geometry added by adding a 'part' to the feature using the advanced digitizing toolbar. Here's the workflow:

Bring table containing the geometry-missing feature into QGIS
Select the layer from the layers panel and start an edit session on that layer
Right-click the toolbar area of QGIS (where there is no button) and open the advanced digitizing toolbar
Open the attribute table of the layer and select the feature that is missing geometry
On the advanced digitizing toolbar, click the add part tool
You can now add the geometry of the feature (point, line, or polygon) as you would add any new feature to a table
Save your edits, and stop editing. The feature now has geometry!

Related Solutions

[GIS] How to cast geometry to varbinary in PostGIS / PostgreSQL

They're two different representations of the same binary data. SET bytea_output = 'hex' will switch to the hex representation that it sounds like you want.

Here's a couple of examples:

postgres=> SET bytea_output='escape';
postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry);
                              st_asbinary                              
-----------------------------------------------------------------------
 \001\001\000\000\000\315\314\314\314\314L8@\232\231\231\231\231\331D@
(1 row)

and

postgres=> SET bytea_output to 'hex';
postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry);
                 st_asbinary                  
----------------------------------------------
 \x0101000000cdcccccccc4c38409a99999999d94440
(1 row)

[GIS] Duplicate entries in table list when adding PostGIS layer in QGIS

QGIS is happiest with PostGIS layers when they have complete information in the geometry_columns view. To achieve this state, you have to make sure the geometry columns are created with useful metadata. This kind of issue usually comes up with tables created using CREATE TABLE AS SELECT ... style queries.

Try something like this:

CREATE TABLE foo AS 
SELECT ST_Union(geom)::Geometry(Point, 4326), gid 
FROM bar;

Note that in defining the geometry output of the query, we are very specifically telling the system what kind of geometry to expect. This will get stored in the system catalogues and then reflected in the geometry_columns table, which will then make QGIS happy.

You can also update columns after the fact with an ALTER TABLE query:

ALTER TABLE foo
ALTER COLUMN geom type Geometry(Point, 4326) USING geom::Geometry(Point, 4326)

Note we also included a primary key, gid. This is something you might have to add by hand afterwards, using an ALTER TABLE to define it as a primary key.

Best Answer

Related Solutions

[GIS] How to cast geometry to varbinary in PostGIS / PostgreSQL

[GIS] Duplicate entries in table list when adding PostGIS layer in QGIS

Related Question