Solved

Extracting geometry from SQL Reader

  • 8 March 2021
  • 5 replies
  • 19 views

Badge

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_addresses

I 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?

 

Screenshot 2021-03-08 at 16.30.25

icon

Best answer by david_r 9 March 2021, 08:18

View original

5 replies

Userlevel 4

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"?

Badge

 SELECT '0101000020E61000000C3C69A7CF178E3FF54274395CBF4940'::GEOMETRY works fine.

 

Postgis stores geometries natively as wkb

Badge +10

I 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

Capture 

 

Userlevel 4

 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

 

Badge

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

 

Reply