Bug report #5235

Link to SQL server spatial view fails

Added by James Perrins over 7 years ago. Updated about 7 years ago.

Status:Closed
Priority:Normal
Assignee:Tamas Szekeres
Category:Data Provider
Affected QGIS version:master Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed
Crashes QGIS or corrupts data:No Copied to github as #:14971

Description

Trying to link to a SQL view that contains geometry.
I get offered the table in the list of possible layers.

Invalid Layer
---------------------------
dbname='CMSI' host=JAMESP-THINK srid=27700 type=POLYGON table="dbo"."vFeaturesGeom_Polygon" (Geom) sql= is an invalid layer and cannot be loaded.

The underlying SitesGeom table connects OK.

The SQL which fails was: (I can provide more detail if required)

SELECT dbo.Sites.SiteCode, dbo.Sites.SiteName, dbo.SitesGeom.Geom, dbo.SitesGeom.MI_PRINX, dbo.SiteTree.SiteTreeName
FROM dbo.Sites INNER JOIN
dbo.SitesGeom ON dbo.Sites.SiteCode = dbo.SitesGeom.SiteCode LEFT OUTER JOIN
dbo.SiteTree ON dbo.Sites.SiteTreeCodeParent = dbo.SiteTree.SiteTreeCode

script.zip (1.14 MB) James Perrins, 2012-03-30 03:53 AM

History

#1 Updated by James Perrins over 7 years ago

Tested on version 1.9.90-Alpha

#2 Updated by Nathan Woodrow over 7 years ago

  • Assignee set to Tamas Szekeres

#3 Updated by James Perrins over 7 years ago

The error message should have of course been:

dbname='CMSI' host=JAMESP-THINK srid=27700 type=POLYGON table="dbo"."vSitesGeom" (Geom) sql= is an invalid layer and cannot be loaded.

(I was trying a variety of views - some of which worked - some didn't - but I can't (yet) see a pattern. I pasted a mismatch error with the view - apologies)

#4 Updated by James Perrins over 7 years ago

A bit more information - hope this helps:

OK - I set up a really simple view - I have a table SitesGeom that is displaying correctly - so I don't think its a data issue

I created a view on this table that simply dropped a number of columns and called it SitesTest
(SELECT SiteCode, Geom AS MyGeom FROM dbo.SitesGeom)

I made sure all entries were in geometry_columns table (same settings for base table and view)

When I tried to add table - I watched in profiler and got:

select f_geometry_column, coord_dimension, srid, geometry_type from geometry_columns where f_table_schema = 'dbo' and f_table_name = 'SitesTest'
exec sp_columns N'SitesTest', NULL, NULL, NULL, NULL
select [MyGeom] from [dbo].[SitesTest]

I didn't get this SQL (which I do for the base table)
select srtext from spatial_ref_sys where srid = 27700

So presumably problem is between those 2 statements. All SQL in profiler seemed valid.

Hope this helps

#5 Updated by Tamas Szekeres over 7 years ago

James Perrins wrote:

A bit more information - hope this helps:

OK - I set up a really simple view - I have a table SitesGeom that is displaying correctly - so I don't think its a data issue

I created a view on this table that simply dropped a number of columns and called it SitesTest
(SELECT SiteCode, Geom AS MyGeom FROM dbo.SitesGeom)

I made sure all entries were in geometry_columns table (same settings for base table and view)

When I tried to add table - I watched in profiler and got:

select f_geometry_column, coord_dimension, srid, geometry_type from geometry_columns where f_table_schema = 'dbo' and f_table_name = 'SitesTest'
exec sp_columns N'SitesTest', NULL, NULL, NULL, NULL
select [MyGeom] from [dbo].[SitesTest]

I didn't get this SQL (which I do for the base table)
select srtext from spatial_ref_sys where srid = 27700

So presumably problem is between those 2 statements. All SQL in profiler seemed valid.

Hope this helps

Could you test the same thing using https://github.com/szekerest/Quantum-GIS ?
There have been a couple of changes recently which may affect this issue

#6 Updated by James Perrins over 7 years ago

I can try - is there a Windows build in there somewhere - or is it just source ?

In which case presumably I need to set up a build environment - which I don't have at the moment (but was planning on trying to get going in the next couple of weeks ).

I assume its not quite as straight forward as simply downloading git repository and hitting build in VS 2008 ?

Thanks

#7 Updated by Tamas Szekeres over 7 years ago

James Perrins wrote:

I can try - is there a Windows build in there somewhere - or is it just source ?

In which case presumably I need to set up a build environment - which I don't have at the moment (but was planning on trying to get going in the next couple of weeks ).

I assume its not quite as straight forward as simply downloading git repository and hitting build in VS 2008 ?

Thanks

Uploaded a Windows build here: http://www.gisinternals.com/tests/qgis1.9.90.zip

#8 Updated by James Perrins over 7 years ago

Same error with your latest build I'm afraid

#9 Updated by Tamas Szekeres over 7 years ago

James Perrins wrote:

Same error with your latest build I'm afraid

Do you have an integer column with unique values in the view?

