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$

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:

 (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:

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

One thought on “Radian Engine and Multiple Databases

  1. This is pretty cool stuff. I noticed that in your previous examples you were testing Radian’s ability to use the CPU cores. Can you also command it to use the GPU?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s