1

I am new to GIS data.

Our organization uses ArcGIS to map parcels of land used for agriculture. The lowest addressable level is currently a single polygon, representing a parcel. Throughout the year, these parcels may change in size or name.

We are importing shapefile data to extract parcel data, and all information associated with each parcel. We are storing this information in SQL Server, merging it with work data, and tracking changes using a data warehouse framework and slowly changing dimensions, but we have run into a problem I can't seem to solve.

Consider this scenario.

Yesterday we had a five acre parcel named A, and work was performed in said parcel. Today, management changed its name to B, and more work is performed. We track those changes, and show parcel A as being in existence until today, when parcel B sprang to life. When we aggregate acres for parcels in which work was performed, we have a total of 10 acres, when in reality, it's the same five acre parcel.

Currently, the parcel name is the key, and needs to be visible for the users to make sense of their data. They know it was named A, but is now B, and want to see the cost per acre of both A and B. The challenging bit is they want the current acres to be accurately represented as 5; not 10.

From a data perspective, I have no way to know this is the same land.

Is there a way to make the physical land be the key instead of an arbitrary name?

I'm envisioning a data store filled with lat/long coordinates for each square foot (or whatever is the smallest addressable unit), and extracting each addressable unit from the parcel polygon. That way, the parcel name is simply an attribute of each addressable unit, and can change as business needs evolve. With this approach, I can see tracking changes over time being easier, as we would track the land rather than the arbitrary labels.

  • 1
    This could be as simple as running a spatial joint between the two layers. This is assuming that your A and B features are stored in separate layers.desktop.arcgis.com/en/arcmap/latest/manage-data/tables/… – Kartograaf Jan 9 at 19:31
  • 4
    The ID should be immutable; your name attribute "A" and "B" is clearly mutable, and that is a critical mistake in your data modelling. – alphabetasoup Jan 9 at 19:43
  • @alphabetasoup, this is exactly the issue I am attempting to solve; how to create an immutable identity for an area within a polygon that could change, be renamed, or completely removed. – FlogDonkey Jan 9 at 20:03
  • 2
    I think you're approaching the problem form the wrong angle; you don't need to generate an ID from an area, or anything. You can just generate an ID using your RDBMS (SQL Server), one for each row. This should be your primary key, rather than a name string. – alphabetasoup Jan 9 at 21:12
  • 1
    I second the comment by @alphabetasoup about using an immutable key. One way to do that would be to store the geographical data in an ArcGIS Enterprise database, and enable 'archiving' on this particular table. This will create a Guid of be used as the ID, it will automatically set start and end dates for new or deleted parcels, and store a complete history of every attribute change ever made. Of course, your data warehouse should be able to play nice with an ArcGIS database, and you'll need an Editor license. – Berend Jan 10 at 11:25
1

You are talking about data evolving through time, but you haven't mentioned recording this time: for each of your object, record 2 dates: valid_from and valid_to.

Current records have a null valid_to. When a current record is changed (duplicated as per your scenario), set a valid_to timestamp with the current date/time on the old record.

To display current records, select entries where valid_to is null. To display the evolution of a parcel, select the old records and order by valid_from.

You then have the luxury of selecting old data by attribute (show me how my property has changed in the last x years) or by a spatial selection (what was at a given location during the last x years). Remember that a land can be split/merged etc, so boundaries can change through time (let's not even talk about the accuracy of historical data)

| improve this answer | |
  • Sorry, I did say it was stored in a data warehouse using slowly changing dimensions, but you are correct; I was not explicit. Yes, each parcel is stored with valid StartDate and EndDate, where current acres are identified by a NULL EndDate. Please note, parcel allocation is but part of the problem we are attempting to solve. The real issue is labor. If X dollars were spent in a parcel, even if the polygon boundaries are changed (as you accurately identified) or the polygon is renamed, the physical dirt should not lose its identity. – FlogDonkey Jan 9 at 20:01
0

To me its seems like tracking would be the best solution. for this type of database, you do not want to delete any records, but "retire" them. For example:

You have s shapefile named "Parcels" and in the attribute table you have all the data you need for your project with the addition of three columns named: "Retired_Date", "Retired_By" and "Comments/Reason".

When you receive an instruction to replace parcel A with parcel B you do not delete parcel A OR modify it. You make a copy of Parcel A and rename it to parcel B, keeping all the other fields the same. Then you go back to parcel A and add a date to the "Retired_Date" column, add your name to the "Retired_By" and add any comments you find appropriate (i.e. "Parcel B replaced Parcel A for the following reason...").

Now, you have two identical shaped parcels on top of each other with two different names. You can easily add a definition query to your shapefile to display only the records that has a "Retired_Date is NULL" for example. If you ever need to go back and revisit Parcel A, you can remove the query.

Also, ArcMap has a tracking tool that you can enable that will track changes such as when a record was added or modified and by who without you doing it manually in an attribute table. But to me it looks like you need more than just what that tool has to offer.

Hope this helps.

| improve this answer | |

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.