Combining polygons by date fields

[deleted-user-_78mlgGtbbzB]

I have an interesting problem.  I have been asked to create a set of polygons using dates.  Below is an example table showing the data.  As you can see the table is sorted by start year.

What I have to do is generate a set of polygons each time the start year, month and day changes.  So the first polygon will just contain the first row of data.  The second polygon will contain both the first and second and so on.

There is also another element to this.  Each row of data has an end year, month and day.  If a row has an end year of 2300 then that row will stay in the output once it has been added.  If there is an end year that does not equal 2300 then that row will not be included in future versions of the output.

So is there anyone who can help?  I have about two hundred tables like the one above. Would like a model builder solution.

Thanks in anticipation.

Paul

[XanderBakker]

There is a point I don't really get; when the end date of a record (current date you want to process) is not equal to 2300, BUT it is higher than the current date, should it be removed from the output?

It seems more logic to me if for each unique date in the StartDate you use all the records for which the start date is before the current date and the end date is after the current date and use those records to create the polygon.

Anyway, I would probably use some Python code to:

• create a unique sorted list of start dates
• create a layer through the select by attributes
• dissolve the polygons and create a new featureclass (or add it to an existing one if you want the new merged polygons to be stored in 1 featureclass)

Then there is still the issue of what to do with the attributes of the records you're merging...

If you're willing to post a piece of your data, I can have a look and see what code is needed.

Kind regards, Xander

[deleted-user-_78mlgGtbbzB]

Thanks for the reply Xander,

To answer your first question if the end year is not equal to 2300 but is higher than the current date, then it should be removed when it's end date is less than any of the start dates.

Could you tell me how I upload a file so you can get to it?

I can discuss the attributes once you have the file.

Thanks

Paul

[XanderBakker]

ZIP the extract of your data (shapefile or FGBD), and use the "reply" option in this thread. In the editor in the upper right corner you will see a link "Use advanced editor", once in the advanced editor you will see in the lower right corner the possibility to attach a file.

[deleted-user-_78mlgGtbbzB]

Hi Xander,

Here is the zip file containing the shapefile.

Many thanks

Paul

[XanderBakker]

Hi Paul,

I have included some Python code that created a new shapefile with the merged polygons. The attributes are still to be defined. In the code the processing date is assigned the the output date fields.

Prerequisites:

• Create two new columns that hold the start and end date as date (not as text).
  • You can do this by adding the fields "DateFrom" and "DateTo" as date fields
  • Calculate the new fields and simply assign the field StartDate and EndDate to it

The reason to do this is to simplify the syntax of the where clause used in the script.

Configuration:

• Change line 6 to point to your input shapefile
• Change line 7 and 8 to hold the field names of the date fields (the newly added fields)
• Change line 11 to point to a shapefile that will be created

The script will:

• Create an empty output shapefile
• Create a list of unique dates from the start date field
• Loop through each date and create an expression (see lines 29 - 31). It will select all polygons with a start date less than of equal to the current date and  where the end dates are after the current date
• On lines 35 - 42 the polygons that match the query will be merged into a single (multipart) polygon
• On line 46 the polygon is written to the output shapefile

import arcpy, os
from datetime import datetime
arcpy.env.overwriteOutput = True

# change path to datasource
fc = r"D:\Xander\GeoNet\MergePolsDate\Reserves_copy.shp"
fld_start = "DateFrom" # create date field and fill it with content of StartDate
fld_end = "DateTo" # create date field and fill it with content of EndDate

# output shape
fc_out = r"D:\Xander\GeoNet\MergePolsDate\Reserves_merged.shp"

# create empty output shapefile
ws_path, fc_out_name = os.path.split(fc_out)
arcpy.CreateFeatureclass_management(ws_path, fc_out_name,
    "POLYGON", fc, "SAME_AS_TEMPLATE", "SAME_AS_TEMPLATE", fc)

flds = ("SHAPE@", fld_start, fld_end)

# create a unique list of dates from start date
lst_dates = list(set([row[0] for row in arcpy.da.SearchCursor(fc, (fld_start))]))
lst_dates.sort()

# insert cursor to store features to new featureclass
with arcpy.da.InsertCursor(fc_out, (flds)) as curs_out:
    # loop through list of unique dates
    for date in lst_dates:
        # create an expression
        expression = "{0} <= date '{2}' AND {1} >= date '{2}'".format(
            arcpy.AddFieldDelimiters(fc, fld_start),
            arcpy.AddFieldDelimiters(fc, fld_end), date)
        i = 0
        # create the searchcursor with the expression
        with arcpy.da.SearchCursor(fc, flds, where_clause=expression) as curs:
            for row in curs:
                pol = row[0]
                if i == 0:
                    # first polygon is just the polygon"
                    polygon = pol
                else:
                    # next polygons are added with union
                    polygon = polygon.union(pol)
                i += 1
            # insert the feature into the output featureclass
            # TODO: manage attributes
            curs_out.insertRow((polygon, row[1], row[1]))

