Skip to main content
Question

Multiple geometries with different SRSID ?

lifalin2016
Contributor
Forum|alt.badge.img+29

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
Supporter
Forum|alt.badge.img+16
  • itaySupporter
  • Supporter
  • November 16, 2016

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

lifalin2016
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • November 17, 2016
itay wrote:

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
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • November 17, 2016
itay wrote:

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

Nope, sorry. Didn't change anything.

 

 

-- Cheers, Lars I.
itay
Supporter
Forum|alt.badge.img+16
  • itaySupporter
  • Supporter
  • November 17, 2016
lifalin2016 wrote:
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
Evangelist
  • david_rEvangelist
  • November 17, 2016

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
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • November 21, 2016
david_r wrote:

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
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • November 21, 2016

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
Contributor
Forum|alt.badge.img+29
  • lifalin2016Contributor
  • Author
  • Contributor
  • November 21, 2016
lifalin2016 wrote:

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
Supporter
Forum|alt.badge.img+16
  • itaySupporter
  • Supporter
  • November 21, 2016
lifalin2016 wrote:
test-multiple-table-geometries.zip

 

 

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

 

 

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • November 21, 2016

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
Safer
Forum|alt.badge.img+43
  • mark2atsafeSafer
  • Safer
  • November 21, 2016
itay wrote:
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

 

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • November 22, 2016

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
Contributor
Forum|alt.badge.img+15
  • reveszContributor
  • Contributor
  • January 16, 2018

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


Helpful Members This Week

Community Stats

30,985
Posts
117,432
Replies
38,764
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.