SQL for ArcGIS Pro

Welcome!  SQL for ArcGIS® Pro is an add-in for Esri's ArcGIS® Pro.   SQL for ArcGIS Pro is often called SQL or sql4arc for short.   

 

SQL for ArcGIS Pro makes it easy for ArcGIS Pro users to enjoy the outstanding power of real SQL.  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.

 

SQL is powered by a version of the Manifold® Release 9 spatial engine that has been adapted with special support for ArcGIS Pro.   The add-in automatically runs fully parallel, both CPU parallel and GPU parallel, and it includes hundreds of spatial SQL functions for vectors, rasters, and attribute data.

 

Although the primary mission of SQL for ArcGIS Pro is to provide SQL for vector layers from file geodatabases, mobile geodatabases, or GPKG or SQLite layers that have been added to the ArcGIS Pro project,  the add-in also includes hundreds of other Manifold features that can be used in Advanced mode but which have not been closely integrated into ArcGIS Pro.  For example, Advanced mode provides access to point and click dialogs that capture the power of SQL with no need to write queries, as well as sophisticated transform, selection, analytic, editing and visualization capabilities.   

 

Over time, Manifold plans to custom tailor more and more of those other features to provide a better user experience with ArcGIS Pro.  In the meantime, by using the add-in's Advanced mode, users who would like to explore beyond SQL can take advantage of the many Manifold features as they are.

 

This documentation covers features specific to SQL together with the most relevant related features in the initial SQL for ArcGIS Pro chapter.   Features not directly relevant to using SQL are covered in the Advanced chapter.  

 

Most topics in the Advanced chapter are unmodified Release 9 topics that are the same as published for other Manifold products.  Using unmodified Release 9 topics makes it possible to immediately provide new Manifold features to SQL users as soon as they become available in any Manifold product.

Overview

To launch SQL, launch ArcGIS Pro, open a project, then click the Add-In tab and press the Open SQL 64-bit button in the sql4arc group.  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.

 

 

 If desired, the add-in can be launched in 32-bit mode by clicking on the Open SQL 32-bit button.  That is an optional capability that is not normally used when working with layers within the ArcGIS Pro project.  See the discussion under Advanced mode for when it may be handy to launch the add-in in 32-bit mode.

 

Launching the add-in opens the SQL desktop.   The add-in's desktop will be sized to fit within the ArcGIS Pro desktop window.  You can resize and move the add-in's desktop as desired.

 

 

When launched, SQL scans the ArcGIS Pro project and connects directly to all file geodatabases, mobile geodatabases, and GPKG or SQLite layers that are used in the project.  Tables and their fields from those geodatabases or layers will populate the lower right pane in the SQL add-in.  We can double-click those to add to queries in the main, upper, text pane without having to manually keyboard long names of tables or fields.  A sample query appears in the text pane to help us get started.

 

The add-in's desktop runs in a separate session from the ArcGIS Pro session, with special links maintaining communications between the two sessions.   To ensure reliable operation, if Arc crashes the SQL add-in will continue to run.   If you would like to close the ArcGIS Pro session or use it with a different project, you can do that and SQL will continue running on the session it has loaded.   To resynchronize with whatever is the new ArcGIS Pro session, close SQL and then relaunch the add-in from Pro.

Geodatabases

File geodatabases and mobile geodatabases can handle connections from more than one process.  By connecting directly, SQL can read and write data from the geodatabases much faster than passing requests through the ArcGIS Pro session.  The SQL add-in maintains bidirectional connections with ArcGIS Pro.   Comments below apply to both file geodatabases and mobile geodatabases.

 

 

If the ArcGIS Pro project contains layers from data sources that do not allow simultaneous read/write connections from more than one process, such as shapefiles, SQL will not see those layers and will not be able to work with them while they are part of the ArcGIS Pro project. 

 

