RustProof Labs: blogging for education (logo)

PostGIS: Tame your spatial data (Part 2)

By Ryan Lambert -- Published December 01, 2018

In a previous post PostGIS: Tame your spatial data I illustrated how large-area polygons in your spatial data can take up a lot of space. That post examined one method (ST_Simplify) to reduce the size of the data (45% reduction of size on disk) in order to improve performance (37% faster queries). The goal of reductions like this are to improve the life of the analysts working with spatial data for their job. These analysts are often using spatial data within a GIS tool such as QGIS, and in those tools Time-to-Render (TTR) is quite important.

The topic of that prior post solved a specific problem (large polygons), and unfortunately that solution can't be applied to all problems related to the size of spatial data.

This post is an advanced topic of the series PostgreSQL: From Idea to Database.

Continue Reading

Enhance your PostgreSQL Data Dictionary

By Ryan Lambert -- Published November 25, 2018

This post continues the topic of creating a robust data dictionary in PostgreSQL. My previous post on building a data dictionary in PostgreSQL focused on using built-in psql tools, such as \dn and \dt. It also quickly covered the use of comments to document database objects. Those tools and shortcuts are great and readily available, but beyond quick exploration tasks these built-in options don't provide enough functionality. This is especially true in today's data-filled world with increasing regulation.

This post is part of the series PostgreSQL: From Idea to Database.

Meet pgdd: PostgreSQL data dictionary

The PostgreSQL Data Dictionary (pgdd) project, available on GitHub, intends to improve our ability to document databases in PostgreSQL. This open-source project super-powers your PostgreSQL databases by introducing additional meta-tracking abilities. It also makes the internal database meta-data more user accessible and user friendly through a handy set of views. These views can be queried and joined using standard SQL syntax using any tool you like.

pgdd exists to make PostgreSQL data dictionaries more usable.

Data dictionary should be for everyone

Imagine an analyst with access to a PostgreSQL/PostGIS database. That database probably has data from a variety of open and closed sources. That analyst spends quite a bit of time in QGIS, but very little time in psql. Being able to run standard SQL type queries in their favorite tool makes the data dictionary more usable for this user.

Screenshot showing pgdd in action via the DB Manager GUI within QGIS.

Continue Reading

Data Analytics and Software: It still isn't easy

By Ryan Lambert -- Published November 21, 2018

This post examines the work of producing analytics/visualizations and the role software plays. Business Intelligence (BI) software, such as Power BI, Tableau, and SAP Dashboards are a few examples of software with a lot of buzz. The marketing folks in that industry would have us believe their software enables analysts to point and click at data and enable magic. This magic feature provides instant access to interactive dashboards, drill-down functionality, and more. Don't forget, the data will be reliable, accurate, easy to share, easy to secure, and cross-platform (mobile-friendly) too!

Reality check

Data visualization software in 2018 doesn't have the magic feature working yet. I believe it takes at least as much human effort today to produce a great visualization as it did a decade ago. The improvements in the software are not making the process quicker or easier. The reality is that working with data isn't for everyone, and #DataIsHard.

I recently attended a PASS webinar by Scot Reagin who stated:

"90% of users are not self-service capable."

Continue Reading

Build your Data Dictionary in PostgreSQL

By Ryan Lambert -- Published September 24, 2018

This post provides an introduction to building a data dictionary directly in our PostgreSQL databases. The steps outlined here are specific to PostgreSQL, though every database platform has these basics components in place.

This post is part of the series PostgreSQL: From Idea to Database.

What is a Data Dictionary?

Documentation is a good thing. Data dictionaries are one important component of documenting your databases. A data dictionary is a common tool used to provide database-specific documentation to analysts, developers, and other business users. A good data dictionary provides insights into a database's structure, constraints, relationships, and sources of data found inside a system.

Continue Reading

Geometry viewer added to pgAdmin 4

By Ryan Lambert -- Published September 13, 2018

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.


Screenshot showing a pgAdmin 4 3.3's new Geometry View tab in the GUI.

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

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.

Continue Reading