Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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:

SELECT ROAD_NM, AVG(VOL) as VOL
FROM TABLE
GROUP BY ROAD_NM;
share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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')
share|improve this answer
    
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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