mleahyesri-ca-esridist

ArcGIS 10.1 - query outStatistics with database views?

Discussion created by mleahyesri-ca-esridist Employee on Apr 19, 2012
Latest reply on May 29, 2013 by citrusmosquito
Hi all,

I'm currently setting up some map services on a server running the 10.1 prerelease, and I'm running into a couple issues when using database views as layers in a map.  I've tried this with both SQL Express and PostgreSQL database backends, and while I can create a mapserver service that contains layers based on database views, I don't seem to be able to run statistics on these.  I can run a normal query with a where statement (e.g., where For example, in SQL Express, I might create a view named "test" that joins two tables (a feature layer named 'points', and some other table named 'joined_table') - the query looks something like (the column produces a unique ID):

select 
 cast((row_number() over (order by [points].[objectid], [joined_table].[objectid])) as int) as objectid,
 [points].[objectid] as [point_oid],
 [points].[shape],
 [points].[join_id],
 [points].[category],
 [joined_table].[joined_attribute]
from points left join joined_table on [points].[join_id]=[joined_table].[join_id];


When I create this view in the database, it seems to look fine - I can add it to a map in ArcMap (when I'm prompted to select a unique ID).  I can then export that to a *.sd file, and publish that as a MapService (if I try FeatureService, I get warned that it's not supported).

Next, I try to query one of these layers via the REST api using the following outStatistics parameter:

[
  {
    "statisticType": "count",
    "onStatisticField": "*",
    "outStatisticFieldName": "Total"
  }
]


If I use this on any layer that is based on a normal feature class table in the database, I get valid statistics reported...and I can group the output by specifying the groupByFieldsForStatistics parameter as appropriate.

However...if I try this on a layer based on a view (as described above), the JSON response for the query request looks like the following:

{
 "error": {
  "code": 400,
  "message": "Unable to complete operation.",
  "details": [
   
  ]
 }
}


After a bit more testing, I have determined that the REST API will successfully calculate stats for a view served in a MapServer services, as long as the view just contains normal values and does not include a [shape] column...as soon as the view contains a shape (and is therefore treated as a Layer instead of a Table in the MapServer service), the stats can no longer be calculated.

Can anyone suggest whether what I'm trying to do should be possible, and/or is there an alternative?  I'd like to keep the shapes in the views if possible, although I can work around it if needed using views without shapes.

Outcomes