Service

• Product Downloads
• Free Updates
• Activation Guide
• Serial Number Status
• Technical Support
• Online Store

Contact

• sales@manifold.net
• Suggestions
• Contact Info

Real SQL vs. Not Really SQL

Surprisingly, ArcGIS Pro does not have real SQL built-in. Instead, Pro has less than 5% of real SQL. Pro includes only a few bits of SQL-like syntax for very simple expressions. It's not SQL, but a painfully small slice of SQL.

Most SQL statements, clauses and other SQL syntax are not built into ArcGIS Pro. With 95% of SQL missing, including the most powerful parts, the 5% of syntax that ArcGIS Pro provides is not really "SQL" as real SQL users know it.

SQL has around 90 or so statements and clauses. ArcGIS Pro expressions are limited to only one statement, a limited SELECT statement, and only one clause, a limited WHERE clause. ArcGIS Pro has no JOINs, no UPDATEs, no DISTINCT, no DROP, no ALTER, and none of the other critically important capabilities that real life SQL users depend upon. Pro has no SQL for rasters.

The sliver of SQL syntax in ArcGIS Pro is also read-only, a crushing limitation that gives up the intense power of SQL as a means of creating new tables and editing, updating, and transforming existing layers.

If you rely on the phenomenal power and convenience of SQL in databases, you want 100% of the power and convenience of SQL for your ArcGIS Pro desktop, not a 5% subset. The SQL for ArcGIS Pro add-in installs 100% of SQL so you can work spatial SQL magic on file geodatabases and hundreds of other data sources.

What's in Real SQL?

This page summarizes reports by SQL for ArcGIS Pro users of why they consider SQL in SQL for ArcGIS Pro to be a superb SQL, especially for spatial work, and why default ArcGIS Pro does not provide real SQL as SQL users want.

The following sections take a look at what goes into real SQL, to see how the SQL for ArcGIS Pro add-in installs key SQL capabilities that are missing in default ArcGIS Pro.

Real SQL Built In or Just a Client?

Most desktop GIS packages don't have any SQL, although some, like ArcGIS Pro, can connect as dumb clients to DBMS packages that do have SQL. When connected as a client, ArcGIS Pro depends totally on whatever the external data source can do. If the data source doesn't have real SQL, Pro doesn't have it either. Connect to a different DBMS and you need to learn a different SQL, whatever SQL that different DBMS uses.

• Default ArcGIS Pro - If an external DBMS package with SQL is used, ArcGIS Pro can launch queries in that DBMS's SQL, but only for data in that DBMS.
• SQL for ArcGIS Pro - SQL for ArcGIS Pro can be a client too, even while it is acting as its own server. SQL for ArcGIS Pro can query external DBMS sources using its own SQL, or the native SQL for the package, or mix of both in the same query. SQL for ArcGIS Pro can mix data from different DBMS sources in the same query.

It's easy to tell if a GIS is just a client instead of having real, built in SQL capability: connect to a shapefile or to a GeoTIFF and run an SQL query like the simple example below. If the package can't do that, it doesn't have real SQL.

For example, suppose your shapefile has lines for highways in the US, with Name and Length attributes for each highway, but each highway, like Interstate 80 across the US, is represented by many small lines laid end to end instead of one big line. ArcGIS Pro does not allow writing a simple query to merge all like-named lines into a single polyline, adding up their lengths, and placing the result in a new table. That's a typical job in real SQL that can't be done in Pro without using SQL for ArcGIS Pro.

With SQL for ArcGIS Pro it's easy. Here's the simple query to do that in SQL for ArcGIS Pro, using one of the add-in's many SQL functions to merge the lines:

INSERT INTO [merged roads] ([Name], [Length], [Geom]) 
  SELECT [Name], Sum([Length]), GeomMergeLines([Geom]) 
    FROM [roads] GROUP BY [Name];

A few, simple lines of SQL can easily replace hundreds of lines in scripting code that re-invent the wheel when you don't have SQL. SQL for ArcGIS Pro can run the query above, but ArcGIS Pro without the add-in cannot.

