Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
A common situation in the spatial data world is having discrete measurements of a continuous variable. Every place in the world has a temperature, but there are only a finite number of thermometers: how should we reason about places without thermometers and how should we model temperature? For many use cases, the right way to model a continuous spatial variable is a raster: a regularly spaced grid where each square in the grid contains a value of the variable. This works for temperature and prec...
Read MorePaul Ramsey
Paul Ramsey
Spatial indexes are used in PostGIS to quickly search for objects in space. Practically, this means very quickly answering questions of the form: • "all the things inside this this" or • "all the things near this other thing" "all the things inside this this" or "all the things near this other thing" Because spatial objects are often quite large and complex (for example, coastlines commonly are defined with thousands of points), spatial indexes use "bounding boxes" as index and search keys: •...
Read MorePaul Ramsey
Paul Ramsey
We at Crunchy Data put as much development effort into improving GEOS as we do improving PostGIS proper, because the GEOS library is so central to much geospatial processing. The GEOS library is a core piece of PostGIS. It is the library that provides all the "hard" computational geometry functionality: • Intersections of geometry • Unions of geometry • Differences of geometry • Buffers of geometry • Geometry relationship evaluation Intersections of geometry Unions of geometry Differences of...
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 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 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 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 MorePaul Ramsey
Paul Ramsey
Summarizing data against a fixed grid is a common way of preparing data for analysis. Fixed grids have some advantages over natural and administrative boundaries: • No appeal to higher authorities • Equal unit areas • Equal distances between cells • Good for passing data from the "spatial" computational realm to a "non-spatial" realm No appeal to higher authorities Equal unit areas Equal distances between cells Good for passing data from the "spatial" computational realm to a "non-spatial" realm...
Read MorePaul Ramsey
Paul Ramsey
Open source developers sometimes have a hard time figuring out what feature to focus on to generate the greatest value for end users. As a result, they will often default to performance . Performance is the one feature that every user approves of. The software will keep on doing all the same cool stuff, only faster . For PostGIS 3.1, there have been several performance improvements that, taken together, might add up to a large performance gain for your workloads. Spatial joins have slowed down...
Read More