Real SQL vs. Arc SQL
This page summarizes reports by Manifold users of why they consider SQL in Release 9 to be a superb spatial SQL implementation, and why SQL in ArcGIS Pro falls short in comparison.
Comments on SQL in 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 by ArcGIS Pro users are welcome and encouraged.
What's In the Package?
Don't be led astray by comparisons that take credit for capabilities in third party software. Advocates of ESRI packages may cite the marvels of SQL when their product connects as a client to Oracle or SQL Server. But that is using Oracle's SQL, not ESRI's SQL. Run ArcGIS Pro without using Oracle or SQL Server as a data source and what do you have for SQL? - A very small subset of SQL with woefully insufficient capabilities.
In contrast, Manifold provides genuine, full-fledged SQL, comparable to Oracle, SQL Server and PostgreSQL/PostGIS as an SQL and exceeding even those fine SQL implementations in the hundreds of GIS-oriented spatial SQL functions Manifold provides. You don't need to install and learn a huge, enterprise DBMS to get superb spatial SQL in Manifold. With Manifold, it's all built-in with no need to buy, install, learn, or maintain anything extra.
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. Manifold 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.
Manifold 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 Manifold user manual.
Manifold not only has very broad implementation of SQL, it is also an exceptionally deep and thorough implementation. For example, Manifold fully supports Unicode with collations throughout Manifold 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 the database (either within Manifold's internal database, or any external database you might use).
Arc, in contrast, has an extremely limited SELECT with WHERE and that's about it.
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.
Manifold has hundreds of SQL functions that service almost any need involving attributes, vectors, rasters, or combinations of attributes, vectors and rasters. For spatial SQL, Manifold has 136 geometry functions and counting. Manifold also provides a vast number of raster SQL functions.
In contrast, ArcGIS Pro appears to have no spatial SQL functions at all, not for vectors and not for rasters.
Considering only elementary SQL functions for manipulating non-spatial attribute data like text and numbers, ArcGIS Pro has very few SQL functions for routine tasks. For example, while Manifold 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.
Manifold 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 Manifold, 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, as illustrated as these SQL examples:
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. Manifold SQL automatically handles all coordinate systems, so you have greater freedom to mix geometries that use different coordinate systems in SQL expressions.
Manifold handles pathologies so you don't have to first eliminate overlapping polygons, bowties or other issues. Manifold automatically transforms whatever coordinate system you used into the closest SRID match when you upload into external DBMS packages or mix data from within Manifold and an external DBMS in the same query. There are hundreds of small details like that which are automated in Manifold's SQL engine.
Seamless Access to Enterprise DBMS
Manifold 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, or to multiple external DBMSs, and run native Manifold SQL queries that utilize tables in all of them, as well as data stored in the Manifold project itself.
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 Manifold 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 Manifold SQL that Oracle can't do, for example, a massively GPU parallel SQL expression.
ArcGIS Pro, 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 small subset of SQL capabilities provided by ArcGIS Pro's partial SQL implementation.
With Manifold, you can connect to all those data sources at once and use the same full-power SQL from Manifold for all of them. Write one query that works with exactly the same syntax on all the different data sources, within Manifold or within any of the vast list of data sources and enterprise DBMS packages to which Manifold can connect. Or, if you like, use native SQL within the various data sources either standalone or mixed in with Manifold SQL.
For example, you can connect to Oracle, SQL Server, and PostgreSQL servers and in a single query write overall workflow in Manifold'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 overall.
Everything in Manifold SQL works the same way regardless of whether the data source is the Manifold database (the project), a data source like Oracle or SQL Server, a linked file, 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 ESRI, in contrast, what you can do in SQL depends on what the data source is or where you are using the SQL expression.
Manifold SQL 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 ArcGIS Pro (which also allows limited SQL expressions in some tools), those expressions are not limited in Manifold but can be arbitrarily massive and complex if you want.
Practical use of spatial SQL is helped a lot by practical extensions. For example, in Manifold 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.
Automatic CPU and GPU Parallelism
A superb spatial SQL is an automatically, fully CPU and GPU parallel SQL. Manifold'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 in SQL like Manifold.
CPU and GPU parallel SQL is built into Manifold - no need to buy or to install anything extra.
Manifold SQL parallelizes all query statements to use all CPU threads available: if you have 16 cores the query runs in 32 threads. Manifold also GPU parallelizes queries where that makes sense. Manifold SQL 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.
ArcGIS Pro's Spatial Analyst extension, an expensive option, has a limited number of geoprocessing tools that are CPU parallelized, but Arc's SQL is not at all parallel. For that matter, even Spatial Analyst's parallelization of geoprocessing tools is woefully partial, and is also hobbled by the bottleneck of a non-parallel ArcGIS Pro main application. See the Faster than ESRI collection of videos to watch how full Manifold parallelization absolutely crushes partial ESRI parallelization.
You get all that parallelization automatically in Manifold SQL: Write ordinary SQL and the Manifold query engine will optimize it and will parallelize it for execution, automatically utilizing CPU and GPU parallelism. If you have no GPU, Manifold will optimize for CPU parallelism. If you have a GPU, Manifold 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 Manifold SQL 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.
ArcGIS Pro SQL has no GPU parallelization at all. Manifold 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 Manifold SQL 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. Manifold 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 Manifold point and click user interface tools, like the Transform pane and Select pane, the Join dialog, and similar. In panes and dialogs you can click the Edit Query button and Manifold 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.
ArcGIS Pro is not a database, with Arc itself 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. Manifold, in contrast is very tightly integrated, with the entire architecture designed from bottom to top for parallelized spatial data work. Manifold's unified archiecture supports global optimizations that help eliminate bottlenecks, for better performance, expecially with complex queries operating on larger data.
Manifold is also spatially optimized. Classic DBMS packages like Oracle originally were designed mainly for lots of small transactions, typically with only a few thousand bytes per record, while spatial work usually involves very "fat" transactions, where, say, 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.
Manifold's internal structure as a database was designed to handle such "fat" spatial transactions, and the Manifold SQL engine was designed to do highly complex global query optimizations on those on the fly. Special internal structures allow Manifold 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 Manifold they often will run faster than what can be hand coded using C++ or Python.
Bonus: Free Viewer with Full Spatial SQL
Manifold users can publish sophisticated projects that leverage the power of full, spatial SQL, complete with parallel CPU and parallel GPU speed that anybody can exploit for free, using the free Manifold Viewer. Viewer provides full Manifold SQL and full CPU and GPU parallelism. Viewer is read-only and does not support scripting, but publishing Manifold projects for use with free Viewer installations is a fantastic way to provide astonishing performance to anyone for free.
- ADD PROPERTY
- DROP PROPERTY
- ALTER TABLE
- ADD CONSTRAINT
- ADD INDEX
- DROP CONSTRAINT
- DROP INDEX
- RENAME CONSTRAINT
- RENAME INDEX
- CREATE DATASOURCE
- CREATE ROOT
- CREATE TABLE
- DELETE FROM
- DROP ROOT
- DROP TABLE
- EXECUTE WITH
- INSERT INTO
- PRAGMA PUSH
- PRAGMA POP
- RENAME TABLE
- ORDER BY
- GROUP BY
- UNION EX /INTER
- USE CHILD
- USE PARENT
- IS NULL
- CASE WHEN
How Fast Is Manifold 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 without Manifold.
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 Manifold license the first time you use it for anything serious. It's that fast.
Manifold Viewer is the free, read-only version of Manifold Release 9. Although Viewer cannot write projects or save edited data back out to the original data sources, Viewer provides phenomenal capability to view and to analyze almost all possible different types of data in tables, vector geometry, raster data, drawings, maps and images from thousands of different sources. Viewer can also create and write spatial indices for entire folders full of LiDAR files, save connections to favorite files and data sources, and even edit and save localization files to translate Manifold into new languages.
Manifold Viewer delivers a truly useful, fully CPU-parallel and GPU-parallel Manifold tool that gives you parallel power and endless Manifold capabilities at zero cost. No need to register, no adware, no requests for donations and no selling: use Viewer however you like, including commercial purposes. You can even redistribute Viewer, all for free.
Viewer is a great way to share the amazing projects you create in Manifold for free. Publish multi-hundred GB projects created in Manifold that anybody can pop open in 1/10th second for free using Viewer. Publish projects that include automatic connections to your organization's databases and worldwide webserved data, and include pre-built, sophisticated analytics and dynamic reporting that users can launch with a point and click. Track pandemics, help first responders fight wildfires, or just help your organization do a better job.
Viewer's small download, instant launch, and clean display - free of convoluted ribbons and endless, confusing buttons - make it perfect for unskilled users. With Viewer a million people can share the dazzling analytics and insights you create with a single $95 Manifold license, all at fully parallel speed with no need to pay for costly, cloud-based, web processing. Like Manifold, Viewer never crashes, no matter how big or complicated the job.See Viewer in action Watch the Manifold Viewer Introduction YouTube video.
"Mfd 9 is becoming a really good tool for sharing data with non-GIS folks. Clean interface makes it easy to teach others simple tasks. Easy to transfer a project. Easy to install software. Free viewer." - Forum post
Buy Now via the Online Store
Buy Manifold products on the Online Store. The store is open 24 hours / seven days a week / every day of the year. Orders are processed immediately with serial number email sent out in seconds. Use Manifold products today!
A Sample List
Tired of "spatial" SQL products that provide a handful of functions? That's not Manifold. Manifold 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 Manifold.
Manifold products deliver quality, performance and value in the world's most sophisticated, most modern and most powerful spatial products for GIS, ETL, DBMS, and Data Science. Total integration ensures ease of use, amazing speed, and unbeatably low cost of ownership, for only $95. Tell your friends!