The example also shows how Pro being limited to read-only expressions eliminates the really powerful part of SQL, the ability to create new layers and to edit existing layers. For example, simple UPDATE queries, adding data with JOINs and other routine SQL queries are easy with the SQL for ArcGIS Pro add-in, but impossible in default Pro without the add-in.

Check the Documentation

Another way to tell if a GIS package has real SQL is to search for "SQL" in the user manual. If it doesn't have a chapter with many topics providing an SQL reference and full discussion of how to use SQL, it doesn't have real SQL.

Search for "SQL" in the SQL for ArcGIS Pro User Manual search box and you get almost 500 topics, including numerous, step-by-step SQL example topics. The main set of reference SQL topics appears in the SQL chapter in the user manual.

Search for "SQL" in the documentation for ArcGIS Pro and you find only a one page discussion of Arc's limited capability for SQL expressions, plus some links to using SQL in other packages, like Microsoft's SQL Server.

No Database? ...No SQL

ArcGIS Pro does not have real SQL because Pro has no internal DBMS at its core. That's a fairly typical architecture for desktop GIS packages. Almost all originally were designed as viewers of data stored in external data sources, so they don't have their own data storage engines inside the GIS. Because they don't have their own data storage engines, they don't have real SQL either.

Instead, they connect as dumb clients to external DBMS packages that do have SQL, like Oracle or PostgreSQL DBMS servers, or to file databases like Microsoft Access or SQLite. That means you only get SQL when connecting to those data sources, and you get zero SQL with hundreds of other formats or data sources.

ArcGIS Pro is a typical example of a viewer/client GIS that is not a database itself and has no native SQL: there's no such thing as importing data from a file format, like GeoTIFF, into ArcGIS Pro and then doing SQL on the raster data. Pro has to leave the data in the file format or data store to which it has connected. If that data store is a DBMS package with the SQL capabilities you want, you're in luck and you can use Pro as a client to launch queries in that DBMS. You're out of luck for real SQL if the data source, like a file geodatabase, a GeoTIFF, or a shapefile, is not a DBMS package that provides SQL.

Esri's ArcGIS Pro does provide some native "SQL-like" capabilities even when not connected to a real DBMS package like Oracle, MySQL, or SQL Server. But those capabilities are such an extremely limited, 5% subset of SQL, basically only using SQL syntax in simple expressions, that you don't get the power of real SQL. Surprisingly, that's true even when connected to Esri's own file geodatabases. If you want real SQL when connected to an Esri file geodatabase, the SQL for ARcGIS Pro add-in provides that missing capability.

What's In the Package?

SQL for ArcGIS Pro includes within it an immensely powerful, fully parallel spatial DBMS, complete with an extensive, fully-articulated SQL engine. Launch SQL for ArcGIS Pro and you always have the full power of SQL at your fingertips. In contrast, ArcGIS Pro without the add-in has no DBMS and no real SQL engine inside. Instead, Pro relies on being a client to other software that provides a genuine SQL implementation.

With the SQL for ArcGIS Pro add-in installed, you can connect to a shapefile and have full SQL. Without the add-in, if you add a shapefile layer you can use a few, read-only, limited SQL expressions in Pro, but you don't get real SQL. ArcGIS Pro doesn't even provide real SQL for file geodatabases, but SQL for ArcGIS Pro does.

That may come as a surprise to Esri users who have heard Esri describe real SQL capability within ArcGIS Pro. But all those descriptions of real SQL capability are carefully worded to apply only when Pro is connected to an Enterprise geodatabase on Oracle or some other Enterprise server, or otherwise working as a client to other software that provides real SQL. But that is using Oracle, not ArcGIS Pro, and that seriously limits what can be done with SQL in Pro.

For example, with default Pro, if you connect to Oracle, SQL Server, and PostgreSQL you need to know three different SQLs, because you'll be using three different SQLs provided by the three different database packages. Some SQL you can't do at all. You can't do SQL that combines tables from the three different databases, not even simple JOINs. You can't combine tables, for example, using data brought in from a CSV file or a shapefile, with a JOIN using tables in the enterprise databases.

In contrast, with SQL for ArcGIS Pro you always have full SQL for all data sources. SQL for ArcGIS Pro provides genuine, full-fledged SQL, comparable to Oracle, SQL Server, MySQL, and PostgreSQL/PostGIS. SQL for ArcGIS Pro exceeds even those fine SQL implementations in the hundreds of GIS-oriented spatial SQL functions that SQL for ArcGIS Pro provides.

