Text field width exceeded on Spatial Join

[deleted-user-zpcJw1u0IXiO]

I have a Spatial Join that has a JOIN merge rule on the text field name ZONING. I find I keep having to widen the ZONING field whenever it fails on a value too long to store in it. The latest iteration failed on a value 10,704 characters long. When I attempted to make the field 11,000 wide, it told me 8000 is the max. So what can I do?

Later in the script I eliminate duplicates and put the ZONING field content in alphabetical order, but because this extreme length of value occurs in the middle of the executing Spatial Join, I don't know how to intercede there. I am eager for any suggested workarounds, please.

Thanks,

Justin

[JoeBorgione]

Can you perform your spatial joins in 'pieces'?  I take it you are joining many feature classes which is leading to exceeding the 8,000 character limit.  If you only did a few at a time, and then cleaned out the duplicates as you go, would that help?

[deleted-user-zpcJw1u0IXiO]

Thanks Joe, but unfortunately it is just two feature classes I'm joining: parcels and the zoning. Some parcels have many zones intersecting them, and apparently one has many dozens--likely a railway parcel stretching the length of a municipality. My plan is to turn off the join merge rule to run the script and sort by Join_Count. Perhaps then I can find a workaround to filter that one out

[deleted-user-zpcJw1u0IXiO · Accepted solution]

I've done the Spatial Join with merge rule of 'First' and in the output I found maximum Join_Count value is 1223, with second most at 815. Here I noticed the parcel ID field is blank on these. So by this I realized the MakeFeatureLayer_management() statement in the code that creates the parcel layer lacks a where clause to limit the layer to parcels alone.This is critcial because the parcel feature class includes road allowances in a subtype. Road allowances don't break at every intersection. Hence the hundreds of joins encountered.

So I've added the where clause to MakeFeatureLayer_management() and am running the script now. I will reply back to report what happens.

[deleted-user-zpcJw1u0IXiO]

With the where clause in place, the maximum Join_Count dropped to 66 or thereabouts... I'm unsure the exact number because I'm running the script again with the merge rule changed back to JOIN, and can't look again because it would risk locking the schema and causing the script to fail. Something I'm loath to do given the Spatial Join takes nearly 20 minutes. Over 50k parcels collectively intersecting thousands of zone polygons across eight municipalities is why so slow.

[deleted-user-zpcJw1u0IXiO]

I've run the script with the JOIN merge rule restored, and with the where clause in place on the Make Feature Layer can report that Maximum Join_Count is 60, and thanks to the code that eliminates duplicates, there's only 5 unique zones on that parcel. 

I'm back in business, so thank you to both Joshua Bixby and Joe Borgione for the help!

[JoshuaBixby]

What spatial data formats are you working with?  File geodatabases allow text fields with millions of characters, but shape files allow only 254 characters.

[deleted-user-zpcJw1u0IXiO]

Thanks Joshua. I'm using a SQL Server SDE geodatabase. I had wondered about changing the field type from TEXT to BLOB, but then was unsure whether it could store plain text, so did not proceed. I am curious if that could be a viable option.

[JoshuaBixby]

Use VARCHAR(MAX) or NVARCHAR(MAX):  SQL Server data types supported in ArcGIS—Help | Documentation