I have an SQL reader the loads json and a geometry from a postgis table
SELECT $SQL$@Value(schema)$SQL$ AS schema,
attributes,
wkb_geometry
FROM @Value(schema).features_addressesI have to do this as the database connection is dynamic as is the schema and cannot be fixed in a Reader.
My question is how do I extract the geometry as an FME geometry so it can be passed to a FeatureWriter?
I tried using a GeometryReplacer and WKB format but FME rejects the WKB even though it is valid?
Best answer by david_r
SELECT '0101000020E61000000C3C69A7CF178E3FF54274395CBF4940'::GEOMETRY works fine.
Postgis stores geometries natively as wkb
I agree with @ebygomm , I also suspect that it's the datatype. If you look at the ST_AsEWKB() function in PostGIS, it returns a bytea column, not a geometry or varchar-style column. If you do the same thing it should work in FME.
Suggestion:
SELECT $SQL$@Value(schema)$SQL$ AS schema,
attributes,
ST_AsEWKB(wkb_geometry) as wkb_geometry
FROM @Value(schema).features_addresses
I use that technique all the time and I've never had any problems with it. How do you know that the WKB is valid? What datatype is the column "wkb_geometry"?
SELECT '0101000020E61000000C3C69A7CF178E3FF54274395CBF4940'::GEOMETRY works fine.
Postgis stores geometries natively as wkb
+44I think the issue is the datatype of the column, try putting a BinaryDecoder in to get the attribute containing the geometry to be encoded as fme-binary before sending it into the GeometryReplacer
SELECT '0101000020E61000000C3C69A7CF178E3FF54274395CBF4940'::GEOMETRY works fine.
Postgis stores geometries natively as wkb
I agree with @ebygomm , I also suspect that it's the datatype. If you look at the ST_AsEWKB() function in PostGIS, it returns a bytea column, not a geometry or varchar-style column. If you do the same thing it should work in FME.
Suggestion:
SELECT $SQL$@Value(schema)$SQL$ AS schema,
attributes,
ST_AsEWKB(wkb_geometry) as wkb_geometry
FROM @Value(schema).features_addresses
Thanks for the help.. I fixed it with a CAST in the SQL
SELECT $SQL$@Value(schema)$SQL$ AS schema,
attributes,
ST_ASEWKB(wkb_geometry) AS wkb_geometry
FROM @Value(schema).features_addresses