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.
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(',')
You can get elaborate and convert the split values to numbers if you want
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
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.
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,%')
WHERE ',' + intx_field + ',' LIKE ('%,1,%')
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.