Dear FME community,
Problem description: Attempting to run an inner table join between two tables using SQL within the FME PostGIS reader (i.e. under the "Format Parameters" tab in the "SELECT statement" form) does not output the correct number of records expected; in contrast to running the same query in the PgAdmin III Query window which outputs the expected number of records.
Data: I use the Ordnance Survey (OS) AddressBase Premium data (EPOCH 36) which are stored in nine tables according to the OS documentation within a test environment PostGIS 9.4 database (localhost). A "geom" column including not null values and representing points is stored within the blpu table while the other tables although they have a "geom" column the values stored in them are null.
SQL statement:
SELECT DISTINCT dvp.uprn, dvp.dependent_locality, dvp.double_dependent_locality, blpu.geom
FROM public.abp_delivery_point AS dvp, public.abp_blpu AS blpu
WHERE dvp.uprn = blpu.uprn AND dvp.dependent_locality != ''Feature Type Properties: "Merge Feature type" is ticked under "General Tab".
Expected outcome: The SQL statement returns 832101 records in PgAdmin III in contrast to FME returning the whole lot found in the "dvp" table (i.e. 246303 records).
Have other users come across a similar problem using SQL statements and inner joins in the PostGIS or PostgreSQL readers? I suspect that the "Merge Feature Type" option should be not ticked... but how would you do an inner join within the FME reader otherwise?
Any help will be greatly appreciated.
