Issue in reprojected MSSQL data while visualizing in QGIS

[Faysal Kabir Shuvo]

Hi there.

Good morning from Sydney.

I am wondering whether anyone is aware of why a layer from MSSQL misses CRS information while displaying in QGIS?

I have  re-projected (from MGA94 to MGA2020) a number MSSQL tables and then written into another MSSQL database by using FME. I can see the layers are re-projected in both SQL and FME. However, when adding the layer in QGIS, it stands with question mark and no CRS information is there. When I manually change it to MGA2020, then only the question mark goes away.

Does anyone have idea why this type of issue is happening in QGIS? 

Looking for kind response.

best regards,

Faysal

[Andrew Jeffrey]

Hi Faysal,

I've seen this before and my issue was that there is no record for the projection in the "spatial_ref_sys" table within the MSSQL database.

The "geometry_columns" and "spatial_ref_sys" table will be created automatically in the database if you use OGR to upload a layer or if you upload a layer to the database via the QGIS UI.

You can upload a layer to the database in QGIS using the UI by dragging the layer from your layer list and dropping it on your database connection. Make sure that the layer is a GDA2020 layer and has the correct projection set in the layer settings first. This should then put an entry in the "spatial_ref_sys" table for that epsg code and projection.

--
You received this message because you are subscribed to the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/065bf4a3-e716-41f3-b1c8-6f40a3ad2e24n%40googlegroups.com.

[Faysal Kabir Shuvo]

Hi Andrew,

Thank you so much for the advice on the solution.

As I understand from your explanations, when I am transforming the MSSQL table and writing into another database, the 'spatial_ref_sys' is not getting created.

But in my case, both tables (before and after reprojection) got geom column and when I search geom.STSrid, I can see respective EPSG no.

Before reprojection:

After reprojection:

But when I bring both layers on QGIS, the second layer (after reprojection) is appearing with the question mark:

Now the way you are suggesting should work but considering a bulk update process, I have to first manually set the coordinates of all the reprojected layers to GDA2020 in QGIS and then send them back to MSSQL. This will take a heap of time, is not it? Not sure it is QGIS issue or MSSQL issue.

best regards,

Faysal

To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/CADTxF6YFrtBxEqPSeP-kBsJce6vs23Ha%2B55tit%2Bhr5Ubrawb_A%40mail.gmail.com.

[Andrew Jeffrey]

Hi Faysal,

I'm not suggesting bulk updating that table, the issue doesn't have anything to do with the data in either of your tables. I've seen this issue multiple times in GDA2020 data conversion projects that I have worked on. The issue here is the "spatial_ref_sys" table, and we need to populate it with a record for the GDA2020 EPSG code that you are using.

What I am suggesting is as follows:

1. Load a layer into QGIS that is in GDA2020 in your case EPSG:7855. It doesn't have to be  your "PS_BBQ" layer, it can be any layer as long as it is in GDA2020 MGA55.
2. Rename the layer in your QGIS project to "test_upload". This will ensure it is uploaded as a new table.
3. Drag the layer from the layer list into the database connection as my screen shot in my first reply shows. When the table uploads this will add a record to your "spatial_ref_sys" table in the database for the EPSG code 7855. This happens on the database side, you will not see anything in QGIS.
4. You should get a confirmation window that your table was successfully uploaded. 
5. Close and reopen QGIS and then load a GDA2020 table from your database into QGIS and you should notice that QGIS recognises the table as GDA2020 now.

If you follow those steps, you'll be sorted!

Good luck.

Andrew

 

On 26 Aug 2022, 5:34 PM +1000, Faysal Kabir Shuvo <faysa...@gmail.com>, wrote:

Hi Andrew,

Thank you so much for the advice on the solution.

As I understand from your explanations, when I am transforming the MSSQL table and writing into another database, the 'spatial_ref_sys' is not getting created.

But in my case, both tables (before and after reprojection) got geom column and when I search geom.STSrid, I can see respective EPSG no.

Before reprojection:

<image.png><image.png>

After reprojection:

<image.png><image.png>

But when I bring both layers on QGIS, the second layer (after reprojection) is appearing with the question mark:

Now the way you are suggesting should work but considering a bulk update process, I have to first manually set the coordinates of all the reprojected layers to GDA2020 in QGIS and then send them back to MSSQL. This will take a heap of time, is not it? Not sure it is QGIS issue or MSSQL issue.

best regards,

Faysal

On Fri, Aug 26, 2022 at 1:33 PM Andrew Jeffrey <aljef...@gmail.com> wrote:

Hi Faysal,

I've seen this before and my issue was that there is no record for the projection in the "spatial_ref_sys" table within the MSSQL database.

<image.png>

The "geometry_columns" and "spatial_ref_sys" table will be created automatically in the database if you use OGR to upload a layer or if you upload a layer to the database via the QGIS UI.

You can upload a layer to the database in QGIS using the UI by dragging the layer from your layer list and dropping it on your database connection. Make sure that the layer is a GDA2020 layer and has the correct projection set in the layer settings first. This should then put an entry in the "spatial_ref_sys" table for that epsg code and projection.

<image.png>

On Fri, Aug 26, 2022 at 10:10 AM Faysal Kabir Shuvo <faysa...@gmail.com> wrote:

Hi there.

Good morning from Sydney.

I am wondering whether anyone is aware of why a layer from MSSQL misses CRS information while displaying in QGIS?

I have  re-projected (from MGA94 to MGA2020) a number MSSQL tables and then written into another MSSQL database by using FME. I can see the layers are re-projected in both SQL and FME. However, when adding the layer in QGIS, it stands with question mark and no CRS information is there. When I manually change it to MGA2020, then only the question mark goes away.

Does anyone have idea why this type of issue is happening in QGIS? 

Looking for kind response.

best regards,

Faysal

--
You received this message because you are subscribed to the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/065bf4a3-e716-41f3-b1c8-6f40a3ad2e24n%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/CADTxF6YFrtBxEqPSeP-kBsJce6vs23Ha%2B55tit%2Bhr5Ubrawb_A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/CACnK%2B3Cg2DHBRiOku8__LH0Lb3wkC5PxaKuxs2W2Fs_uVZCGYg%40mail.gmail.com.

[Faysal Kabir Shuvo]

Brilliant!

Thanks so much Andrew.

Really worked very well :)

You are a champion! 

Have a great weekend.

To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/CADTxF6bSbp7TmrJstc1Oc_NxESSXapeu3z_HTbTfQisdOVTGyw%40mail.gmail.com.