Even better, you don't need to install, learn, configure, load, and administer a huge, enterprise DBMS server to get superb spatial SQL with SQL for ArcGIS Pro. You always have full-power SQL at your fingertips. Amazingly, the only way ArcGIS Pro users can use real SQL with file geodatabases is by installing SQL for ArcGIS Pro.

With SQL for ArcGIS Pro, it's all built-in with no need to buy, install, learn, or maintain anything extra. SQL for ArcGIS Pro is always there for you no matter what the data source, whether the data is local in your project or linked from an external data source. If you want to use the an external server's SQL, SQL for ArcGIS Pro lets you do that too. You can even mix external SQL and SQL for ArcGIS Pro's native SQL in the same query for a mix of server-side and client-side SQL capabilities.

SQL for ArcGIS Pro is only $145, fully paid. The world's most advanced spatial SQL is only $145. Amazing!

What do you need for a superb spatial SQL? There are ten points to consider:

Complete SQL Infrastructure

If you don't have all the JOINs you don't have an SQL: To have a real SQL you need a full roster of fully-implemented statements, clauses, aggregates, operators, and data types. SQL for ArcGIS Pro has all that, providing a very extensive and fully articulated SQL, including statements like ALTER, DROP, UPDATE, and many more, including of course SELECT (with all the aggregates and other clauses like COLLECT, DISTINCT, SPLIT, INTO), all the various JOINs (JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, LEFT OUTER JOIN, etc.), WHERE, GROUP BY, ORDER BY, UNION, EXCEPT, INTERSECT, and more. You not only get tremendously useful aggregates like COLLECT, you also get splitters (the reverse of aggregates) like SPLIT. SQL for ArcGIS Pro is fully read/write.

SQL for ArcGIS Pro provides an extensive choice of data types, including a variety of geometry and vector (tuple) types, and operators, including tuple operators, CASE, CASE WHEN, CAST, CASTV, and much more. There are very long listings in the various SQL topics in the SQL for ArcGIS Pro user manual.

SQL for ArcGIS Pro not only has very broad implementation of SQL, it is also an exceptionally deep and thorough implementation. For example, SQL for ArcGIS Pro fully supports Unicode with collations throughout SQL for ArcGIS Pro, something that not even IBM's highly-respected DB2 DBMS provides. There are endless details like spatial indices for vectors, rasters, and LiDAR point clouds, with indices automatically being surfaced in results tables, so if you generate a results table you can interactively edit through that results table, changing values in the cells to change the data within the database (either within the SQL for ArcGIS Pro internal database, or any external database you might use).

In contrast, ArcGIS Pro without the add-in has an extremely limited SELECT with WHERE and that's all. Pro only allows simple, read-only, expressions. There is no ability to edit through results tables, and no other write capabilities using SQL expressions.

Extensive Set of SQL Functions

You don't have a spatial SQL if you don't have an extensive roster of spatial SQL functions for various spatial tasks. A classic example of SQL functions playing a key role in spatial SQL are the spatial SQL functions provided for PostgreSQL by the PostGIS extension. Oracle and SQL Server both include spatial SQL functions as well.

SQL for ArcGIS Pro has hundreds of SQL functions that service almost any need involving attributes, vectors, rasters, or combinations of attributes, vectors and rasters. For spatial SQL, SQL for ArcGIS Pro has 140 geometry functions, with more added every month. SQL for ArcGIS Pro also provides a vast number of raster SQL functions.

In contrast, ArcGIS Pro without the add-in has no spatial SQL functions at all, not for vectors and not for rasters.

Considering elementary SQL functions for manipulating non-spatial attribute data like text and numbers, ArcGIS Pro without the add-in has very few SQL functions. For example, while SQL for ArcGIS Pro has 77 SQL functions to manipulate strings (including functions for full use of regular expressions), Esri has only six, very simple, SQL functions for strings.

