Skip to main content
  • EN

When using SQL Executor or SQL Creator with a PostGIS reader on our Postgres datawarehouse I run into problems with spatial queries.

Example given this query:

select 
    table1.name
  , table2.whatever
  , table1.geometry
from table1
join table2 on ST_Intersects(table1.geometry, table2.geometry)

this works only if the two tables are of a different geometry type (e.g. Polygons and Points). If they are both Polygons, FME does not recognise the geometry anymore. The geometry field from the output features does have content but when connected to a GeometryFilter the type is 'Null'.

 

There is a workaround, when using a 'with' statement, like so:

with polygons as
   ( select * from table1 )
select 
    polygons.name
  , table2.whatever
  , polygons.geometry
from polygons
join table2 on ST_Intersects (polygons.geometry, table2.geometry
 

Then, FME does recognise the geometry and displays them correctly (geometry coming from 'polygons' and thus, from 'table1')

 

Is there anything I can check on our Postgres environment that might be causing this? Should my spatial query be different? or is this a bug in FME? 

 

Using  FME 2021.1.3.0 (20211002 - Build 21631 - WIN64)

 

Thanks in advance!

 

@annetdeboer​ I always get a geometry, it's just not obvious which one FME will return. Try explicitly naming the geometry you want returned, i.e.:

SELECT "mytable".geom, ...

instead of mytable.*

Even with two different types it's hard to know which geometry will be returned, it seems to be the last table referenced in the SELECT:

SELECT "mytable".* , "nexttable".*

generally will return the geometry from "nexttable"

Is it the same as this issue? Where you're getting the geometry through as an attribute, and FME is not interpreting it as a geometry?

 

https://community.safe.com/s/question/0D54Q0000986rE0/sqlcreator-only-reading-geometry-column-as-geometry-sometimes

@annetdeboer​ I always get a geometry, it's just not obvious which one FME will return. Try explicitly naming the geometry you want returned, i.e.:

SELECT "mytable".geom, ...

instead of mytable.*

Even with two different types it's hard to know which geometry will be returned, it seems to be the last table referenced in the SELECT:

SELECT "mytable".* , "nexttable".*

generally will return the geometry from "nexttable"

Tnx for your reply Mark. If you use my query on a Postgres database accessing two tables with the same geometry type (e.g. both tables have polygons) you also get a geometry?

 

Even if I explicitly name the geometry it does not give me one.

Only if I rewrite the query and use the workaround with the "with" statement.

I found out later that besides using 'with' also queries that use the old fashioned way of joining deliver a geometry. With old fashioned I mean:

Select a, b, c from table1, table2

where ......

 

(instead of .. select a,b,c from table1 JOIN table2 ON ....)

Is it the same as this issue? Where you're getting the geometry through as an attribute, and FME is not interpreting it as a geometry?

 

https://community.safe.com/s/question/0D54Q0000986rE0/sqlcreator-only-reading-geometry-column-as-geometry-sometimes

Tnx Ebygomm, this could be related indeed... User @virtualcitymatt​ gave you a PostGIS advice there which I have tried in my (PG) situation but unfortunately that did not work (as in: the geometry is not recognised by FME)

Also the comment of Mark earlier on left me thinking how I can be sure to have received the right geometry in the case where I DO get one... Will need more time to figure this out I'm afraid.

 

Have you tried rewriting your MS Sql query to using a 'with' statement? Perhaps that is a workaround in your case as well?

Tnx for your reply Mark. If you use my query on a Postgres database accessing two tables with the same geometry type (e.g. both tables have polygons) you also get a geometry?

 

Even if I explicitly name the geometry it does not give me one.

Only if I rewrite the query and use the workaround with the "with" statement.

I found out later that besides using 'with' also queries that use the old fashioned way of joining deliver a geometry. With old fashioned I mean:

Select a, b, c from table1, table2

where ......

 

(instead of .. select a,b,c from table1 JOIN table2 ON ....)

@annetdeboer​ If I use your query, I don't get the geometry. Only if I add an explicit geometry geometry name do I get the geometry

Tnx for your reply Mark. If you use my query on a Postgres database accessing two tables with the same geometry type (e.g. both tables have polygons) you also get a geometry? 

 

Even if I explicitly name the geometry it does not give me one. 

Only if I rewrite the query and use the workaround with the "with" statement. 

I found out later that besides using 'with' also queries that use the old fashioned way of joining deliver a geometry. With old fashioned I mean: 

Select a, b, c from table1, table2

where ......

 

(instead of .. select a,b,c from table1 JOIN table2 ON ....)

I explicitly name the geometry field I want. Or maybe I don't understand what you mean by " geometry name "? (see my initial post, line 4):

select 
   table1.name
 , table2.whatever
 , table1.geometry 👈 
from table1
join table2 on ST_Intersects(table1.geometry, table2.geometry)

 

Tnx for your reply Mark. If you use my query on a Postgres database accessing two tables with the same geometry type (e.g. both tables have polygons) you also get a geometry?

 

Even if I explicitly name the geometry it does not give me one.

Only if I rewrite the query and use the workaround with the "with" statement.

I found out later that besides using 'with' also queries that use the old fashioned way of joining deliver a geometry. With old fashioned I mean:

Select a, b, c from table1, table2

where ......

 

(instead of .. select a,b,c from table1 JOIN table2 ON ....)

@annetdeboer​ yes - that worked for me

Reply


Community Stats

30,171
Posts
114,534
Replies
37,830
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.