Target Parameter Tricks - AppGeo/GPV GitHub Wiki

Target Parameters provide an extremely flexible way of integrating search pages & other web pages/apps with the GPV. The purpose of this page is to document some useful Target Parameter tricks.

Creating a Multi-Parameter Target Parameter (SQL Server)

Through some creative querying, it is possible to set up a Target Parameter that uses multiple parameters, querying from several fields at once. An example of this would be a Property Search function that maps all results of a search rather than just one at a time, passing any mix of parameters for Address Number, Street Name, Parcel ID, etc. This has been implemented on the Amherst GIS Property Search page, which has a "Map all results" button that displays when there is a result set. This button generates a GPV URL with a pipe delimited target parameter that the associated TargetParams stored procedure parses out into values that are selected through subqueries. For example, a search for the following parameters:

• Parcel Map IDs that start with "2"
• Address Numbers that start with "1"
• Street Names that start with "M"

would look something like this:

http://gis.amherstma.gov/public/viewer.aspx?Application=ParcelApp&targetparams=propsearch,2|1|M

The target parameter named "propsearch" in this case takes the "2|1|M" string & passes it to a custom SQL Server function named "Split" that recognizes the pipes as delimiters & makes a table in memory that stores each piece of text between the delimiter as a record in a field named "myval". Each record is given sequential unique ID that allows it to be queried out from the table. The resulting table in memory with the data from above would look like this:

The SQL Server custom Split function needs to be set up first & the user that the GPV uses needs Select privileges to it.

The target parameter looks like this:

...
else if @paramType = 'propsearch'
begin
  select distinct MapParID
  from sde.gisadmin.TOA_CAMA_TABLE
  where MapParID like 
  (SELECT myval FROM dbo.split(replace(@paramId,' ','%20'), '|') where ID = 1) + '%' 
  and 
  Number_char like
  (SELECT myval FROM dbo.Split(replace(@paramId,' ','%20'), '|') where ID = 2) + '%'   
  and 
  replace(Street,' ','%20') like 
  (SELECT myval FROM dbo.Split(replace(@paramId,' ','%20'), '|') where ID = 3) + '%' 
end

The heavy lifting is being done in the WHERE statement with these fields being combined using AND: MapParID = the Parcel ID; Number_Char = the Address Number & Street = the Street Name. Each one uses a LIKE clause & invokes a subquery that uses the Split function to select the appropriate record based upon the ID field in the table that the Split routine creates in memory. Spaces are replaced with a "%20" to ensure that the values can be used in a URL & the '%' is added to end of each LIKE clause to make it a partial string match.

Questions? Feel free to contact Mike Olkin