Managing a municipality’s parcel dataset can be a full-time job, depending on several factors including the size of the municipality and the nature of growth in that municipality. For example, an up-to-date parcel dataset will include the most recent information about several potentially regularly changing attributes, such as who owns the property, and what kind of taxes are owed by the owner. Not only is the attribution of the parcel dataset changing on a regular basis, but the geometry that represents the boundaries of each parcel can also change. One of the most common causes of a change in parcel geometry is a subdivision.

Take, for example, a parcel representing a large farm. If the farm is sold to a development company, it is unlikely that the purchasing company will retain the property as a single land entity. They are likely going to build several houses on the property, divide the property into plots for each home, and sell the plots one by one to new homeowners. Not only do new attribute records need to be kept for each new parcel, but new geometries need to be drawn and the original geometry needs to be modified to reflect its new boundary. Often times the leftover areas of the original parcel will represent common areas, such as parks, playgrounds, or streets.

Fortunately, if you are managing data in a desktop GIS environment, there are several tools at your disposal that will allow you to create new polygons and cut out their footprints from the original polygons. However, if your data is hosted in a spatial database, you may not have the tools at your disposal to do this. The following SQL script was written for this exact function, assuming that you already have the footprints of the subdivided parcels created, and you need to update the geometry of the original parcel to reflect the subdivisions that have been removed. The script discussed below was written for Microsoft SQL Server (T-SQL), however, the common principals can be applied to many other versions of SQL. Below is the full script, followed by a walkthrough of important steps in the code and what they do.

SQL script used to cut several polygon geometries out of a single, source polygon

The script does the following:

1. Predefines the original parcel’s geometry record ID that you wish to modify.
2. Select’s the spatial record based off of a list of ID’s provided (representing the geometries of new parcel created, to be clipped out of the original parcel) and passes those records into a cursor.
3. Within the cursor, the geometry of the first record is selected.
4. Next, all other geometries are unioned together.
5. Once the cursor has completed unioning new parcel geometries, the original parcel’s location record is selected.
6. Finally, the original parcel’s geometry is updated, using STDifference to remove the unioned parcels’ geometries from the original parcel’s geometry.

The result of running the SQL script to cut a single polygon by the union of several other polygons.

After running the script on a sample of data, the result can be seen above. The light blue outline represents the original polygon, while the smaller light green parcels represent the new subdivisions.