Question

MSSQL Spatial Column with type 'geometry' does not exist in table

  • 16 October 2015
  • 6 replies
  • 41 views
A
Badge
Hi,

 

I'm trying to use FMEObjects, particularly read, modify data and write back to MSSQL database and I'm hitting a strange behaviour in 2015 version. Maybe I'm doing something wrong but.. I managed to read data OK but when I'm writing back it complains "Spatial Column 'geometry' with type 'geometry' does not exist in table 'dbo.simple_test_data'. Please specify an existing spatial column to write to". As far as I'm concerned - I have specified - here's my writer directives:

 

Writer parameters: COMMAND_TIMEOUT=34

 

RETRIEVE_ALL_SCHEMAS=YES

 

SERVER=server

 

USER_NAME=fme_datastore_test

 

PASSWORD=***

 

WHERE_CLAUSE=

 

CLIP_TO_ENVELOPE=NO

 

READ_CACHE_SIZE=10

 

ASSUME_ONE_SRID_PER_COL=YES

 

EXPOSED_ATTRS=

 

SPATIAL_TYPE=geometry

 

SPATIAL_COLUMN=GEOM

 

 

Here's extract from the log:

 

0.1|INFORM|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Feature Type: `dbo.simple_test_data'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Attribute(32 bit integer): `ID' has value `1'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Attribute(encoded: utf-8): `fme_db_operation' has value `UPDATE'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Attribute(string)        : `fme_geometry' has value `fme_polygon'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Attribute(string)        : `fme_type' has value `fme_area'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Attribute(encoded: utf-8): `fme_where' has value `ID = 1'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Coordinate System: `'

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Geometry Type: IFMEPolygon

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|Boundary:

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|   Geometry Type: IFMELine

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|   Number of Coordinates: 4 -- Coordinate Dimension: 2

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|   (50,0)(50,50)(0,0)(50,0)

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|===========================================================================

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|FME API version of module 'MSSQL_SPATIAL' matches current internal version (3.7 20121025)

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|FME API version of module 'MSSQL_SPATIAL' matches current internal version (3.7 20121025)

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|MS SQL Server (Spatial) Writer: Opening `fme_datastore_test' for write operation

 

2015-10-15 16:28:03|   0.2|  0.0|INFORM|MS SQL Server (Spatial) Writer: Read 3 DEF line(s).  Found 5 attribute(s)

 

2015-10-15 16:28:03|   0.2|  0.0|ERROR |MS SQL Server (Spatial) Writer: Spatial Column 'geometry' with type 'geometry' does not exist in table 'dbo.simple_test_data'. Please specify an existing spatial column to write to

 

2015-10-15 16:28:03|   0.2|  0.0|ERROR |A fatal error has occurred. Check the logfile above for details

 

2015-10-15 16:28:17|   0.2|  0.0|INFORM|MS SQL Server (Spatial) Writer: Closing `fme_datastore_test' . Write operation complete

 

2015-10-15 16:28:17|   0.2|  

 

 

What am I missing?

6 replies

david_r
Rank
Userlevel 4
Hi

 

 

How is your table defined?

 

 

David

 

 
A
Badge
Hi David. Sorry forgot to mention the important detail:

 

CREATE TABLE simple_test_data(

 

ID int PRIMARY KEY IDENTITY(1,1),

 

GEOM geometry

 

);
david_r
Rank
Userlevel 4
What happens if you drop the SPATIAL_TYPE and SPATIAL_COLUMN directives? You're just using the default values anyway, so it should make no difference.

 

 

You may also want to use one of the FME dialogs to create a template for your directives, it can be a lot easier than starting from scratch. You can adapt the code posted here: https://knowledge.safe.com/CommunityAnswers?id=906a0000000d4mdAAA

 

 

Adapt it to use the destPrompt() (http://docs.safe.com/fme/html/FME_Objects_Python_API/fmeobjects.FMEDialog-class.html#destPrompt) function and see what it returns.

 

 

David
A
Badge
It still fails in the same way - I added 

 

SPATIAL_TYPE and SPATIAL_COLUMN only because I thought they'd help. Ok thanks for the links. Maybe I'll try and write a unit test first and see if it works there. At the moment I'm just debugging a part of a complex system...
david_r
Rank
Userlevel 4
I tried the destPrompt() using FME 2015.1.1 and I got the following directives:

 

 

DestSPATIAL_TYPE,geometry,DestSPATIAL_COLUMN,GEOM

 

 

So it seems FME expects a prefix "Dest" on the directive names.

 

 

Here is the entire directive string as returned from destPrompt():

 

 

['RUNTIME_MACROS', 'FME_CONNECTION_GROUP,,NAMED_CONNECTION,username<at>server<solidus>database,SPATIAL_TYPE,geometry,SPATIAL_COLUMN,GEOM,DESTINATION_DATASETTYPE_VALIDATION,Yes,COORDINATE_SYSTEM_GRANULARITY,FEATURE_TYPE', 'META_MACROS', 'DestFME_CONNECTION_GROUP,,DestNAMED_CONNECTION,username<at>server<solidus>database,DestSPATIAL_TYPE,geometry,DestSPATIAL_COLUMN,GEOM,DestDESTINATION_DATASETTYPE_VALIDATION,Yes,DestCOORDINATE_SYSTEM_GRANULARITY,FEATURE_TYPE', 'METAFILE', 'MSSQL_AZURE_SPATIAL', 'COORDSYS', '']

 

 

David

 

 
A
Badge
Hi again. I just found a bug in our software that caused it to specify wrong spatial column name for MS SQL - instead of GEOM it passed 'geometry' hence the error. Sorry for trouble, everyone, turns out not an FME bug at all ;)

Reply


Community Stats

28,650
Posts
109,263
Replies
36,523
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.