Solved

How to convert ESRI st_geometry to geometry

  • 25 March 2021
  • 3 replies
  • 61 views

Badge +1

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.

icon

Best answer by jimo 25 March 2021, 22:01

View original

3 replies

Badge +2

@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.

Badge +1

@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.

Userlevel 4

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.

Reply