Spatially Extending Database Management and Development Tools: GeoRaptor, DBeaver, pgAdmin and SSMS

“Imitation is the Greatest Form of Flattery”

Before I talk about SSMS, pgAdmin and DBeaver spatial visualisation solutions, some history about the first spatial tool that extended a (non-GIS) database management and development tool: GeoRaptor.

GeoRaptor History

Many years ago a visionary programmer added spatial capabilities to SQL Developer 0.9, calling his extension GeoRaptor (the code name for the beta version of SQL Developer was called Raptor).

His name was Matic Petek.

Why “was”?

Well, within a few months of the release of SQL Developer 1.0, he disappeared.

No one could support or extend it as we could not find him, or access the source code.

It took a while but some colleagues managed to get access to the source code (SourceForge) around the time SQL Developer 2.0 was released.

This was needed because changes in the Extension API broke the operation of GeoRaptor.

A small team then took over GeoRaptor’s development, but over time this slowly reduced (eg companies moving from Oracle to some other platform).

This time, the GeoRator development team had dropped to one: me. While I could handle creating new functionality, debugging, extending and improving existing code.

GeoRaptor’s functionality and popularity continued to grow with its best release being for SQL Developer 3.2.

Why GeoRaptor

Let’s get something straight: GeoRaptor was never a GIS.

It was an extension that allowed a SQL developer to work with the sdo_geometry data type.

Without GeoRaptor a SQL Developer user would find working the sdo_geometry object type (just as SQL development tools allow a user to visualise and create TIMESTAMPs etc), or any other object type a programmer might create within the Oracle database.

Success

GeoRaptor was very popular as was witnessed by the number of downloads.

Whether GeoRaptor’s success drove the creation of Oracle’s own Spatial extension or not, is hard to know. It is also impossible to say which was more popular as Oracle’s extension was built in. Anecdotally some GeoRaptor users reported that they preferred GeoRaptor, as did I, but perhaps that was confirmation bias?

Demise

GeoRaptor’s demise was not to do with its user base. It remained popular up until its end.

Its demise came about when, at SQL Developer 4.0 (now 18/19), Oracle changed its extension API again (the third in the life of GeoRaptor).

I found that I did not have the skills to work out how to port GeoRaptor to the new Extension API and there was no one else left in the team who could do so.

I tried a few times, but I found there was not the information or support to make the port work. This was confirmed by an Italian developer who offered to do the port. Initially some progress was made on the basic integration. But when it came to implement the functions for the data grid everything ground to a halt. In both cases, no information could be found to make it work.

Last year (2019) I tried for the last time.

I managed to get a final year computer science student project accepted at my local university. I hope that fresh, keen, faces could collectively succeed where all others had failed. The team made quite a bit of headway (a big thanks to Philipp Salvisberg of the utPLSQL project for his invaluable help), getting quite of a bit of GeoRaptor to work, but in the end they could not create a hook against a SQL data grid which is fundamental to the effectiveness of GeoRaptor (see image below from GeoRaptor 3.2).

Since GeoRaptor we are now seeing some spatial visualisation extensions added to:

• SQL Server Management Studio
• pgAdmin 4
• DBeaver

Here are a few comments on them (I may write longer articles on each one later on):

SQL Server Management Studio

This has been around for quite a while (something around, or later than, 2008 which was the year the spatial data types were first released).
I find it great to use for visualizing simple queries.
It cannot display very large numbers of rows (a few thousand).
It does allow for spatial objects to be visualized but I have to STBuffer() point geometries to be able to visualize and label them.
There is no ability to style a displayed geometry (everything is black).
For geographic data a few projects are offered for visualization which is neat.
I miss the ability to visualize data from a table/view and from more than one source at a time: there is no persisting of any one query.
If you wish to display data from more than one source, one has to construct UNION ALL queries.

pgAdmin 4

While there were some very limited attempts to provide visualization in pgAdmin II, it was not will pgAdmin 4 was this made available.
The spatial extension suffers from all the styling and persisting limitations of SSMS-Spatial, at least it exists (and I use it regularly).
The spatial extension does, for geographic/geodetic data use OpenStreetMap as background which is useful for context.
One hassle is that extension seems to use Google Maps type scale thresholds, as such one cannot zoom in as far as one might want especially for non-geodetic data.

DBeaver

I only recently came across DBeaver and its spatial visualization extension.
One particularly nice thing is that if visualizes any geometry in a SELECT rowset within the same grid as the attribute data as WKT.
However, there is no Tools>Option extension that would allow me to change from WKT to EWKT.
Like pgAdmin 4, the spatial map background is OpenStreetMap which is nice with again the scale threshold problem.
DBeaver spatial has no ability to persist queries or visualize complete tables, views or SQL Select result sets such that they share the same view: have to use SELECT UNION ALL.
Again, DBeaver spatial has no ability to style geometry objects, While ever visualisation is specific to a single query, this is not a big issue.

Conclusion

I hope the non-GeoRaptor extensions continue to develop, adding in new and exciting functionality.

While I am biased towards GeoRaptor, I have used (and use almost every day) all of the above alternate implementations and am happy they are there, and am happy to recommend them to any person whose principle tool is SSMS, pgAdmin 4 or DBeaver.