Working with Spatial GIS data

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.

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.

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:

1. Name of the column in the header
2. Displayable data (strings, numbers, dates etc.) from every other column in the corresponding row

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.

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 L.tileLayer() 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 L.tileLayer() function, refer to the official Leaflet documentation. To get started, you can also look at the definition of predefined tiles in DBeaver.

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.

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.

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 ST_MakeEnvelope function creates a rectangular polygon using these coordinates.
• The ST_Intersects function then finds any spatial objects in the provinces table that intersect with this rectangle.
• The ST_Transform function is used to ensure that the geometries are in the same coordinate system (in this case, EPSG:4326).

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.

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 latitude, longitude to the clipboard.\

Note: The coordinates are copied according to EPSG:4326 CRS and are just raw numbers. You may need to remove a comma and switch the latitude and longitude places to correctly insert it into a database.

You can use buttons at the bottom of the view for additional features:

• Open the temporary generated HTML 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 latitude and longitude coordinates in the source data. This can be useful if the data in your table is saved in (latitude/longitude) format while the Leaflet reads it as (longitude, latitude). 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.

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 CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON, MULTICURVE, and MULTISURFACE.

You can use SQL functions like st_geomfromtext and st_curvetoline 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.