#10 Updated by James Perrins over 7 years ago

Yes - unique integer field is present.

Same view works well in your mapserver sql2008 plugin

CREATE TABLE [dbo].[SitesGeom](
[guid] [uniqueidentifier] NOT NULL,
[SiteCode] [nvarchar](10) NOT NULL,
[geom] [geometry] NULL,
[VersionNumber] [timestamp] NOT NULL,
[MI_PRINX] [int] IDENTITY NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](255) NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[xgLockedBy] [nvarchar](50) NULL,
[xgGeometryType] [int] NULL,
[GISType] [int] NOT NULL,
CONSTRAINT [PK_SitesGeom] PRIMARY KEY CLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_SitesGeom] UNIQUE NONCLUSTERED
(
[SiteCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

#11 Updated by Tamas Szekeres over 7 years ago

James Perrins wrote:

Yes - unique integer field is present.

Same view works well in your mapserver sql2008 plugin

CREATE TABLE [dbo].[SitesGeom](
[guid] [uniqueidentifier] NOT NULL,
[SiteCode] [nvarchar](10) NOT NULL,
[geom] [geometry] NULL,
[VersionNumber] [timestamp] NOT NULL,
[MI_PRINX] [int] IDENTITY NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](255) NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[xgLockedBy] [nvarchar](50) NULL,
[xgGeometryType] [int] NULL,
[GISType] [int] NOT NULL,
CONSTRAINT [PK_SitesGeom] PRIMARY KEY CLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_SitesGeom] UNIQUE NONCLUSTERED
(
[SiteCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

How is your view definition looking like?

#12 Updated by James Perrins over 7 years ago

OK - a bit more...

This view works:

SELECT SiteCode, Geom AS MyGeom, MI_PRINX
FROM dbo.SitesGeom

In my attempt to simplify I had removed the unique integer field by mistake - stupid - sorry...)

This is the real view that I was trying to use (and still fails). It works fine in mapserver. It appears OK and correctly identifies SRID and that it contains polygons - but trying to add to map gives error above:

CREATE VIEW [dbo].[vSitesGeom]
AS
SELECT dbo.Sites.SiteCode, dbo.Sites.SiteName, dbo.SitesGeom.Geom, dbo.SitesGeom.MI_PRINX, dbo.SiteTree.SiteTreeName
FROM dbo.Sites INNER JOIN
dbo.SitesGeom ON dbo.Sites.SiteCode = dbo.SitesGeom.SiteCode LEFT OUTER JOIN
dbo.SiteTree ON dbo.Sites.SiteTreeCodeParent = dbo.SiteTree.SiteTreeCode

In case its useful the SiteTree table that is joined in view above is:

CREATE TABLE [dbo].[SiteTree](
[SiteTreeCode] [nvarchar](10) NOT NULL,
[ParentSiteTreeCode] [nvarchar](10) NULL,
[SiteTreeName] [nvarchar](100) NULL,
[SiteTreeOrder] [int] NULL,
[VersionNumber] [timestamp] NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](255) NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[guid] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_SiteTree] PRIMARY KEY NONCLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [LogicalKey_SiteTree] UNIQUE CLUSTERED
(
[SiteTreeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SiteTree] WITH CHECK ADD CONSTRAINT [FK_SiteTree_SiteTree] FOREIGN KEY
REFERENCES [dbo].[SiteTree] ([SiteTreeCode])
GO

ALTER TABLE [dbo].[SiteTree] CHECK CONSTRAINT [FK_SiteTree_SiteTree]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Modifi__29EC2402] DEFAULT (getdate()) FOR [Modified]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Modifi__2AE0483B] DEFAULT (user_name()) FOR [ModifiedBy]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Create__2BD46C74] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__Create__2CC890AD] DEFAULT (user_name()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[SiteTree] ADD CONSTRAINT [DF__SiteTree__guid__61074EC2] DEFAULT (newsequentialid()) FOR [guid]
GO

#13 Updated by Tamas Szekeres over 7 years ago

Are you sure the left outer join on the view definition doesn't affect the uniqueness of the MI_PRINX column?
If you could provide some test data, that would be helpful to reproduce this behaviour.

#14 Updated by James Perrins over 7 years ago

I don't think the join makes it non unique - as I say - it definitely works in map server - wouldn't that have the same issue ?

SQL script that will create tables and data in your db attached

Many Thanks

#15 Updated by Tamas Szekeres over 7 years ago

James Perrins wrote:

I don't think the join makes it non unique - as I say - it definitely works in map server - wouldn't that have the same issue ?

SQL script that will create tables and data in your db attached

Many Thanks

Tested with your data and works for me. Are you sure you use the latest version?

#16 Updated by Tamas Szekeres over 7 years ago

  • Status changed from Open to Feedback

#17 Updated by James Perrins about 7 years ago

This appears to now work in 1.9-0 Master
QGIS code revision
829f672

Thanks

#18 Updated by Giovanni Manghi about 7 years ago

  • Status changed from Feedback to Closed
  • Resolution set to fixed

Also available in: Atom PDF