If the ArcGIS Pro project contains layers from data sources that do allow simultaneous read/write connections from more than one process but which are not  file geodatabases, mobile geodatabases, and GPKG or SQLite layers, SQL can work with those data sources if we add them manually in Advanced mode.  Enterprise geodatabases (not yet automatically loaded by SQL for ArcGIS Pro) are a good example of such data sources that can be added manually in Advanced mode.

 

SQL contains a very fast database engine that can store vast amounts of data.  It is faster in many cases for spatial work than even enterprise DBMS packages like PostgreSQL.  That enables SQL to work on its own, independently of ArcGIS Pro, to link to data sources to which ArcGIS Pro cannot connect.  SQL can also import data into its own internal database from a vast range of file formats, databases, and other data sources, if desired saving the imported data within its own extremely fast file format.  That enables using the add-in's own internal storage as scratch pad storage to save intermediate results in complex workflow involving a sequence of queries, to provide better performance and the ability to work with larger data sets.

Advanced Mode

Advanced mode simply means turning on additional controls within the SQL desktop that enable Manifold features beyond SQL for file geodatabases, for example, allowing SQL independently to connect to and to work with data sources other than file geodatabases that are used within the ArcGIS Pro project.   

 

For example, we can turn on the Project pane in the SQL desktop to maintain a roster of data sources that are linked into SQL independently of ArcGIS Pro, or which have been imported into the add-in's  internal database.  When using Advanced mode the add-in works with a vast range of other data sources that can be imported or linked into the add-in's project, such as shapefiles, rasters, and many other types of GIS data.

 

 

In Advanced mode, we can link to enterprise geodatabases or to file or mobile geodatabases that are not currently used within the ArcGIS Pro project, and we can write SQL that uses tables in those data sources in addition to tables in the geodatabases that are used within the ArcGIS Pro project.   We can connect to or import from file formats such as shapefiles, or GeoTIFF, or a seemingly endless range of other file formats.   That is a great way to use SQL to transform or otherwise to manipulate data sets, such as shapefiles, which we intend to use in our ArcGIS Pro project.    Do the work first in SQL, and then add the resulting data set layers to the ArcGIS Pro project.

 

Advanced mode is also great for working with enterprise geodatabases that are part of the ArcGIS Pro project.   Current SQL builds do not automatically connect to enterprise geodatabases that are used in the ArcGIS Pro project, but we can manually connect to them in Advanced mode and work with them.

Optional 32-Bit Mode

SQL for ArcGIS Pro is fully, natively 64-bit, and that is the default way we normally launch the add-in.  However, a 32-bit version of the add-in is also provided for optional use for those people who have a legacy, 32-bit version of Microsoft Office installed in their 64-bit Windows system.  In such cases the 32-bit version makes it easy to connect to 32-bit data sources like legacy Microsoft Office formats when using Advanced mode.  Except for use when 32-bit Microsoft Office has been installed on the same system, the 32-bit version of SQL for ArcGIS Pro is not used.

 

Microsoft Office formats like Access MDB and Excel XLS are poorly supported by Microsoft in 64-bit Windows.  Conflicts between Microsoft Office versions and other Microsoft software can make it difficult to install the right combination of Microsoft software that will enable 64-bit software in 64-bit Windows to connect to Microsoft Office formats.  

 

To link to or import from a Microsoft Office file, launch SQL for ArcGIS Pro in the usual way and try linking to or importing from a Microsoft Office format file, like an .mdb or .xlsx.  In most systems it will just work.   If it does not work, the following tips will make connecting to Microsoft Office formats easy, depending on what version of Microsoft Office, if any, has been installed.   Since ArcGIS Pro is a 64-bit application, we assume you are working with 64-bit Windows:

 

 

 

 

The only time we normally launch SQL for ArcGIS Pro in 32-bit mode is to connect to Office formats when we have an old, 32-bit version of Microsoft Office installed.  We do that because 32-bit Microsoft Office conflicts with Microsoft's 64-bit code for connecting to Office formats.

 

