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;
Answer
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.
No comments:
Post a Comment