I have been able to connect with FME directly to the PostgreSQL database that holds the ArcGIS Portal host feature services. The features are stored as tables with a "shape" field holding the geometry. However, I can't figure out how to convert that shape field, which is labelled "st_geometry" into actual geometry. FME writes it to the data inspector as a text, for example, 280000000100000001000400E193040010000000010000009587C3B5B486AD0380BFE7BF9590AC13.
Thanks for your help.
Best answer by Anonymous
@Jim O'Leary are you using the Esri Geodatabase (ArcSDE Geodb) reader to access the database? Or you could probably use the Esri ArcGIS Portal Feature Service reader. The PostgreSQL or PostGIS readers won't recognise the st_geometry and will probably just read them as a blob.
Actually I found that I could use an SQLExecutor and the sde.st_totext function to transform the shape field to WKT like this:
select name,address, sde.st_astext(shape) as mypoint
from hsu_ds6o9.fire_halls12_fire_halls
Then I use the GeometryReplacer to replace the OGC Well Known Text to geometry.
The point doesn't know where it is, but it is actually in Web Mercator so I use a CoordinateSystemSetter to tell it that it is in EPSG:3857. Then because I want it in UTM83 10, I use the CsmapReprojector to reproject it to EPSG:26910.
I think I tried the Esri Geodatabase reader without success. I don't think the ArcGIS Portal Feature Service reader would work because it is not a feature service where I access it, just a table in a PostgreSQL database.
@Jim O'Leary are you using the Esri Geodatabase (ArcSDE Geodb) reader to access the database? Or you could probably use the Esri ArcGIS Portal Feature Service reader. The PostgreSQL or PostGIS readers won't recognise the st_geometry and will probably just read them as a blob.
@Jim O'Leary are you using the Esri Geodatabase (ArcSDE Geodb) reader to access the database? Or you could probably use the Esri ArcGIS Portal Feature Service reader. The PostgreSQL or PostGIS readers won't recognise the st_geometry and will probably just read them as a blob.
Actually I found that I could use an SQLExecutor and the sde.st_totext function to transform the shape field to WKT like this:
select name,address, sde.st_astext(shape) as mypoint
from hsu_ds6o9.fire_halls12_fire_halls
Then I use the GeometryReplacer to replace the OGC Well Known Text to geometry.
The point doesn't know where it is, but it is actually in Web Mercator so I use a CoordinateSystemSetter to tell it that it is in EPSG:3857. Then because I want it in UTM83 10, I use the CsmapReprojector to reproject it to EPSG:26910.
I think I tried the Esri Geodatabase reader without success. I don't think the ArcGIS Portal Feature Service reader would work because it is not a feature service where I access it, just a table in a PostgreSQL database.
Actually I found that I could use an SQLExecutor and the sde.st_totext function to transform the shape field to WKT like this:
select name,address, sde.st_astext(shape) as mypoint
from hsu_ds6o9.fire_halls12_fire_halls
Then I use the GeometryReplacer to replace the OGC Well Known Text to geometry.
The point doesn't know where it is, but it is actually in Web Mercator so I use a CoordinateSystemSetter to tell it that it is in EPSG:3857. Then because I want it in UTM83 10, I use the CsmapReprojector to reproject it to EPSG:26910.
I think I tried the Esri Geodatabase reader without success. I don't think the ArcGIS Portal Feature Service reader would work because it is not a feature service where I access it, just a table in a PostgreSQL database.
This is the solution I would have suggested also. You could also consider using sde.ST_AsBinary() to convert to WKB, then set the GeometryReplacer to OGC Well-Known Binary. It is a bit more efficient than WKT.