SQL for ArcGIS Pro also provides many spatial SQL functions that combine attribute, vector and raster data. These service raster to vector operations such as creating contours, watersheds, and viewsheds, as well as vector to raster operations such as Kriging. There are also many utility functions that are essential for real world GIS automation in SQL, for example projection (coordinate system) services such as reprojecting vector or raster data, controlling the projection pipeline, manipulating projection definitions, registering raster or vector data using control points, and much more.

You can also define new SQL functions on the fly within a query in SQL for ArcGIS Pro, a great way to organize queries for clarity and maintainability by modularizing repeated tasks within functions.

The combination of having a fully articulated SQL with an extensive set of spatial SQL functions provides spectacular SQL functionality not available in ArcGIS Pro without the add-in.

Extensive Internal Automation

For an SQL implementation to be convenient in real life spatial tasks it has to do many housekeeping chores automatically and internally, like automatic conversion of coordinate systems. SQL for ArcGIS Pro automatically handles all coordinate systems, so you have greater freedom to mix geometries that use different coordinate systems in SQL expressions.

SQL for ArcGIS Pro handles pathologies so you don't have to first eliminate overlapping polygons, bowties or other issues. SQL for ArcGIS Pro automatically transforms whatever coordinate system you used into the closest SRID match when you upload into external DBMS packages or mix data from within SQL for ArcGIS Pro and an external DBMS in the same query. There are hundreds of small details like that which are automated in the SQL for ArcGIS Pro query engine.

Seamless Scripting

You can do scripting inline within an SQL for ArcGIS Pro query, and also launch SQL for ArcGIS Pro inline within a script. You can also call functions written in scripts, in any of the 10 languages SQL for ArcGIS Pro supports, for example, in any .NET language, VBScript, Javascript, and more. All that works with any data source, so you can write an SQL for ArcGIS Pro query that connects to tables in your Oracle database and which, on the fly, calls an inline script to do custom work within the query.

Seamless Access to Enterprise DBMS

SQL for ArcGIS Pro itself is a database with a full-power, native SQL engine so it's not restricted to just being a client. That makes it a much better partner for enterprise DBMS installations, either as enterprise geodatabases or via direct connections to database servers like Oracle, SQL Server, MySQL, PostgreSQL/PostGIS, and many others. You can connect to an Oracle database, or to multiple external DBMSs, and run native SQL for ArcGIS Pro queries that utilize tables in all of them, as well as data stored in the SQL for ArcGIS Pro project itself, or that effortlessly utilize data in file geodatabases in your ArcGIS Pro project.

Or, you can write queries using whatever is the native SQL for the external DBMS to which a data source is connected. Or, you can write queries that are a mix of both external SQLs and SQL for ArcGIS Pro SQL, all within the same query. That lets you write queries which can do something server-side in, say, Oracle, and then use that result within the query to do something in SQL for ArcGIS Pro that Oracle can't do, for example, a massively GPU parallel SQL expression.

ArcGIS Pro, in contrast, when connected as a client to an enterprise geodatabase can only use whatever SQL the data source provides. That forces you to learn and to use a different SQL syntax for each different DBMS package that is running the enterprise geodatabase you use. If you connect to three different data sources, like Oracle, SQL Server, and PostgreSQL, you have to learn three different SQLs. When working with Esri file geodatabases, shapefiles or similar, you are limited to the tiny slice of read-only expressions provided by ArcGIS Pro's 5% SQL implementation.

Add SQL for ArcGIS Pro, and you can connect to all those data sources at once and use the same full-power SQL from SQL for ArcGIS Pro for all of them. Write one query that works with exactly the same syntax on all the different data sources, within SQL for ArcGIS Pro or within any of the vast list of data sources and enterprise DBMS packages to which SQL for ArcGIS Pro can connect. Or, if you like, use native SQL within the various data sources either standalone or mixed in with SQL for ArcGIS Pro.

For example, you can connect to Oracle, SQL Server, and PostgreSQL servers and in a single query write overall workflow in SQL for ArcGIS Pro, but then call three different subqueries each written in Oracle SQL, SQL Server SQL, and PostgreSQL SQL to do something server-side in each of the three servers, all within the same query overall.

Orthogonality

Everything in SQL for ArcGIS Pro works the same way regardless of whether the data source is a file geodatabase in your ArcGIS Pro project, in SQL for ArcGIS Pro native storage, a data source like Oracle or SQL Server, a linked file like a shapefile, or an SQL expression used within an argument box in a tool.

