Geometry viewer added to pgAdmin 4

In general, the core PostgreSQL community doesn't latch onto GUI tools. That said, pgAdmin is one of the more popular GUI options for PostgreSQL. I hadn't used pgAdmin for quite a while (the transition from III to 4 was... rough), but a recent feature caught my eye: Geometry viewer.

This feature, available in pgAdmin 4 v3.3, allows you to see your PostGIS data in a separate tab directly in pgAdmin. When you execute a query that includes PostGIS data, pgAdmin overlays your data on the OpenStreetMap background in the geometry viewer.

Awesome!!

Boston GIS introduced the basics of how the Geometry Viewer functions. This post illustrates why I think this feature is so cool.

DBeaver now has a geometry viewer feature too!

Why is geometry viewer awesome?

Spatial (PostGIS) data typically doesn't translate well in most SQL tools. pgAdmin's new geometry viewer provides a dead-simple way to visualize, verify and troubleshoot spatial data in their databases. This feature is targeted for database professionals who support PostGIS databases.

The following query returns a result set of the boundaries of the counties in Colorado (data from Tiger/LINE).

SELECT ST_Transform(geom, 4326) AS geom
    FROM tl_2014.county;

Raw results, not helpful

This is a tiny fraction of the nonsensical mess you can expect to see in the grid results from the database. Without the ability to easily view the spatial data it is hard to know if your spatial joins, intersections, and other functions are functioning properly.

┌─────────────────────────────────────────────┐
│                    geom                     │
├─────────────────────────────────────────────┤
│ 3A10570514170BF31C504B166C1EA1B7B2815705141 │
│ 8DCB2085241F783415A96C765C17E8720EFB2085241 │
│ 209400E5341770EE6A1ADCB65C1375787F1420E5341 │
│ A84899C5141A81573169D7666C1661A8501BA9C5141 │
│ 0544E0C5141C01B615068FE66C1EA23C7B25F0C5141 │
│ FE6804C5141F384521602ED66C1C5D28BF18B4C5141 │
│ BB0F802524108C29B9868B366C194D39821F0025241 │
│ F276FF651414BBCD7884C8066C1E53EE43070F65141 │
│ 05DD43D514184A59918CA8366C18BFA52D1E73D5141 │
│ 4F3289D514111F0D17DB4B365C1647DFCAA289D5141 │
└─────────────────────────────────────────────┘

Enter geometry viewer

With pgAdmin's new geometry viewer, we can see a visual representation that makes sense to humans. Not only can you see the counties of Colorado, there are plenty of reference points shown on the OpenStreetMap background provided.

Lower overhead spatial exploring

Until now, my main option for visualizing spatial data has been QGIS. I love QGIS (see here, here, or here!), it's great, but it's high-overhead software. It is not ideal for designing complex spatial queries. QGIS is geared for GIS professionals so the learning curve can be steep for a database professional.

PgAdmin4, on the other hand is more likely to already be installed on a DBA or developer's machines. Because after all, that's who gets called when the analyst is dealing with slow spatial queries.

Exploring an area

Suppose you are doing a spatial analysis in a particular area. I happen to be familiar with downtown Golden, CO and know there is OpenStreetMap coverage of the area. I can write a simple query of the buildings layer from OSM to find Pangea Coffee Roasters, the host of our April 2016 mapping party.

With that simple starting point, I could now find all the trees within 25 meters of the building. The geometry viewer shows the results as blue dots over the green trees from the OSM background.

-- Trees w/in 25 meters of Pangea Coffee Roasters
SELECT n.osm_id, ST_Transform(n.way, 4326) AS way_tree
    FROM osm.buildings_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%' 
;

Visually explore changes

When developing and testing new queries, or changes to existing queries, it's important for the developer to be able to see the effects of changes to the query. With geometry viewer it is now trivial to see the impact of changing the query to expand from 25 to 250 meters by updating the join:

AND ST_DWithin(b.way, n.way, 250)

The geometry viewer automatically zooms to fit the scale of the data your query returns.

Summary

I'm impressed by this feature. The speed of rendering PostGIS data and the OSM layer is very usable. pgAdmin's geometry viewer provides exactly the basics to make the task of visualizing PostGIS data easy for DBAs. This is a good thing!

The geometry viewer gives database professionals an easy, high performance, way to visualize PostGIS data.

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