Skip to main content
Solved

how to read all tables in a PostGIS schema ?

  • June 20, 2013
  • 8 replies
  • 160 views
U
Forum|alt.badge.img
Hi,

 

 

I'm looking for a way to read all the tables which are in one schema without having to select them manually.  I have to export all the table which are stored in one particular schema in spatialite. New tables can be added end old one deleted. That's why I need something automatic...

 

 

 

Does anyone have a clue ? 

 

 

Thank's

 

 

 

Best answer by uccelli

Ok, I finally manage to do it with python script... thank's
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

8 replies

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • June 20, 2013
Hi,

 

 

two possibilities, either dynamic schema or a combination of the reader "Schema (any format)" and a FeatureReader.

 

 

David
U
Forum|alt.badge.img
  • uccelli
  • Author
  • 8 replies
  • June 21, 2013
Still trying to find out this problem. As soon as I add a postgis reader I have to select at least one table. Selecting all table is not what I want because if I select another schema, the table structure will be different. 

 

 

Does "Dynamic schema" mean "single Merges Feature type" ? how to select only a postgis schema and not a table ?

 

 

If I chose "Schema (any format)", I can apparently add only directories or files in the dataset. How can I select the postgis database ?

 

 

Cheers 

 

 

 

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • June 21, 2013
Hi,

 

 

I do not know of any very simple way of doing this, you will probably have to be a bit creative.

 

 

To get a list of all the tables with a geometry column in PostGIS ("feature classes", if you will), you can use an SQLCreator with the following:

 

 

select array_to_string(array_agg(f_table_schema || '.' || f_table_name), ':') as featurelist from geometry_columns

 

This will create an attribute "featurelist" with a colon-separated list of all the tables, suitable for the FeatureReader. Hopefully you can take it from there.

 

 

David
U
Forum|alt.badge.img
  • uccelli
  • Author
  • 8 replies
  • Best Answer
  • June 26, 2013
Ok, I finally manage to do it with python script... thank's
A
  • abdelandre
  • 2 replies
  • July 5, 2013
Hi,

 

any chance you share the python script you used, i'am trying to do the same but linked as a parameter for filtering schemas without to load all the table

 

Arnaud
U
Forum|alt.badge.img
  • uccelli
  • Author
  • 8 replies
  • July 15, 2013
hi Arnaud,

 

 

Give me your email adress, I'm gonna send you the workbench.

 

 

Cheers
K
Forum|alt.badge.img
  • ken
  • 48 replies
  • July 17, 2014
I realize this is a year after the fact but I had to solve this for another user and just noticed this post. Here are the steps that worked for me using the approach suggested by David R.
  1. SQLCreator transformer can get all of the PostGIS tables names with the query: select * from geometry_columns
  2. Add an AttributeExposer transformer to expose the attribute: f_table_name
  3. Add a FeatureReader transformer which can then "Query the Feature Types based on the attribute below" - use the attribute f_table_name
  4. Using the menu add a "Reader > Add Reader as Resource" to get a Workspace Resource for your PostGIS database, this is needed to provide schema for the dyanamic writer
  5. The destination feature type must be set to Dynamic so that it can write any table."Schema Sources" will be your PostGIS Workspace resource, and both "Feature Type Name" and "Schema definition come from the attribute f_table_name
rimonku
Contributor
Forum|alt.badge.img
  • rimonkuContributor
  • Contributor
  • 1 reply
  • August 6, 2019
Ok, I finally manage to do it with python script... thank's

Hi @uccelli!

I would be very much thankfull if i can get your script. I am trying something alike to read all the tables from sql server. Thanks in avdance.

 

Rimon

 

 

 

Community Stats

32,079
Posts
121,818
Replies
39,714
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.