Hi all,
I'm trying to read from a Postgresql DB (postgis) applying a spatial filter using the SQLCreator transformer.
Here's the sql statement set in the transformer:
SELECT capture_start, capture_end, metadata_url, status, project, geom FROM "public"."Metadata_Index" WHERE ST_Intersects(ST_Transform(ST_GeometryFromText('POINT(142.94 -37.28)',4326),7844), geom)
This is the error I'm getting - it's logged as a warning but definitely what is failing the process:
2021-06-23 13:07:41| 1.7| 0.0|WARN |Error executing SQL command ('declare "public_custom_query_crsr" cursor for SELECT capture_start, capture_end, metadata_url, status, project, geom FROM "public"."Metadata_Index" WHERE ST_Intersects(ST_Transform(ST_GeometryFromText('POINT(142.94 -37.28)',4283),7844), geom)'): 'ERROR: GetProj4StringSPI: Cannot find SRID (7844) in spatial_ref_sys
'
I've read through the docs available on the Safe website/forum and couldn't find answers but I did find this mentioning the upgrade to PROJ version 6 in FME 2020.
https://community.safe.com/s/article/gdalproj-library-updates-affect-coordinate-system
After doing some reading on the PROJ site, it seems that complex transformation such as those associated with going from WGS84 to GDA2020 should be covered by PROJ 6 so thought what I'm trying should be possible in FME 2020.
https://proj.org/development/migration.html?highlight=gda2020
I also tested using GDA94 (EPSG:4283) instead of EPSG:4326 in the sql to see if it was an issue with the multi-step transform required when going from 4326 to 7844 but got the same error.
Not sure if the reference to GetProj4StringSPI in the error means it's still using Proj4...
Does anyone know what's going on here?