There are tricky ways of installing Microsoft code to enable 64-bit access to Office formats even if 32-bit Office is installed.  But those methods are such a hassle it is much easier to just launch SQL for ArcGIS Pro in 32-bit mode if we have 32-bit Office installed.

 

Important details:

 

 

 

If we launch SQL for ArcGIS Pro in 64-bit mode from one ArcGIS Pro session, and then from a different, second ArcGIS Pro session we launch the add-in by clicking the 32-bit button, the add-in will nonetheless launch in 64-bit mode.    Likewise, if we have a 32-bit session of SQL for ArcGIS Pro running and then we launch the add-in from a second ArcGIS Pro session by clicking the 64-bit button, the add-in will nonetheless launch in 32-bit mode, since a 32-bit session is already running.

 

Product Downloads

Visit the Product Downloads web page for SQL for ArcGIS Pro installation packages.  SQL requires the latest version of ArcGIS® Pro.  Installation packages are automatically updated to track Esri releases of ArcGIS Pro.

 

SQL builds are issued either as Cutting Edge builds or as Base builds.   

 

 

 

Manifold strongly recommends using the latest Edge build.  Although Edge builds are technically beta builds, they are rock-solid reliable and include the latest bug fixes, which a Base build issued two months prior will not have.  Don't be scared of using Edge builds.  Learn how to work with portable installations and quite likely you'll never go back to using Windows Installer.   However, if you prefer a Windows Installer installation, do not hesitate to download and to use whatever is the latest Base build.

 

In either case, newer versions of SQL for ArcGIS Pro are free to download.  If SQL for ArcGIS Pro has been activated on a machine, no new activation will be required to uninstall an older build and to then install a newer build.    SQL builds are cumulative, including all new features and fixes issued in previous builds. You do not need to install any previous builds to get everything in the latest build.

 

When launched, SQL automatically checks if a new Base build has been released.   SQL does  not automatically check for updates for new Edge builds, which are announced in the georeference.org user forum.

 

See the What's New in the Latest Build topic for changes in the latest build.  That topic is updated within a day or two of a new build being issued.   Notes on changes will help you get started with new features while comprehensive updates to documentation are prepared and published.

Installation

For installation and activation guides please see:

 

 

 

 

Localization

The SQL GUI uses English by default. To use other languages for the GUI, provide a localization file as discussed in the Localization topic.   Localization files, of course, do not translate SQL key words since SELECT is SELECT in SQL for French users as well as for English users, but it can be handy to have various menus and point and click dialogs translated to your own native language.

 

SQL uses the same localization files as Release 9 and the free Manifold Viewer, so there are many languages to choose from.  Since virtually all of the GUI words, like the File, Edit, View, and other menu words are the same, a localization file for Release 9 will work well for SQL and vice versa.   Anyone can create a localization file for a desired language.  

 

See the Product Downloads web page for links to localization files for Arabic, Bengali, Chinese (simplified, Mandarin), Dutch, Estonian, Filipino (Tagalog), French, German, Hindi, Japanese, Portuguese, Russian, Spanish, and Turkish that have been contributed by users.

 

User Manual Translation

This User Manual is currently available only in English.    However, a remarkably good translation can be made on the fly into many languages by using the DeepL Translator, a free application.  Download and install DeepL's Windows app.  You can then highlight some text in the manual or on a web page and then while holding the Ctrl key down press the C key twice.  The app will pop open and translate the highlighted text for you.  

 

In Manifold's testing of different translators for the user manual and web site, DeepL's translations were significantly better than Google or Microsoft translators.  DeepL translations also provide a realistic and convenient way for users whose native language is not English to participate in the English language georeference.org user forum.

 

Three Letter Extensions

Most file names in Windows end in a three letter extension, which is usually three or four letters at the end of the file name following a dot . character.    The three letter extension is one way Windows  keeps track of what a file is supposed to be.    Unfortunately, by default Windows hides the three letter extensions of files and instead tries to associate files with whatever program it thinks should be used to open that file.   That may be helpful for inexpert consumers, but for skilled users it is inconvenient and can be misleading when working with the many file formats that ArcGIS Pro, SQL and other GIS   products utilize.  

 

