AutoCAD Map 3D General Discussion

AutoCAD Map 3D General Discussion

Reply
Active Member
dlalonde
Posts: 9
Registered: ‎11-12-2009
Message 1 of 9 (361 Views)

Shapefile joined to ODBC connected table - Filter not working

361 Views, 8 Replies
12-11-2013 07:25 AM
I have a Shapefile layer added to the map via FDO. I joined it to a SQL Server based table via ODBC. I can see all the data (shapefile & ODBC table) correctly in the attribute table, but when comes time to "Query to Filter Data...", only fields in the Shapefile are available and displayed in the Filter dialogue. I want to be able to Filter using fields in the joined. If I export that same Shapefile to an SDF and THEN try the same join and filter, all the fields from the SDF as well as the joined ODBC table are available in the filter dialogue. What's up? Thanks, Denis
*Expert Elite*
Alfred.NESWADBA
Posts: 9,216
Registered: ‎06-29-2007
Message 2 of 9 (320 Views)

Re: Shapefile joined to ODBC connected table - Filter not working

12-13-2013 06:28 PM in reply to: dlalonde

Hi,

 

what version of Map3D do you have?

 

- alfred -

-------------------------------------------------------------------------
Alfred NESWADBA
Ingenieur Studio HOLLAUS ... www.hollaus.at
-------------------------------------------------------------------------
Active Member
dlalonde
Posts: 9
Registered: ‎11-12-2009
Message 3 of 9 (311 Views)

Re: Shapefile joined to ODBC connected table - Filter not working

12-14-2013 06:32 PM in reply to: Alfred.NESWADBA
Oops, sorry. It's 2013
*Expert Elite*
braudpat
Posts: 2,277
Registered: ‎12-15-2006
Message 4 of 9 (305 Views)

Re: Shapefile joined to ODBC connected table - Filter not working

12-15-2013 12:58 AM in reply to: dlalonde

 

Hello

 

*** 1 ***

I don't think it will solve your problem but it's better !

Have you installed the SP 2.1 for MAP 2013 ?

http://usa.autodesk.com/adsk/servlet/ps/dl/item?siteID=123112&id=21949876&linkID=9240858

 

*** 2 *** The bad news

I have found sometimes strange bugs/limitations with joins with MAP 2013 (even with SP 2.1)

And I have not the same problem with MAP 2012 (+ SP 2), so ...

Maybe it's your problem !, Not sure at all !

Can you do a test with MAP 2012 + SP 2 or with MAP 2014 + SP 1 ?

 

Have you done the same join with an older MAP ?

 

Bye, Pat

 

Bye, Pat ( Supporting Troops )
If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Compliment) - Thanks
*Expert Elite*
Alfred.NESWADBA
Posts: 9,216
Registered: ‎06-29-2007
Message 5 of 9 (301 Views)

Re: Shapefile joined to ODBC connected table - Filter not working

12-15-2013 02:19 AM in reply to: dlalonde

Hi,

 

>> If I export that same Shapefile to an SDF and THEN try the same join and filter

Tested that and sorry to say:

  • connection from SHP to SQL-Server/ODBC (via connectionstring) does not allow to filter by using joined fields
  • when exporting the SHP to SDF, then connect to the SDF and join again to SQL-Server I'm also not able to get the joined fields in the list for filtering ... so you have an advantage if you are able to do that with SDF, I can't.
  • Sometimes it makes a difference depending on how the primary key or index is defined in SQL-Server, but with a little bit playing I have not got a solution now, sorry.

     

    In some situations it might be an option to load the SHP, run the joining and export that as new SDF ... because then you have the data if the joined fields transfered from SQL_Server into your SDF.

    The advantage is then

  • you can do your filters then as the fields are stored directly to the geometry
  • that you don't need the (slower) join-connection to SQL-Server
  • The disadvantage might be

  • the data is duplicated and so any changes to the data of the SQL-Server tables are not represented in your SDF/your project.
  • This image shows the joined options, 2 fields in the SDF (+ 1 for geometry) and the rest is from relation to data from SQL-Server ==> and the query dialog does not show the additional fields.

     

    2013-12-15 11-04-21.png

     

    - alfred -

    PS: I have tested with AutoCAD Map3D 2013 (G.114 HF1), AutoCAD Map3D 2014 (I.108 SP1) ... all German ... on Win7x74 with SQL-Server 2012 (64bit)

    -------------------------------------------------------------------------
    Alfred NESWADBA
    Ingenieur Studio HOLLAUS ... www.hollaus.at
    -------------------------------------------------------------------------
    Active Member
    dlalonde
    Posts: 9
    Registered: ‎11-12-2009
    Message 6 of 9 (286 Views)

    Re: Shapefile joined to ODBC connected table - Filter not working

    12-16-2013 05:59 AM in reply to: Alfred.NESWADBA
    Thanks for the help guys. I tried installing SP2.1 to no avail. Indexes and PKs are all correctly defined. I do believe, like braudpat asked, that this did work in previous versions of Map3D. Unfortunately, the data has to remain SHP, so exporting to SDF (while it is a workaround) isn't a viable option.
    Product Support
    phil_borycens
    Posts: 196
    Registered: ‎10-25-2011
    Message 7 of 9 (279 Views)

    Re: Shapefile joined to ODBC connected table - Filter not working

    12-16-2013 10:24 AM in reply to: dlalonde

    There is an issue that I have reproduced on Map 3D 2014 (with SHP joined to ODBC) that might also be the cause of the issue you report. It is being tracked as DE1705 internally. I call it "Impossible to label using joined fields". Basically when you got to Style Editor > Style Label > and choose Expression to build an expression to label features, only the native fields 'stick'; when the Expression includes fields from the joined attributes, the Expression changes itself unexpectedly after hitting OK to leave the Create/Modify Expressions window.

     

    This is nominated for a fix but unfortunately I do not know if/when/how a fix will be produced or released.

     

    I hope this helps to at least confirm the behavior that you are seeing.



    Phil Borycens
    *Expert Elite*
    braudpat
    Posts: 2,277
    Registered: ‎12-15-2006
    Message 8 of 9 (275 Views)

    Re: Shapefile joined to ODBC connected table - Filter not working

    12-16-2013 10:58 AM in reply to: dlalonde

     

    Hello Autodesk & Everyone (from France)

     

    An other bug/problem maybe linked to the ODBC problem ...

     

    Today I haven't any MAP and Oracle to check  again, sorry !

     

    But If I remember well when doing double join (this means join on a Oracle Table and join again on a other Oracle table) 

    With MAP 2013 (and may be with MAP 2014) I can't use any field coming from the double join (second table) to do something ...

    But I can use fields coming from the first join ...

     

    And with MAP 2012 : no problem !

     

    So I think it could be linked to the general problem of join with ODBC !

     

    Waiting for your answer ...

     

    Bye, Pat

     

    PS: I know, it's bad to do that ! - A good view from Oracle could be the solution !?

     

     

     

     

     

    Bye, Pat ( Supporting Troops )
    If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Compliment) - Thanks
    Active Member
    dlalonde
    Posts: 9
    Registered: ‎11-12-2009
    Message 9 of 9 (273 Views)

    Re: Shapefile joined to ODBC connected table - Filter not working

    12-16-2013 11:00 AM in reply to: phil_borycens
    Thanks Phil. Sorry about complaining here, but both my bug and the one you bring up are pretty basic stuff and are yet another example of why serious ArcGIS guys like myself have a hard time making the jump. Hope to see a fix soon...
    Post to the Community

    Have questions about Autodesk products? Ask the community.

    New Post
    Need installation help?

    Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.