Skip to main content
Solved

SQL2008R2 with UTM x,y,z to convert and add new fields for LL, geometry and geography (SQL spatial types)

  • January 18, 2019
  • 7 replies
  • 134 views
trevorm
Contributor
Forum|alt.badge.img+3

we have 12 tables with about 400,000 records so i want to know if I can create 4 new fields; Lat, Long, and both Spatial SQL fields from the current UTM zone 13 Nad83 data.

New user, so the help posts I have ready don't directly answer this question and I am still trying to learn how to create the new fields.

Can I do this in one project, or does this become a multi project for each table?

If not to much to ask, can you give me the functions in order for this? I can supply data if that will help also.

Best answer by gio

@trevorm

 

You can use coordinateextractors, reprojectors to get the LL.

Geometry (and geography) can be written by the relevant writer.

You can do it in one go as each table is identifiable.

 

For examples, some data is needed. So if you could post a sample, help will come fast on this forum.

View original
Did this help you find an answer to your question?

7 replies

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • Best Answer
  • January 21, 2019

@trevorm

 

You can use coordinateextractors, reprojectors to get the LL.

Geometry (and geography) can be written by the relevant writer.

You can do it in one go as each table is identifiable.

 

For examples, some data is needed. So if you could post a sample, help will come fast on this forum.

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Author
  • Contributor
  • January 21, 2019
gio wrote:

@trevorm

 

You can use coordinateextractors, reprojectors to get the LL.

Geometry (and geography) can be written by the relevant writer.

You can do it in one go as each table is identifiable.

 

For examples, some data is needed. So if you could post a sample, help will come fast on this forum.

@gio Is this sort of what it should look like? Also I couldn't find the geography transformer

 

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • January 22, 2019

@trevorm

Yes that about it.

You don't need to extract geometry in this process, unless you wish to store it. But as they are points, they are already in store.

 

Geography is a data type. Chosen/set on the writer side.

See relevant info:

This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. SQL Server supports a set of methods for the geography spatial data type.

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Author
  • Contributor
  • January 22, 2019
gio wrote:

@trevorm

Yes that about it.

You don't need to extract geometry in this process, unless you wish to store it. But as they are points, they are already in store.

 

Geography is a data type. Chosen/set on the writer side.

See relevant info:

This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. SQL Server supports a set of methods for the geography spatial data type.

@gio Thank you for the help, the results of my current field shows in the Inspector, but the reason I'm doing this is to produce new fields in the table. I need to create 4 new fields LAT , LONG , Geometry and Geography.

So when I look at my new table in SQLSMS it doesn't show those fields that were supposed to be created?

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • January 23, 2019

@trevorm

Extract coordinates after the reprojector.

The coordinates will be in attributes, you need to rename/copy/create them to Lat/Long.

As far is geometry and geography are concerned, writing them using a spatial writer, will create the columns. Those attributes don't show in the writer, you can see them in your table.

 

If you actually want a string representation of them, you can indeed extract them using Geomtery Extractor. But you do need to add the attribute to the writer (as with the Lat/Long).

 

But that would leave you with a table with geometry and its string representation (seems superfluous to me).

 

I can't access your sample sheet, alas.

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • January 23, 2019

Hi @trevorm,

If you only need the coordinate values you can also use the AttributeReprojector.

Hope this helps,

Itay

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Author
  • Contributor
  • June 25, 2019

Hello, just a quick update; it turns out I didn't have a lic to export to SQL Spatail, once Safe hooked me up it works like a charm.

I've used all the above suggested with success, and I can even just write using the SQL Spatial server.

Thank you.

Reply


Helpful Members This Week

Community Stats

31,383
Posts
119,019
Replies
39,012
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.