Skip to main content
  • EN

Hi there,

Today I noticed something peculiar when working with data from a PostgreSQL DB (that includes the PostGIS extension).

Initially I used the PostgreSQL reader in an SQLExecutor to query for some specific data, and also included the geometry column 'standardized_location’ in the select statement.
That seems to output the geometry column in a ‘Hex Encoded OGC Well Known Binary’ encoding. Using the GeometryReplacer I turned this back into a ~'fme geometry object’. If I then however filtered on the GeometryType, it shows up as a Polygon, where I knew in the PostGIS DB the standardized_location geometry column was defined with type ‘geometry(MultiPolygon, 4258)’.

I then used the ST_AsEWKT(), ST_AsGML() and ST_AsGeoJSON() functions to return the geometry from the database in some different encodings. If I use the GeoJSON or GML encoding in the GeometryReplacer, the geometry does still show up as a MultiPolygon feature. Also the EWKT encoding depicted the geometry as 'MultiPolygon’.

Furthermore, when I performed the same query as before, but now using the PostGIS reader (instead of the PostgreSQL reader) in the SQLExecutor, the 'fme geometry object’ that is created upon including the geometry column 'standardized_location’ in the select statement, also results in a MultiPolygon geometry.

That lead me wondering. Is ‘Hex Encoded OGC Well Known Binary’ encoding maybe not equipped to be able to distinguish between a Polygon geometry and a MultiPolygon geometry?
N.b. this behaviour is probably isolated to MultiPolygon geometries that have only a single part.

Curious if someone maybe knows a bit more on this.

Kind regards,

Thijs

For illustration to above description, see the screenshot below;

 

Is there a reason why you do not use the PostGIS reader? This would give you the geometries immediately.

The PostgreSQL reader does not know about geometries, so you get the geometry as an attribute, as it is stored in the database.

I was testing out a query that included the geometry. Using the PostGIS reader the geometry column doesn't show up in the SQL Editor (in the (contents of the) Database Tables section of the top left).

With the PostgreSQL reader this geometry column does show up, so it was just a bit easier to set up the query.

If I would be interested in fetching the data in FME, I agree that the PostGIS reader would be the way to go. But that's not the case here. I was just wondering why using the GeometryReplacer with the ‘Hex Encoded OGC Well Known Binary’ encoding apparently changes the geometry type. 
I guess it may have something to do with the encoding type?

Small sidenote, I may have been wrong that the geometry column does show up when selecting the PostGIS reader. It seems that those columns don't show up in either the PostgreSQL reader or the PostGIS reader (when using these readers in the SQLExecutor).

There was just something that out of habit leads me to using the PostgreSQL reader first. If I know recall correctly, it's that the PostGIS reader only lists tables and views that actually contain a geometry. Makes sense of course, but if you are querying data to combine geometry tables with non geometry tables, it helps if you have a complete overview of the tables.

Getting back to the main question. Unfortunately I think it's not answered yet. I'm wondering if someone can explain the behaviour I encountered. So how come this happens, and not how could you deal with this in other ways.

In other words, how come that a MultiPolygon geometry that is stored in a ‘Hex Encoded Well Known Binary’ encoding, shows up as a Polygon geometry after the GeometryReplacer?

Is it something related to this type of encoding, or is this something related to the working of FME, or maybe a combination?

 

Reply


Community Stats

30,149
Posts
114,438
Replies
37,792
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.