Solved

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

  • 2 April 2024
  • 3 replies
  • 32 views

Badge +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.

icon

Best answer by nielsgerrits 2 April 2024, 21:22

View original

3 replies

Userlevel 6
Badge +32

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.

Badge +4

 

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!

Userlevel 6
Badge +32

Cheers :)

Reply