SQLExecutor in FME2024.1 doesn't show Geometry of PostGIS raster, in contrary with FME2023.1

In the workspace 2024-1, the Where-clause of the FeatureReader contains a full SELECT statement, which I suspect is not supported (or necessary).

Try only giving the Where-clause, without the semi-colon at the end:

"seq_model_id" = 92 and "co2_ppm" = 540 and "raster_type_id" in (7,23) and "level_m" = 0

This still outputs 11030 rasters. It was something we did try.

Hi ​@moustik, I think this may be a bug but I’m having trouble recreating your issue. Would you be able to attach your PostGIS_raster_2024-1 workspace as a .fmwt so that I can take a look? You can do this by running your workspace with feature caches enabled, and then navigating to File > Save As Template and making sure that Include Feature Caches is checked. 

In the meantime, does the PostGIS Raster reader work for you? You can still specify a WHERE Clause by going into the Parameters. 

Hello ​@saraatsafe and Happy New Year.
Sorry to have just seen this now.
You will find attached a copy of the fmwt workbench.

The PostGIS Raster reader outputs all the rasters and not the only two we want.

Thank you

Sébastien

Greetings ​@saraatsafe . I’m working on the same project with ​@moustik and we are still completly stuck.

I recently confirmed that the same settings work perfectly in 2023.1.0.0 (Build 23619) but is failing in 2024.1.3, 2024.2.3 and the latest 2025 beta ( both downloaded and tested on the 07th of February).

When reading postgis rasters, SQLExecutor reads everything but the geometry. If you run with featurecaching on, it will appear as successful but clicking on the cache, will throw an error about missing geometry. This does not work.

Doing the same with a featurereader can only read every single row of the table. We tried almost any type of “where” clause and with different syntax (single quotation marks, double, etc). So you are forced to read the whole table before applying a tester. With multiple thousands of rows of very extensive and heavy rasters, this tends to kill the whole process, making it unusable.

We are therefore currently paralysed. Can you please help? Many thanks!!

Hi ​@moustik & ​@benji, 

Thanks both for the additional info! I did a little bit of digging and found a ticket that describes the almost the same issue as yours-- when using a PostGIS SQLCreator, geometry_type = fme_undefined and fme_type = fme_no_geom. 

I’ll add a note in the ticket to help add motivation (internal reference: FMEENGINE-85078) and will update this thread when it’s resolved. As a workaround for now, I was able to find success by specifying the geometry column in my SQL statement.

This is my example SQL statement: 

SELECT "id", "geom" AS geom_type
FROM "public"."PublicArt"
WHERE "id" IN (316);

I hope this helps!

Hello ​@saraatsafe,

Thank you for your answer and your suggestion.
Sadly the suggested workaround is not solving our problem :

• using “geom” as geom_type :

then results are rejected, which is expected as the PostGIS table doesn’t contain a column named geom (it is the rast column)

• using our “rast” column as geom_type 

which returns the following error:

Hi ​@saraatsafe .

Can I please request an update on this major issue? Basically a part of our company, which heavily uses FME, is prevented to use any FME version younger than 2023 for multiple workflows, while we use 2024 (desktop and cloud) for others. We really need to see this issue fixed -- both the SQLExecutor and the feature reader but the former is even more important for us.

Can you please let us know when a fix will be made available?

Many thanks for this,

Benji

Hi ​@benji & ​@moustik, 

It looks like the issue is being worked on now and will hopefully be done soon-- apologies that I’m not able to provide a concrete timeline but I’ll see if I can get it expedited a bit. 

Are you able to clarify if you’d be able to update to FME 2025.x in order to access the fix, or are you locked into using FME 2023 & 2024? 
Thank you both for your patience on this! 

Hi ​@saraatsafe ,

Not that we are locked onto 2023/24 as we will eventually move to FME2025 but this is generally a move done company-wide. The ideal would be to have a fix in 2024 as FME2025 is still to receive its first stable version. But a fix in FME2025 would be far better than status quo, which represents a major blocker for our content generation workflows. It would be really appreciated if you can get it expedited indeed.

Hoping this helps,

Thanks!

Hi ​@benji & ​@moustik, I touched base with the devs working on this issue and you can expect a fix in FME 2025.0.1 sometime this spring. Thanks again for your patience on this!

Hi ​@saraatsafe . That is a good news indeed, I now can’t wait to move to a version without these blocking issues. Looking forward to this next version! Thank you for the support! 

Hi ​@saraatsafe .
I got pretty optimistic seeing yesterday that FME 2025.0.1 Build 25220 was containing the following correction: 

which I thought corresponded to your accounce of 2025.0.1 fixing the issue of reading (with their geometry/rast) rasters stored in postgis, whether it is through SQLExecutor (so far failing extracting the geometry) or FeatureReader (failing to apply any “where” clause) but… no. Nothing is fixed on this front, unfortunately.

Can I please enquire on the latest plans on this fix? It’s really not efficient for us to be split between 2 versions of FME (2024.1 and 2023.1). Thank you!

​@moustik FYI

Hi ​@benji & ​@moustik, 

Apologies for the delayed response! I confirmed with dev that the above fix does not cover rasters-- my apologies with that, I had thought that all geometries were included. We’ll file another issue to include rasters in the fix. (Internal reference: FMEENGINE-86599)