I have a Road Network shp file.(attributes include Names and Volumes ) Each road in my network consists of many links with the same name and different volumes. I want to display Road Names and volumes as labels using sql query or python function to group by names and have volume Average.

** In normal SQL query I would type:


Operations such as ORDER BY, GROUP BY are not supported in File Geodatabases, Shapefiles. As @Evil already answered you can use Summary Statistics to calculate basic stats but forget about fully functioning SQL. ALTERNATIVELY, you can do this if your back-end geodatabase is a Personal Geodatabase which is essentially an MS Access database. You can open a Personal Geodatabase in Access and write regular SQL queries or you can create a DAO/ADO connection programatically and execute SQL queries that way. (Take care not to mess around with the spatial tables that are generated when the ESRI "Spatial" component is added to the database) Unfortunately, this will only work with Personal (Access) Geodatabases and possibly ArcGIS Server databases that run on top of an enterprise database back end but I can't say for sure because I don't have any experience with these.

It seems that Personal Geodatabase, although it runs on top a fully fleshed out database, and can therefore be queried with SQL, is being phased out (unconfirmed but ESRI staff was hinting at this at the UC). The greatest limitation of a Personal Geodatabase is inherited from its host - 2GB storage limit and a performance decrease when nearing this limit.

  • You need to put order by and group by in a sub query, they're supported. – user125266 Oct 29 '18 at 16:11

You can accomplish that using the Summary Statistics GeoProcessing Tool: Summary Statistics

In your case, python usage would look like:

arcpy.SummaryStatistics_management(roads_shp, 'output_stats_table', [['VOL','MEAN']],'ROAD_NM')
  • Thank u, I have tried Summary Statistics. It is good for giving a table but There were problems in joining it with the geometry. – user30189 May 14 '14 at 16:24

I have found an easy way by using Data Management>Generalization>Dissolve tool. I was capable of grouping Roads by road name and have Volumes Average.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.