How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

[Anonymous User]

I’ve used the Spatial Join geoprocessing tool using the JOIN_ONE_TO_ONE option to join a line feature class (target) and polygon feature class (join). I’ve also used the merge rule of join on an ID field with a comma delimiter. So in my resulting feature class has one feature for each of the inputs but it has a field with a comma delimited list of the IDs for the polygons that each line intersected with (like what is described in this blog). So if line A intersected with polygon 1 and 2 it would have a value of ‘1,2’. Now I want to build a query that returns all the lines that intersected polygon 1. You might think that a SQL statement that uses the LIKE operator would work, but then any polygon with 1 in the ID would also be returned (Like ID = 10 or 100). What I really need is something like the IN operator but with the field values providing the list. But I can’t get that to work. Does anyone have an idea on how to build this query? Ideally I would make this query from the operations dashboard but any examples would do. One more snag. If you are thinking of using a bitmask, I have more than 64 polygon IDs.

[DanPatterson_Retired]

don't know about the query but here is an example that might work in some situations

>>> a = '10,3,1,2'

>>> str(2) in a.split(',')

True

You can get elaborate and convert the split values to numbers if you want

[BlakeTerhune]

With SQL, I think the "proper" way to do this would be with regular expressions (regex). Not sure if you need SQL Server or Oracle syntax but you can go on the regex adventure yourself. An uglier/messier way to do this would be to just have three LIKE statements; one for each possible scenario the search string could exist.

where intx_field like '1,%'  -- Beginning
    or intx_field like '%,1,%'  -- Middle
    or intx_field like '%,1'  -- End
    or intx_field = '1'  -- Single

Editied to include where clause for single value; thanks Richard Fairhurst​

[Anonymous User]

I like “%” that solution. Get it? Ah SQL jokes are the best.

You would just need to add a line for each significant digit which in my case is 3. Good idea.

I was thinking regex but wasn’t too excited about that. Thanks for the help.

Tom

[RichardFairhurst]

You forgot to include:

or intx_field = '1'

[JoshuaBixby]

If commas are prepended and appended to the field in question, either to the data itself or a DBMS-specific concatenation function, a single LIKE statement of '%,1,%' will work.  Using SQL Server and concatenation function:

WHERE CONCAT(',', intx_field, ',') LIKE ('%,1,%')

or

WHERE ',' + intx_field + ',' LIKE ('%,1,%')

[BlakeTerhune]

As usual, Joshua Bixby​ has a more elegant solution.

[JoshuaBixby]

Thanks.  Overall, the OP's approach is very non-relational, so I don't know how elegant any specific syntax can be.  In a relational context, there would be a second table that lists each feature along with each intersecting feature, and a simple SELECT, and possibly a JOIN, would be used to identify the features.  Unfortunately, being relational in ArcGIS isn't always straightforward, so practicality wins out.