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

Question

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

nielsgerrits · Accepted Answer

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 · Answer

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!

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

3 replies

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded