Solved

"Join" SQL statement in PostGIS reader

  • 2 December 2015
  • 5 replies
  • 16 views
D
Badge


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.

icon

Best answer by dimmihel 2 December 2015, 12:53

View original

5 replies

D
Badge

I managed to make the inner join query described above to return the same number of records as in PgAdmin III using the "SQLCreator" transformer. I am not sure whether I still need to keep this question open in case someone has successfully executed an inner join using the PostGIS reader.

fmelizard
Rank
Userlevel 4
Badge +13

I managed to make the inner join query described above to return the same number of records as in PgAdmin III using the "SQLCreator" transformer. I am not sure whether I still need to keep this question open in case someone has successfully executed an inner join using the PostGIS reader.

Sounds like a bug in our PostGIS reader and I'll ask the team to check into it. But honestly I think SQLCreator is a better way to go in any case -- I'd argue it is clearer what is going on and easier to get to the "meat" of the query.

D
Badge

Sounds like a bug in our PostGIS reader and I'll ask the team to check into it. But honestly I think SQLCreator is a better way to go in any case -- I'd argue it is clearer what is going on and easier to get to the "meat" of the query.

Agreed. The "SQLCreator" feels more like a means of communication with the database in contrast to the PostGIS reader which is running more sophisticated processes in the background and feels more like a semi-transparent box of processes sometimes. It is worth mentioning that any simple query that I have tried using one table only within the PostGIS reader works fine.

M
Badge +2

I think this would work for you if you don't use "Merge Feature Type". When you add the feature type, select one of the tables that is present in your query,

i.e. public.abp_delivery_point. Then add your SELECT Statement in the feature type parameters. This should give you the correct result, but also has the advantage of exposing some of the attributes.

D
Badge

I think this would work for you if you don't use "Merge Feature Type". When you add the feature type, select one of the tables that is present in your query,

i.e. public.abp_delivery_point. Then add your SELECT Statement in the feature type parameters. This should give you the correct result, but also has the advantage of exposing some of the attributes.

Hi Mark,

I haven't had the time to test your solution but I assume that it should work.

Thanks for your time and effort.

Reply


Community Stats

28,707
Posts
109,476
Replies
36,551
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.