Tech tip:  Please turn off the hiding of extensions by Windows.  A typical way to do so in most versions of Windows would be: In Windows Explorer, choose Tools - Folder options, press the View tab and then in the Advanced Settings pane ensure that the Hide extensions for known file types is unchecked.  Press Apply to Folders and then press OK.  You will then be able to see extensions such as .gdb, .shp, .map and others.  See also the Essay on three letter extensions.  (Tech tips in this documentation are often highlighted by an avatar for a member of Manifold's engineering staff.)

 

Learn SQL Fast

To start work right now, go straight to Getting Started with SQL.   

 

Advice:  SQL for ArcGIS Pro is a very big, sophisticated package.   Please do not make the mistake of thinking it can be learned by poking at toolbar buttons.  Instead, take advantage of this user manual and training and education materials for a fast launch.    

 

Working with SQL is straightforward for people who know SQL, but there is also a huge universe of capabilities in Advanced mode use of SQL for ArcGIS Pro.   That's basically an alternative approach to GIS that rivals ArcGIS Pro itself in terms of thousands of features.   You don't have to learn all those Advanced features to do SQL, which is the purpose of the add-in, but they are there for you to use if you want to learn about them.    To learn about Advanced mode features, start reading through the topics in the Advanced chapter in order, at least covering topics up through the Basics chapter and working through introductory Examples.

 

Since the focus of SQL for ArcGIS Pro is SQL, it helps if you know SQL. SQL is very easy to learn: even if you don't know SQL at all, in about 15 minutes you can learn enough to immediately become more productive  in your GIS work.  SQL is one of those truly super inventions where just a little bit of learning pays off right away, while continued learning will keep paying off for an entire lifetime.  

Training and Education

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.

 

Let's Go !

To start work now, go straight to Getting Started with SQL.   

 

About SQL for ArcGIS Pro

SQL for ArcGIS Pro is based on a special version of Manifold Release 9.   It packages Manifold's parallel spatial technology in a convenient add-in that is integrated with ArcGIS Pro.   The parallel spatial SQL power of SQL for ArcGIS Pro comes from Manifold's Radian® spatial database engine, which is built into SQL for ArcGIS Pro and is the same spatial database engine used in Manifold's own Release 9 GIS package.   Having been created expressly for GIS, the Radian engine is an especially good add-in for a premium GIS like ArcGIS Pro.

 

By sharing a common code base with Release 9, SQL for ArcGIS Pro automatically benefits from Release 9 improvements such as enhanced performance, more powerful SQL, new features, and bug fixes.  Ongoing engineering work for 9 helps make SQL faster and better as well.  SQL is a relatively new product for ArcGIS Pro but the code inside SQL has been thoroughly proven and debugged in years of demanding, real life work by many users around the world.

 

Although the mission of SQL is to provide Esri users with the world's best spatial SQL to use with geodatabases and other layers in ArcGIS Pro, the add-in also works with a vast range of other data sources, and it includes hundreds of other features from Release 9 that SQL users can use in Advanced mode.  Over time, Manifold plans to custom tailor more and more of those features to provide a better user experience with ArcGIS Pro.  In the meantime, intrepid ArcGIS Pro users can take advantage of the many features beyond SQL as they are.   After all, what you get for free beyond killer SQL for file geodatabases is icing on the cake.

 

SQL is usually pronounced "Ess Que Ell", but some users prefer "sequel".  Both forms are OK and are widely used in the IT / DBMS community.

 

Manifold Software Limited is not affiliated with Esri.  ArcGIS is a trademark of Esri.  Manifold and Radian are registered trademarks of Manifold Software Limited.

 

See Also

Install SQL for ArcGIS Pro

 

Getting Started with SQL

 

Localization