Geometry viewer added to DBeaver

PostGIS users have a new option for viewing spatial data! DBeaver has joined the spatial database club with its geometry viewer, available since version 6.0.3. I previously wrote about PgAdmin4's geometry viewer, now we have two general purpose database GUI options supporting PostGIS spatial data. This post gives an overview of DBeaver's implementation and my initial thoughts.

This post was written using DBeaver v6.1.0, PostgreSQL 11.3, and PostGIS 2.5.

What it looks like

DBeaver's geometry viewer allows you to easily select and display a single row, multiple rows, or all rows with relative ease. This is handled by you selecting one or more rows of spatial data. Clicking the spatial column's header displays all returned rows.

This query was used in the PgAdmin4 post, it finds trees within 25 meters of Pangea Coffee Roasters in Golden, CO.

SELECT n.osm_id, ST_Transform(n.way, 4326) AS way_tree
    FROM osm.building_polygon b
    INNER JOIN osm.natural_point n ON n.code = '4121' -- Trees
        AND ST_DWithin(b.way, n.way, 25) -- Source SRID = 900913 so units is Meters
    WHERE b.name ILIKE '%pangea%' 
;

Not only SRID 4326

The query above uses ST_Transform(n.way, 4326) to convert the spatial data to SRID 4326, or WGS-84. I started with that transformation because PgAdmin's geometry viewer requires it in that projection to render over OpenStreetMap data.

This is not a requirement in DBeaver's implementation! Check it out, if we transform to SRID 3857 (change to ST_Transform(n.way, 3857), it looks pretty much the same. Look closely though, there are two (2) subtle differences. First, just below the map now reads EPSG:3857 instead of EPSG:4326. This is a good indicator to keep your eye on. Second, the data in the way_tree column of the grid now has numeric values like -11713414.94 instead of -105.22339669.

What about 900913

Another common SRID used is 900913 and happens to be the format our OpenStreetMap data is stored in, as the comments in the above queries indicate. This is the format osm2pgsql uses by default and I haven't had any reason to change it.

What happens if we try to visualize some spatial data in SRID 900913 without using ST_Transform? DBeaver detects it (see EPSG:900913 below the map area), but apparently it doesn't know how to handle the data properly and instead displays as a line.

SELECT * FROM osm.boundary_polygon;

Don't worry though, there's an easy workaround to this! To the right of the EPSG:900913 there's a little click-able down-arrow. Click that down arrow, select EPSG:3857, and we are back on the map!

This is handy because while the syntax for ST_Transform is straight-forward, we like to be lazy whenever possible! In my post on PgAdmin4's geometry viewer I explain why a lightweight, speedy, efficient GUI option was important to PostGIS administrators.

Another benefit by not requiring ST_Transform is that function requires CPU on the server. By not having to transform the data it should be little more CPU friendly for your PostGIS / PostgreSQL servers.

Getting all the data

The above screenshot showed only some of the data for the southern area of Colorado being returned. This is because DBeaver limits the number of rows returned by default. You can fetch all easily with the control at the bottom of the window.

You should get the warning that you might need to go make some coffee.

Overall impression

DBeaver has done a stellar job with their geometry viewer implementation! Since I first installed the initial version having this feature (6.0.3) I have largely stopped using PgAdmin in favor of DBeaver. DBeaver is currently my go-to choice for quickly exploring, visualizing and administering our PostGIS data.

Of course I still use psql significantly more... 😏

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!