AnsweredAssumed Answered

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

Question asked by tbrenneman-esristaff Employee on Jun 15, 2016
Latest reply on Jun 20, 2016 by bixb0012

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.

Outcomes