Solved

Cannot copy tables in PostGIS using dyamic reader and writer

  • 19 January 2018
  • 6 replies
  • 12 views
A
Badge

I simply would like to set up a dyamic Reader in PostGIS that will read all the tables I select at runtime, then having a postGIS dynamic writer connected to this reader that would copy the same geometry, schema definition and data from the reader, the name of the table will be the name of the of the original table plus underscore, something like "original_table" => "original_table_".

Though it seems a very simple task to accomplish I tried in many ways but I cannot get FME to do it.

I tried to follow the tutorial

https://knowledge.safe.com/articles/1157/dynamic-workflow-tutorial-destination-schema-as-a.html

but I get first a warning:

Cannot find a matching schema named 'name_of_db_schema.original_table_' in the known set of schemas: 'name_of_db_schema.original_table_. Known schema sources are: 'POSTGIS_1'. Please verify writer feature type configuration.

Then an error message:

POSTGIS Writer: Fanout for feature type 'name_of_db_schema.original_table_' failed to find feature type template 'name_of_db_schema.original_table_'

It looks like FME wants is looking for the schema of the table I would like to create before creating it, which is what FME should do dynamically, without knowing anything about the destination.

Why is that? Can anyone help?

I know it is a very simple task but I am new to FME and I cannot seem to find a solution.

icon

Best answer by takashi 19 January 2018, 10:24

View original

6 replies

A
Badge
These are the settings I have on the writer

 

david_r
Rank
Userlevel 4

FME can create your tables for you, but the target schema will have to be created manually first.

You can e.g. use pgAdmin to create a schema in your database.

takashi
Rank
Userlevel 2
Badge +17

Hi @alex_chris, try setting "fme_feature_type" to the Schema Definition Name in the dynamic writer feature type explicitly.

A
Badge

Hi @alex_chris, try setting "fme_feature_type" to the Schema Definition Name in the dynamic writer feature type explicitly.

Yes, that did it. Thank you

 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @alex_chris, try setting "fme_feature_type" to the Schema Definition Name in the dynamic writer feature type explicitly.

Elaboration. FME identifies a schema definition with it's name (Schema Definition Name) which is unique in a workpace, and usually the schema definition name is equal to the name of the source feature type, from which the schema definition has been derived.

 

By default, the Schema Definition Name in a dynamic writer feature type is set to use the destination feature type name you have set. If you would create a new table with the same name as the source table, it's not necessary to change the default behavior.

 

However, in the question case, you have set the destination table name which is different from the source table. It's the reason why you have to set the Schema Definition Name explicitly.

 

 

B
Badge

Hi,

i have nearly the same Problem.

Copying a postgis table with FME-Desktop works fine. But copying with FME-Server fails.

I use the same Version (2017.3) dwith FME-Desktop and FME-Server

do you have any ideas?

 

287INFORM0.00.2Today at 13:28:43Executing metadata query: 'SELECT f_table_name, f_geography_column, coord_dimension, srid, type FROM geography_columns WHERE f_table_schema = 'ausbildung' AND f_table_name = 'beispiel'' 288INFORM0.00.2Today at 13:28:43Executing metadata query: 'SELECT f_table_name, f_geometry_column, coord_dimension, srid, type FROM geometry_columns WHERE f_table_schema = 'ausbildung' AND f_table_name = 'beispiel'' 289INFORM0.00.2Today at 13:28:43Executing metadata query: 'SELECT r_table_name, r_raster_column, '2', srid, 'RASTER' FROM raster_columns WHERE r_table_schema = 'ausbildung' AND r_table_name = 'beispiel'' 290ERROR0.00.2Today at 13:28:43Error executing SQL command ('select * from "ausbildung"."beispiel" where false limit 0'): 'ERROR: relation "ausbildung.beispiel" does not existLINE 1: select * from "ausbildung"."beispiel" where false limit 0 294WARN0.00.2Today at 13:28:43No spatial column was found for 'ausbildung.beispiel'. This is most likely a PostgreSQL table with no spatial data. No geometry will be read. If spatial data is not present, please try using the PostgreSQL reader instead 295WARN0.00.2Today at 13:28:43PostGIS geometry type unknown or empty (). Setting PostGIS geometry to 'postgis_none' 296ERROR0.00.2Today at 13:28:43Error executing SQL command ('select * from "ausbildung"."beispiel" where false limit 0'): 'ERROR: relation "ausbildung.beispiel" does not existLINE 1: select * from "ausbildung"."beispiel" where false limit 0,

Hi,

i have the same Problem. Reading and writing a postgistable dynamically works fine with fme-desktop, but if i run this workspace with FME-Server (2017) it fails.

fme-Server and FME-Desktop : same Version

Same postgis-db, same data

part of the fme-server log:

287INFORM0.00.2Today at 13:28:43Executing metadata query: 'SELECT f_table_name, f_geography_column, coord_dimension, srid, type FROM geography_columns WHERE f_table_schema = 'ausbildung' AND f_table_name = 'beispiel'' 288INFORM0.00.2Today at 13:28:43Executing metadata query: 'SELECT f_table_name, f_geometry_column, coord_dimension, srid, type FROM geometry_columns WHERE f_table_schema = 'ausbildung' AND f_table_name = 'beispiel'' 289INFORM0.00.2Today at 13:28:43Executing metadata query: 'SELECT r_table_name, r_raster_column, '2', srid, 'RASTER' FROM raster_columns WHERE r_table_schema = 'ausbildung' AND r_table_name = 'beispiel'' 290ERROR0.00.2Today at 13:28:43Error executing SQL command ('select * from "ausbildung"."beispiel" where false limit 0'): 'ERROR: relation "ausbildung.beispiel" does not existLINE 1: select * from "ausbildung"."beispiel" where false limit 0 294WARN0.00.2Today at 13:28:43No spatial column was found for 'ausbildung.beispiel'. This is most likely a PostgreSQL table with no spatial data. No geometry will be read. If spatial data is not present, please try using the PostgreSQL reader instead 295WARN0.00.2Today at 13:28:43PostGIS geometry type unknown or empty (). Setting PostGIS geometry to 'postgis_none' 296ERROR0.00.2Today at 13:28:43Error executing SQL command ('select * from "ausbildung"."beispiel" where false limit 0'): 'ERROR: relation "ausbildung.beispiel" does not existLINE 1: select * from "ausbildung"."beispiel" where false limit 0

do you have any idea?

Reply


Community Stats

28,709
Posts
109,482
Replies
36,556
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.