9

I have a big shapefile that contains all the buildings and houses of the town that I work in (approx. 90,000 features). The data of the buildings/houses are saved by the town's surveying engineers and due to bad practice and the access of different surveyors to that data, many buildings/houses have been saved twice and show up in the map as duplicates.

Some of them are exactly duplicated (they appear one over the other) while others are duplicated with a space between the two objects (like one object is inside the other - see the attached screen shot).

enter image description here

I want to clean that data so that I have only the correct buildings/houses in the town so my question is:

Is there any GIS analysis or SQL expression that I can run to find all the duplicated features (both the exact ones and the ones that are located inside others)? I have both ArcGIS and QGIS so I am open to all your suggestions.

  • You can try to explore the Delete Identical Tool. However, it requires an enterprise license level. You can review some other options available in Technical Article 36031 Does ArcGIS provide a way to identify or remove features with duplicate geometries your best bet is the Data Reviewer Extenstion. none of these tools will address your split geometries though – MDHald Sep 29 '14 at 15:21
  • also, you will have to consider that the tabular components will not be compared in the delete identical tool. I know its not an answer but hopefully it helps in the problem solving. – MDHald Sep 29 '14 at 15:23
  • Is the data in a database? Which type? – Russell at ISC Sep 29 '14 at 15:43
  • One option might be using the Intersect Tool (as described in this answer) in ArcMap, which would output any locations of overlap. That would require manually checking and deciding which polygon to delete, but in the case of not-exact duplicates I think you'd need to do that anyway. – Erica Sep 29 '14 at 17:16
  • 4
    Use of the term 'duplicate' is a bit misleading in this question. For the case of exact, identical, stacked copies, then yes, they are (or could be - attributes might vary) duplicates and as others have suggested the Delete or Find Identical tool might be helpful if you have that license level. But if they're offset at all, or a different shape, they're not really duplicates per se. If you have an Advanced license I'd look at a geodatabase topology, running the Must Not Overlap check. Without Advanced, perhaps the same can be done with QGIS and a plugin as Luigi's answer suggests. – Chris W Sep 29 '14 at 18:44
4

in QGIS, Topology Checker plugin can propably solve your problem

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • 3
    I agree a topology is probably the best single solution to the problem of cleaning the data. However you might want to expand your answer a bit by providing a link to the plugin and a brief description of what topology is or does and which check you would run. I fear your answer as typed will likely be flagged as low quality. – Chris W Sep 29 '14 at 18:48
  • ok: a description of the plugin is here: docs.qgis.org/2.2/en/docs/user_manual/plugins/… and "must not overlap" can approach the problem. A video guide of the plugin is here: youtube.com/watch?v=huhkTZkoKC8. – Luigi Pirelli Sep 30 '14 at 8:54
6

I would use Python's itertools and a SearchCursor for a very efficient way to find the spatial relationships you are after. You can incorporate the geometry methods overlaps, contains, and equal to get at the geometry properties.

  1. Start off by creating a function to better organize the workflow and for repeatability

    def findOverlaps(x):

  2. Open a search cursor to loop over individual feature geometry

    with arcpy.da.SearchCursor(x, ['OID@', 'SHAPE@']) as cur:

  3. use itertools.combinations() to return subsequences of elements from the input iterable cur

    for feature1,feature2 in itertools.combinations(cur, 2):

  4. Access the geometry properties with the following methods: equals(), overlaps(), and contains(). These are set-up in a logical sequence--you can tweak this to meet your specific objectives if necessary.

        if feature1[1].equals(feature2[1]):
            print "{} equals {}".format(feature1[0],feature2[0])
        if feature1[1].overlaps(feature2[1]):
            print "{} overlaps {}".format(feature1[0],feature2[0])
        if feature1[1].contains(feature2[1]):
            print "{} contains {}".format(feature1[0],feature2[0])
    
  5. Run it...

enter code herefindOverlaps(fc)


import itertools, arcpy

fc = r'C:\path\to\your\fc'

def findOverlaps(x):
    with arcpy.da.SearchCursor(x, ['OID@', 'SHAPE@']) as cur:
        for feature1,feature2 in itertools.combinations(cur, 2):
            if feature1[1].equals(feature2[1]):
                print "{} equals {}".format(feature1[0],feature2[0])
            if feature1[1].overlaps(feature2[1]):
                print "{} overlaps {}".format(feature1[0],feature2[0])
            if feature1[1].contains(feature2[1]):
                print "{} contains {}".format(feature1[0],feature2[0])

findOverlaps(fc)

enter image description here

The screenshot shows a variety of features that are overlapping, overlapping & identical, and unique.

enter image description here

2

I have an idea what may work for you. It is going to be based off some assumptions, but it would help narrow down your list of possible identical features. This would not be an automated process, but it would require manually looking at the duplicates. Based off the comments, it seems like the automated tools don't compare attributes so this would help you not accidentially delete features.

Using ArcMap

