Solved

Merging geometry from a database table

lifalin2016
Rank
Userlevel 1
Badge +22

Hi list.

This may have a simple answer, but I'll try and ask anyway.

I have some 200+ features, that I want to add a geometry to. Simple, right?

My table with geometry has 2.5 million rows, and I have to match on two string values.

I initially tried to use DatabaseJoiner, but apparently it doesn't handle geometries ?

I then got stuck with reading all 2.5 million records and use a FeatureJoiner. It works, but it's not optimal.

What is the better way when fetching geometries from a database table/view for very few features ?

Cheers.

icon

Best answer by david_r 4 February 2021, 14:12

View original
-- Cheers, Lars I.

10 replies

ebygomm
Rank
Userlevel 1
Badge +10

Do you have any ability to create temporary tables in the environment that your table with geometry is in? In the past when i've had to do similar I found the quickest way is to create a temp table with your 200 features, then an sql exectutor to join this table with your geometry table to retrieve the geometry.

 

(I've presumed your 200 features and geometry aren't already in the same environment so you can't simply use an sql creator?)

redgeographics
Rank
Userlevel 5
Badge +25

SQLExecutor to pull just those records you need and then FeatureJoiner. That would mean 1 query per feature though, definately preferable over reading 2.5 million but maybe still too much of an overhead?

david_r
Rank
Userlevel 4

SQLExecutor to pull just those records you need and then FeatureJoiner. That would mean 1 query per feature though, definately preferable over reading 2.5 million but maybe still too much of an overhead?

That would be my first idea as well. Executing 200-and-something queries shouldn't be too bad, especially if the fields referenced in the WHERE-clause are indexed.

lifalin2016
Rank
Userlevel 1
Badge +22

SQLExecutor to pull just those records you need and then FeatureJoiner. That would mean 1 query per feature though, definately preferable over reading 2.5 million but maybe still too much of an overhead?

Will SQLExecutor merge the geometry as well ?

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

Will SQLExecutor merge the geometry as well ?

Depends, but you can also cast the geometry into e.g. WKB in the SELECT-statement and then retrieve the geometry in the workspace using a GeometryReplacer. Exactly how you do it depends on if you're using e.g. SDE, SQL Server Spatial, PostGIS, etc.

david_r
Rank
Userlevel 4

Example joining using the SQLExecutor:

select a.*, st_asbinary(b.shape) as shape_wkb
from mysmalltable as a
join mylargetabe as b on a.key1 = b.key1 and a.key2 = b.key2

You should then get an attribute shape_wkb (or SHAPE_WKB in Oracle) that you can pass into the GeometryReplacer.

As long as your join keys are indexed, this should be very efficient. Just be aware that both tables must reside in the same database for this to work.

lifalin2016
Rank
Userlevel 1
Badge +22

Example joining using the SQLExecutor:

select a.*, st_asbinary(b.shape) as shape_wkb
from mysmalltable as a
join mylargetabe as b on a.key1 = b.key1 and a.key2 = b.key2

You should then get an attribute shape_wkb (or SHAPE_WKB in Oracle) that you can pass into the GeometryReplacer.

As long as your join keys are indexed, this should be very efficient. Just be aware that both tables must reside in the same database for this to work.

Hi David.

That's not T-SQL. But luckily the STAsBinary() function do exist in T-SQL.

So what you're saying, is that FME will recognize such a construct, and automagically insert this as the new feature geometry ?

Because my features (w/o geometry) are not in the database, they're extracted from a spreadsheet. I need to insert a geometry on them from the database.

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

Hi David.

That's not T-SQL. But luckily the STAsBinary() function do exist in T-SQL.

So what you're saying, is that FME will recognize such a construct, and automagically insert this as the new feature geometry ?

Because my features (w/o geometry) are not in the database, they're extracted from a spreadsheet. I need to insert a geometry on them from the database.

Hi, you're right! Since I don't know what backend you're using, it's a general example off the top of my head, untested ;-)

In this case the geometry will get back to FME as a regular attribute containing binary data (BLOB), you'll have to use the GeometryReplacer after the SQLExecutor to transform it back to an FME geometry.

Apparently there are cases where the SQLExecutor can recognize a geometry column by itself, but the exact requirements are unclear to me, so I prefer going the explicit route. Casting the geometry back and forth to WKB does have a small performance penalty, however, but for 200-something records I don't think it should be very noticable.

lifalin2016
Rank
Userlevel 1
Badge +22

Example joining using the SQLExecutor:

select a.*, st_asbinary(b.shape) as shape_wkb
from mysmalltable as a
join mylargetabe as b on a.key1 = b.key1 and a.key2 = b.key2

You should then get an attribute shape_wkb (or SHAPE_WKB in Oracle) that you can pass into the GeometryReplacer.

As long as your join keys are indexed, this should be very efficient. Just be aware that both tables must reside in the same database for this to work.

Hi David.

Actually it turned out to be much simpler than using WKB.

The last dropdown in the parameters dialog (Combine Geometry) allows the setting "Result Geometry Only", which was exactly what I needed. I then just had to issue a "SELECT TOP 1 [geometry_column] FROM ... WHERE ..." to have the resulting geometry be attached to my feature.

No need for additional conversions and exposed attributes. Neat!

Thanks for your help.

2021-02-05 13_15_02-SQLExecutor Parameters

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

Hi David.

Actually it turned out to be much simpler than using WKB.

The last dropdown in the parameters dialog (Combine Geometry) allows the setting "Result Geometry Only", which was exactly what I needed. I then just had to issue a "SELECT TOP 1 [geometry_column] FROM ... WHERE ..." to have the resulting geometry be attached to my feature.

No need for additional conversions and exposed attributes. Neat!

Thanks for your help.

2021-02-05 13_15_02-SQLExecutor Parameters

Excellent, thanks for the tip!

Reply


Community Stats

28,670
Posts
109,364
Replies
36,531
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.