Latest posts from Kat Batuigas

In my last post, I did a simple intro to foreign data wrappers in PostgreSQL. postgres_fdw is an extension available in Postgres core that allows you to issue queries against another Postgres database. It's just one of many foreign data wrappers

The idea of writing a database query that can then go out to an external sourcemay not occur to someone who is not a DBA early on. That is: instead of figuring out how to grab then load multiple data sets into the same store, or configuring your application backend to connect to a bunch of disparate sources, why not use query JOINs like you usually would across tables within one database?

In case you're not familiar, the dblink module

Early in on my SQL journey, I thought that searching for a piece of text in the database mostly involved querying like this:

SELECT col FROM table WHERE col LIKE '%some_value%';

Then I would throw in some wildcard operators or regular expressions if I wanted to get more specific.

Later on, I worked with a client who wanted search functionality in an app, so LIKE

In the last several months, we've featured simple yet powerful tools for optimizing PostgreSQL queries. We've walked through how the pg_stat_statements extension can show which queries are taking up the most time to run system-wide. We've also looked at how to use the EXPLAIN command

In a previous post, I talked about pg_stat_statements as a tool for helping direct your query optimization efforts. Now let's say you've identified some queries you want to look into. The EXPLAIN

"I want to work on optimizing all my queries all day long because it will definitely be worth the time and effort," is a statement that has hopefully never been said. So when it comes to query optimizing, how should you pick your battles? Luckily, in PostgreSQL we have a way to take a system-wide look at database queries:

• Which ones have taken up the most amount of time cumulatively to execute
• Which ones are run the most frequently
• And how long on average they take to execute

As a GIS newbie, I've been trying to use local open data for my own learning projects. I recently relocated to Tampa, Florida and was browsing through the City of Tampa open data portal and saw that they have a Public Art map

There are a lot of ways to load data into a PostgreSQL/PostGIS database and it's no different with spatial data. If you're new to PostGIS, you've come to the right place. In this blog post, I'll outline a few free, open source tools you can use for your spatial data import needs.

You can use a desktop GUI application like QGIS, and/or command-line utilities. If you want more flexibility, it's great to have both types in your toolkit.

I'll talk about importing to PostGIS within the context of vector data, since it's a much more common use case. It's possible to import raster data

In this series so far we've talked about how to get our Django application to save uploaded images as bytea in Postgres. We've also walked through an example of a PL/Python function that processes the binary data to apply a blur filter to the uploaded image. Now, we'll show how to retrieve the blurred image from Django.

Use the function in a SQL view mapped to a Django model

Now that we have a PL/Python function, what do we do with it? The function lives in PostgreSQL, so we'll have to figure out how to call the function from Django.

You do have the option in Django to perform raw SQL queries

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