(1) Make a copy of your shapefile in case things go wrong.

(2) Add a column to your shapefile as a double.

(3) Calculate area for each feature using the most descriptive (most precise) format you can. Something where rounding may not be an issue.

(4) Run a summary (summarize) on that column. Make sure you select a unique identifier in the summarize and mark both first and last.

(5) In your output table, look for those records where the count field is higher than 1.

(6a) Manually check the features and repeat the process until there are no more duplicates.

(6b) You could just create a list of those unique ids and delete the features through arcpy, but you run the chance of possibly having two unidentical features with the same area.

Another Technique Using ArcPy

As I was constructing the above answer, I thought of the possibility that somehow the multiple authors of this data may have actually used the same unique identifiers for duplicated features. IF that is the case, you may be able to find duplicates through looping in arcpy.

The way I would think about doing this using ArcPy could be taxing on your system and take a little bit.

(1) Make a copy of your shapefile (in case again)

(2) Add a new column to denote duplicates. Something that takes like a 'y' or 'n' or 0 or 1 or whatever would work.

(3) Create a list in python to store the unique identifier.

(4) Run an Update Cursor (arcpy.UpdateCursor('LAYERNAME')). For each record, check you list to see if it contains that identifier and mark your column for duplicates if it is there.

myList = []
rows = arcpy.UpdateCursor("layername")
for row in rows:
  if str(row.UniqueIdentifier) in myList:
    #value duplicated
    row.DuplicateColumnName = "y"
  else:
    #not there, add it
    myList.append(row.UniqueIdentifier)
  rows.updateRow(row)

(5) Then you can compare or do whatever you want with those marked columns.

There are probably better ways to do these comparisons, but those are two that I believe should work or at least get you started.

Edit

Based on the comment by elrobis, you could utilize the minimum bounding rectangle to further decrease the chance of removing incorrect features.

Using ArcMap, you can run the Minimum Bounding Geometry tool in Data Management. After checking over the options, I think using the CONVEX_HULL option would probably be best.

If you compare the MBG_APodX/Y1, MBG_APod_X/Y2 fields along with MBG_Orientation for duplicates, you should be able to get a good idea of duplicated features. I would suggest using the Summarize method I described above to compare. Pick one of the vertices (coordinates) from the bounding rectangle to find duplicates. You may get a few incidental 'matches', but once you add in the other vertices plus orientation, it would be a fairly safe bet that the results features are duplicates.

Although I haven't used it and am not quite sure of the results from this tool, you might find examining the resulting shapefile easier if you used the Summary Statistics tool in ArcMap. It looks like you can summarize multiple columns that way instead of my single column option.

I don't think there would be a completely automated way of doing this without having the worry of possibility deleting a not duplicate feature. These methods should help limit the number of features you would need to manually review though.

  • I am assuming those were polygons. If they are lines, you could use length. Points are easiest with X/Y coordinates. – Branco Sep 29 '14 at 16:07
  • 2
    I thought about "equal area features", too, but it strikes me as likely that bldg footprints might have enough of the same types of shapes to create unintended matches. I think it would improve odds to further refine things w/ an MBR intersection of the features. That is, if they have the same area (and might be the same feature) AND their MBRs intersect, then perhaps it's likely they are two generations of the same feature. Does that make sense? – elrobis Sep 29 '14 at 18:16
2

You can do this in SQL using a spatial self join. You don't state which SQL dialect you are using, so this example uses Postgres/Postgis, but it could be easily adapted to Oracle or SQL Server. Assuming a table called buildings, with geometry stored in a column called geom:

SELECT a.id, b.id from buildings a, buildings b WHERE 
  ST_INTERSECTS(a.geom, b.geom) AND a.id < b.id;

This will find the intersects. If you want total equality then replace ST_Intersects with ST_Equals. Or, just combine the two:

SELECT a.id, b.id from buildings a, buildings b WHERE 
   (ST_INTERSECTS(a.geom, b.geom) OR ST_EQUALS(a.geom, b.geom)) 
   AND a.id < b.id;

Note, the a.id < b.id means that you only consider half the cases in the self join, which makes it a) faster and b) gives you a list you can use to delete half of the overlapping polygons without deleting them all. Clearly, this is still an O(n²) algorithm, but in practice, will be a lot quicker if you have a spatial index in place -- which is really a total requirement for any non-trivial data set.

You might need to massage this a little bit to fit some definition of overlapping -- you don't want to delete neighboring houses that have been badly surveyed.

  • If you are missing a unique attribute in the shapefile, you can use a.rowid instead of a.id. rowid is a keyword in SQLite which will give you the internal ID of the dataset. – LuWi May 8 '17 at 8:34
1

The Topology Checker plugin is a good tool if used correctly. You still have to have a fundamental understanding of your data AND you have to make the 'corrections' manually. The plugin will highlight what it thinks are errors. It is up to you to then examine each and make the appropriate decision for you and your data. With 90 000 items in your layer, you may be home by Christmas!

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.