Hi Altruist,
I would like to bulk reproject on a number of database table in SQL spatial server and write (update) the tables into same database after the reprojection. All tables are in same CRS currently but obviously schema are different. Can anyone kindly suggest a tutorial/workspace of same sort?
best regards and thanks in advance.
+2
@smfks911 I would use staging tables. If you just reproject the coordinate then the metadata attached to the spatial table (coordinate system etc.) will remain unchanged. You can then use SQL to remove the old tables and rename the staging tables. To do this dynamically just follow Tutorial: Dynamic Workflows
@smfks911 I would use staging tables. If you just reproject the coordinate then the metadata attached to the spatial table (coordinate system etc.) will remain unchanged. You can then use SQL to remove the old tables and rename the staging tables. To do this dynamically just follow Tutorial: Dynamic Workflows
Hi @Mark Stoakes, thanks for your kind advise. Honestly I am not expert in SQL. And therefore I am not familiar with staging tables or how to create that. Can you elaborate a little more kindly? I have gone through the tutorial you have suggested but the demonstration on geopkg data is not replicable to SQL database tables I guess. What I am trying to do like this image actually. 
+2
@smfks911 Table1 is written to Table1Reprojected. Table1Reprojected is the staging table. Once all the tables are converted, you can us SQL (SQLExecutor) to delete the original tables and rename the staging tables
@smfks911 Table1 is written to Table1Reprojected. Table1Reprojected is the staging table. Once all the tables are converted, you can us SQL (SQLExecutor) to delete the original tables and rename the staging tables
Thank you @Mark Stoakes . I have realized that I have already a staging database with same schema in SQL server. So the transformation and writing part is ok. What I have been facing is to fanning out the individual tables after using the reprojection. But anyway, it is resolved now :)
@smfks911 Table1 is written to Table1Reprojected. Table1Reprojected is the staging table. Once all the tables are converted, you can us SQL (SQLExecutor) to delete the original tables and rename the staging tables
hi @Mark Stoakes, sorry to stretch the issue again. I have dynamically re-projected a number of tables from one database and written into another database but with same schema. I have re-checked by using CoordinateSystemExtractor that the projection system was changed. However, I have just realized that the data is not displayed in QGIS also the metadata is still showing old projection system Attached is my workspace. Can you kindly tell me whats the issue?
+2
@smfks911 Does the target coordinate system "MGA2020-56_FME" exist in PostGIS? It looks like you have to match the FME coordinate system to a custom PostGIS coordinate system. I'm not sure how to do that. Perhaps someone in community with more coordinate system knowledge knows how to resolve that.
@smfks911 Does the target coordinate system "MGA2020-56_FME" exist in PostGIS? It looks like you have to match the FME coordinate system to a custom PostGIS coordinate system. I'm not sure how to do that. Perhaps someone in community with more coordinate system knowledge knows how to resolve that.
Hi @Mark Stoakes, I think the issue I was having because the reproject SQL table was missing 'Spatial_ref_sys' table which is not exposed to FME. I have resolved the case now by just dragging an existing MGA2020-56 data into the corresponding SQL schema within QGIS. Its kinda force SQL to fix the issue :) Got help from QGIS user group.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.