Working with Stacked Parcels and Condos

Why are some parcels duplicated or "stacked"?

Our data is directly from county GIS and county assessor's offices around the US. They are primarily focused on collecting taxes so recording ownership and mailing addresses is their main goal. They often use their software in creative ways to get things recorded so they can track the taxes. For parcels with multiple, individual owners the counties often create multiple parcels so they can enter different owner and contact information for each owner. The 3 most common ways are:

1. Identical polygons "stacked", exactly the same geometry, just 4 or 10 or 100 stacked, all exactly on top of each other, with different attributes for the different owners or other attributes. By far this is the most common way and is widespread around the US. These parcels are identified by the ll_stack_uuid column, described below.

2. Puzzle pieces, ground parcels with exact cutouts for the footprint of the building. This is common for downtown buildings. They have no intentional overlap and are not stacked. This example from Washtenaw County, Michigan shows puzzle piece parcels:

   

3. Laying condo parcels on top of a ground parcel. Condos are typically "slivers" laid on top of the ground parcel. These usually only 2 have layers: one big ground parcel, with smaller parcels overlapping the ground parcel, but spread out. This example from Detroit, MI shows one ground parcel with many smaller condos laid on top of it:

In these cases, our dataset usually does contain all of the addresses associated with the parcel, as each owner's 'parcel' usually has what is considered a primary address.

The vast majority of the counties create unique parcel numbers for each stacked parcel. The unique parcel number is a benefit of the stacked parcel approach. However, some counties will duplicate the parcel number and use a secondary id field for the sub-parcels. In our experience this is much more rare, and we would retain any secondary parcel numbers as a custom column in our data.

Working with stacked parcels

The ll_stack_uuid

We define a parcel stack to be a group of parcels with exactly the same geometry (case 1 above).

We identify parcels that are parts of stacks with the ll_stack_uuid column. The UUID in this column will refer to the ll_uuid of one arbitrary parcel in the stack. All parcels with the same geometry share the same ll_stack_uuid.

Finding stacked parcels

You can find stacked parcels by querying for parcels where the ll_stack_uuid column is not null.

Removing stacked parcels (flattening the data)

You can keep one geometry for each parcel with a query that removes all but the first parcel for any stack.

Please note that removing these parcels will likely remove important information, like the owners of those additional parcels.

In PostgreSQL, a sample query would look like this:

WITH row_number_added AS (
    SELECT
    	*, 
    	ROW_NUMBER() OVER (PARTITION BY ll_stack_uuid ORDER BY ogc_fid DESC) AS row_number
    FROM parcel_table
)
DELETE FROM parcel_table
WHERE ll_uuid IN (
  SELECT ll_uuid 
  FROM row_number_added 
  WHERE row_number != 1
)
;

You could also select only parcels that are not stacks, or the first parcel in a stack. Note that the first parcel is set arbitrarily and is not necessarily a "ground" or "primary" parcel:

SELECT FROM parcels
WHERE ll_stacked_uuid IS null
OR ll_stacked_uuid = ll_uuid

Limits of the ll_stack_uuid

In the current implementation, a stack refers only to parcels with identical geometries.

While many stacked parcels represent condo units, not all do, and this is not a solution for finding only condo units. For example, some counties represent condos as "slivers" of a parcel, and those will not be identified by this solution.

Parcels that overlap by some amount but are not exact duplicates are not currently tracked by the ll_stack_uuid. These parcels represent cases 2 and 3 under "Why are some parcels duplicated or "stacked"?" above, and may be included in future revisions.

The ll_uuid selected for the ll_stack_uuid is arbitrary and does not indicate an order, or a primary or canonical parcel for the stack.

The ll_stack_uuid is not a stable stack ID and may shift across parcel updates.