Fetching the geometry and attributes from PostgreSQL Using FME Workbench

Question

I have 2 fetching administrative areas one inside the other. For that, I am having servers to get the data.

When I am running anyone query in SQLCreator, it is working fine separately, but while using union the output is only from the first query. Any comment on this about where am I going wrong?

--admin area 0a
(select na.*,aa.geom, aa.name as A09, feat_type, a9_admin_id, a8_admin_id, a7_admin_id,a6_admin_id, a5_admin_id, a4_admin_id, a3_admin_id, a2_admin_id, a1_admin_id from x.mnr_admin_area aajoin x.mnr_admin_area2nameset an on aa.a9_admin_id = an.admin_area_idjoin x.mnr_nameset2name nm on an.nameset_id = nm.nameset_idjoin x.mnr_name na on nm.name_id = na.name_idwhere aa.country='IND'  and  na.iso_lang_code='ENG' and aa.feat_type = '112')
union
--admin area 0b
(select na.*,aa.geom, aa.name as A08, feat_type, a9_admin_id, a8_admin_id, a7_admin_id,a6_admin_id, a5_admin_id, a4_admin_id, a3_admin_id, a2_admin_id, a1_admin_id from x.mnr_admin_area aajoin x.mnr_admin_area2nameset an on aa.a8_admin_id = an.admin_area_idjoin x.mnr_nameset2name nm on an.nameset_id = nm.nameset_idjoin x.mnr_name na on nm.name_id = na.name_idwhere aa.country='IND'  and  na.iso_lang_code='ENG' and aa.feat_type = '111')

• Mark Ireland: "I have 2 fetching administrative areas" - two what? Workspaces? Transformers? Readers?
• Mark Ireland: Or are you "fetching two administrative areas"?
• GforGIS: Attaching your workspace or a screenshot would be very helpful.

Answer 1

Is there a reason you're using SQL and the SQLCreator? Normal FME practice would be to handle that sort of query/merge using transformers such as the FeatureReader, SpatialFilter, Clipper, etc.

One of the reasons for doing that is that FME has a lot of data inspection tools built into it, for checking the data at each step of the process. But if you bundle everything inside a SQL statement, you lose all those capabilities.

So maybe add a PostGIS/Postgres reader and adjust the where clauses to read the required data (or just add the reader then filter data with a Tester), then carry out a union with either a Dissolver or Aggregator transformer (I'm not entirely sure what the end result of a PostGIS union is meant to be)? That way you could check the results step-by-step.