Question

Multiple geometries with different SRSID ?

  • 16 November 2016
  • 13 replies
  • 11 views
lifalin2016
Rank
Userlevel 1
Badge +22

Hi,

I've aquired the task of populating a MS/SQL table with four (!) geometry columns.

I've looked at this article, which helped me get going.So far so good, the 4 different geometries get saved as they should, and STAsText() returns the correct coordinates.

But when I look at the geometries' SRSID, i.e. using the geometry::STSrid property, they're all zero (0). Including the original unaltered geometry read from the source.

Is there a way (in FME) to set the individual SRSID on each geometry, so each target column has it's own SRSID ?

Cheers

Lars I.

-- Cheers, Lars I.

13 replies

itay
Rank
Badge +16

Not sure but could be that you are looking for the coordinatesystemsetter?

lifalin2016
Rank
Userlevel 1
Badge +22

Not sure but could be that you are looking for the coordinatesystemsetter?

Well, the input data have a set coordinate system, and the reprojected have that reset to the target system (I assume). Wouldn't it be adding what's already there ?

 

 

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

Not sure but could be that you are looking for the coordinatesystemsetter?

Nope, sorry. Didn't change anything.

 

 

-- Cheers, Lars I.
itay
Rank
Badge +16
Nope, sorry. Didn't change anything.

 

 

Are you using FME or the underlaying db to define the output coordinate system? as mentioned in the article
david_r
Rank
Userlevel 4

Maybe this article can give you some pointers, if you haven't read it already: https://knowledge.safe.com/articles/28728/writing-to-tables-that-contain-multiple-geometry-c.html

lifalin2016
Rank
Userlevel 1
Badge +22

Maybe this article can give you some pointers, if you haven't read it already: https://knowledge.safe.com/articles/28728/writing-to-tables-that-contain-multiple-geometry-c.html

That's the article I started out with :-)

 

 

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

Hi,

I'm uploading a workspace + a Shape table with test data + a SQL query script which includes the target table definition and a sample query. The target is an MS/SQL, which you'll have to provide (Express will work, I think).

The table has four geometry columns, two pairs of geometry + center point in each projection.

The query shows that the SRID is 0 as-is for all four geometry columns.

The query also shows, the if saved with an non-zero SRID (the "wkt" columns), the SRID is non-zero upon request.

Cheers

Lars I.

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

Hi,

I'm uploading a workspace + a Shape table with test data + a SQL query script which includes the target table definition and a sample query. The target is an MS/SQL, which you'll have to provide (Express will work, I think).

The table has four geometry columns, two pairs of geometry + center point in each projection.

The query shows that the SRID is 0 as-is for all four geometry columns.

The query also shows, the if saved with an non-zero SRID (the "wkt" columns), the SRID is non-zero upon request.

Cheers

Lars I.

test-multiple-table-geometries.zip

 

 

-- Cheers, Lars I.
itay
Rank
Badge +16
test-multiple-table-geometries.zip

 

 

I would contact Safe for this issue http://www.safe.com/support/

 

 

erik_jan
Rank
Userlevel 2
Badge +16

I have done a similar process before.

The only way I could get this done is writing the record with a single geometry (and SRID).

Then writing the other geometry using a writer in UPDATE mode (or a DatabaseUpdater transformer) adding the second geometry (with a different SRID) to the existing record.

mark2atsafe
Rank
Userlevel 4
Badge +25
I would contact Safe for this issue http://www.safe.com/support/

 

 

Yes, please do contact support. I looked into the issue but I don't see any existing reports of this and nothing from our developers. The only thing I saw was when the SRID was not in the FME coordinate systems and didn't have an EPSG number - but I think that's unlikely to be an issue for all four of your coordinate systems. I think we'll need our SQL Server developers to comment on the issue.

 

Mark

 

G
Badge +3

I haven't tried different SRIDs for multigeometrycolumns.

wkt can't pass SRID's. Afaik only extended Wkt wich fme does not use. In fact fme uses old one (at least in 2015)

Either build SDO_GEOmetry strings. Wich i did to by-pass the wkt extractor limitation using external (not in database) geometries , tough not easy.

Better yet use a update function.

Run it after it is written to the tables.

UPDATE tabelname T

SET T.geometryname.SDO_SRID = YOUR_SRID

WHERE T.geometryname IS NOT NULL

revesz
Badge +3

I have the same issue in SQL Server (FME 2017 & 2018).

I have a point feature type. I can write it with proper coordinate system as single geometry.

When I try the multiple geometry (the modified geometry has the same coord sys) the SRID becomes 0.

I tried to set the coordinate system at several points (writer parameter, CoordinateSystemSetter after the reader, before the writer, after the GeometryPropertySetter, before the Aggregator) without any success.

I ended up with the

update <table> set <column1>.STSrid=<SRID>, <column2>.STSrid=<SRID>;

SQL Command After the Translation.

Reply


Community Stats

28,707
Posts
109,478
Replies
36,551
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.