Bug report #20414

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

Added by CSW Broadband about 6 years ago. Updated over 5 years ago.

Status:Feedback
Priority:High
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:3.4.0 Regression?:Yes
Operating System:Windows 10 v 1803 Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:28234

Description

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

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, "Date Introduced" DATE, "Date Withdrawn" DATE, "CodePoint UPP" TEXT, "geom" TEXT)) 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 NOT NULL,
[Postcode] [nvarchar](10) NOT NULL,
[Date Introduced] [date] NULL,
[Date Withdrawn] [date] NULL,
[CodePoint UPP] [nvarchar](255) 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 - Attrib table, source fields, Info from provider, connection (356 KB) CSW Broadband, 2018-11-08 03:47 PM

History

#1 Updated by Giovanni Manghi about 6 years ago

  • Priority changed from Normal to High
  • Regression? changed from No to Yes

#2 Updated by Martin Dobias about 6 years ago

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?

#3 Updated by CSW Broadband about 6 years ago

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

#4 Updated by Giovanni Manghi about 6 years ago

  • Status changed from Open to Feedback

#5 Updated by CSW Broadband about 6 years ago

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))

#6 Updated by Giovanni Manghi over 5 years ago

Please try with 3.4.5 or 3.6

Also available in: Atom PDF