topic Re: ArcGIS 10.1 - query outStatistics with database views? in ArcGIS REST APIs and Services Questions

ArcGIS 10.1 - query outStatistics with database views?

Anonymous User — Thu, 19 Apr 2012 14:22:15 GMT

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.

Re: ArcGIS 10.1 - query outStatistics with database views?

BrianLeroux — Wed, 01 May 2013 19:21:19 GMT

It looks like query layers are not supported by summary statistics in server 10.1. I also have a service based on a view and I notice that the layer has an attribute that is called "Supports Statistics" and is set to False. I have not been successfully in finding much documentation about supported layer types. I only found in the REst Server API that Layers and Tables are supported. I hope that there is either a feasible workaround or this will be enhanced in a future update.

Re: ArcGIS 10.1 - query outStatistics with database views?

RobChouinard — Wed, 29 May 2013 16:52:39 GMT

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.

My views show as tables in my MapServer services without any shape column and I am not able to calculate statistics on them. The layer property shows "Supports Statistics: true". You can see a thread I started here: http://forums.arcgis.com/threads/78141-Problems-with-Statistics