Skip to main content
Solved

How do I write Lat Long to a non-spatial table with XY coordinate?

ravibansal
Contributor
Forum|alt.badge.img+4

I have a non-spatial table with about 5 million records of XY coordinates. I need to update the table with the Lat Long values in the so-named columns.

I am facing problems with writing these to the table. 

When I choose UPDATE/UPSERT/fme_db_operation, the writing takes a seconf for every 10 records. It’s impossibly slow.

When I choose, Insert with DROP and CREATE or TRUNCATE EXISTING, the script gets stuck with following last 4 lines in the log:

 

Writer `MSSQL_ADO_2' of type `MSSQL_ADO' using group definition keyword `MSSQL_ADO_2_DEF'
Microsoft SQL Server Non-Spatial Writer: Opening `GISReporting' for write operation
Microsoft SQL Server Non-Spatial Writer: Read 1 DEF line(s).  Found 9 attribute(s)
Microsoft SQL Server Non-Spatial Writer: Trying to connect using Microsoft OLE DB Driver 19 for SQL Server...
MSSQL_ADO writer: Splitting bulk features into individual features
 

 

NOTE: I have set BULK Read and Write at 100000 records for every transaction.

Best answer by nielsgerrits

I can imagine if you try to read and drop-create the same table in one go things go south, the writer dropping the table before it has finished reading it. Maybe you can force it to read all features before writing using a FeatureHolder.

As an alternative you should be able to update the records using a SQLExecutor. This way you can use SQL and you are more free to do what you think is best.

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

3 replies

nielsgerrits
VIP
Forum|alt.badge.img+53
  • nielsgerritsVIP
  • Best Answer
  • April 2, 2024

I can imagine if you try to read and drop-create the same table in one go things go south, the writer dropping the table before it has finished reading it. Maybe you can force it to read all features before writing using a FeatureHolder.

As an alternative you should be able to update the records using a SQLExecutor. This way you can use SQL and you are more free to do what you think is best.

ravibansal
Contributor
Forum|alt.badge.img+4
  • ravibansalContributor
  • Author
  • Contributor
  • April 3, 2024

 

nielsgerrits wrote:

I can imagine if you try to read and drop-create the same table in one go things go south, the writer dropping the table before it has finished reading it. Maybe you can force it to read all features before writing using a FeatureHolder.

As an alternative you should be able to update the records using a SQLExecutor. This way you can use SQL and you are more free to do what you think is best.

Thanks a lot! Featureholder is what I was missing!

nielsgerrits
1 person upvoted this
  • nielsgerrits
    nielsgerrits
nielsgerrits
VIP
Forum|alt.badge.img+53

Cheers :)

Reply


Helpful Members This Week

Community Stats

30,957
Posts
117,330
Replies
38,745
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.