Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Kat Batuigas
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 that you can use in Postgres, so for today's post we'll look at another that works especially well with spatial data formats: ogr_fdw. I had also previously talked about some different ways to get spatial data into a Postgres/PostGIS database , but...
Read MoreKat Batuigas
Kat Batuigas
The idea of writing a database query that can then go out to an external source may 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 in PostgreSQL, along with the concept of database links o...
Read MoreKat Batuigas
Kat Batuigas
Early in on my SQL journey, I thought that searching for a piece of text in the database mostly involved querying like this: 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 and regex weren't going to cut it. What I had known all along was just pattern matching . It works perfectly fine for certain purposes, but what happens when it's not just a matter of checki...
Read MoreKat Batuigas
Kat Batuigas
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 to uncover query plans for individual queries. You can get a lot out of those two, but you may have also wondered, "What about logs? Surely I can use Postgres' logs to help me find and track slow queries too?" T...
Read MoreKat Batuigas
Kat Batuigas
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 command helps you look even closer into an individual query. If you're already proficient in EXPLAIN, great! Read on for an easy refresher. If you're less familiar with it, this will be a (hopefully) gentle introduction on what insights it might help provide. I'm going to demonstrate simple EXPLAIN...
Read MoreKat Batuigas
Kat Batuigas
"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 Which ones ha...
Read MoreKat Batuigas
Kat Batuigas
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 . That looked like a cool dataset to work with but I couldn't find the data source anywhere in the portal. I reached out to the nice folks on the city's GIS team and they gave me an ArcGIS-hosted URL. To get the public art features into PostGIS I decided to use the "ArcG...
Read MoreKat Batuigas
Kat Batuigas
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 v...
Read MoreKat Batuigas
Kat Batuigas
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...
Read MoreKat 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 More