Query Linked Data
Has anyone been able to do a query on a linked database? I would like to search for a parcel by owner name with the owner name in a linked table. The link is already defined in the Site.xml.
-
Hi Bill,
Have you got any feedback to your question yet?
I am having the same question.
Thanks
Alex
0 -
No feedback but I understand that linked tables now show up in CSV export in the new release. Are the two issues connected? Once I get the new software loaded I will test this out.
You can link the table (now) in your mxd and those field values should be available for queries if they are configured correctly.
0 -
Link the table in your MXD which when published will create a linktable with its own REST Endpoint then you should be able to use this as the URL in a QueryTask activity. Specify the Parcel ID in the Out Fields section. Let's say you store the resultant featureSet in a variable called query_results. Then you will need another QueryTask activity, which has the URL to the Rest Endpoint for the Parcels layer. In the Where clause for this activity you will need to have something like
"PID = '" + query_results.features(0).attributes("ParcelID") + "'"
where PID is the name of the parcel ID field in the parcels layer, and ParcelID is the name of the parcel ID field in the Owner table. This also assumes that you will only have one result from the Owner table. If you have multiple results it will currently use the first one only. If you want to use multiple results to do multiple queries, you will have to add a for each loop and make some other modifications.
You don't need to have a Geocortex datalink set up in Essentials to accomplish this search.
Mark Norbury
ESRI Canada
(With support from Latitude)
0 -
Further to my last post, I've now added a foreach loop in the Workflow, which has a Sequence within it, which contains a Query Task tool and a Select Features tool.
The forEach tool has a 'TypeArgument' of ESRI.ArcGIS.Client.Graphic and a 'Values' of legalquery_result.Features, where legalquery_result is the featureset variable returned from my earlier Query Task.
For the Query Task inside the Sequence (inside the foreach loop), the Query Service Url is to the Parcel layer and the Where clause is "PLID = '" + cstr(i.attributes("PLID")) + "'". the Result is the final featureset variable parcelquery_result. For the Select Features tool inside the Sequence (inside the foreach loop), the Feature Set is parcelquery_result, the Layer Id is also referring to the Parcel layer and the Collection Name I've called "Search parcels".
This works, but it's slow. Is there a faster way to do this? For example, is it possible to create a complete Featureset that contains the whole set of results, then run a Select Features tool only once on this whole set of results?
Any advice would be gratefully received.
Mark Norbury
ESRI Canada
0 -
Hi Mark,
Instead of having a query task inside the loop, you could just have an assign activity that builds up a where expression for all of the parcel ids. Your assign activity would use a string variable, and assign it the value of:
"PLID = '" + cstr(i.attributes("PLID")) + "' OR "
Then after the loop, just have a single query task that uses that where expression you built up inside the loop. A couple of things with this solution - first, you're going to end up with a trailing " OR " at the end of the where expression that'll need to be dealt with. You could just append a "1=1" after the loop, or you could actually use an expression to trim off the final 4 characters, either will work. Second, it might be more efficient to construct this query using an IN clause rather than multiple clauses separated by OR, but you can test that out for performance if you need.
John
0 -
John
Thanks for that. It works much faster now. My previous method was also not clearing the results after each query, which made it difficult to work with, so this new way is better in that respect too.
Here's what I've done.
Inside the sequence within the foreach activity I now have just an 'if' activity which test the condition:
i is pidquery_result.Features(0)
If true then assign parcelquerystr = "PLID in ('" + cstr(i.attributes("PLID")) + "'"
If false then assign parcelquerystr + ", '" + cstr(i.attributes("PLID")) + "'"
Following the foreach activity is another assign activity:
parcelquerystr = parcelquerystr + ")"
Following this is the parcel query task, but its where clause is now parcelquerystr.
Following this is the Select Features activity, same config as before, except that it's outside the foreach activity and hence only executes once.
This works well and could be adapted for varying relational queries.
Thanks
Mark Norbury
ESRI Canada
0
Du måste logga in om du vill lämna en kommentar.
Kommentarer
6 kommentarer