Viewing results of ST_Difference

Question

I've got an ST_Difference query runnning error free in PostGIS, and in PGAdmin I can see the output table but it's not viewable in QGIS - The table name appears in DB Manager but says "There is no entry in Geometry Columns".

What do i need to do to view the results?

Here's my query:

DROP TABLE IF EXISTS processing.newdifference;

CREATE TABLE processing.newdifference AS 
SELECT 1 AS gid, ST_Difference(tdhab.geom, tdbuf.geom) AS "diff_geom" 
FROM processing."tdhab", processing."tdbuf";

Any pointers?

Answer 1

I usually don't populate any records in geometry_columns table anymore. You must verify if you have checked the option "Only look in the geometry_columns table" in the "add PostGIS connection" dialog.

If you have already opened a connection previously configured (without "Only look in the geometry_columns table" option) you need to reconnect to see a newly created table (or view) in the available themes / tables list.

If your newly created table is in "processing" schema (as it seems), you surely MUST uncheck the option "Only look in the ‘public’ schema" in the connection setup dialog.

Answer 2

Depending on how a table is created you sometimes need to add the new column to the geometry_columns view. This is done with the Populate_Geometry_Columns function.

SELECT Populate_Geometry_Columns('processing.newdifference'::regclass);

For more information see http://postgis.net/docs/Populate_Geometry_Columns.html