Solved

MS/SQL geometry limitations ?

  • 22 August 2017
  • 7 replies
  • 12 views
lifalin2016
Rank
Userlevel 1
Badge +22

Hi,

I've received a MapInfo TAB dataset, in which the user has "exploded" all their (CAD) point symbol into multi-polylines with 142 segments each with a simple 2-point line !

I'm trying to read them into MS/SQL, but it errs (as just one of 24 datasets). FME logs them instead with this error message: "Microsoft SQL Server Spatial Writer: Failed to get the SQL Server native binary form for column 'GEOM'"

FME and MapInfo Pro handles the geometries perfectly well, but I can see, that the geometries exported as WKT are close to/over 10K characters, so I'm curious as to precisely how FME writes the geometry to the SQL tables ?

All datasets are created with "Drop and Create" (which works), and the created table's geometry column is defined as "GEOM (geometry, null)".

Please advise. Cheers.

Lars

icon

Best answer by trentatsafe 23 August 2017, 19:16

View original
-- Cheers, Lars I.

7 replies

fmelizard
Rank
Userlevel 4
Badge +13
Hi @lifalin2016, can you share what the geometry type you've set on the Writer is?
lifalin2016
Rank
Userlevel 1
Badge +22
Hi Tia,

 

I'm not sure what you mean. I did write: "... and the created table's geometry column is defined as "GEOM (geometry, null)". Does this answer your question ?

 

Cheers

 

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22
Besides, all the other 23 datasets are imported just fine (and the writers are identically set up), so it's specific to this dataset. All features have the same strange symbol/geometry.

 

 

I did manage to import it by replacing the geometry with its center point, so it's definitely the symbol/geometry that's causing the problem. I'm attaching the dataset in question, so you can play around with it.dvg-boring.zip

 

 

-- Cheers, Lars I.
T
Rank
Badge +6

Hello @lifalin2016,

 

 

You could avoid, the GeometryReplacer, and use a Geometry Validator. If you set the 'Issues to Detect to 'Basic Geometry Integrity', this should allow you to write the table correct. Doing the above, I was able to correctly write the data to SQL Server, as it corrects the 'Corrupt/Degenerate' Geometries.
lifalin2016
Rank
Userlevel 1
Badge +22

Hello @lifalin2016,

 

 

You could avoid, the GeometryReplacer, and use a Geometry Validator. If you set the 'Issues to Detect to 'Basic Geometry Integrity', this should allow you to write the table correct. Doing the above, I was able to correctly write the data to SQL Server, as it corrects the 'Corrupt/Degenerate' Geometries.
Hi Trent,

 

But the geometry isn't invalid !?? Both FME and MapInfo Pro handles and displays it fine, but it's somehow just not valid in MS/SQL. But your suggestion is a valid work-around.

 

I'm just trying to understand why it fails, since FME doesn't write an error message about this (just that it fails).

 

So how does FME transfer geometries to MS/SQL ? WKT ? WKB ? Something else ??

 

Cheers

 

-- Cheers, Lars I.
tim_wood
Rank
Badge +7

Some suggestions which you may already have tried...

- Set FME to full logging to capture as much info as possible

- Inspect the output (add an Inspector or switch on Writers > Redirect to FME Data Inspector) and analyse the feature information compared to the other layers.

- Copy the WKT string and try to write it into SQL Server using an SQL Query e.g. in Management Studio. If you can't access that, maybe the SQLExecutor will give you the SQL Server response. If it's an SQL Server issue or constraint, the error it returns might be more helpful.

lifalin2016
Rank
Userlevel 1
Badge +22

Hello @lifalin2016,

 

 

You could avoid, the GeometryReplacer, and use a Geometry Validator. If you set the 'Issues to Detect to 'Basic Geometry Integrity', this should allow you to write the table correct. Doing the above, I was able to correctly write the data to SQL Server, as it corrects the 'Corrupt/Degenerate' Geometries.
Hi Tia. The data didn't load, and I was also getting the WARNS.

 

I disassembled the symbol/geometry, and there were some self-intersecting lines, and that's a no-go in MS/SQL.

 

Maybe an option on the SQL Server Spatial writer to automatically apply geometry validation before attempting to write might be beneficial. If -as I - one has 20+ feature type connections, inserting a GeometryValidator on each connection is cumbersome and tedious work.

 

Cheers

 

 

-- Cheers, Lars I.

Reply


Community Stats

28,670
Posts
109,366
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.