MSSQL: Layer loads but does not display (Attribute Table correct)

[qgib]

Author Name: CSW Broadband (@crpage)
Original Redmine Issue: 20414
Affected QGIS version: 3.4.0
Redmine category:data_provider/mssql

---

Can't see anything special about this table (I've created a minimal copy which still has error, see below) but it does not display in v3.4 but does in v3.2.
Tried Exporting Features to a GeoPackage ( to troubleshoot the error) which shows that it adds another field called GEOM, seeing the actual geometry one as TEXT(255)

Export to vector file failed.
Error: Feature write errors:
Feature creation error (OGR error: sqlite3_exec(CREATE TABLE "Temp" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geom" POLYGON, "ID" MEDIUMINT, "Postcode" TEXT(10), "Date Introduced" DATE, "Date Withdrawn" DATE, "CodePoint UPP" TEXT(255), "geom" TEXT(255))) failed: duplicate column name: geom)

In MSSQL, this is the temp file I created which has Ordnance Survey Polygon data in, all validated.

USE [BDUK 3.0]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Temp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Postcode] nvarchar NOT NULL,
[Date Introduced] [date] NULL,
[Date Withdrawn] [date] NULL,
[CodePoint UPP] nvarchar NULL,
[geom] [geometry] NULL,
PRIMARY KEY CLUSTERED
(
[Postcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert INTO [dbo].[Temp]
( Postcode,[Date Introduced],[Date Withdrawn],[CodePoint UPP],[geom])
(SELECT Postcode,[Date Introduced],[Date Withdrawn],[CodePoint UPP],[geom]
FROM [CSW3 Postcode Mapping IA]
)
--(25651 rows affected)

---

• QGIS Bug 20414.docx (CSW Broadband)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• priority_id was changed from Normal to High
• regression was changed from 0 to 1

[qgib]

Author Name: Martin Dobias (@wonder-sk)

---

If I understand correctly, the problem is that you can load the layer in QGIS, but nothing is displayed in canvas, however attribute table shows values fine.

Unfortunately your INSERT command takes features from some other table so it is not possible to replicate your exact situation.

I would suggest checking:

• does the layer have correctly recognized CRS when loaded?
• what extent rectangle is reported for the layer? (look into layer properties)
• does the layer have an entry in geometry_columns table?
• if you check [geom].STIsValid() in SQL query - does it return 1 for all rows?

[qgib]

Author Name: CSW Broadband (@crpage)

---

The INSERT is just to populate the GEOM, I will add a version which uses static data but this is the Ordnance Survey data which is not sharable. I've added a few screenshots as a document

The Error shows that the table is loaded correctly
I noticed one unusual thing is that the Postcode text Field is the primary key

CRS and extent shown below from the information tab; the entry in geometry_columns table is correct (it listed the table); STIsValid() is 1 for all rows (remember this displays perfectly in version 3.2:

Name
Temp
Source
dbname='BDUK 3.0' host=cswSQL srid=27700 type=Polygon disableInvalidGeometryHandling='0' table="dbo"."Temp" (Geom) sql=
Storage
MSSQL spatial database
Comment

Encoding
UTF-8
Geometry
Polygon (Polygon)
CRS
EPSG:27700 - OSGB 1936 / British National Grid - Projected
Extent
0.0000000000000000,0.0000000000000000 : 456472.5000000000000000,310029.3125000000000000
Unit
meters
Feature count
25,651

---

• 13714 was configured as QGIS Bug 20414.docx

---

• QGIS Bug 20414.docx (CSW Broadband) - Attrib table, source fields, Info from provider, connection

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: CSW Broadband (@crpage)

---

Static data insert still fails in the same way. Server version is MSSQL 2017

Insert INTO [dbo].[Temp]
( Postcode,[Date Introduced],[Date Withdrawn],[CodePoint UPP],[geom])
VALUES ('CV2 1QL','1980-01-01',NULL,00004000000000301149,
[geometry]::STGeomFromText('POLYGON ((436139.98268005234 283763.73273166915
, 436145.74597776204 283757.59448872291
, 436155.84617761086 283749.1269125414
, 436146.99922704941 283769.001223579
, 436139.98268005234 283763.73273166915))',27700))

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Please try with 3.4.5 or 3.6

[alexbruy]

Closing for the lack of feedback. Please reopen if necessary.