Spatial data, often represented as either geometric or geographical values, can be visualized on a map or graph. A
geometric object is typically composed of a sequence of points that define its shape. For a more comprehensive
understanding of spatial data, you can refer to this detailed explanation.
DBeaver’s support of spatial data covers the following databases:
- PostgreSQL (PostGIS)
- Greenplum
- Redshift
- CockroachDB
- MySQL
- MariaDB
- SQLite (GeoPackage)
- H2GIS
- SAP HANA
- DuckDB
- Exasol
- Altibase
- Oracle
- SQL Server
- BigQuery
- Snowflake
- AlloyDB
Each of these databases has its own set of features and capabilities for handling spatial data, and DBeaver is equipped
to work effectively with each of them.
Spatial data viewer
The Spatial data viewer in DBeaver is a tool designed to visualise and interact with spatial data. It provides a
graphical representation of your spatial data, enhancing your ability to understand and analyze it.
Differentiating data on the map
Every table column has its own color on the map. This helps you find the information you need on the map if you know
which column it belongs to.
If you click on an object on the map the following information will be displayed:
- Name of the column in the header
- Displayable data (strings, numbers, dates etc.) from every other column in the corresponding row
Tile layer management
DBeaver has several predefined map tiles. The tiles can be chosen with the combo below the viewer:
You can select which tile layers you want to see using the layers button. This button includes a drop-down menu where
you can either select a layer or click on the Manage option.
The Manage option provides the ability to upload your own layers and hide pre-installed layers from the menu.
Defining custom tile layer
At this point, you may be wondering what to put in the Layers definition box. Here is a brief explanation.
DBeaver’s spatial data viewer uses Leaflet under the hood. To define a custom tile layer, you wll need to provide
arguments for the
function in the Layers definition box. This function is responsible for loading and
displaying the tile layers on the map.
For more detailed information on how to use the
function, refer to
the
official Leaflet documentation. To get started, you can also look at
the definition of predefined tiles in DBeaver.
Viewing string or binary data from any Database on a map
In DBeaver, you can visualize your geodata on the map, regardless of whether it is stored as string or binary data. To do
this, select the data cell and navigate to the View/Format setting. From there, choose the Set columnName format option
and select Geometry from the available formats.
This feature allows you to transform and visualize your data in a spatial context, enhancing your understanding of the
data.
Tip: It is particularly useful when working with geospatial datasets, as it enables you to see the geographical
distribution and relationships of your data.
Labels on a map
You can also enable labels on the map using the Show labels button. This feature allows you to visualize specific data
on the map, providing a more detailed view of your spatial data.
The Show labels button includes a drop-down menu with an option to Configure labels. Selecting this option opens a
window where you can customize what data will be visualized in the labels, the order of the data, and the delimiter
between the data.
Tip: Once the labels are enabled, the button’s name changes to Hide labels, allowing you to easily toggle the display of
labels on the map.
Copying the viewport area
DBeaver’s spatial data viewer also includes a feature that allows you to copy the extent of the current viewport area.
To use this feature, adjust the viewport to the area of interest using the zoom and drag functions. Then, select the
Copy viewport bounds option. This action will copy the coordinates that represent the extent of the current view area.
You can then paste these coordinates into your SQL query or any other place where you need to use them.
Tip: This feature can be useful when you need to examine all spatial data within a specific area or when you want to
check their attributes in relation to a particular spatial object.
Here’s an example of how you can use these coordinates in an SQL query:
SELECT geom
FROM your_table
WHERE ST_Intersects(
ST_MakeEnvelope(<paste bounds here>, 4326),
ST_Transform(geom, 4326)
);
In this query, you replace
<paste bounds here>
with the coordinates you copied from the viewport.
- The
function creates a rectangular polygon using these coordinates.
- The
function then finds any spatial objects in the provinces table that intersect with this rectangle.
- The
function is used to ensure that the geometries are in the same coordinate system (in this case,
).
Tip: If you want to copy the extent of a specific selection, you can use the Toggle lasso tool to Copy selection bounds,
and copy the coordinates.
Additional features
Copying coordinates
You can copy coordinates to the clipboard from any point on the map: just right-click anywhere and select Copy coordinates. It copies the coordinates formatted as
,
to the clipboard.\
Note: The coordinates are copied according to
and are just raw numbers. You may need to remove a comma and switch the
and
places to correctly insert it into a database.
Miscellaneous buttons
You can use buttons at the bottom of the view for additional features:
- Open the temporary generated
file in your default browser.
- Copy the current map to the clipboard as a picture.
- Save the current map as a picture into the selected folder.
- Print the current map.
Flip the
and
coordinates in the source data. This can be useful if the data in your table is saved
in (
/
) format while the Leaflet reads it as (
,
). This button does not change anything
in the source data, it just changes how this data is read to show the accurate information on the map.
Viewing Curve Geometries
You can now visualize the curve geometries in DBeaver’s spatial data viewer. This enhancement allows you to work with
complex spatial data types such as
,
,
,
, and
.
You can use SQL functions like
and
to create and view curve geometries.
Tip: This feature can be specifically useful when dealing with complex spatial datasets in PostGIS that include
curved geometries. It allows for more precise representation and analysis of geographical features that are not
adequately represented by simple polygons or lines, such as circular areas of interest, roads that follow curved paths,
or areas defined by a radius from a certain point.
.
.
and select the format.
located at the bottom of the Properties editor.
located at the bottom of the
Properties editor and press Persist to save the changes.
to open the properties of the selected foreign key in the
Editor panel.
(in the
Bottom Menu of the Properties Editor).
3) A confirmation dialog will appear, asking you to confirm the deletion. Before proceeding, ensure that you’ve selected
the correct foreign key for deletion.
4) After confirming the deletion, you’ll need to persist the changes to apply them to the database.