Kat Batuigas
Kat Batuigas
I recently wrote about building a Django app that stores uploaded image files in bytea format in PostgreSQL. For the second post in this series, we're now going to take a look at applying a blur filter to the uploaded image using PL/Python . Lately we've been wading into PL/Python waters, and want to learn how to process raw binary data using some popular Python libraries. Python has an almost dizzying array of options when it comes to data analysis and scientific computing. Pair that with...
Read MoreJonathan S. Katz
Jonathan S. Katz
What if I told you that you can create an out-of-the-box active-active, federated PostgreSQL cluster on Kubernetes? Since logical decoding was introduced in PostgreSQL 9.4, I have been fascinated by the various applications it has. In fact, I've used this feature to apply the concepts of change data capture both in theory and practice to the benefit of both applications and users. Logical replication and native partitioning support , introduced in Postgres 10, offered even more possibilit...
Read MorePaul Ramsey
Paul Ramsey
The PostGIS raster extension has a steep learning curve, but it opens up some unique possibilities for data analysis and accessing non-standard data from within PostgreSQL. Here's an example that shows how to access raster data from PostGIS running on Crunchy Bridge . The raster data type in PostGIS can be used to store any kind of raster data: images, elevation grids, model outputs, and more. One band, four band, multi-band and anything in between. This flexibility leads some beginners to du...
Read MoreKat Batuigas
Kat Batuigas
I'm not someone who spends a lot of time or energy on digital images and photography. I'm usually set with my phone's camera app and maybe applying a filter as I upload to Instagram. But when you work at a database company like Crunchy Data , anything in your life can inspire a demo application. How about a simple image upload app built with Django 3.1 and backed by PostgreSQL 13 , that takes advantage of the PL/Python procedural language for processing? You may have seen some of our DevR...
Read MoreSteve Pousty
Steve Pousty
In our last blog post on using Postgres for statistics , I covered some of the decisions on how to handle calculated columns in PostgreSQL . I chose to go with adding extra columns to the same table and inserting the calculated values into these new columns. Today’s post is going to cover how to implement this solution using PL/pgSQL . I bet your first question is going to be: Why did you choose to do this in PL/pgSQL rather than just some quick editor work? I actually started with handcrafti...
Read MorePaul Ramsey
Paul Ramsey
While we talk about " PostGIS " like it's one thing, it's actually the collection of a number of specialized geospatial libraries, along with a bunch of code of its own. • PostGIS provides core functionality bindings to PostgreSQL, the types and indexes format reading and writing basic algorithms like distance and area performance tricks like caching simple geometry manipulations (add a point, dump rings, etc) algorithms that don't exist in the other libraries • bindings to PostgreSQL, the type...
Read MoreSteve Pousty
Steve Pousty
Greetings readers, today we're going to take a semi-break from my “doing data science in SQL ” series to cover a really cool use case I just solved with regular expressions ( regex ) in Postgres . For those of you who have a bad taste in your mouth from earlier run-ins with regexs, this will be more use case focused and I will do my best to explain the search patterns I used. If you've never heard of regex, there are good resources to learn more about them but I will not be giving a t...
Read MoreKat Batuigas
Kat Batuigas
Crunchy Data's second annual PostGIS Day took place a couple weeks ago on November 19th, and as a first-time attendee I was blown away by the knowledge-sharing and sense of community that I saw, even as I was tuning in remotely from my computer at home. This year's PostGIS Day was virtual, which allowed even more attendees from all over the world to participate. The talks had a live Q&A section and we set up a Discord server so attendees and speakers could all interact freely with each other....
Read MoreSteve Pousty
Steve Pousty
In the last two blog posts on data science in Postgres, we got our data ready for regression analysis and had predictive variables that are on wildly different scales. Another example of data on different scales would be annual income versus age. The former is usually at least tens of thousands while age rarely gets to a hundred. If you do the regression with non-transformed variables, it becomes hard to compare the effect of the different variables. Statisticians account for this by convertin...
Read MoreCraig Kerstiens
Craig Kerstiens
At most of the places I've worked, the primary language used was not what I gravitated to naturally. If you're going to ask for a language of choice personally, it's python. I appreciate the explicit nature, that it's often pseudocode that can execute and it has a rich ecosystem of libraries (though that’s most languages these days). But as much as anything I latched onto Django in its early days. If you're not familiar with Django it's a popular framework for web apps written in Python. What...
Read More