SQL for ArcGIS® Pro is an add-in for Esri's ArcGIS® Pro, called SQL or sql4arc for short in this documentation. The add-in provides full SQL capabilities when working in ArcGIS Pro with vector layers from file geodatabases, mobile geodatabases, or GPKG or SQLite layers The add-in also provides an Advanced mode for using SQL with hundreds of other data sources, including shapefiles, rasters like GeoTIFFs, and many other file formats and other data sources.
To install and activate your SQL for ArcGIS Pro license, see the Install SQL for ArcGIS Pro topic. That topic also covers installation of optional files required for work with Esri mobile geodatabases.
See the SQL for ArcGIS Pro - Install and Activate video for installation using Windows Installer. The first three minutes show the easy installation and activation process with the rest of the video showing a quick start to writing and executing SQL queries, including use of the query builder.
To
launch SQL for ArcGIS Pro we must have a project open in ArcGIS Pro that
has a geodatabase layer in it. Before launching SQL for ArcGIS Pro,
add any geodatabase layer to your ArcGIS Pro project if there is not already
a geodatabase layer in the project.
Activation: The first time the SQL for ArcGIS Pro add-in is launched it will raise an activation dialog. Enter your serial number and press the Accept button to activate the license. Your computer must be connected to Internet to activate. For details, see the Install SQL for ArcGIS Pro topic. Information on managing activations is in the SQL Activation topic.
To launch the add-in, first launch ArcGIS Pro and open the desired project. We will use the ubiquitous Venice Acqua Alta project that is popular for ArcGIS Pro tutorials.
Click the Add-in tab in the ribbon.
Click Open SQL. The SQL for ArcGIS Pro desktop will launch in 64-bit mode in a new window.
New! SQL for ArcGIS Pro as of Cutting Edge build 177.2 has dropped the 32-bit version previously installed. There is no downside to that for SQL for ArcGIS Pro users because ArcGIS Pro itself is 64-bit and does not require any 32-bit interactions. However, if you are operating the 177 Base build for SQL for ArcGIS Pro you will see buttons to open SQL either in 64-bit or 32-bit:
Click Open SQL 64-bit. The SQL for ArcGIS Pro desktop will launch in 64-bit mode in a new window. The 32-bit button is an option provided for people who have 32-bit Microsoft Office installed on their 64-bit Windows system and who want to work with 32-bit Microsoft Office formats using Advanced mode. That option has been dropped since ArcGIS Pro users very rarely have not updated 32-bit Office to 64-bit Office, and in those rare cases it is easier to simply install Microsoft compatibility software than to launch SQL in 32-bit mode.
The Refresh ButtonThe Add-in ribbon also provides a Refresh button. Pressing the Refresh button will automatically update all items in our ArcGIS Pro project that might have been changed by operations in the add-in.
The SQL for ArcGIS Pro desktop launches with a command window that allows writing SQL queries. You can resize and move the add-in's desktop as desired. You can also drag the blue border between the upper and lower panes to provide more or less room for each.
To close the add-in, press the X symbol in the upper right corner of the window, or choose File - Exit in the add-in's menu.
The upper pane provides a text pane for writing SQL. The upper pane initially is populated with a simple SELECT query using a geodatabase layer, if there are any in the ArcGIS Pro project, to illustrate how to refer to geodatabases and tables in geodatabases.
The lower left pane provides statements, operators, and other SQL infrastructure. Those can be used in a Query Builder that allows us to double-click SQL language elements, like statements, clauses, or functions, to add them to the query text in the upper window. That is much faster than manually keyboarding them.
The lower right pane provides tables with schemas for all the tables in the file geodatabases, mobile geodatabases, or GPKG or SQLite layers that are used in the ArcGIS Pro project. We can double-click on any item in the lower left or right panes to quickly add it to the query text, saving the need to manually keyboard what might be long table names or field names.
Click the ! Run button in the toolbar to run the query.
The command window switches to the Results tab in the lower tab strip to show the results. We can adjust the widths of columns in the results table by dragging their boundaries to the left and right. We can also edit the contents of the Landmarks table through the results table by double-clicking into a cell. There are many capabilities for working with tables, including results tables, that are part of SQL for ArcGIS Pro.
We can edit the query in the text pane right away, or click on the Query Builder tab to switch to the query builder panes for assistance in writing queries.
In the illustration above we have clicked the Query Builder tab, and then entered stringl into the filter box for the lower left pane. That filters down the list of hundreds of functions and other infrastructure to only the two items that have that sequence of characters in them.
Position the cursor where desired in the query text and then double-click on the entry for StringLength to enter that function into the query without having to keyboard it. In the query text pane, double-click on the <string> argument to highlight it and then in the lower right pane double-click on the [Name] field in the Landmarks table schema to replace the highlighted <string> argument with the [Name] field name. With a bit of experience and muscle memory we can very rapidly assemble queries by picking items from the lower panes, instead of manually keyboarding.
Delimiters around identifier names are not required for identifier names that follow the usual SQL rules, such as no spaces in the name. We can use names that break the usual rules by quoting them with delimiter characters, which can be square brackets [ ], double quote " characters, or back tick ` characters. This documentation uses square [ ] brackets in examples.
Our new query text:
SELECT * FROM [final.gdb]::[Landmarks]
WHERE StringLength([Name]) < 15;
The StringLength SQL string function returns the length of a string. We have adapted the query to return only those landmarks with a name that has less than 15 characters in it.
Press the ! Run button to run the query.
The results table now includes only those records from the Landmarks table in the final file geodatabase where the Name field value has fewer than 15 characters.
That is a very simple query that reports results, like we might use with SELECT, SELECT DISTINCT, UNION and similar queries that generate results tables but which do not modify the original table or create new tables. Queries also can be SELECT ... INTO queries to create new tables or UPDATE, INSERT, DELETE and similar queries that modify geodatabase tables.
There is reasonable synchronization between geodatabase and other layers in ArcGIS Pro and actions in SQL so long as table schemas are not changed. For example, when SQL launches it will automatically link file geodatabases, mobile geodatabases, or GPKG or SQLite layers that are used in the Pro project, and will automatically populate the table and schema pane with tables for those layers used in the project. With SQL open, if we click on the ArcGIS Pro desktop and add a new file or mobile geodatabase layer to the project, if we close and then re-open the add-in that layer will appear in SQL as well. Sometimes we might have to refresh a table in Pro to see editing changes made in SQL, such as altering the value of a field for a particular record.
To refresh an attribute table in Pro, press the Refresh button in the Add-in ribbon in Pro. Pressing the Refresh button in the Add-in ribbon in Pro will refresh attribute tables to show new records, and to show any changes to the attribute table's schema that may have been made in SQL.
Pressing the Refresh button in the Add-in Ribbon will also update the Pro session if we use SQL to make changes to a file or mobile geodatabase. For example, if we add a layer to a file geodatabase by copying and pasting a table within SQL for ArcGIS Pro into the geodatabase, pressing the Refresh button will refresh Pro's view of the GDB and will allow us to see that new layer in the geodatabase in ArcGIS Pro.
ArcGIS Pro users know that attribute tables in ArcGIS Pro should be closed before trying to alter them using ArcGIS Pro tools, such as in the toolboxes. The same rule applies for SQL. Do not open attribute tables in ArcGIS Pro before altering them in SQL. It is usually OK to leave attribute tables open in Pro if all we intend to do is to view those tables in SQL (with no modifications attempted), but the most reliable way to proceed is to leave tables closed in the Pro session.
The reason for this rule, both when using ArcGIS Pro's own tools and also for SQL, is that ArcGIS Pro locks tables in a wide variety of situations, even when locks are not necessary, and Pro leaves those locks in place even after the need for them has passed. When an attribute table is open in the ArcGIS Pro session, Pro may lock that attribute table in a way that makes it impossible for SQL to update values in the table as long the attribute table remains open in Pro.
For example, if an attribute table is open in Pro and we try to use an UPDATE statement in SQL with that table to populate the fields with values for each record, the UPDATE will fail with an error message about not being able to lock the table. The problem is that Pro locked the attribute table in a way that prevents updates.
To avoid such situations, close an open attribute table window in Pro before altering tables or doing updates in SQL or otherwise writing to the attribute table. Given the many ways in which Pro can unexpectedly lock a file or a table, the best approach is to not open attribute tables in ArcGIS Pro when working with both Pro and SQL. Instead, open the table in SQL.
SQL takes a much more reliable approach to locks: tables and files are locked only when absolutely necessary, for the brief moments when they are being written to, and then the locks are immediately cleared, freeing up the tables for read/write use by other processes. Besides more reliable locking protocol, SQL also has easier and more productive ways of viewing and working with attribute tables than Pro. Get in the habit of opening, viewing, editing, and transforming attribute tables in the SQL for ArcGIS Pro add-in, instead of popping open attribute tables in Pro.
If you get an error message in SQL about not being able to lock the table, that indicates the table is open in Pro or it is being locked by Pro in some other way. Close the table if it is open in Pro, and click some other layer to make sure the feature class is not highlighted in the Contents or Catalog panes in ArcGIS Pro (Pro will sometimes lock a file even if is not in use but simply has been clicked to be highlighted).
The SQL for ArcGIS Pro add-in includes within it the same, full-power, high speed Radian® spatial data engine used for Manifold Release 9. That gives the add-in very fast local spatial storage capability independent of the ArcGIS Pro instance and independent of the geodatabases used in the Pro project. The add-in can store over a terabyte of data within the add-in's own local storage, and it can save that stored data to a project file in Manifold .map format.
Local storage within the add-in is a great facility for storing intermediate tables and results created with SQL. That allows high speed execution of even very complex queries between data sources, with only the results being used to alter the ArcGIS Pro project's geodatabases.
To see the add-in's local storage, in the main menu, choose View - Panes - Project to turn on the Project pane.
The Project pane lists all data sources which are linked into the add-in's local storage, and it will show all new tables and other components that are created in the add-in's local storage, either using SQL or by importing from any of hundreds of different data sources or file formats that the add-in understands. In this case, we see that the ArcGIS Pro projects has layers from three different file geodatabases. The System Data folder in the Project pane
SQL for ArcGIS Pro automatically links all file and mobile geodatabases used in the ArcGIS Pro project, so the Project pane will show data sources (the items with a database cylinder icon) for those geodatabases. If we add layers from other geodatabases to the ArcGIS Pro project, when we close and re-launch the add-in those new geodatabases will also appear in the Project pane and in the Query Builder.
We can create new tables in local storage in the add-in using SQL or by importing or linking into the add-in.
For example, a SELECT INTO query that does not specify a data source as the destination will create the new table in local storage:
SELECT * INTO [Shortnames] FROM [final.gdb]::[Landmarks]
WHERE StringLength([Name]) < 15;
Press the ! Run button to run the query.
That creates a new table called Shortnames. So far, that new table exists in memory in the add-in's local storage. If we close the add-in without saving the project, the new table will disappear. If we use File - Save to save the project to a .map file, the new table along with the links to the geodatabases will be saved in that .map file. When the add-in has been launched, we can use File - Open, File - Link, or File - Import to access data in saved .map project files.
SQL for ArcGIS Pro uses Manifold .map project files for storage. .map files are fully parallel data access and are incredibly fast to open, to read, and to write. A .map file that is 100GB in size with hundreds of tables, queries and other items in it can be opened in 1/2 second. .map files are also extremely durable, with fault tolerant protections for things like power failures in the middle of saving changes to a .map file. Because a project is saved as a single file, it is easy to backup and to share with others.
Being able to save the contents of an SQL for ArcGIS Pro project in .map files allows us to focus on writing sophisticated queries with no fear any of our work product will be lost.
The SQL project pane in the illustrations above shows the gdb file geodatabases currently used by Pro, which were automatically linked into SQL when the add-in was launched from Pro. We can link into SQL a geodatabase that is not currently in use by Pro.
In the illustrations below we have closed the gdb data sources to provide more room in the illustration.
We click onto the lower part of the project, outside of any of the gdb data sources. SQL can link or import data directly into a data source, so we move the focus into the local storage part of the project so that anything we link or import will be created in SQL's own storage, and not within one of the geodatabases we have linked.
Next, from the menu we choose File - Link.
In the Link dialog we navigate into the file geodatabase folder and click on the gdb file in that folder. File geodatabase folders have zillions of files in them, so to make it easy to click on the one gdb file we choose GDB Files (*.gdb;gdb) in the filter box in the lower right so that only gdb files are shown.
A small difference between ArcGIS Pro and the SQL add-in is that to choose a file geodatabase in Pro we normally click on the folder name for the file geodatabase, while in SQL we navigate into the folder for the file geodatabase and we click on the gdb file that is in the folder. This is likely to be updated in new versions of the add-in, so that we can just pick the folder in SQL the same as we do in Pro.
Press Link.
A new Venice Points of Interest.gdb data source appears in the project list.
We expand the data source and double-click the Points_of_interest table.
That opens the table in a window in the SQL add-in, allowing us to see the contents of the table. If we know OpenStreetMap, we can see this data set was originally imported into a file geodatabase from OpenStreetMap. It provides points of interest in Venice, with each point being identified as a pharmacy, restaurant, or whatever, in the fclass field.
Fields in a table that cannot
be edited will be shown with a gray background color. Examples are
special fields like the OBJECTID
field seen in the illustration above, or calculated length or area fields
that may appear in attribute tables. Fields with a gray background
color are read-only
fields and are typically calculated or otherwise automatically generated
fields.
We can interactively edit fields in a GDB by double-clicking into a cell, making the edit, and then pressing Enter. We must, of course respect the GDB's limitations on fields, such as the limitation on the length of a text field that a GDB may impose. Attempting to write a text value into a GDB that is too long will cause an error message with the name of the field, the maximum length allowed by the field and the actual length of the value.
When we link a geodatabase or other data source into SQL's local storage, the data stays in the original data source, be it a geodatabase or some file format. That's how ArcGIS Pro works all the time: the various layers we have in our Pro project remain stored within their original sources.
The SQL add-in gives us the additional option of importing data from the original source, so the data is stored in the SQL add-in's own internal database. When we import data into SQL we make a copy of the data from the original source and save that within SQL's own data store. A huge range of file formats, databases, and other data sources are available from which we can either link or import data into a project.
For example, we can import raster data from a GeoTIFF file.
With the focus on the lower part of the project, choose File - Import.
Navigate to the folder of interest and click the .tiff file desired. Press Import.
The GeoTIFF we have imported is a terrain elevation raster giving the elevation of the ground surface in Venice with one meter resolution. The data appears in the SQL project as a table storing raster tile data, a metadata text component, and as a raster image that visualizes the data in the table.
When we link or import data into an SQL project, we can run queries on tables from different data sources as well as tables in geodatabases used in the Pro project. For example, we can run queries on tables in geodatabases and tables in a SQL Server database, or between geodatabases in the Pro project and in SQL local storage but not in the Pro project. The possibilities are endless.
For example, now that we have a raster providing ground elevation in local storage, we can use SQL to do raster to vector JOINs, for example, to add to each of the buildings in the Structures layer a Ground_level field that gives the lowest ground elevation under any part of the building's footprint. That will tell us which buildings will experience flooding in some part of their footprint at various high water levels.
SQL can take the data from the Venice_Ground_Surface raster and do the JOIN, even though the raster is not in the ArcGIS Pro project while the Structures layer is in the ArcGIS Pro project. It can even add the new Ground_level attribute field to the Structures layer and update it with the results of the JOIN.
The illustrations above are just a beginning. SQL for ArcGIS Pro provides very many capabilities for doing SQL with projects and data in Pro. The hundreds of topics and thousands of pages in this documentation cover use of SQL in general, and then the many other Advanced capabilities beyond SQL.
SQL for ArcGIS Pro allows working with hundreds of other data sources, some of which support data types or field content sizes that are different than Esri file geodatabases. In general, a simple Copy from the other data source and a Paste into the geodatabase is all that need be done, with SQL for ArcGIS Pro automatically making any conversions.
Although very long text fields might not be a performance issue in some other databases, very long text fields might impact performance in file geodatabases. Therefore, to protect file geodatabases from accidental insertions of very long text values, copying data into a file geodatabase or exporting a table into Esri file geodatabase format automatically truncates text values to 4096 characters. The Log Window will report the names of fields containing truncated values as well as the total number of truncated values.
As a reference for writing SQL queries for the most common GIS tasks, Manifold strongly recommends How do I do that in SQL for ArcGIS® Pro by Dr. Arthur Lembo, available on amazon.com.
For a hands-on learning experience, Manifold also strongly recommends Dr. Lembo's video training materials, at www.gisadvisor.com/sql4arc.
There are many good books on SQL, such as Chris Fehily's excellent books, which are used as examples in many topics in this documentation. You can read Chris's latest book online for free, or get your own copy with useful extras for a low price.