Service

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

Contact

• sales@manifold.net
• Suggestions
• Contact Info

Real SQL for ArcGIS Pro

If you rely on the phenomenal power and convenience of SQL in databases, you want that same power and convenience in your desktop GIS. SQL for ArcGIS Pro is a new add-in for Esri's ArcGIS^® Pro desktop GIS. The add-in provides spatial SQL for Esri file geodatabases in your ArcGIS Pro project in tight integration with ArcGIS Pro.

Without the add-in, ArcGIS Pro as shipped by Esri does not provide real SQL with file geodatabases. Pro has an extremely limited SELECT with WHERE, has no JOIN, or UPDATE, or CREATE, or any of the dozens of other key SQL statements and clauses on which real SQL depends.

The add-in enhances ArcGIS Pro with the world's most powerful desktop spatial SQL engine, Manifold's Radian^® engine, to unleash the phenomenal power of SQL from your ArcGIS Pro desktop. Enjoy full-featured SQL, superior quality, and the amazing speed of parallel CPU and parallel GPU query execution, all supported with hundreds of SQL functions for every spatial need.

In Advanced mode the add-in runs spatial SQL with hundreds of file formats, databases, web servers, and other data sources outside of ArcGIS Pro. Ultra fast scratchpad storage in the add-in makes it easy to combine huge data from outside your ArcGIS Pro project with data inside the project.

Special introductory offer: Save $150 off the regular price of $395 - Only $245.

Complete SQL Infrastructure

If you don't have all the JOINs you don't have real 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 many more. You not only get tremendously useful aggregates like COLLECT, you also get splitters (the reverse of aggregates) like SPLIT.

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, 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 originating tables, for example, within the file geodatabase layer used in ArcGIS Pro, or within the add-in's own scratchpad data store, or within any external database you might use.

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 136 geometry functions and counting. SQL for ArcGIS Pro also provides a vast number of raster SQL functions.

SQL for ArcGIS Pro also has a vast range of functions for manipulating ordinary attribute data, such as numbers, dates, and text. For example, SQL for ArcGIS Pro has 77 SQL functions to manipulate strings (including functions for full use of regular expressions).

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 building 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, as illustrated as these SQL examples:

• Re-create Lines from a CSV Table of Points
• Extract Airport Runways from an OpenStreetMap PBF
• Unions using Topology Overlays
• Process RGB Images using Matrix Filters

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 SQL for ArcGIS Pro's query engine.

Seamless Scripting

You can do scripting inline within an SQL query, and also launch SQL 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 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. You can connect to an Oracle database, to SQL Server, or to multiple other external DBMSs, including multiple different Esri enterprise geodatabases, and run native SQL queries that utilize tables in all of them, as well as data stored in file geodatabases used by the ArcGIS Pro project or data stored in the add-in's own hyperfast parallel data store.

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 local 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 without the add-in, in contrast, 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 very small subset of SQL capabilities provided by ArcGIS Pro's partial SQL implementation.

With SQL for ArcGIS Pro, 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 file geodatabases used in the ArcGIS Pro project, or within any of the vast list of data sources and enterprise DBMS packages to which SQL for ArcGIS Pro can connect in Advanced mode. Or, if you like, use native SQL within the various data sources either standalone or mixed in with SQL.

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's native SQL, 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.

Orthogonality

Everything in SQL works the same way regardless of whether the data source is a file geodatabase used in the ArcGIS Pro project, some other data source or file format accessed in Advanced mode by the add-in like Oracle or SQL Server, or a linked file. It even works the same when using a snippet of SQL within an argument box in one of the add-ins tools provided by Advanced mode.

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. Without the add-in, in contrast, what you can do in SQL depends on what the data source is or where you are using the SQL expression. If the data source doesn't provide real SQL, ArcGIS Pro without the add-in doesn't have it.

SQL in SQL for ArcGIS Pro also works everywhere in the add-in an expression works, which is in virtually all Advanced mode dialogs that take parameters. For example, when point and click Transform pane templates provide a drop down box to enter or to choose an argument, you can choose Expression and write an SQL expression. Those expressions are not limited in SQL for ArcGIS Pro but can be arbitrarily massive and complex 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. Using VALUE to declare global values is a great way to dramatically simplify queries to make them easily understood and easy to debug.

Automatic CPU and GPU Parallelism

SQL for ArcGIS Pro provides automatically, fully CPU 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, none of which have achieved GPU parallelism in SQL like SQL for ArcGIS Pro.

CPU parallel 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 all 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.

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.

SQL for ArcGIS Pro uses unlimited GPU parallelization everywhere, automatically utilizing multiple GPUs even from different GPU generations, in hundreds of settings. 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 available in Advanced mode, like the Transform pane and 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 and also a great way to build queries for customization and repeated use.

Spatial Optimization

The ArcGIS Pro desktop GIS is not a database and isn't built on an internal DBMS foundation: Pro itself is remarkably separate from Esri's file geodatabase data storage technology. 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 with very tight integration between the add-in's own internal, hyperfast, parallel data storage, the parallel query engine, and parallel data processing, with the entire architecture designed from bottom to top for parallelized spatial data work.

When launched as an add-in from ArcGIS Pro, SQL for ArcGIS Pro launches separate processes to enable direct connections to the file geodatabases used within the ArcGIS Pro project without any delays from lack of database infrastructure within Pro. SQL for ArcGIS Pro's unified archiecture supports global optimizations that help eliminate bottlenecks, for better performance, especially with complex queries operating on larger data.

SQL for ArcGIS Pro as a data engine 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, while spatial work usually involves very "fat" transactions, where, for example, an intersection between two complex objects like a big polygon and a very complex polyline (such as a contour line 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 engine was designed to do highly complex global query optimizations on those 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 geoprocessing large, complex vector layers.

How Fast Is SQL for ArcGIS Pro Parallel GPU?

If you are doing computations it's fast. Really fast. Gains are usually from 20 times faster to 100 times faster than running typical computations on CPU alone, even when using very inexpensive GPU cards. 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 second or two what takes more than five minutes in ArcGIS Pro without the SQL for ArcGIS Pro add-in.

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 an 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.

 

A Sample List

Tired of "spatial" SQL products that provide a handful of functions? That's not SQL for ArcGIS Pro. SQL for ArcGIS Pro has you covered for every possible need with a vast selection of SQL statements, operators and functions. Manifold's spatial experts with decades of experience in spatial engineering have provided everything that everyone from a novice to maximum expert will want, all neatly organized within the query builder with easy filtration to find just the right function for a task. Following is a sample list of some, but not all, of what you get in SQL with SQL for ArcGIS Pro.