topic Re: Certain users can't Review/Rematch Addresses using SDE Locator in Data Management Questions

Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Wed, 14 Sep 2011 18:55:28 GMT

I may have found a bug (or a feature) when using geocoding locators stored in SDE. We are in an ArcGIS 10 SP2 environment running on MS SQL Server 2005. We have a user I'll call "GISAdmin" that has SQL Database Role
Membership as a "db_datareader", "db_datawriter", "db_ddladmin", "db_owner". We have a user I'll call "GISUser" that has SQL Database Role Membership as a "db_datareader" on all datasets by default. We use Database
Authentication when making a Spatial Database Connection in ArcCatalog.

Using ArcCatalog I create an ArcGIS 10 geocoding locator, called Addr_Pnt_Locator, using the GISAdmin user in the SDE database. The locator reference dataset is in the same SDE database. Following the ArcGIS 10 Help instructions "Accessing an ArcSDE address locator" I grant the GISUser "Allow this user to use the selected object(s)" privileges to the newly created locator. When I review what has happened in SQL Server I see that the Addr_Pnt_Locator_LOX table now has the GISUser added to its permissions with SELECT permissions.

I then open ArcMap, bring in a File GDB table to geocode, open a connection as the GISUser to the SDE database and add the Addr_Pnt_Locator to the project. When I geocode the table it works fine, until the point where it asks if you want to Review/Rematch. If you choose "yes" you receive the following error "There was an error reading the geocoding information from this dataset." You can't Review/Rematch after the fact using the toolbar button, you receive the same error.

If I do the same test, but use a connection to the database as the GISAdmin user I can Review/Rematch without a problem.

As another test I used SQL Server Management Studio to Grant the GISUser the following additional permissions on the Addr_Pnt_Locator_LOX table: Alter, Delete, Insert and Update. When I attempted to Review/Rematch I received the same error message. I do not want to go so far as to give the GISUser SQL Database Role Membership as a "db_datawriter" because we don't want this user to be able to edit the data in this database.

I know that this error message came up in another forum post, but I don't think the "export unmatched records and re-geocode" is an adequate solution. If the record didn't match the first time I don't think it's going to magically match successive times through the
process. The Review/Rematch functionality should work in the interface so that the human element can triage ambiguous/wrong addresses.

I have come up with a work around, which involves creating an ArcGIS Server Geocoding Service from the address locator and then have the GISUser consume the service in ArcMap. It does allow you to Review/Rematch addesses. If there is anyone out there that has seen this and can provide a solution that would be great. Thanks!

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeBorgione — Wed, 14 Sep 2011 21:51:56 GMT

This is a total WAG (wild a** guess), but what are the permission/privileges on the geocoding results point feature class? If you are working in the SDE environment, wouldn't that feature class need to be registered as versioned?

Sorry this isn't much help but might be worth looking into. While I use an SDE locator, I typically write the results to a pgdb or a fgdb so I've never encountered what you are experiencing.

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Thu, 15 Sep 2011 15:45:52 GMT

Joe B.,

Thanks. That's a great thought, but the weird things is that I am writing the results FC to a FGDB, not to the SDE DB. I was mucking around with permissions on the *_LOX table because I figured as a shot in the dark that maybe the geocoding process needed to "temporarily" write something in the LOX, but I threw the question out here hoping somebody from ESRI would give me a clue.

Thanks again!

Joe H.

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeBorgione — Thu, 15 Sep 2011 20:24:11 GMT

But can the users edit any other fc in that fgdb? If they can then it's weirder than a permissions issue, right?

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Fri, 16 Sep 2011 14:41:35 GMT

Joe B.

I haven't tested if the GISUser can edit a FC in a FGDB because that user only exists to connect to the SDE database and isn't a Windows Active Directory/Computer User. It would be my understanding, maybe incorrectly, that when a user is interacting with the FGDB they would not be connecting to it as the GISUser, but as their Windows user (Bubba). Bubba can do whatever they want to the FGDB as long as they have permissions to the directory (locally or network share). I would think that Bubba is writing the geocoded results into the FGDB, but is just using the GISUser to connect to the SDE database. I tested this theory by creating a GISUser account on my local machine and then giving it full permissions to the folder which contains the FGDB into which I'm writing data. No luck, still getting the same error message...

Also, I'm not aware of being able to set permissions on a FGDB or FC's within a FGDB. I poked around ArcCatalog and didn't see where it could be set. Am I missing something?

Thanks for the suggestions!

Re: Certain users can't Review/Rematch Addresses using SDE Locator

BobLaverty — Fri, 16 Sep 2011 15:50:26 GMT

Looking at it as DBA (Oracle), I'm wondering whether the rematch is trying to establish an updateable join between the output FGDB FC and the locator in SDE. It might be looking to have 'write' privileges on the locator table that it doesn't need, but the database isn't smart enough to know that. It would explain why it works with the other ID.

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Fri, 16 Sep 2011 16:59:49 GMT

Bob,

