Bug report #20414
MSSQL: Layer loads but does not display (Attribute Table correct)
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)
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
- File QGIS Bug 20414.docx added
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