I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count function for my query. I'd prefer not creating an output file, but rather simply updating the file itself. I've looked at the cursors but I cannot seem to accomplish what I need to with them. I essentially need to get a count for duplicates, do a comparison, and then accordingly append values of one field with the values of another field.

  • What kind of duplicates are you looking for? Geometry duplicates (overlapping features), duplicates values within a field, or duplicate rows comparing multiple fields? – RyanKDalton Dec 5 '12 at 16:23
  • 1
    Also, since you are working with a shapefile which uses a DBF to store its database info, you will be limited to the query capabilities of the DBF format. – RyanKDalton Dec 5 '12 at 16:37
  • Oh so conversion doesn't change the DBF? Thanks for the info. Sorry I forgot to explain what the duplicates are I will do so. – Carl Carlson Dec 5 '12 at 17:55
  • Yes, if you convert from a shapefile to another format (file gdb, personal gdb, arcsde gdb, postgis, etc) then of course the database specs will change and you will have different sql capabilities based upon whatever the native database format is. You said that your didn't want to convert the data, though... – RyanKDalton Dec 5 '12 at 18:12

(This uses neither python nor ArcGIS but it might be a useful approach.)

One alternative approach could be to read the dbf part of the shapfile into R (using the foreign package). Then install and load the package sqldf, which provides full SQL functionality. So long as you do not reorder or change the number of rows you can write out a new dbf file which will work^ with your shapefile.

^although I imagine it is possible to break the shapefile in certain circumstances I have not done this yet using this approach.

  • A creative sounding workaround. Kind of fakes out the original shapefile, and it's none the wiser. – Baltok Dec 5 '12 at 19:22

If the duplicates you wish to do something about are across rows I would look at , specifcally ogr sql.

If OGR doesn't do what you want and the data you wish to change are across columns (fields) and not rows (records) with suitable care you can use any tool which understands DBF, which should any db tool worth it's salt, including Access/Excel. Something to watch for, especially with Excel, are silent reformatting of date/time fields, and truncation of total records (Excel has max row limit, and may or may not say something about it). Always work on a copy, validate the results, and never delete, add or re-order rows when using a non-spatial dbf tool.

  • Thanks, I'll look into OGR, but I'm not very good at macros on Access and Excel and I wanted to concatenate string values in two different fields without merging based on data from another so I think this takes a more programmatic solution. – Carl Carlson Dec 5 '12 at 18:32
  • Do you know if the python version of ogr sql can be integrated seamlessly with arcpy? – Carl Carlson Dec 5 '12 at 20:18
  • 1
    @carl yes, with Arcgis 9.3, following Can ArcGIS and OSGeo4W share the same python install?. I'm not sure what would need to change for 10.x. – matt wilkie Dec 6 '12 at 16:53

If you install pywin32, you can query your .dbf files with any Jet compatible SQL query and access the results in a RecordSet from Python. You can run an aggregate function like count, nested query, update query, etc or output to another dbf. In my experience, the dbf file name must be 8.3 compiant.

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.