How to retain the explicit geometry type of a column in a database view of PostGIS?

Question

I try to retrieve "generalized" table's info for a long-term backup project from a PostgreSQL database version 11, with the PostGIS 2.5.3 extension under Freebsd 12.0.

I want to use the PostgreSQL type info to generate the expected info and twist type names and length declarations for a subsequent running parser. With the normal PostgreSQL types (INTEGER, VARCHAR etc. ) it works well.

Unfortunately I cannot retrieve the info of the original type (POINT, LINESTRING, POLYGON etc.) of PostGIS geometries neither for normal nor calculated fields. What do I have to do to get these geometry info for a view?

DETAILS:

To collect these data without bothering the running working processes, I've isolated the content of interest in the schema "backup" with a bunch of database views.

For the backup schema I've created a metadata view that provides a mechanism to rename/ abstract the data type and size info for each column in a table:

CREATE VIEW backup.metadata AS
SELECT
    table_schema AS mt_schema,
    table_name   AS mt_table,
    column_name  AS mt_column,
    data_type, -- SQL92 type name
    udt_name,  -- original pg_type
    -- adopted type names
    CASE 
       WHEN udt_name = 'varchar' AND 
            character_octet_length>  8152 
            THEN 'text'::VARCHAR(16)
       WHEN udt_name = 'varchar' AND 
            character_octet_length<= 8152 
            THEN 'string'::VARCHAR(16)
       WHEN udt_name = 'int2'      THEN 'int16'::VARCHAR(16)
       WHEN udt_name = 'int4'      THEN 'int32'::VARCHAR(16)
       WHEN udt_name = 'int8'      THEN 'int64'::VARCHAR(16)
       WHEN udt_name like 'int'    THEN  'int'::VARCHAR(16)
       WHEN udt_name = 'float4'    THEN  'float32'::VARCHAR(16)
       WHEN udt_name = 'float8'    THEN  'float64'::VARCHAR(16)
       WHEN udt_name = 'timestamp' THEN  'tstamp'::VARCHAR(16)
       WHEN udt_name = 'timestamp' THEN  'tstamptz'::VARCHAR(16)
       WHEN udt_name = 'bool'      THEN  'bool'::VARCHAR(16)
       WHEN udt_name = 'bool'      THEN  'boolean'::VARCHAR(16)
       ELSE udt_name
    END as mt_type, 
    -- adopted field sizes
    CASE
       WHEN udt_name = 'char'
            THEN character_octet_length::VARCHAR(16)
       WHEN udt_name = 'varchar' AND 
            character_octet_length>  8152 
            THEN NULL::VARCHAR(16)
       WHEN udt_name = 'varchar' AND 
            character_octet_length<= 8152 
            THEN character_octet_length::VARCHAR(16)
       WHEN udt_name = 'varchar' 
            THEN character_octet_length::VARCHAR(16)
       WHEN udt_name = 'numeric' 
            THEN numeric_precision::VARCHAR||':'||
                 numeric_scale::VARCHAR)::VARCHAR(16)
    END AS mt_length
 FROM information_schema.columns
 WHERE table_schema  = 'backup';

For certain view poc_geometry, a partial sensor synchronization view for example.

CREATE VIEW backup.poc_geometry AS
SELECT 
 sync_id as id,
 gps_dt,
 gps_tm,
 1::INT AS  cam,
 cam1_id AS imgage,
 x::NUMERIC(8,2) AS utmx,
 y::NUMERIC(8,2) AS utmy,
 z::NUMERIC(8,2) AS alt,
 geo_ct as geo_ct_utm,
 ST_TRANSFORM(geo_ct, 4326) as geo_ct_ll
FROM sync_utm32 ;

and:

SELECT mt_table, mt_column, mt_type, mt_length
FROM backup.metadata_proto 
WHERE mt_table='poc_geometry';

I get the expected result:

  mt_table   | mt_column  | mt_type  | mt_length 
--------------+------------+----------+-----------
 poc_geometry | id         | int32    | 
 poc_geometry | gps_dt     | date     | 
 poc_geometry | gps_tm     | time     | 
 poc_geometry | cam        | int32    | 
 poc_geometry | imgage     | string   | 256
 poc_geometry | utmx       | numeric  | 8:2
 poc_geometry | utmy       | numeric  | 8:2
 poc_geometry | alt        | numeric  | 8:2
 poc_geometry | geo_ct_utm | geometry | 
 poc_geometry | geo_ct_ll  | geometry | 

Here is geo_ct_utm the original field and geo_ct_ll a calculated one. Unfortunately PostGis handles all geometry columns under term/type geometry. The original setup of the source table was:

  SELECT f_table_name as table, 
         f_geometry_column as column,
         srid, type 
  FROM geometry_columns 
  WHERE f_table_name = 'poc_geometry' AND
        f_geometry_column = 'geo_ct';

Result:

    table    |  column   | srid  |    type    
 ------------+-----------+-------+------------
  sync_utm32 | geo_ct    | 32632 | POINT

When I read the table geometry_columns for the view,

  SELECT f_table_name as table, 
         f_geometry_column as column,
         srid, type 
  FROM geometry_columns 
  WHERE f_table_name = 'poc_geometry';

I get only a generalized info for the type and the spatial reference:

    table     |    column    | srid |   type   
--------------+--------------+------+----------
 poc_geometry | geo_ct_utm   |    0 | GEOMETRY
 poc_geometry | geo_ct_ll    |    0 | GEOMETRY
 poc_geometry | st_transform |    0 | GEOMETRY

What can I do, to reach the info beyond the view?

Answer 1

Based on the chapter 9 (Geometries) of the Introduction to PostGIS Workshop (http://postgis.net/workshops/postgis-intro/geometries.html), seems to me like the value stored in the type field of the geometry_columns table is showing the type of the first geometry stored in the referenced table.

To store an explicit type and srid of geometry you need to cast the queried field explicitly. If the geometry is not casted explicitly as any type, the GEOMETRY generic type is stored.

If you are sure that you are querying points (or if you want to cast to points anyway), you can define the creation of the view as follows:

CREATE VIEW backup.poc_geometry AS
SELECT 
 sync_id as id,
 gps_dt,
 gps_tm,
 1::INT AS  cam,
 cam1_id AS imgage,
 x::NUMERIC(8,2) AS utmx,
 y::NUMERIC(8,2) AS utmy,
 z::NUMERIC(8,2) AS alt,
 geo_ct::geometry(Point,32632) as geo_ct_utm,
 ST_TRANSFORM(geo_ct,4326)::geometry(Point,4632) as geo_ct_ll
FROM sync_utm32 ;