A common use case with PostGIS data is to calculate things, such as distances between points, lengths of lines, and the area of polygons. The topic of accuracy, or inaccuracy, with GEOMETRY data comes up often. The most frequent offenders are generic SRIDs such as 3857 and 4326. In some projects accuracy is paramount. Non-negotiable. On the other hand, plenty of projects do not need accurate calculations. Those projects often rely on relationships between calculations, not the actual values of the calculations themselves. If Coffee shop Y is 4 times further away than Coffee shop Z. I'll often go to Coffee shop Z just based on that.

In most cases, users should still understand how significant the errors are. This post explores one approach to determine the how accurate (or not!) the calculations of a given SRID are in a particular region, based on latitude (North/South). The queries used in this post can be adjusted for your specific area.

Set the stage

The calculations in this post focus on the distance of two points situated 40 decimal degrees apart. The points are created in pairs of west/east points at -120 (W) and -80 (W). Those were picked arbitrarily, though intentionally spread far enough apart to make the errors in distance calculations feel obviously significant. The point pairs are created in 5 decimal degree intervals of latitude from 80 North to 80 South. The following screenshot shows how the points frame in much of North America.

While the points on the map using a mercator projection appear to be equidistant... they are not!

Continue Reading

Welcome to another #PGSQLPhriday post! This month's host is Lætitia Avrot, who picked the topic of Triggers with these questions:

"Do you love them? Do you hate them? Do you sometimes love them sometimes hate them? And, most importantly, why? Do you know legitimate use cases for them? How to mitigate their drawbacks (if you think they have any)?"

Let's dive in!

Triggers are a specialized tool

I rarely use triggers. I don't hate triggers, I just think they should be used sparingly. Like any specialized tool, you should not expect to use triggers for every occasion where they could be used. However... there is one notable use where case I really like triggers: audit tables. Part of the magic of using triggers for auditing data changes in Postgres is the JSON/JSONB support available.

Continue Reading

Thank you everyone who contributed to PgSQL Phriday #005! This month's topic: "Is your data relational?" If I missed any contributions, or if new ones are published, let me know and I'll try to update this post. These snippets are in a somewhat random order, loosely threaded together by sub-topic.

Contributing posts

Hetti D. wrote a great post starting by addressing the bonus question. I put that question last partly because I have struggled with a succinct definition myself. I also put it last because I hoped the initial 3 questions would lead us to answer the bonus question in our own ways. Hetti also discusses storing blobs and objects, and considerations between complexities and trade-offs with more targeted technology.

Continue Reading

I was the host for this month's #PGSQLPhriday topic (#005), and decided on the topic question: Is your data relational? This is my submission on the topic, and how I use Postgres for Relational Plus usages.

Non-relational data

Q: What non-relational data do you store in Postgres and how do you use it?

PostGIS is the most prominent non-relational data I am involved with. Pretty much all of the PostGIS data I am involved with is rooted alongside solidly relational data. Want to know demographics of customers within 10 miles of a specific location? The location portion is spatial, the demographic data is relational.

Continue Reading

Welcome to the 5th installment of the #PGSQLPhriday blogging series. I am thrilled to be this month's host! The topic posts should be published by Friday February 3rd.

When Ryan Booz proposed the idea for #PGSQLPhriday I was immediately excited about it. Other than our first names, Ryan and I have a other few things in common. One of these common points is we both started our database careers in the world of MS SQL Server and later found our way to Postgres. My move to Postgres, and why I discovered Postgres, is at the heart of this month's topic for PGSQL Phriday 005.

Is your data relational?

The entire reason I discovered and started using Postgres was PostGIS. I needed PostGIS because I had a project in 2011 that could benefit from the OpenStreetMap data. The project still needed rock solid support for relational data and the SQL Standard, which Postgres also provides. However, it was the spatial support of PostGIS that pulled me into the world of Postgres.

Continue Reading