Problems with Layer Definition Query

[DerrickDFS]

I am using ArcMap 10.6 and the Beta version of GISquirrel and MS SQL Express 2012. I have been able to create databases on the SQL Server and import data to them then use those layers in ArcMap for mapping using different Layer Definition Querys successfully. Today I created a new database, imported data into the new database, and then added the data to ArcMap. All of the features are visible and the table attributes are correct. But when I create a Layer Definition Query with fields and values that do exist, no records are returned.

The first three images show the number of records before and after the Definition Query and how the query was created. The fourth image shows a successful query on a geodatabase that was previously created. All the geodatabases are on the same SQL Server.

Derrick

[CrispinF]

@DerrickDFS I cannot see anything immediately obvious here. Are you able to send us or upload the TSL_Block dataset or a sample of it? @HartmutT any thoughts?

[DerrickDFS]

I can try. I tried doing a def query on a point feature in the same BCTS10 SQL database and it worked as expected. I also tried doing a def query on different fields in TSL_Block and one other text field failed the same way and a different text field worked properly. When i do an edit session with TSL_Block and set the def query on the local database it works properly. Would you be able to connect to our SQL server remotely and see if you get the same issue?

Derrick Carlson

GIS Analyst

Drake Forestry Services Ltd.

1936 Bayview Crt

Kelowna, BC, V1Z 3L8

Cell: (250) 469-0142

On Wed, Oct 3, 2018 at 2:45 PM Crispin Flower notifications@github.com wrote:

@DerrickDFS https://github.com/DerrickDFS I cannot see anything immediately obvious here. Are you able to send us or upload the TSL_Block dataset or a sample of it? @HartmutT https://github.com/HartmutT any thoughts?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-426813995, or mute the thread https://github.com/notifications/unsubscribe-auth/AllkV3LFl06xgYyGlw99kOGWvsFv5oJUks5uhTAWgaJpZM4XG9Vc .

[HartmutT]

I agree, there is nothing obvious in those screen shots. I'd have to debug this with the original data. Directly connecting to your SQL Server might not work, but I can give it try if you provide me with connection details. Otherwise a backup of the database that I restore to my own server would be the next best option.

[CrispinF]

Or @DerrickDFS supply the shapefile or whatever that you imported into GISquirrel.

[DerrickDFS]

Here is a zipped copy of a file geodatabase that contains TSL_Block that is giving me problems with definition querys. Also there is another feature class that I have added called Drake_TOC_Roads. This feature class has been giving me grief adding to SQL. The domains for this feature class are not importing into SQL. I get an error as attached. Drake_Test.zip

[DerrickDFS]

Is it possible that because the field name has an underscore in it the definition query does not work in the SQL GISquirrel layer. If there is not underscore in the name then the definition query works. Does this sound right?

Derrick Carlson

GIS Analyst

Drake Forestry Services Ltd.

1936 Bayview Crt

Kelowna, BC, V1Z 3L8

Cell: (250) 469-0142

On Wed, Oct 3, 2018 at 2:45 PM Crispin Flower notifications@github.com wrote:

@DerrickDFS https://github.com/DerrickDFS I cannot see anything immediately obvious here. Are you able to send us or upload the TSL_Block dataset or a sample of it? @HartmutT https://github.com/HartmutT any thoughts?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-426813995, or mute the thread https://github.com/notifications/unsubscribe-auth/AllkV3LFl06xgYyGlw99kOGWvsFv5oJUks5uhTAWgaJpZM4XG9Vc .

[DerrickDFS]