Good thought, but I went down the path (for testing purposes only on our "test" server) of giving the GISUser all of the same Database Role Membership permissions as our GISAdmin user in order to suss out if something was getting written (updateable join as you mentioned?) into SDE behind the scenes. I still get the error.

Thanks,

Joe H.

Re: Certain users can't Review/Rematch Addresses using SDE Locator

BobLaverty — Fri, 16 Sep 2011 20:43:15 GMT

Joe B. might have touched on it then. SDE version control might be preventing the locator table from joining in an updateable view. It's locked for the GISuser but not the GISadmin.

Another shot in the dark. Good luck.

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Mon, 19 Sep 2011 14:26:04 GMT

Bob, Joe B.,

Thanks for the continued support and ideas. I took another test DB, deleted all the versions that we had on it so only DEFAULT was present. I created the address locator as GISAdmin. Used GISUser to geocode, but still couldn't rematch. I'm out of ideas.

Thanks,

Joe

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeBorgione — Mon, 19 Sep 2011 14:54:14 GMT

Probably time to contact tech support....

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Mon, 19 Sep 2011 15:52:45 GMT

Joe B.,

Thanks again to you and Bob for all your help. I was hoping that someone from ESRI's geocoding crew would be eavesdropping on this conversation and chime in at some point. No such luck. I'll start the process with tech support and post back here if or when there is an explanation.

Joe H.

Re: Certain users can't Review/Rematch Addresses using SDE Locator

MichelleBoivin — Tue, 28 Feb 2012 13:23:54 GMT

Just to chime in, it is now February 28th and we had the same issue when our emergency response teams were trying to rematch incident addresses from a previously geocoded shapefile. After calling ESRI, here was their response:

"This is Andrew from ESRI Technical Support. I have taken ownership of incident 1005918 and will be assisting you with the issue you are experiencing.

I appreciate the detailed description of the problem that you provided. It seems that you are encountering a bug - NIM065152: "Unable to rematch when geocoding with an ArcSDE address locator, unless the address locator is added to ArcMap as its owner in the DBMS."

You may wish to consider that at version 10.0, it is no longer considered best practice to use ArcSDE for deploying server-side geocoding services. A possible workaround to the issue that you have encountered would be to create the address locator in an file geodatabse and publish the address locator to ArcGIS Server. Please see the following article for more details:
http://resources.arcgis.com/content/kbase?fa=articleShow&d=35871

I have added your incident to NIM065152 - your business is very important to us and I apologize for any inconvenience this may cause."

~Michelle

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeBorgione — Tue, 28 Feb 2012 14:55:14 GMT

Interesting Michelle- thanks for the update.

Since this original post I've gained an appreciable amount of experience using 10.0 for locators. Painful at times, but in a good sort of way....

One of the things that I've gone to is creating locators in their own stand-alone directory. I used to keep them in the same geodatabase as the matching data, address list, and results. A nice tidy package, but it's been explained to me that keeping a locator in a geodatabase creates a bunch of un-needed over head. For distribution I publish them as suggested in Michelle's thread.

Also noticed in the original post (several months old now) that Joe H was at sp2. My suggestion is to be sure SP3 is installed and there is a special Geocoding Patch for sp3 available. See an earlier post of mine for a link to the patch...

Re: Certain users can't Review/Rematch Addresses using SDE Locator

JoeHeggenstaller — Mon, 05 Mar 2012 11:53:44 GMT

Thanks Michelle and Joe B.,

We have deployed ArcGIS Server Geocode Services that reference the SDE Locators which seems to be working for our users. I developed a template DefaultLocators.xml file that points all our users to our ArcGIS Server Services so that they get them by default in ArcMap/ArcCatolog. Users are able to review/rematch until their hearts are content. We developed a Python script to automatically rebuild the SDE locators and made it a scheduled task. Life is good in geocoding land....

Joe

Re: Certain users can't Review/Rematch Addresses using SDE Locator

MichelleBoivin — Thu, 19 Jul 2012 17:00:37 GMT

Hey Joe,

We did the exact same thing. We set up the python script and set it up as a scheduled task on the server and haven't had any issues since. It's a shame we had to go through all of this to figure it out, but peers are great!

Good luck!
Michelle

Thanks Michelle and Joe B.,

We have deployed ArcGIS Server Geocode Services that reference the SDE Locators which seems to be working for our users. I developed a template DefaultLocators.xml file that points all our users to our ArcGIS Server Services so that they get them by default in ArcMap/ArcCatolog. Users are able to review/rematch until their hearts are content. We developed a Python script to automatically rebuild the SDE locators and made it a scheduled task. Life is good in geocoding land....

Joe

Re: Certain users can't Review/Rematch Addresses using SDE Locator

Kathleen_Crombez — Thu, 29 Dec 2016 19:20:10 GMT

This thread is fairly old and I am not certain what version on ArcGIS the user of the original post was referring to...

But I just want to mention that we are still experiencing this issue at version 10.4.1

Thanks for providing the work around by using a geolocator server!