SQLExecutor doesn't produce a view with any geometry when it should do
I have many SQLExecutor transformers and for any reason they use to lost the geometries.
The visual preview nor the data inspector show any geometry, only a “There is no geometry in this view” message.
When they should save the results in the next FeatureWriter (in a postgis database) only the alphanumeric fields are correctly writed, but the geom field is fill with nulls.
When I execute the same sql sentence in QGis or pgAdmin I get the geom field with the correct geometries.
I'm not be able to find the fix for this issue. Any idea will be appreciated. Thanks in advance.
Page 1 / 1
Are you querying using the PostGIS (rather than PostgreSQL).
And are you making sure to use the Result Geometry when using PostGIS:
I would similarly observe as @todd_davis points out that the “Combine Geometry” Parameter needs to be “Result Geometry Only” , and the SQL needs to include the Geometry/Geography table field in the SELECT Field/Expression1, Field/Expression2…….Geometry/GeographyField (Eg. “SHAPE”, “geom” etc.) … statement.
Having said that, there are some SQL statements that SQLExecutor will not auto-detect the geometry field/expression as a valid feature geometry. Without extensive testing, the common types that have “failed” for me is where there are multiple/complex statements being executed like using SELECT with a preceding CTE WITH statement, or populating a Table Variable Geometry fields with VALUES() and trying to select from the declared table variable geometry field etc.
I suspect if the SELECT field name or field alias can’t be easily interpreted by SQLExecutor as parsing back to a corresponding physical table name and physical geometry/geography field name, this can also fail, but it should only be for “complex” SQL statements.
I would wish in this case for a SQLExecutor enhancement that allows the user to explicitly state which SQL SELECT Field/Expression to “Treat as the Feature Geometry”……….but in these cases where SQLExecutor does not auto-detect the geometry field/expression then the workaround is:
Expose the geometry field/expression as an Attribute (Which will have an Eg. Binary encoded geometry value)
Transpose this attribute to an FME Geometry with GeometryReplacer
I would wish in this case for a SQLExecutor enhancement that allows the user to explicitly state which SQL SELECT Field/Expression to “Treat as the Feature Geometry”
Agreed. Since my database is fairly wel set up towards 2NF, most of my SQL statements tend to get fairly complex. I’ve given up on relying on the SQL Executor’s (or de SQL Creator’s) dealing with (or not dealing with) geometry, so I tend to select the geometry as WKT, then use a GeometryReplacer and a CoordinateSystemSetter immediately after. This way I always know what to expect…
This could be another workaround for the OP. But yes, if both the SQL Executor and the SQL Creator would have the possibility of selecting which attribute contains the geometry, that would be very helpful.
Are you querying using the PostGIS (rather than PostgreSQL).
And are you making sure to use the Result Geometry when using PostGIS:
todd_davis, Yes, I’m using format: PostGIS with Embed Connection Parameters, and Combine Geometry with “Resul Geometry Only”
This FME Workbench has been working properly, but for any reason it hasn´t worked well since one or two months ago.
I would similarly observe as @todd_davis points out that the “Combine Geometry” Parameter needs to be “Result Geometry Only” , and the SQL needs to include the Geometry/Geography table field in the SELECT Field/Expression1, Field/Expression2…….Geometry/GeographyField (Eg. “SHAPE”, “geom” etc.) … statement.
Having said that, there are some SQL statements that SQLExecutor will not auto-detect the geometry field/expression as a valid feature geometry. Without extensive testing, the common types that have “failed” for me is where there are multiple/complex statements being executed like using SELECT with a preceding CTE WITH statement, or populating a Table Variable Geometry fields with VALUES() and trying to select from the declared table variable geometry field etc.
I suspect if the SELECT field name or field alias can’t be easily interpreted by SQLExecutor as parsing back to a corresponding physical table name and physical geometry/geography field name, this can also fail, but it should only be for “complex” SQL statements.
I would wish in this case for a SQLExecutor enhancement that allows the user to explicitly state which SQL SELECT Field/Expression to “Treat as the Feature Geometry”……….but in these cases where SQLExecutor does not auto-detect the geometry field/expression then the workaround is:
Expose the geometry field/expression as an Attribute (Which will have an Eg. Binary encoded geometry value)
Transpose this attribute to an FME Geometry with GeometryReplacer
Thank you very much for your answers, @bwn@s.jager . I will try manage the result of my select as you are suggesting.
I would similarly observe as @todd_davis points out that the “Combine Geometry” Parameter needs to be “Result Geometry Only” , and the SQL needs to include the Geometry/Geography table field in the SELECT Field/Expression1, Field/Expression2…….Geometry/GeographyField (Eg. “SHAPE”, “geom” etc.) … statement.
Having said that, there are some SQL statements that SQLExecutor will not auto-detect the geometry field/expression as a valid feature geometry. Without extensive testing, the common types that have “failed” for me is where there are multiple/complex statements being executed like using SELECT with a preceding CTE WITH statement, or populating a Table Variable Geometry fields with VALUES() and trying to select from the declared table variable geometry field etc.
I suspect if the SELECT field name or field alias can’t be easily interpreted by SQLExecutor as parsing back to a corresponding physical table name and physical geometry/geography field name, this can also fail, but it should only be for “complex” SQL statements.
I would wish in this case for a SQLExecutor enhancement that allows the user to explicitly state which SQL SELECT Field/Expression to “Treat as the Feature Geometry”……….but in these cases where SQLExecutor does not auto-detect the geometry field/expression then the workaround is:
Expose the geometry field/expression as an Attribute (Which will have an Eg. Binary encoded geometry value)
Transpose this attribute to an FME Geometry with GeometryReplacer
Thank you very much for your answers, @bwn@s.jager . I will try manage the result of my select as you are suggesting.
hey, @bwn @s.jager, the solution worked! Thanks a lot.
