[fdo-users] SQL 2008. View with JOINS. Not able to select features
on the layer.
miansi
AndreiSinelnikov at SierraSystems.com
Thu Feb 19 18:46:24 EST 2009
Hello,
Please advise what, where and how should I poke :confused:
Here is the issue:
I have SQL 2008 table with geospatial data. I created Dataconnection, Layer,
Map and am able to see data from view, which selecting everything from my
geospatial table:
TABLE:
/****** Object: Table [dbo].[Sanit_GM] Script Date: 02/19/2009 16:14:56
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sanit_GM](
[GeometryItem] [geometry] NULL,
[GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
[AssetID] [varchar](10) NULL,
[InstallYea] [int] NULL,
[LifeStatus] [varchar](50) NULL,
[LocDesc] [varchar](50) NULL,
[MaterialID] [varchar](50) NULL,
[SpatialKey] [varchar](10) NULL,
[SpatialSta] [varchar](50) NULL,
[AssetType] [varchar](50) NULL,
[Backfill] [varchar](50) NULL,
[BedMat] [varchar](50) NULL,
[Capacity] [decimal](38, 18) NULL,
[Diameter] [int] NULL,
[DoStreamIn] [decimal](38, 18) NULL,
[HasLining] [bit] NULL,
[Height] [int] NULL,
[Length] [decimal](38, 18) NULL,
[LiningYear] [int] NULL,
[LiningLen] [decimal](38, 18) NULL,
[PipeClass] [varchar](50) NULL,
[PipeFunc] [varchar](50) NULL,
[PipeShape] [varchar](50) NULL,
[UpStreamIn] [decimal](38, 18) NULL,
[Width] [varchar](50) NULL,
CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
(
[GeometryIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] WITH CHECK ADD CONSTRAINT
[Sanit_GM_GeometryItem_srid] CHECK (([GeometryItem].[STSrid]=(4269) OR
[GeometryItem] IS NULL))
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_srid]
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] WITH CHECK ADD CONSTRAINT
[Sanit_GM_GeometryItem_type] CHECK
(([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem] IS
NULL))
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_type]
GO
VIEW:
SELECT GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus,
LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill, BedMat,
Capacity, Diameter,
DoStreamIn, HasLining, Height, Length, LiningYear,
LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
FROM dbo.Sanit_GM AS new
I am able to see layer pointing to this view. AND I am able to select
features on the map.
http://n2.nabble.com/file/n2356197/1.Selecting.png
http://n2.nabble.com/file/n2356197/2.Selected.png
If I modify View to include any JOIN I will be able to see layer pointing to
view, BUT would NOT be able to select features on the map:
http://n2.nabble.com/file/n2356197/1.Selecting%2B-%2BJOIN.png
http://n2.nabble.com/file/n2356197/2.Selected%2B-%2BJOIN.png
VIEW:
SELECT new.GeometryItem, new.GeometryIndex, new.AssetID, new.InstallYea,
new.LifeStatus, new.LocDesc, new.MaterialID, new.SpatialKey, new.SpatialSta,
new.AssetType,
new.Backfill, new.BedMat, new.Capacity, new.Diameter,
new.DoStreamIn, new.HasLining, new.Height, new.Length, new.LiningYear,
new.LiningLen, new.PipeClass,
new.PipeFunc, new.PipeShape, new.UpStreamIn, new.Width
FROM dbo.Sanit_GM AS new INNER JOIN
dbo.Asset AS a ON a.DisplayId = new.AssetID
Now, MYSTERY %-|
If I will not refresh Dataconnection after adding join to the view - I am
still able to select features on the map.
Since Map, Layer and FeatureDource XML looks exactly the same in both cases
I wonder what is the difference? What is changing when FDO detects JOIN in
view?
Can I override this change? Can I force FDO to ignore it? Is it FDO, which
prevents selection or something else?
Is this right forum to post? Is this a bug?
Thank you,
--
View this message in context: http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-features-on-the-layer.-tp2356197p2356197.html
Sent from the FDO Users mailing list archive at Nabble.com.
More information about the fdo-users
mailing list