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%' ;
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
it looks pretty much the same. Look closely though, there are
two (2) subtle differences. First, just below the map now
EPSG:3857 instead of
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
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
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
is straight-forward, we like to be lazy whenever possible!
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.
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!
Published June 13, 2019
Last Updated June 13, 2019