In this article you will learn how to create a SQL expression from a selection by location.
Surely it has happened to you that you must show a subset of elements that belong to a feature class (or shapefile) that intersect with another layer or elements that you have manually selected, but you do not want to create a new layer, one option is to make a selection by location, or already with the selected records right click on the layer, press the “Selection” button and select the “Create Layer From Selected Features” option; this will create a new temporary layer with only the selected elements, but you will have lost the symbology and this layer will not be involved in any element of any legend that you have previously established, and as I mentioned, it is temporary.
While you can list the elements manually and then create an SQL expression, this starts to get complicated when there are tens, hundreds or thousands of elements and although there is no way to directly make an SQL expression from a selection in ArcGIS, I will show you a solution, but I need you to have an open mind, this procedure is unconventional, however, remember that science is to see what everyone sees, and think what no one has thought, taken to our context, you can achieve new things with your previous knowledge if you are resourceful. The step-by-step is described below.
SQL from a selection
Step 1: Select the elements you need, either with a selection by location, manual selection of the geometric elements or selection of the records from the attribute table.
Step 2: Right click on the layer and press the “Open Attribute Table” button to open the attribute table (a).
Step 3: Press the “Show selected records” button (a) and go to the upper left corner, right click on the first record (b) and press the “Copy Selected” button (c).
Step 4: Open an Excel workbook, and paste the information from the attribute table you just copied by going to cell A1 (a) and press Ctrl+V (b).
Step 5: Select the column that has the tributes with which you can differentiate the selected elements, it can be the OBJECTID field, or some other; discarding in the selection the column header (a).
Step 6: Copy the records of this column (a) and create a new spreadsheet, go to cell A1 in the Paste menu, select the transpose option (b) to paste the information in this new sheet.
Step 7: Save this new spreadsheet as a comma separated file “.CVS” (a), and open this file with a text editor, such as blog notes (b).
Step 8: If the original attribute table field is numeric type (double or integer, etc.) copy the entire contents of the notepad and go to the next step (a). If the data type is alphanumeric, replace the commas (,) with comma, comma, quote (',') and copy the entire contents of the entire notepad once this modification is made.
Step 9: Back in ArcGIS, right click on the layer to which you want to apply the SQL expression and press the “Properties...” button (a), then go to the “Definition Query” tab (b) and paste the following expression in the corresponding box.
FIELD in ('REPALCE')
Step 10: From the above expression pasted in ArcGIS, replace the word FIELD with the name of the field to be used, the same as in step 5, and the word REPLACE, with the copied from the notes blog (Keep the original quotes of the expression if the field is alphanumeric, if the field is numeric just keep the parentheses).
Step 11: Press the “OK” button to apply the expression.
Done, with this you will have succeeded in making a SQL expression from selected elements.
I hope you enjoyed this article, soon I will upload a video explaining the procedure that you can consult in this blog. Save my blog among your favorite links, I will be uploading many more tricks of this style, remember that you can send me your concerns in the contact page or leave your comment, I will be attentive to respond.
