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?
type
column of thegeometry/geography_columns
View does specify the geometry type (e.g.POINT
,LINESTRING
etc.) explicitly...you just didn't specify the type explicitly when creating those columns (or so I believe). instead, the genericGEOMETRY
(not to be confused with the PostGIS data type!) is used by default. you also didn't specify a CRS (SRID
), so it's set to0
by default!ST_SETSRID(ST_TRANSFORM(geo_ct, 4326),4326)
takes no effect on thegeometry_columns
. I cannot find an explicit type cast mechanism for Postgis types.geo_ct::geometry(Point,32632) as geo_ct_utm
andST_TRANSFORM(geo_ct, 4326)::geometry(Point,4326) as geo_ct_ll
is enough. But this is not my especiality, please check.