0

ESRI offers a ParksFinder map that's part of their LGDM (Local Government Data Model) that they have begun offering freely. This was on an external-facting server running 10.1 ArcGIS Server, connected to a 10.1 Enterprise GeoDB, using a 10.2.2 Desktop with the 10.2 version of the ParksFinder. We couldn't get the 'Search by Activity' to work. At all. The other searches gave us issues as well but we discovered little adjustments we could make to the Database, or through the Desktop MXD file, etc. that cleared up all of the searches EXCEPT for the one by Activity.

We wanted to showcase this functionality to our Parks department despite these issues so we setup the same app on an internal-facing server. This server was running 10.2.2 ArcGIS Server, 10.2.2 ArcGIS Desktop, but connected to the same 10.1 Enterprise GeoDB. We have to hack around and change up some minor edits but we got it working.

Chalking the issues up to a difference in Versions of our software and DB we waited for our Quarterly Release when we could upgrade our servers. Which we did yesterday.

So now it was time to implement the ParksFinder app on the newly updated servers. We ported the app over exactly the same. The config.js and proxy.config files were updated with the correct url's to services and everything seemed to work... EXCEPT THE SEARCH BY ACTIVITIES!

We spent 4 hours, adjusting permissions, rebuilding application pools, deleting and re-creating, everything... but nothing worked. I had gotten to work at 6 that morning and by 6:41 that night I was done. My co-worker (a much more advanced GIS professional) and our GIS DBA couldn't let it go and kept trying out as many things as they could.

The thing that fixed it is what has me stumped.

In the locator.js file of an untouched ParksFinder app there is a variable called activityQueryString that creates the URL Query Builder for the searches and around line 1560-ish it get's this little addition.

activityQueryString += "1=1";

I would never have thought to do what they did... by changing that line to the following:

activityQueryString += " 1=1 ";

Adding spaces to the query string fixed the issue.

My questions is, why?

What does that add to the search functionality that would turn it from non-working to working?

I'm confused. I've got a LOT of experience with web dev, mainly with CMS systems and basic websites, and I've dealt with query strings being difficult before but nothing like this.

Just trying to make sense of a crazy situation.

2 Answers 2

3

Sounds like a pretty crazy bug to track down! If you can imagine a couple of possibilities activityQueryString could be, I think it would be clearer:

-- Perhaps default SQL without any additional query
SELECT * FROM parks WHERE 1=1

Which will return everything.

Say now you want to narrow down the results by adding something like swimming='Yes'. Without the space added like you did, constructed query probably would be:

SELECT * FROM parks WHERE swimming='Yes' AND1=1

Which is a syntax error. By you prefixing a space, the concatenated string becomes correct.

I'm just hypothesizing. You can confirm it for yourself by added something like console.log(activityQueryString) or alert(activityQueryString) right after that 1=1 line.

3
  • It would appear that the problem is a For/Each loop that adds AND between the query pieces. Since it doesn't know to stop at the last one (due to the For/Each) then it just adds a "1=1" at the end of the query string. I'm looking into what it would take to run the For/Each more intelligently and STILL not get erased with any updates to this app. Oct 1, 2014 at 16:55
  • Cool. Seems though that the version that you linked would have this issue resolved already? I just looked at the code a bit... should work, no?
    – janechii
    Oct 1, 2014 at 17:12
  • That's just it. In our Internal map this code didn't give us any issues at all. But on our external site, the exact same code, crashed the Search By Activities option. It was extremely ridiculous. Oct 1, 2014 at 20:59
1

As mentioned by @janechii, the use of "1=1" is a nice trick to allow you to build long query strings on-the-fly programatically. I use this method all the time after I first learned about it.

However, the "1=1" should immediately follow the WHERE clause, because that will guarantee all values are returned (1=1 is always true) ... First you start with:

-- The default SQL query should return all records
SELECT * FROM parks WHERE 1=1

In straight SQL, I start with the 1=1 as part of the base query. Using this construct, you then just append "AND" or "OR" clauses to the end of the base query.

activityQueryString = "SELECT * FROM parks WHERE 1=1"

and precede each subsequent parameterswith a space.

activityQueryString += " AND swimming='Yes'"

ArcGIS applications (using ArcMap's "Search By Attributes" dialog box as an example), though, typically try to "make it easy" for the user by building out the initial query to include the WHERE keyword. So based on the brief code in your question, the programmer may have planned for this fact and wrote the code to append the "1=1" as a placeholder. Assuming this is the case and Arc automatically starts with:

activityQueryString = "SELECT * FROM parks WHERE"

Adding activityQueryString += "1=1" (note: no spaces before or after the 1's) would yield the following SQL, which would fail as invalid syntax.

"SELECT * FROM parks WHERE1=1"

However, by adding the spaces before (and after, which may not actually come into play here), you get:

"SELECT * FROM parks WHERE 1=1 "

Next, to build on additional query parameters, adding activityQueryString += " AND swimming='Yes'" (note: space before AND) would yield:

"SELECT * FROM parks WHERE 1=1 AND swimming='Yes'"

Finally, adding activityQueryString += " OR swimming='Maybe'" (note: space before OR) would yield:

"SELECT * FROM parks WHERE 1=1 AND swimming='Yes' OR swimming='Maybe'"

So there you have it. Hopefully that all makes sense to why ArcGIS might be a little different than straight SQL development.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.