I have made a bit of a discovery in regards to the definition query issue. It seems that if the field name in the SQL feature class has an underscore in the name, not the first character but somewhere after the first character and before the last character, then definition queries do not work for that feature class. This applies to polygon or line features. If the feature class is a point feature, then that rule does not seem to apply and definition queries can successfully be created on any field. We tested renaming a field in the File GDB to remove the underscore and imported that into Squirrel and even though the field no longer showed an underscore it the definition query did not work. We then created a new field without underscores and made the Alias have the underscores in the file GDB and calculated the data from the problem field to the new field. Then from ArcCatalog we deleted the problem field and then imported the file GDB into Squirrel. We were now able to create a definition query on the new field and the data driven page labels recognized the Alias name. Are we missing something or is there an easier way to make this work?

[HartmutT]

No, I’m afraid it does not. Underscores are legal characters in field and column names. I can add definition queries referencing fields whose names contain underscores and the layer will be properly filtered.

From: DerrickDFS notifications@github.com Sent: 04 October 2018 18:16 To: exeGesIS-SDM/GISquirrel GISquirrel@noreply.github.com Cc: Hartmut Tschauner HartmutTschauner@esdm.co.uk; Mention mention@noreply.github.com Subject: Re: [exeGesIS-SDM/GISquirrel] Problems with Layer Definition Query (#18)

Is it possible that because the field name has an underscore in it the definition query does not work in the SQL GISquirrel layer. If there is not underscore in the name then the definition query works. Does this sound right?

Derrick Carlson

GIS Analyst

Drake Forestry Services Ltd.

1936 Bayview Crt

Kelowna, BC, V1Z 3L8

Cell: (250) 469-0142

On Wed, Oct 3, 2018 at 2:45 PM Crispin Flower notifications@github.com<mailto:notifications@github.com> wrote:

@DerrickDFS https://github.com/DerrickDFS I cannot see anything immediately obvious here. Are you able to send us or upload the TSL_Block dataset or a sample of it? @HartmutT https://github.com/HartmutT any thoughts?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-426813995, or mute the thread https://github.com/notifications/unsubscribe-auth/AllkV3LFl06xgYyGlw99kOGWvsFv5oJUks5uhTAWgaJpZM4XG9Vc .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-427099434, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AVAV0b31_-qLGw2yorSoCnU8mRRjuQSAks5uhkJlgaJpZM4XG9Vc.

[HartmutT]

I’ve tested MSSQL layers that have field names with underscores as described and definition queries work fine. Could you perhaps send me a copy of the file GDB that you’re importing from and tell me which feature classes and field names produce the errors you describe?

From: DerrickDFS notifications@github.com Sent: 04 October 2018 19:47 To: exeGesIS-SDM/GISquirrel GISquirrel@noreply.github.com Cc: Hartmut Tschauner HartmutTschauner@esdm.co.uk; Mention mention@noreply.github.com Subject: Re: [exeGesIS-SDM/GISquirrel] Problems with Layer Definition Query (#18)

I have made a bit of a discovery in regards to the definition query issue. It seems that if the field name in the SQL feature class has an underscore in the name, not the first character but somewhere after the first character and before the last character, then definition queries do not work for that feature class. This applies to polygon or line features. If the feature class is a point feature, then that rule does not seem to apply and definition queries can successfully be created on any field. We tested renaming a field in the File GDB to remove the underscore and imported that into Squirrel and even though the field no longer showed an underscore it the definition query did not work. We then created a new field without underscores and made the Alias have the underscores in the file GDB and calculated the data from the problem field to the new field. Then from ArcCatalog we deleted the problem field and then imported the file GDB into Squirrel. We were now able to create a definition query on the new field and the data driven page labels recognized the Alias name. Are we missing something or is there an easier way to make this work?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-427128496, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AVAV0eEOR4dF4rg4-Hzdno1Rw7ex-G9Rks5uhleRgaJpZM4XG9Vc.

[DerrickDFS]

There is a geodatabase on GitHub that I posted 6hrs ago. There are two feature classed in the geodatabase. TSL_Block is a polygon feature class and the field that works in that one is UBI, while I have tried Block_ID, Permit_ID, and Licence_ID. All three of those fields produce problems. Same thing with Drake_TOC_Roads which is a line feature class. Same fields cause problems.

Derrick Carlson

GIS Analyst

Drake Forestry Services Ltd.

1936 Bayview Crt

Kelowna, BC, V1Z 3L8

Cell: (250) 469-0142

On Thu, Oct 4, 2018 at 3:04 PM HartmutT notifications@github.com wrote:

I’ve tested MSSQL layers that have field names with underscores as described and definition queries work fine. Could you perhaps send me a copy of the file GDB that you’re importing from and tell me which feature classes and field names produce the errors you describe?

From: DerrickDFS notifications@github.com Sent: 04 October 2018 19:47 To: exeGesIS-SDM/GISquirrel GISquirrel@noreply.github.com Cc: Hartmut Tschauner HartmutTschauner@esdm.co.uk; Mention < mention@noreply.github.com> Subject: Re: [exeGesIS-SDM/GISquirrel] Problems with Layer Definition Query (#18)

I have made a bit of a discovery in regards to the definition query issue. It seems that if the field name in the SQL feature class has an underscore in the name, not the first character but somewhere after the first character and before the last character, then definition queries do not work for that feature class. This applies to polygon or line features. If the feature class is a point feature, then that rule does not seem to apply and definition queries can successfully be created on any field. We tested renaming a field in the File GDB to remove the underscore and imported that into Squirrel and even though the field no longer showed an underscore it the definition query did not work. We then created a new field without underscores and made the Alias have the underscores in the file GDB and calculated the data from the problem field to the new field. Then from ArcCatalog we deleted the problem field and then imported the file GDB into Squirrel. We were now able to create a definition query on the new field and the data driven page labels recognized the Alias name. Are we missing something or is there an easier way to make this work?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub< https://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-427128496>, or mute the thread< https://github.com/notifications/unsubscribe-auth/AVAV0eEOR4dF4rg4-Hzdno1Rw7ex-G9Rks5uhleRgaJpZM4XG9Vc>.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/exeGesIS-SDM/GISquirrel/issues/18#issuecomment-427184787, or mute the thread https://github.com/notifications/unsubscribe-auth/AllkV2ldlQgqOW74O8vtWEqTgD1ayyAkks5uhoX3gaJpZM4XG9Vc .

[HartmutT]

There is a bug in the code that builds the where clause of the SQL statement that pulls data from the database. It erroneously identifies the "ID" part of the "Block_ID", "Permit_ID" and "Licence_ID" fields as the key field name, "id", causing a syntax error. This happens both in MSSQL and in PGSQL. I've corrected the faulty bit of code. It will require a new build for these changes to take effect. In the meantime, a workaround is changing field names so that no field name contains the name of the feature class's ID column. This can be done by changing column names in the database. As long as the column is not the key or geometry column and does not use a domain, there will be no side effects. If the ID or geometry column is renamed, the arcsquirrelmetadata table needs to be updated accordingly. If the column uses a domain the arcsquirreldomains table needs to be updated accordingly.

[HartmutT]

The Drake_TOC_Roads feature class has multiple domains that are used by more than one field. There was an error in the query that determines whether an existing domain has the same value-name pairs and can be re-used by another column of the spatial table created by the feature class import. This resulted in a rollback of the import transaction. I've corrected the error and a similar one in the query that gathers the names of lookup tables to be dropped when a feature class is deleted. Again, this code change will require a new build to take effect. I cannot think of a workaround other than avoiding domains used by multiple columns of the same import feature class, which may not be practical.

[DerrickDFS]

Thanks for the information. I had figured out that the _ID name was probably causing the problem but did not know why. We have renamed the fields that use for definition queries and have moved forward. As for the problem with multiple domains we will just have to figure something out until the next build comes out. Do you have any idea when that might be?

[CrispinF]

From @HartmutT "both issues fixed in most recent code to be included in next build."...which will be very soon now!

[CrispinF]

Was resolved in v1.3.4529