Radian Engine and Multiple Databases

In my previous posts, I have showed the speed of the Radian engine.  Today, I want to introduce you to two other cool things: running multiple SQL statements, and running SQL statements over multiple vendor databases.

Multiple Statements

If you come from an SQL world, you may not be too impressed with the ability to issue multiple statements.  That is something that most SQL databases can do.  But, Manifold 8 could not do that, and it was disappointing.  Typically, you had to create SQL Query components or do some kind of embedding of nested SQL statement.  But, with the new Radian engine, I am able to run the following query:

-- $manifold$
DROP TABLE NewTable;

SELECT GeomBuffer([Geom (I)],10,1) AS g, ID 
INTO NewTable
FROM [L Table];

SELECT [P Table].[ID] 
FROM [P Table], NewTable
WHERE GeomTouches([P Table].[Geom (I)],NewTable.g,1)

In this query, we are dropping an existing table called NewTable, and then we are creating a buffer of the geometries from [L Table].  Finally, we are running a SELECT query where the [P Table] geometries touch the geometry in NewTable.

I apologize that this isn’t a more interesting example, but I was rushed.  For those who are familiar with Manifold 8, this is a real improvement, and will make writing more complex queries even easier.  And don’t forget, you can always add the THREADS directive to make use of more threads.

Multiple Databases

One thing I really like about Manifold 8 is that we can link multiple databases, and query them.  But, when we issue a query in a query component, we are limited to Manifold’s version of SQL.  What if, instead, we wanted to run something in, say, PostGIS because we liked it’s implementation for a certain function better than Manifold’s implementation.  That’s now easy to do, using the EXECUTE directive.

You probably noticed the –$manifold$ directive above. That tells Manifold to run things using the Radian engine.  But, if you leave that directive out, you can then run in the native database language like this:

SELECT * FROM
 (EXECUTE [[ SELECT ST_Buffer(geometry,10) AS g FROM parcels ]] ON [PG])

notice, no –$manifold$ directive. And, also notice the ST_Buffer function – that’s PostGIS!  So, we are running PostGIS directly inside of Manifold.  In the above example, I created a datasource called [PG] which was a link to a PostGRES database – you can call it whatever you want.

But wait, I’m not done yet….

What if there are some things that PostGIS does better than Manifold (or perhaps Manifold doesn’t do it at all), but there are some things that Manifold does better than PostGIS, so I might want to use that.  Can you mix them?  In Radian, the answer is yes.  To do that, we’ll put the $manifold$ directive back in place:

--$manifold$
SELECT sum(ASMT), propclass FROM
 (EXECUTE [[ SELECT propclass, "ASMT" FROM parcels ]] 
  ON [Data Source])GROUP BY propclass

In this example, we are selecting the propclass and the ASMT value from the parcels – this is done from PostGRES! (I’ve highlighted the text in orange for that).  But, in the same SQL query, the outer part of the query (outside of the EXECUTE statement) is summing the data and performing a GROUP BY using Manifold.  This means, part of this query is running PostGRES, while another part is running Radian on the resultant data from PostGRES – wow!

I see this as a really useful tool when there are perhaps huge amounts of data in a backend database and you use the native database language to pull out a subset of the data that is then used inside of Manifold to run a more computationally expensive query on the resultant data.

(note: you’ll notice that the last query I showed does not use a PostGIS function.  Currently, when I pass a geometry back from a PostGIS function (i.e. st_buffer), I can’t get Manifold to understand it –  that is the “fun” part of beta testing: there is either a problem with the software, or with the user!  As I discover more, I will update the code in this post).