That's hugely important because it allows consistent writing of queries that always work, and a "learn once, use many times" model of learning how to work SQL. With default ArcGIS Pro, in contrast, what you can do in SQL depends on whether the data source provides SQL, or where you are using the SQL expression.

SQL in the add-in also works everywhere an expression works, which is in virtually all dialogs that take parameters. For example, when Transform pane templates provide a drop down box to enter or to choose an argument, you can choose Expression and write an SQL expression.

Unlike default ArcGIS Pro (which also allows limited SQL expressions in some tools), expressions in the SQL for ArcGIS Pro add-in can use the full power of SQL. They are not limited to a tiny, 5% subset of SQL syntax, but can be arbitrarily massive and complex, including use of hundreds of SQL functions with full CPU and GPU parallelism, if you want.

Practical Extensions

Practical use of spatial SQL is helped a lot by practical extensions. For example, in SQL for ArcGIS Pro you can define new SQL functions on the fly within queries, which is a great way to write cleaner, more understandable, less error-prone, and more maintainable SQL. Functions you define can both take and return simple values like numbers or strings as well as tables. There are many other practical extensions, like the VALUE statement used to declare global values, which themselves can be expressions.

SQL for ArcGIS Pro users get the benefit of multiple, hardcore features of the SQL engine that are specifically designed for spatial SQL with emphasis on performance and control: constraints, computed fields, spatial and non-spatial indexes, temporary tables and temporary databases, precisely controlled multi-threaded execution, offloading of advanced raster operations to GPGPU, read-only and transparent read-write caches, and much more.

Automatic CPU and GPU Parallelism

A superb spatial SQL is an automatically, fully CPU parallel and GPU parallel SQL. SQL for ArcGIS Pro's seamless CPU and GPU parallelism is so advanced it is not available even in mainstream enterprise DBMS packages, which have not yet achieved GPU parallelism like in SQL for ArcGIS Pro.

CPU and GPU parallel SQL is built into SQL for ArcGIS Pro - no need to buy or to install anything extra.

SQL for ArcGIS Pro parallelizes query statements to use all CPU threads available: if you have 16 cores the query runs in 32 threads. SQL for ArcGIS Pro also GPU parallelizes queries where that makes sense. SQL for ArcGIS Pro can execute on thousands of GPU cores in parallel for astonishing speed. In addition, most spatial functions include parallel versions with even better optimizations specific to the function.

Without SQL for ArcGIS Pro, doing sophisticated analysis in Pro requires Esri's Spatial Analyst option, an expensive extra. Spatial Analyst has a limited number of geoprocessing tools that are CPU parallelized, but ArcGIS Pro itself is not at all parallel, which leads to many bottlenecks. Even within Spatial Analyst, tools are only partially parallelized and usually run fewer than eight to ten threads even when the CPU can handle 32 or more threads. Between less advanced Spatial Analyst parallelization and a non-parallel ArcGIS Pro main application, the result usually is much slower performance than using SQL for ArcGIS Pro's direct connection to file geodatabases and other data sources.

You get all that parallelization automatically in SQL for ArcGIS Pro: Write ordinary SQL and the SQL for ArcGIS Pro query engine will optimize it and will parallelize it for execution, automatically utilizing CPU and GPU parallelism. If you have no GPU, SQL for ArcGIS Pro will optimize for CPU parallelism. If you have a GPU, SQL for ArcGIS Pro will optimize for a mix of CPU and GPU parallelism, on the fly adjusting for the best balance between the number and power of CPU and GPU cores that are available. If hardware is reconfigured, the same query automatically will be reoptimized during execution to take best advantage of the resources you have, to run at top performance.

GPU acceleration works everywhere in SQL for ArcGIS Pro where worthwhile work arises: in the SELECT list, in WHERE, in EXECUTE, ...everywhere. For example, if you add to a table a computed field that combines multiple tiles together, that computed field will use GPGPU. If you do some tile math in a FUNCTION, that FUNCTION will use GPGPU as well.

