[fdo-users] SQL 2008. View with JOINS. Not able to select features on the layer.

Brent Robinson brent.robinson at autodesk.com
Fri Feb 27 10:47:48 EST 2009 

Hi,

When the SQLServerSpatial provider generates a class for a view, it derives the identity properties from the primary key (or unique index if primary key is not present) of the base table. However, this is only done if the view is only based on one table. If the view joins 2 tables, it ends up without identity. It's features can be drawn in MapGuide but are not selectable. I looked at a couple of options but some limitations prevent them from being used until modifications are done to the provider.

One way to specify the identity properties is through a config document, which can be attached to the feature source. Unfortunately, the SQLServerSpatial advertises that it does not support config documents even though it does. I've logged a trac ticket (http://trac.osgeo.org/fdo/ticket/469) for this defect.

SQL Server allows clustered indexes to be set on deterministic views but the provider does not currently look at indexes on views. A ticket has also been logged for this issue (http://trac.osgeo.org/fdo/ticket/470). In another e-mail, you mentioned trying the deterministic view (with schemabinding). Did you encounter a problem setting up the view, or did you get it created successfully but found that the features were still not selectable?  

Unfortunately, none of these lead to an immediate solution for FDO 3.4. I'll continue to investigate to see if there is another alternative. 

Brent.
 
-----Original Message-----
From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Jackie Ng
Sent: Thursday, February 19, 2009 10:30 PM
To: fdo-users at lists.osgeo.org
Subject: RE: [fdo-users] SQL 2008. View with JOINS. Not able to select features on the layer.


A Feature Source describes a FDO connection, so get the XML content for that
feature source, and look at each <Name> and <Value> element between the
<Parameter> elements.

When you connect via the generic dialog, select the SQL Server Spatial
provider. The connection property grid should be filled with the required
parameters (which should match your <Name> elements). Fill in the blanks
using the matching <Value> elements in the feature source XML.

You shouldn't need to use the ConnectionString property, but if you do I
think you have to surround the value with quotes (like the ODBC provider)

- Jackie


miansi wrote:
>
> Thank you Jackie.
>
>
>
> Can you help me with connection string? Seems like FDO toolbox missing
> simple example of connection string for SQL 2008.
>
>
>
>
>
> Thank you,
>
>
>
> From: Jackie Ng (via Nabble)
> [mailto:ml-user+64137-1002950291 at n2.nabble.com]
> Sent: Thursday, February 19, 2009 5:15 PM
> To: Sinelnikov, Andrei
> Subject: Re: [fdo-users] SQL 2008. View with JOINS. Not able to select
> features on the layer.
>
>
>
> Maybe try looking at this data source through FDO Toolbox
> (http://fdotoolbox.googlecode.com), and take a look at the feature
> classes, and see if the view (with the join) is structurally different
> from the view (without the join).
>
> For example, I could foresee some problem if the view (without the join)
> has an Identity Property and the view (with the join) doesn't.
>
> - Jackie
>
> miansi wrote:
>
> Hello,
>
> Please advise what, where and how should I poke
> <http://n2.nabble.com/images/smiley/anim_confused.gif>
>
> 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  <http://n2.nabble.com/images/smiley/anim_crazy.gif>
>
> 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 FeatureSource 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,
>
>
>
> ________________________________
>
> This email is a reply to your post @
> http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-featu
> res-on-the-layer.-tp2356197p2356328.html
> You can reply by email or by visting the link above.
>
>
>
>
>
> ----Notice Regarding Confidentiality----
> This email, including any and all attachments, (this "Email") is intended
> only for the party to whom it is addressed and may contain information
> that is confidential or privileged.  Sierra Systems Group Inc. and its
> affiliates accept no responsibility for any loss or damage suffered by any
> person resulting from any unauthorized use of or reliance upon this Email.
> If you are not the intended recipient, you are hereby notified that any
> dissemination, copying or other use of this Email is prohibited.  Please
> notify us of the error in communication by return email and destroy all
> copies of this Email.  Thank you.
>
>

--
View this message in context: http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-features-on-the-layer.-tp2356197p2356967.html
Sent from the FDO Users mailing list archive at Nabble.com.

_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users

More information about the fdo-users mailing list