In case of any doubts, just post a reply.

Kind regards, Xander

[deleted-user-_78mlgGtbbzB]

Hi Xander,

That worked.

Just need to include the name and date attributes in the output.

What is being asked of me is to combine polygons into financial years, please see the example below.

Hope this makes sense.

Thank you for your time on this.

Paul

[XanderBakker]

Hi Paul,

From your example I don't really understand the concept of a "financial year". To fill the EndDate columns with the value 31/21/2300 doesn't seem right, since polygons that expire before that date, but have an end date after the current processing date, are included too.

It is possible to add the name and create a new column "OutputName" that holds the name and a date range. What is not very clear to me is this:

• For the first record in your Excel it seems that the current processing date is "31/03/1965". In the code I provided the first processing date will be "01/04/1962" which will create a name "Reserve A 01/04/1962 - 01/04/1962".
• For the next record it will include those polygons for which the start date is before or on the current processing date ("07/06/1965") and the end date is on or after the current date. This will include also the first record, creating an overlap in range and an OutputName "Reserve A 01/04/1962 - 07/06/1965"

Since the first record in the sample shapefile has an end date of 31/21/2300 it will be included in all the features.

Maybe you can explain a little more on what you are trying to achieve?

[deleted-user-_78mlgGtbbzB]

Hi Xander,

Apologies for this.  Don't worry about the financial year, I can sort that out later.

Have a look at this example

So the first version of the reserve is live between 01/04/1962 until 06/06/1965 as on the 07/06/1965 the reserve then includes both row 2 and 3 and so on.

Hope that makes it easier.

Again thank you for your patience.

Paul

[XanderBakker · Accepted solution]

Hi Paul,

I think you could try something like this:

import arcpy, os
from datetime import datetime, timedelta
arcpy.env.overwriteOutput = True

# change path to datasource
fc = r"D:\Xander\GeoNet\MergePolsDate\Reserves_copy.shp"
fld_start = "DateFrom" # create date field and fill it with content of StartDate
fld_end = "DateTo" # create date field and fill it with content of EndDate
fld_name = "Name"
fld_outname = "OutputName" # output name
len_outname = 75

# output shape
fc_out = r"D:\Xander\GeoNet\MergePolsDate\Reserves_merged3.shp"

# create empty output shapefile
ws_path, fc_out_name = os.path.split(fc_out)
arcpy.CreateFeatureclass_management(ws_path, fc_out_name,
    "POLYGON", fc, "SAME_AS_TEMPLATE", "SAME_AS_TEMPLATE", fc)

# add string field
arcpy.AddField_management(fc_out, fld_outname, "TEXT", "", "", len_outname)

flds = ("SHAPE@", fld_start, fld_end, fld_name)
flds_out = ("SHAPE@", fld_start, fld_name, fld_outname)

# create a unique list of dates from start date
lst_dates = list(set([row[0] for row in arcpy.da.SearchCursor(fc, (fld_start))]))
lst_dates.sort()

# insert cursor to store features to new featureclass
with arcpy.da.InsertCursor(fc_out, (flds_out)) as curs_out:
    # loop through list of unique dates
    for date in lst_dates:
        # create an expression
        expression = "{0} <= date '{2}' AND {1} >= date '{2}'".format(
            arcpy.AddFieldDelimiters(fc, fld_start),
            arcpy.AddFieldDelimiters(fc, fld_end), date)
        i = 0
        # create the searchcursor with the expression
        with arcpy.da.SearchCursor(fc, flds, where_clause=expression) as curs:
            for row in curs:
                pol = row[0]
                name = row[3] # take name of first polygon
                if i == 0:
                    # first polygon is just the polygon"
                    polygon = pol
                else:
                    # next polygons are added with union
                    polygon = polygon.union(pol)
                i += 1

            # insert the feature into the output featureclass
            datefrom = date
            i_date = lst_dates.index(date)
            if i_date + 1 > len(lst_dates) - 1:
                dateto = ""
                outname = "{0} {1} - {2}".format(name, datefrom.strftime('%Y/%m/%d'), dateto)
            else:
                dateto = lst_dates[i_date + 1] - timedelta(days=1)
                outname = "{0} {1} - {2}".format(name, datefrom.strftime('%Y/%m/%d'), dateto.strftime('%Y/%m/%d'))
            curs_out.insertRow((polygon, row[1], name, outname))

The code will create the output column with the format from your example

Kind regards, Xander

PS: If the answer responds you question mark the post as answered at the post that answered your question. If it was helpful, mark it as helpful. This way other forum users will be able to find useful content. More on GeoNet can be found here: GeoNet Help‌