Without the add-in, ArcGIS Pro has no GPU parallelization at all for expressions, with only a handful of modules out of hundreds in Spatial Analyst doing GPU computation in a limited way. In contrast, SQL for ArcGIS Pro uses unlimited GPU parallelization everywhere, utilizing multiple GPUs even from different GPU generations, in hundreds of settings, with automatic GPU parallelization in SQL. GPU parallelization often runs 50 to 200 times faster than not having GPU parallelization: see the Parallel GPU page for examples and discussion on how massively parallel GPU computation in SQL for ArcGIS Pro works.

Extensive UI Support

A truly first-rate SQL doesn't force users to leverage SQL through the keyhole of an exclusively text interface: it also provides the power of SQL with numerous user interface (UI) enhancements. SQL for ArcGIS Pro provides an extensive query builder with all statements and functions that makes it much easier to build SQL queries.

SQL is also totally integrated with SQL for ArcGIS Pro point and click user interface tools, like the Transform pane, the Select pane, the Join dialog, and similar. In panes and dialogs you can click the Edit Query button and SQL for ArcGIS Pro will write a well-organized SQL query for you that accomplishes what has been set up in the dialog. That's a great way to learn SQL, a great way to learn how professionals write better queries by modularizing and organizing them, and also a great way to build queries for customization and repeated use.

Spatial Optimization

ArcGIS Pro is not a database: Pro itself is remarkably separate from, say, Esri's file geodatabase. Various other Esri tools and modules are also separate worlds. The resulting architecture is full of bottlenecks with no global optimization. SQL for ArcGIS Pro, in contrast, itself is a database that is very tightly integrated, with the entire architecture designed from bottom to top for parallelized spatial data work.

SQL for ArcGIS Pro's unified archiecture supports global optimizations that help eliminate bottlenecks, for better performance, expecially with complex queries operating on larger data. For example, when working on file geodatabases in the ArcGIS Pro project, the add-in's own internal database provides ultra-fast storage for cache and other performance enhancements.

SQL for ArcGIS Pro is also spatially optimized. Classic DBMS packages like Oracle originally were designed mainly for very many small transactions, typically with only a few thousand bytes per record. That's a big difference from transactions in spatial work, where a typical operation usually involves very "fat" transactions. For example, an intersection between two complex objects like a big polygon and a very complex polyline (say, a contour in a large, complex terrain) could involve two records where each record has over a gigabyte of data in it.

SQL for ArcGIS Pro's internal structure as a database was designed to handle such "fat" spatial transactions, and the SQL for ArcGIS Pro spatial SQL engine was designed to do highly complex global query optimizations on those fat transactions on the fly. Special internal structures allow SQL for ArcGIS Pro to achieve astonishingly fast performance for spatial work, often dramatically exceeding the speed of enterprise class DBMS packages for tasks like rendering large, complex vector layers.

For that reason, when you do big, complex SQL queries in SQL for ArcGIS Pro they often will run much faster than what can be hand coded using C++ or Python.

How Fast Is SQL for ArcGIS Pro Parallel GPU?

If you are doing intensive computations it's fast. Really fast. Even when using very inexpensive GPU cards, gains are usually from 20 times faster to 100 times faster than running on CPU alone. Running complex computations on faster cards, performance can be 100 to 200 times faster than using CPU alone. It's fairly common to do in a few seconds what takes many minutes without SQL for ArcGIS Pro.

If your time is worth more than minimum wage and you're doing anything that requires your machine to think at a higher level than your coffee pot timer, you'll often pay back the cost of a SQL for ArcGIS Pro license the first time you use it for anything serious. It's that fast.

Links

Comments on ArcGIS Pro are based on Esri's ArcGIS Pro SQL Reference, which is recommended as a guide to SQL in ArcGIS Pro in Esri's Write a query in the query builder page. Suggestions for improvements and any corrections to this page by ArcGIS Pro users are welcome and encouraged.

---

SQL for ArcGIS^® Pro has been Discontinued

SQL for ArcGIS^® Pro has been discontinued in favor of using Manifold System Release 9. The two products are almost identical so instead of providing a separate SQL for ArcGIS Pro product, Manifold is offering all SQL for ArcGIS Pro licensees a free upgrade to Manifold Release 9 Universal Edition, a $195 value at no additional charge.

---