Identify Stored Procedure - AppGeo/GPV GitHub Wiki

This stored procedure fills the right side data details panel with information about the features at a location clicked on the map. This procedure is configured in the StoredProc column of GPVLayerFunction when Function is set to "identify". Note that this procedure is very similar to a data tab stored procedure except that it takes in a map ID instead of data ID. For layers in which map IDs and data IDs are the same, identify stored procedures and data tab stored procedures can be used interchangeably.

Raster layers are supported (ArcGIS for Server only).

Input

The first parameter receives a text string containing the map ID of a feature found on the map or a comma-separated list of raster values. The raster values will be in the same order as those provided by ArcMap's identify tool.

If provided, the optional second parameter receives the role of the current user.

Output

One or more SQL result sets, each containing one or more rows and columns. Each row is displayed on the interface as a group of column label/column value pairs in a list. If the result set contains a column named Header, the value in that field will be displayed as header text for the group.

Links and images can be embedded in the values of the result sets.

Basic Example

This procedure returns the address and city for the specified parcel ID.

SQL Server

create procedure GPVIdentify_ParcelBasic
  @id nvarchar(26)
as 
select prop_street as Address, 
  prop_city as City
  from parcel_base
  where parcel_id = @id
go

Oracle

create or replace package GPVPackage as
  type t_cursor is ref cursor;
  procedure GPVIdentify_ParcelBasic(id in nvarchar2, io_cursor out t_cursor);
end GPVPackage;

create or replace package body GPVPackage as
  procedure GPVIdentify_ParcelBasic(id in nvarchar2, io_cursor out t_cursor) is
    begin
    open io_cursor for select prop_street as "Address", 
      prop_city as "City"
      from parcel_base
      where parcel_id = id;
    end GPVIdentify_ParcelBasic;
end GPVPackage;

Raster Example (SQL Server)

In this example two raster values are returned from ArcGIS for Server, an ID and an elevation, separated by a comma. We'll ignore the ID and extract the text after the comma, convert it to a number, round to whole units, and embed in a text string.

create procedure GPVIdentify_ElevationModel
  @rasterValues nvarchar(20)
as
select CONVERT(nvarchar, ROUND(CONVERT(float,
  SUBSTRING(@rasterValues, CHARINDEX(',', @rasterValues) + 1, 20)), 0)) +
  ' ft' as Elevation

Note that you can return a value without actually querying a table.