Magical PostGIS in three brief movements
CC Attribution - NonCommercial - ShareAlike 3.0 Germany:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Ramsey, Paul
Everyone knows you can query a bounding box or even spatially join tables in PostGIS, but what about more advanced magic? This short symphony of PostGIS examples will look at using advanced features of PostGIS and PostgreSQL to accomplish surprising results: * Using full text search to build a spatially interactive web form. * Using raster functionality to look into the future. * Using standard PostgreSQL features to track and visualize versioning in data. PostGIS is a powerful tool on it's own, but combined with the features of PostgreSQL, it is almost magical.
 So this is Magical Postjust in three brief movements. I'd like to start by acknowledging the good folks at CardiDB, who employ me, let me work on Postjust. And like a lot of softwares of services companies, CardiDB has a strong open source ethic, stronger than most actually, because their system is built on top of open source components. The DB in CardiDB is actually PostGress, Postjust. So much of what I'm actually talking about today can be run on the CardiDB cloud, and some of my examples actually do that. They can also be run on a local Postjust instance as well. In fact, I built a lot of these examples originally on top of the boundless stack. So it's really, it's fairly agnostic as far as what you put on top of the database, and PostGress and Postjust is what it's all about. So this is supposed to be Magical Postjust and perhaps the... It's Magical Postjust. It's an Apple-esque name. It's the kind of thing you'd hear at the Apple user conference. It's magical stuff. And maybe that's what drew you into the room, but in retrospect, I could have called it show and tell, since I seem to have a lot of material about my favorite toys, or maybe stupid extension tricks, would have been more honest. Since I got some crazy examples of crazy extensions. But regardless on the Play Bill, it is Magical Postjust in three brief movements. And I want to give this talk because I feel like people aren't appreciating the kind of... What's up? No. Funny colors? No. I guess we're losing the video on the recording. But I'd like to go on since you're all here. I wanted to give this talk because I feel like folks aren't appreciating the kind of deep and beautiful magic that they can create using little more than their standard back-end database. Too often people have this utilitarian view of their database. They don't really like their database that much. To them, it's just a bit bucket. They just hold a bunch of tables, they stuff data in, they drag data out. Some people hate their database so much that they hide it away behind an object relational mapper, an orb, so they can pretend the bit bucket isn't there in the background doing the hard work, so they can pretend they're all alone, they're beautiful, little, middleware language. And they're really missing out because once you get to know it more intimately, you come to realize that your database is a beautiful, beautiful thing. It's not just a bit bucket, it's a magical toolbox with all kinds of good stuff inside. So this talk is actually not so much about post-jist, it's about the kinds of things you can do with post-jist when you combine it with the magic that's already inside the post-rescue database. Post-rescue is so magical because it was designed from the start to be more than a bit bucket. Michael Stonebreaker had already spent a decade pushing around bits of the Ingress project when he dreamed up his next generation database in 1986. And he wrote a paper called The Design of Post-Res, which laid out his goals for the new, and at that point, unwritten database. And it's those goals which form the foundation of post-rescue. It's awesomeness and for post-jist itself, in particular, support for complex objects. Geometry and geography are complex objects, and so are rasters. User extensibility is what allows post-jist to exist at all. It allows anyone to add types and functions to the database at runtime. And most of the fun stuff in post-rescue takes advantage of extension points. Active features are a pretty common database feature now. The database take a hand in managing data flow, and the relational model is what ties everything together. It's what makes the system as powerful as it is. Every piece of information is a tuple, and tuples are collected in tables. So Postgres lived as Stonebreaker's academic project for almost a decade, but it was useful enough that by the time he moved on to other projects and other topics, it already had a user base who kept it alive. They fitted out with the new SQL standard and eventually it grew into the PostgresQL development community we have today. So for the first movement, or three movements, I want to talk about what's possible when you start making use of PostgresQL's native full-text search support. Because if there is a phrase that makes me want to put my head in the oven, it is, we're using Postgres with Elasticsearch. And I acknowledge Lucene, Elasticsearch, they're nice tools, but boy, I sure hope you need every scrap of functionality they offer, because once you have two different data storage and query systems strapped together, everything in your system gets more complex and uglier. Assuming hopefully that your relational database is your source of truth, all the changes have to be replicated over to your Elasticsearch system, which adds a synchronization step to all the work. And if your data change is fast enough, that can actually be quite complex. But that's actually the easy problem. The hard problem is that once you have two query endpoints, any query that involves both a text search and a spatial search of sufficient complexity to require Postgres, requires that the middleware starts to coordinate the query process. So first it talks to one of the systems and it says, give me all your records on this text search query. And then it has to take all that information, walk over to the other system and say, and give me all the records you have that are in this set and fit my spatial clause. And depending on the query, the order you want to do that in, text first or spatial first, varies. So basically you have to build a little query planner in your middleware, which is a terrible idea because Postgres already has a query planner and already has a full text search system built into it. Postgres T-search has all the basic capabilities you want in a full text search engine. It has stemming, foxes and fox running and run. It has weight in searches so you can give more precedence to results matching, say, the title. It has the ability to create your own dictionaries so you can handle both different languages and specific professional domains, jargon and so on. It's got the ability to rank results based on the quality of the search. It's got highlighting of match terms and output. But what does this all have to do with magical Postgres? Well, if you're a full text engine and you're a spatial engine or on the same database, you can run compound spatial and text queries and you don't have to think about the execution path or efficiency. The database engine just does it for you automatically. So here's a fun example application. It's built using geographic names, in this case from geonames.org because geographic names are basically words. They're just really short documents that come with locations. But any document type with location can be used to build a cool text spatial location application. So with a little data mangling, you can turn the geographic location names file into a table. It looks like this. Primary key, the name, location. In order to get the full text searching enabled, you have to add a TS vector column. So our column type for full text searching is a TS vector. Then we populate it with TS vector data using the English configuration. We'll talk a little bit more about that later. And finally we index it using the full text index for TS vector. It's a GIN index that stands for a generalized inverted index that's also used in the Postgres support for array types. So now we're all set up. I note there's a magic parameter in here, the word English. So we've specified an English configuration. So English grammar rules are used to determine things that like that, that oak and oaks and age and aged are basically the same thing to identify all the articles and pronouns that can be ignored, to reduce the phrase into a simple vector like structure suitable for indexing. So two TS vector gives us a column of TS vectors we can query. But how do we do that? The query of TS vector you need a TS query, which is itself a logical filter. And you can construct one as a combination of and and or clauses. Optionally with weighting and partial matching, this is a simple one though. So this is a query that would match entries with both oak and tree or oak and ridge. And we can use the query in a full text search of our 2 million record geographic names table using the app operator to find all the TS vectors that match the TS query. And it turns out there's only three. But the really, really interesting thing is how quickly it finds the answer. It's just 17 milliseconds. So that's a good fast search of 2.2 million records. And the best part is now that the full text search is handled inside the database, it's possible to build efficient compound spatial text queries too. So like this query, which combines a search for all the records with oak and tree with a spatial filter restricting that it results in nearest hundred kilometers. And because both clauses are handled by the database, all the database machine we use at your disposal, figuring out the most efficient way to access the rows. So this is the explain analyze output for the last query. And reading from the bottom up, you can see that in this case the database ran the full text search first because it was the most selective. I don't return 59 rows as opposed to the all things in 100 kilometers filter, which would have returned several thousand. And then it applied the spatial filter which removed 57 and 59 leaving just the two we got in the result set. So I've shown you a lot of SQL. And maybe now you're starting to wonder where the magic is. If you don't think SQL is magic, usually the magic comes when you bind the power of SQL into a user interface and make that power visually manifest. So take all those place names and subset them quickly using text search, and then those pass the result into a heat map. So here's a map for a unique regalism in the Pacific Northwest of North America. In Cascadia we call mountain lions cougars. So all the place names that have cougar in them turns out mostly the Pacific Northwest. Now there's all kinds of oddities about how we name things, and thus how we perceive ourselves. So there's obviously some cachet. There's northern, southern, okay this makes sense. These are, by the way, these are just place names in the US. Because I'm using English it kind of means I'm stuck to particular places. Eastern, okay this makes sense. And then western. There's always some cachet in being western. I think everything is west of Europe and maybe that's why it all got rammed in there. So that was magical, but perhaps not practical enough. By the way, if you go to my blocks page, the PRAMZ blocks page, you can see this example live and you can type in your own names and see what's going on. All right, so that was magical, but perhaps not very practical. So let's do this one. Suppose you were a county with some standard parcel and dress data, and you wanted to set up a simple parcel finder app for folks to find their home. How would you do it? You want to have a Google style interface, just one input field and magical autocomplete. You're a county, you're a city, you have GIS data. So your GIS data probably has a street name, address number, and a city for every site address. So we'll make use of that. But how? Street address, great. Where are we? So the only trick to do it to make an autocomplete form is you have to be able to look up not just the words that the user has already entered, but the words that they're in the middle of typing. Unfortunately, Postgres Tech Search can do that too. So in this 2TS query function, I'm not just looking for the 256. I'm also looking for the words that start with MAI. So say if I'm looking for 256 Main Street and I haven't quite finished yet. So that's what I could stuff in. Postgres Tech Search calls this prefix matching. With prefix matching and a simple JavaScript, you know, a jQuery UI, in this case, this example, an autocomplete form. You can have a really fast autocomplete search text up and running in a few minutes. And it's uncannily accurate. It doesn't care about word order. If you want to get fancy in addition to having one row for each street address, you can also add rows to your table. So you can add rows for, say, street intersections like Main and Second. But the last example here is interesting because in the search field we've got 349e Main St. And on the map, it's a Google-based map in this case, on the map we've got East, all spelled out Main St. Right? So there's a mismatch here. So what happens if we go back to our form and try to search for the names as they appear on the base map? 349e Main St. Using the fully spelled out word East. Ah! No answers. If you just search for Main St. That's spelling street out in full or searching for the addresses on South Second Street. That appears on the map. But no success. So what's going on here? What's happening is we broke the street names down into words. Each token got saved as the word. And then we saved the words in the full text engine. But the words aren't like English words. They have their own grammar and synonyms. Street names, they look English, but they're not. So the search is failing. Can we fix it? What if instead of treating the words in the index as parts of language, we treated them as parts of addresses? So the system would know that if you wrote St, you meant street. And if you wrote N, you meant north. Then the searches using abbreviations would work. And searches against data that was abbreviated would work. And PostgreSQL T-search allows you to create your own dictionaries. Or synonyms. Or words you want to ignore, words you want to replace with other words. So I created a custom dictionary for street addresses. The PostgreSQL addressing dictionary. And for my basic example, I used a simple dictionary set, which doesn't do any special processing of the words. This is actually better than the English dictionary, which will drop things that aren't English words, like N or St. But it's still not that good, since a search would have to use exactly the same abbreviation style as the data in order to come up with a hit. So in this example, 128 is considered a word, and E is considered a word, and St is considered a word. But when we parse the same thing using the addressing dictionary, the custom address dictionary comes into play, and abbreviations are expanded out. E becomes east, St becomes street. So by altering the search application to use the addressing dictionary, instead, we get much better behavior. East main street works, south second street works. Things even work when the user is mixed up the correct addressing order, and put the directions last, or put the house numbers last. So that's pretty cool. This is one I couldn't demonstrate in Cardi B, because you can't do system level stuff like add dictionaries in the Cardi B. You have to take the extensions that are already in place. But this is what I do with my custom Postgres and GeoServer as the renderer. So rather than give a bunch of URLs, here's the modern version of the AOL keywords for the section. Just type in Postgresql, full text, 9.4 for the latest information. But full text search has been part of Postgres since 9.0. And if you type in PRAMZ GitHub, you can find the addressing dictionaries repository if you want to add that to your database. So that was the first movement. So let's pause for a brief while, while the orchestra flips over the sheet music, and for the second movement, federated systems. So first, upwards federation, pushing data up from my local database into a cloud storage system. And in deference to my employer, and because it's so easy, to sync to a system where there's no impedance mismatch, copying from Postgres to Postgres is pretty easy. I'll be showing how I federated a local Postgres to Cardi B, a cloud Postgres. So first I got some open data from the city of Victoria, my home city. This is a shapefile of public art in the city. And then I loaded it into my local Postgres using shaped PTSD. And I viewed it in QGIS, so there it is. Points. Blah, blah, blah, blah. And then I loaded the same data into Cardi B, and there it is. It's a little more comprehensible with the base map underneath. And I can use the Cardi B visualization tools to make it pretty, in this case with a categorical style. But how do I connect the two systems? How do I get changes in the local Postgres to propagate to the cloud Cardi B? Well, Cardi B is a web service, so we need a web transport to push the changes over. And it happens, as it happens, I wrote one of those, the HTTP extension to Postgres. I'm not especially about this extension, it just allows you to make HTTP calls using PostgresQL functions. So you can run an HTTP get function and get back the results from a web service. Not just the content, but also the MIME type, status codes, headers and so on. And not just get, so you can do posts and puts and deletes, so you can interact with any HTTP web service you want. And here's the thing that makes this work. Cardi B has a web service called the SQL API. And by the name, you can imagine what it is. The SQL API is actually diabolically simple. You call an HTTP endpoint, you tell it what format you want your return to be, and JSON or geoJSON. If you're altering the data or the data is private, you provide an API key to prove who you are, and then you just provide the SQL you want executed. It is so diabolical, I actually described it a couple of years before it was invented, as the architecture of evil. Since with an unprotected SQL pass-through, there's so much evil the outside world could work on your database. Of course, the Cardi B API is protected against SQL injection, and users are isolated in their own databases. Everything's only run at the permissions level of the user that's logging in, which is basically very low-level read access unless you have an API key. So it's not exactly the same thing I described in 2009, but it's incredibly lightweight to pass the SQL through to the database unfiltered. The simplicity of their approach allows for incredible flexibility in building apps, since there's no need for the HTTP interface level to reinvent things to proxy for SQL. If you've used OGCWFS, you might have looked at the XML and said, this looks like really unpleasant SQL, and that's all it is, right? It's just a bunch of ands and ors and filters and so on. And why reinvent that when you can just write the SQL directly? So for this example of Federation, I use QGIS as an editor, and then I directly edited a local Post-Its database. Each database update in turn triggered an HTTP Post call, which called the HTTP extension, passed an update to the Cardi B SQL API. This in turn was applied to the Cardi B database, which made it visible to me in Chrome looking at the Cardi B rendering. So, diabolical, pure evil. So here's the local database trigger that updates Cardi B. It's only tied to update events, but if we're doing a full CRUD implementation, we could make an insert and an delete trigger as well. To write to Cardi B, we need to authenticate, so we provide an API key. The SQL to update this table is simple, since we're only updating the location field. A little more complete version might update all the fields. We have to URL and code the SQL to pass it to the HTTP form, and then run an HTTP Post to get it up to Cardi B, and then check the return code to make sure there's actually a good response that was accepted, and then return the new tuple back for writing to the local database, and that's it. So here it is in action. I positioned my cutest window on top for editing, and the Cardi B map on the bottom for seeing the results. I added a point and moved it, then refreshed the Cardi B window, and see the changes have been set up. So, move, move, and refresh. Refresh, refresh, refresh. Ta-da! So the trigger method is a nice incremental solution, if you want something even simpler, operating in batch, this solution from Martin Jensen is even more about a vehicle. It just dumps a table directly into curl. This example uses CSV, so it's only good for points, and then it slams the table right onto the Cardi B import API. That's probably the smallest piece of SQL I've seen for moving data directly from one database into Cardi B. So this is an example of a push up, moving data from a local Post disk to a remote HTTP host. How would a push down? Pushing data down from the cloud to a local Post disk database. Can we do the reverse? Sure we can. We use a fancy SQL standard called SQL Med, that stands for management of external data, which is exposed in Postgres as a real world piece of functionality called foreign data wrappers, or FTW. Foreign data wrappers expose what looks to the client, just like a table in the database. You get access by running select queries on it. And change it by running insert, update, and delete commands on it. But behind the scenes, a foreign data wrapper table can be anything at all. So it can be a table on a remote database, not necessarily even a remote Postgres database. There's wrappers for Oracle and MySQL, and other databases. It could be a non-database data source, like a flat file, or it could be a non-tabular database, like a Twitter query. And there's FTW implementations for all those things. However, the one I'm going to talk about today is an FTW wrapper for OGR, the Spatial Data Abstraction Library. It's a perfect fit for an FTW wrapper in many ways, since it exposes a very tabular kind of data. The OGR data model is very much a tabular model with spatial objects in it. And since it's a multi-format spatial library, by implementing an OGR FTW, we get access to all the formats of OGR for the price of writing just one wrapper. So here's what it looks like to expose a file geodatabase to Postgres using OGR FTW. So first you turn on the FTW extension, then you create a server that references the data source, in this case a file geodatabase file. And you can see the nomenclature really assumes you'll be working against other database servers, but fortunately that's not actually a real restriction. And finally you create a foreign table that in turn references the server you define. It defines what columns from the foreign server you want to expose in your local database. So here's the same thing only using CodDB as the foreign server. Even though CodDB is a Postgres post just underneath, we don't have access to low level. So we define our server using the CodDB OGR driver, rather than the Postgres OGR driver. And then we define our foreign table to match the CodDB table. So that's our foreign table statement. And once that's defined, we can run queries locally on the table and get results just as if the data were local. So here's a distance query finding the seven nearest pieces of public art. To the piece named fire in the belly. The OGR FDW driver is getting better all the time. So as of a month or two ago, it can send calls. So that's where the where clause, you can send the where clauses to remote servers so that only subsets the data sent back to the client. I think my next revision, I'm going to add spatial filters. And then finally update and delete support. So it's possible to actually edit the remote data without ever leaving the friendly confines of Postgres. So this is the third movement. I think I'm going to have to give a talk model in the four seasons at some point. So summer, fall, winter, spring, that'd be cool. But for now the movement names are a little arbitrary. It's the third movement, time and tide. In the abstract that made me write this talk, I promised I'd do a time travel portion, a look into the future. So for this section I want to turn it over to Karnak the Magnificent. This is also in the blocks URL down there. So this is what Karnak does. You type a city into the autocomplete form, which is driven off of a Postgres query. And then based on the city, Karnak tells you if it's going to rain tomorrow. And this is all done with Postgres and PostGIS. So the video was taken a couple months ago, so the answers are all wrong. But if you go to the demo page, you can find a live Karnak. It is only for the states though. A live Karnak that should be pretty accurate, or as accurate as a fortune teller could be expected to be. So let's peel back the covers and see how the trick works. NOAA, National Oceanic Atmospheric Administration, is nice enough to publish their forecast data in a web directory. It's kept constantly up to date so you can see that I created this slide in February. And for the rain prediction, the file we're interested in is the POP12 file, probably the precipitation given in 12-hour forecast windows. And if you download that file, convert it to GeoTiff, and look at it in QGIS, this is what you see. Which is like totally awesome and trippy. Because QGIS is defaulting to loading the TIF with band 1 is red, and band 2 is green, and band 3 is blue, which gives a really cool picture with lots of fun mixing. Actually, each band is meant to be viewed separately as a forecast period. So if you stream them together, you can see the forecast pattern of precipitation moving from west to east, in this case, as you would expect the general direction of weather in the jet stream in North America to run. So once the data are in GeoTiff, we can put them into PostGIS, but actually getting an optimal conversion from the NOAA net CDF to GeoTiff using Google is a bit of an adventure and learning experience. So I thought I'd share it. The default conversion did preserve all five bands, and included the spatial reference information and the grid metadata of the input file, which is cool. But the input file was 1.5 megs, and the output file was 113 megs. So the first thing you notice when you pop open the output files, the pixel type is double. So that's 8 bytes per pixel. But the input data is just integers from 0 to 100, with a NO data at 9999, which basically fits into a single byte. So there's an eight-fold improvement in storage available if we just change the pixel type, which is pretty easy. And that gets the output down to 14 megs, so it's no longer 100 times larger than the input, only 10 times larger, which is still pretty terrible. When you look at the TIF in QGIS, it has some awful imperfections, where the 999 no-data pixels have been coerced down into the same date range as the 0 to 100. So we need to explicitly map the NO data values into a slot in the number space where there's no real data. So since our data run from 0 to 100, we can map it into 255 safely. But the file is still large. So what's going on? It turns out that Google is producing an uncompressed Geo-TIF by default. So we have to ask for compression. Dflate does an excellent job, and now we're down to 1.4 megabytes, about the same as the original. But it turns out actually that Dflate has some extra options we can twiddle. If we add a higher compression level, which just uses a little more RAM, and if we add a scan line predictor, we can get it down to just over 1 megabyte, which is a pretty nice improvement over the 113 megabyte the default conversion gave us. So the key component of our solution is what Carnac means when he says, tomorrow, and to figure that out, it helps to look at the output from Google Info. There's actually five bands in the Geo-TIF, and Google does a great job preserving the original grid format metadata, which is what you're seeing there. So we can figure out what each band means. The first band is good for 10 hours after the forecast is generated. The second band is good until 22 hours after the forecast. The third band is good until 34 hours. The fourth band is good to 46 hours. Each band has a valid time, which gives the UTC timestamp when the forecast expires. That could be pretty useful, but there's actually two ways to solve the problem. There's the right way, which we carefully look at the metadata and figure out which forecast band we need based on the timestamp. And then there's my way, which was just to average bands three and four together. That was pretty close to tomorrow, between 12 and 36 hours. So before I can do any averaging, first I need to load the data, which involves this usual opaque command line syntax. But there's nothing too crazy here. We choose that 32 by 32 chips because one byte per pixel times 32 times 32 times five bands implies a five kilobyte tile, which is slightly smaller than the page size of Postgres, the 8K page size of Postgres. And once all the data are loaded, we just need two SQL queries to run Karnac. Oops, got some stuff here. Two SQL queries to run Karnac. So first, the query to drive the autocomplete, which just reads the populated places, ordering results by size of cities, so that's really easy. And second, the one that generates the probability guess for Karnac, given the city. So step one is a nice big hunk of SQL. Step one, we use the selected city to generate a buffer ring, which we'll use to summarize the precip probabilities. Step two, we apply that buffer to the raster table and find all the rasters that intersect the buffer, and then mask out just the pixels of those rasters that fall inside the buffer. And then step three, we take those masked rasters and summarize them, finding the maximum value of the probability precipitation for every pixel. That becomes the number we use to drive Karnac's gas. And here's what it looks like visually. We calculate the blue buffer, and then we use that blue buffer to find the intersecting chips. You can see the red boundaries of chips. We just hit two of them barely, and then we mask out the chips to find just the pixels that intersect the buffer. And then in order to keep Karnac up to date, I have a little process running on my home computer every six hours. It pulls the precipitation forecast from NOAA, converts it to Geotiff, stuffs it up into an S3 bucket, and from there, Cardi B automatically syncs it every day using the standard table sync capability of Cardi B. Basically, every refresh period just slurps the file down and replaces the current data with the new data. And that's Karnac, the magnificent looking into the future. I have a little coda to finish it off, but I've used up my time, so I'll say thank you very much for listening to me now. And I don't know if I have time for like one question. I'll take one question. Just one question, please. So you're running out of time. Easiest question ever. If you're shy, you can have two questions, so you are not the only one. That's right. I'm not going to hold you to one. Yes. Would you differentiate between past and future sets on this screen? I would over-determinate, so I would say that I would let you pick both of them out. Yeah.
 So this is Magical Postjust in three brief movements.  I'd like to start by acknowledging the good folks at CardiDB,  who employ me, let me work on Postjust.  And like a lot of softwares of services companies, CardiDB has a strong open source ethic,  stronger than most actually, because their system is built on top of open source components.  The DB in CardiDB is actually PostGress, Postjust.  So much of what I'm actually talking about today can be run on the CardiDB cloud,  and some of my examples actually do that.  They can also be run on a local Postjust instance as well.  In fact, I built a lot of these examples originally on top of the boundless stack.  So it's really, it's fairly agnostic as far as what you put on top of the database,  and PostGress and Postjust is what it's all about.  So this is supposed to be Magical Postjust and perhaps the...  It's Magical Postjust. It's an Apple-esque name.  It's the kind of thing you'd hear at the Apple user conference.  It's magical stuff.  And maybe that's what drew you into the room, but in retrospect, I could have called it show and tell,  since I seem to have a lot of material about my favorite toys,  or maybe stupid extension tricks, would have been more honest.  Since I got some crazy examples of crazy extensions.  But regardless on the Play Bill, it is Magical Postjust in three brief movements.  And I want to give this talk because I feel like people aren't appreciating the kind of...  What's up?  No.  Funny colors?  No.  I guess we're losing the video on the recording.  But I'd like to go on since you're all here.  I wanted to give this talk because I feel like folks aren't appreciating the kind of deep  and beautiful magic that they can create using little more than their standard back-end database.  Too often people have this utilitarian view of their database.  They don't really like their database that much.  To them, it's just a bit bucket.  They just hold a bunch of tables, they stuff data in, they drag data out.  Some people hate their database so much that they hide it away behind an object relational mapper,  an orb, so they can pretend the bit bucket isn't there in the background doing the hard work,  so they can pretend they're all alone, they're beautiful, little, middleware language.  And they're really missing out because once you get to know it more intimately,  you come to realize that your database is a beautiful, beautiful thing.  It's not just a bit bucket, it's a magical toolbox with all kinds of good stuff inside.  So this talk is actually not so much about post-jist,  it's about the kinds of things you can do with post-jist when you combine it with the magic that's already inside the post-rescue database.  Post-rescue is so magical because it was designed from the start to be more than a bit bucket.  Michael Stonebreaker had already spent a decade pushing around bits of the Ingress project  when he dreamed up his next generation database in 1986.  And he wrote a paper called The Design of Post-Res, which laid out his goals for the new,  and at that point, unwritten database.  And it's those goals which form the foundation of post-rescue.  It's awesomeness and for post-jist itself, in particular, support for complex objects.  Geometry and geography are complex objects, and so are rasters.  User extensibility is what allows post-jist to exist at all.  It allows anyone to add types and functions to the database at runtime.  And most of the fun stuff in post-rescue takes advantage of extension points.  Active features are a pretty common database feature now.  The database take a hand in managing data flow, and the relational model is what ties everything together.  It's what makes the system as powerful as it is.  Every piece of information is a tuple, and tuples are collected in tables.  So Postgres lived as Stonebreaker's academic project for almost a decade,  but it was useful enough that by the time he moved on to other projects and other topics,  it already had a user base who kept it alive.  They fitted out with the new SQL standard and eventually it grew into the PostgresQL development community we have today.  So for the first movement, or three movements, I want to talk about what's possible  when you start making use of PostgresQL's native full-text search support.  Because if there is a phrase that makes me want to put my head in the oven,  it is, we're using Postgres with Elasticsearch.  And I acknowledge Lucene, Elasticsearch, they're nice tools,  but boy, I sure hope you need every scrap of functionality they offer,  because once you have two different data storage and query systems strapped together,  everything in your system gets more complex and uglier.  Assuming hopefully that your relational database is your source of truth,  all the changes have to be replicated over to your Elasticsearch system,  which adds a synchronization step to all the work.  And if your data change is fast enough, that can actually be quite complex.  But that's actually the easy problem.  The hard problem is that once you have two query endpoints,  any query that involves both a text search and a spatial search of sufficient complexity  to require Postgres, requires that the middleware starts to coordinate the query process.  So first it talks to one of the systems and it says,  give me all your records on this text search query.  And then it has to take all that information, walk over to the other system and say,  and give me all the records you have that are in this set and fit my spatial clause.  And depending on the query, the order you want to do that in, text first or spatial first, varies.  So basically you have to build a little query planner in your middleware,  which is a terrible idea because Postgres already has a query planner  and already has a full text search system built into it.  Postgres T-search has all the basic capabilities you want in a full text search engine.  It has stemming, foxes and fox running and run.  It has weight in searches so you can give more precedence to results matching, say, the title.  It has the ability to create your own dictionaries so you can handle both different languages  and specific professional domains, jargon and so on.  It's got the ability to rank results based on the quality of the search.  It's got highlighting of match terms and output.  But what does this all have to do with magical Postgres?  Well, if you're a full text engine and you're a spatial engine or on the same database,  you can run compound spatial and text queries  and you don't have to think about the execution path or efficiency.  The database engine just does it for you automatically.  So here's a fun example application.  It's built using geographic names, in this case from geonames.org  because geographic names are basically words.  They're just really short documents that come with locations.  But any document type with location can be used to build a cool text spatial location application.  So with a little data mangling, you can turn the geographic location names file into a table.  It looks like this.  Primary key, the name, location.  In order to get the full text searching enabled, you have to add a TS vector column.  So our column type for full text searching is a TS vector.  Then we populate it with TS vector data using the English configuration.  We'll talk a little bit more about that later.  And finally we index it using the full text index for TS vector.  It's a GIN index that stands for a generalized inverted index  that's also used in the Postgres support for array types.  So now we're all set up.  I note there's a magic parameter in here, the word English.  So we've specified an English configuration.  So English grammar rules are used to determine things that like that,  that oak and oaks and age and aged are basically the same thing  to identify all the articles and pronouns that can be ignored,  to reduce the phrase into a simple vector like structure suitable for indexing.  So two TS vector gives us a column of TS vectors we can query.  But how do we do that?  The query of TS vector you need a TS query,  which is itself a logical filter.  And you can construct one as a combination of and and or clauses.  Optionally with weighting and partial matching, this is a simple one though.  So this is a query that would match entries with both oak and tree or oak and ridge.  And we can use the query in a full text search of our 2 million record geographic names table  using the app operator to find all the TS vectors that match the TS query.  And it turns out there's only three.  But the really, really interesting thing is how quickly it finds the answer.  It's just 17 milliseconds.  So that's a good fast search of 2.2 million records.  And the best part is now that the full text search is handled inside the database,  it's possible to build efficient compound spatial text queries too.  So like this query, which combines a search for all the records with oak and tree  with a spatial filter restricting that it results in nearest hundred kilometers.  And because both clauses are handled by the database,  all the database machine we use at your disposal,  figuring out the most efficient way to access the rows.  So this is the explain analyze output for the last query.  And reading from the bottom up, you can see that in this case the database ran the full text search first  because it was the most selective.  I don't return 59 rows as opposed to the all things in 100 kilometers filter,  which would have returned several thousand.  And then it applied the spatial filter which removed 57 and 59 leaving just the two we got in the result set.  So I've shown you a lot of SQL.  And maybe now you're starting to wonder where the magic is.  If you don't think SQL is magic, usually the magic comes when you bind the power of SQL  into a user interface and make that power visually manifest.  So take all those place names and subset them quickly using text search,  and then those pass the result into a heat map.  So here's a map for a unique regalism in the Pacific Northwest of North America.  In Cascadia we call mountain lions cougars.  So all the place names that have cougar in them turns out mostly the Pacific Northwest.  Now there's all kinds of oddities about how we name things,  and thus how we perceive ourselves.  So there's obviously some cachet.  There's northern, southern, okay this makes sense.  These are, by the way, these are just place names in the US.  Because I'm using English it kind of means I'm stuck to particular places.  Eastern, okay this makes sense.  And then western.  There's always some cachet in being western.  I think everything is west of Europe and maybe that's why it all got rammed in there.  So that was magical, but perhaps not practical enough.  By the way, if you go to my blocks page, the PRAMZ blocks page,  you can see this example live and you can type in your own names and see what's going on.  All right, so that was magical, but perhaps not very practical.  So let's do this one.  Suppose you were a county with some standard parcel and dress data,  and you wanted to set up a simple parcel finder app for folks to find their home.  How would you do it?  You want to have a Google style interface, just one input field and magical autocomplete.  You're a county, you're a city, you have GIS data.  So your GIS data probably has a street name, address number, and a city for every site address.  So we'll make use of that.  But how?  Street address, great.  Where are we?  So the only trick to do it to make an autocomplete form is you have to be able to look up  not just the words that the user has already entered,  but the words that they're in the middle of typing.  Unfortunately, Postgres Tech Search can do that too.  So in this 2TS query function, I'm not just looking for the 256.  I'm also looking for the words that start with MAI.  So say if I'm looking for 256 Main Street and I haven't quite finished yet.  So that's what I could stuff in.  Postgres Tech Search calls this prefix matching.  With prefix matching and a simple JavaScript, you know, a jQuery UI, in this case, this example,  an autocomplete form.  You can have a really fast autocomplete search text up and running in a few minutes.  And it's uncannily accurate.  It doesn't care about word order.  If you want to get fancy in addition to having one row for each street address,  you can also add rows to your table.  So you can add rows for, say, street intersections like Main and Second.  But the last example here is interesting because in the search field we've got 349e Main St.  And on the map, it's a Google-based map in this case,  on the map we've got East, all spelled out Main St.  Right?  So there's a mismatch here.  So what happens if we go back to our form and try to search for the names as they appear on the base map?  349e Main St. Using the fully spelled out word East.  Ah! No answers.  If you just search for Main St.  That's spelling street out in full or searching for the addresses on South Second Street.  That appears on the map.  But no success.  So what's going on here?  What's happening is we broke the street names down into words.  Each token got saved as the word.  And then we saved the words in the full text engine.  But the words aren't like English words.  They have their own grammar and synonyms.  Street names, they look English, but they're not.  So the search is failing.  Can we fix it?  What if instead of treating the words in the index as parts of language,  we treated them as parts of addresses?  So the system would know that if you wrote St, you meant street.  And if you wrote N, you meant north.  Then the searches using abbreviations would work.  And searches against data that was abbreviated would work.  And PostgreSQL T-search allows you to create your own dictionaries.  Or synonyms.  Or words you want to ignore, words you want to replace with other words.  So I created a custom dictionary for street addresses.  The PostgreSQL addressing dictionary.  And for my basic example, I used a simple dictionary set,  which doesn't do any special processing of the words.  This is actually better than the English dictionary,  which will drop things that aren't English words, like N or St.  But it's still not that good,  since a search would have to use exactly the same abbreviation style as the data  in order to come up with a hit.  So in this example, 128 is considered a word,  and E is considered a word, and St is considered a word.  But when we parse the same thing using the addressing dictionary,  the custom address dictionary comes into play,  and abbreviations are expanded out.  E becomes east, St becomes street.  So by altering the search application to use the addressing dictionary,  instead, we get much better behavior.  East main street works, south second street works.  Things even work when the user is mixed up the correct addressing order,  and put the directions last, or put the house numbers last.  So that's pretty cool.  This is one I couldn't demonstrate in Cardi B,  because you can't do system level stuff like add dictionaries in the Cardi B.  You have to take the extensions that are already in place.  But this is what I do with my custom Postgres and GeoServer as the renderer.  So rather than give a bunch of URLs,  here's the modern version of the AOL keywords for the section.  Just type in Postgresql, full text, 9.4 for the latest information.  But full text search has been part of Postgres since 9.0.  And if you type in PRAMZ GitHub,  you can find the addressing dictionaries repository if you want to add that to your database.  So that was the first movement.  So let's pause for a brief while,  while the orchestra flips over the sheet music,  and for the second movement, federated systems.  So first, upwards federation, pushing data up from my local database into a cloud storage system.  And in deference to my employer, and because it's so easy,  to sync to a system where there's no impedance mismatch,  copying from Postgres to Postgres is pretty easy.  I'll be showing how I federated a local Postgres to Cardi B, a cloud Postgres.  So first I got some open data from the city of Victoria, my home city.  This is a shapefile of public art in the city.  And then I loaded it into my local Postgres using shaped PTSD.  And I viewed it in QGIS, so there it is. Points. Blah, blah, blah, blah.  And then I loaded the same data into Cardi B, and there it is.  It's a little more comprehensible with the base map underneath.  And I can use the Cardi B visualization tools to make it pretty,  in this case with a categorical style.  But how do I connect the two systems?  How do I get changes in the local Postgres to propagate to the cloud Cardi B?  Well, Cardi B is a web service, so we need a web transport to push the changes over.  And it happens, as it happens, I wrote one of those, the HTTP extension to Postgres.  I'm not especially about this extension, it just allows you to make HTTP calls  using PostgresQL functions.  So you can run an HTTP get function and get back the results from a web service.  Not just the content, but also the MIME type, status codes, headers and so on.  And not just get, so you can do posts and puts and deletes,  so you can interact with any HTTP web service you want.  And here's the thing that makes this work.  Cardi B has a web service called the SQL API.  And by the name, you can imagine what it is.  The SQL API is actually diabolically simple.  You call an HTTP endpoint, you tell it what format you want your return to be,  and JSON or geoJSON.  If you're altering the data or the data is private, you provide an API key to prove who you are,  and then you just provide the SQL you want executed.  It is so diabolical, I actually described it a couple of years before it was invented,  as the architecture of evil.  Since with an unprotected SQL pass-through, there's so much evil the outside world could work on your database.  Of course, the Cardi B API is protected against SQL injection,  and users are isolated in their own databases.  Everything's only run at the permissions level of the user that's logging in,  which is basically very low-level read access unless you have an API key.  So it's not exactly the same thing I described in 2009,  but it's incredibly lightweight to pass the SQL through to the database unfiltered.  The simplicity of their approach allows for incredible flexibility in building apps,  since there's no need for the HTTP interface level to reinvent things to proxy for SQL.  If you've used OGCWFS, you might have looked at the XML and said,  this looks like really unpleasant SQL, and that's all it is, right?  It's just a bunch of ands and ors and filters and so on.  And why reinvent that when you can just write the SQL directly?  So for this example of Federation, I use QGIS as an editor,  and then I directly edited a local Post-Its database.  Each database update in turn triggered an HTTP Post call,  which called the HTTP extension, passed an update to the Cardi B SQL API.  This in turn was applied to the Cardi B database,  which made it visible to me in Chrome looking at the Cardi B rendering.  So, diabolical, pure evil.  So here's the local database trigger that updates Cardi B.  It's only tied to update events, but if we're doing a full CRUD implementation,  we could make an insert and an delete trigger as well.  To write to Cardi B, we need to authenticate, so we provide an API key.  The SQL to update this table is simple, since we're only updating the location field.  A little more complete version might update all the fields.  We have to URL and code the SQL to pass it to the HTTP form,  and then run an HTTP Post to get it up to Cardi B,  and then check the return code to make sure there's actually a good response that was accepted,  and then return the new tuple back for writing to the local database, and that's it.  So here it is in action.  I positioned my cutest window on top for editing, and the Cardi B map on the bottom for seeing the results.  I added a point and moved it, then refreshed the Cardi B window,  and see the changes have been set up.  So, move, move, and refresh.  Refresh, refresh, refresh.  Ta-da!  So the trigger method is a nice incremental solution,  if you want something even simpler, operating in batch,  this solution from Martin Jensen is even more about a vehicle.  It just dumps a table directly into curl.  This example uses CSV, so it's only good for points,  and then it slams the table right onto the Cardi B import API.  That's probably the smallest piece of SQL I've seen for moving data directly from one database into Cardi B.  So this is an example of a push up, moving data from a local Post disk to a remote HTTP host.  How would a push down?  Pushing data down from the cloud to a local Post disk database.  Can we do the reverse?  Sure we can.  We use a fancy SQL standard called SQL Med,  that stands for management of external data,  which is exposed in Postgres as a real world piece of functionality called  foreign data wrappers, or FTW.  Foreign data wrappers expose what looks to the client,  just like a table in the database.  You get access by running select queries on it.  And change it by running insert, update, and delete commands on it.  But behind the scenes, a foreign data wrapper table can be anything at all.  So it can be a table on a remote database,  not necessarily even a remote Postgres database.  There's wrappers for Oracle and MySQL, and other databases.  It could be a non-database data source, like a flat file,  or it could be a non-tabular database, like a Twitter query.  And there's FTW implementations for all those things.  However, the one I'm going to talk about today is an FTW wrapper for OGR,  the Spatial Data Abstraction Library.  It's a perfect fit for an FTW wrapper in many ways,  since it exposes a very tabular kind of data.  The OGR data model is very much a tabular model with spatial objects in it.  And since it's a multi-format spatial library,  by implementing an OGR FTW,  we get access to all the formats of OGR for the price of writing just one wrapper.  So here's what it looks like to expose a file geodatabase  to Postgres using OGR FTW.  So first you turn on the FTW extension,  then you create a server that references the data source,  in this case a file geodatabase file.  And you can see the nomenclature really assumes you'll be working against other database servers,  but fortunately that's not actually a real restriction.  And finally you create a foreign table that in turn references the server you define.  It defines what columns from the foreign server you want to expose in your local database.  So here's the same thing only using CodDB as the foreign server.  Even though CodDB is a Postgres post just underneath,  we don't have access to low level.  So we define our server using the CodDB OGR driver,  rather than the Postgres OGR driver.  And then we define our foreign table to match the CodDB table.  So that's our foreign table statement.  And once that's defined, we can run queries locally on the table  and get results just as if the data were local.  So here's a distance query finding the seven nearest pieces of public art.  To the piece named fire in the belly.  The OGR FDW driver is getting better all the time.  So as of a month or two ago, it can send calls.  So that's where the where clause,  you can send the where clauses to remote servers so that only subsets the data sent back to the client.  I think my next revision, I'm going to add spatial filters.  And then finally update and delete support.  So it's possible to actually edit the remote data  without ever leaving the friendly confines of Postgres.  So this is the third movement.  I think I'm going to have to give a talk model in the four seasons at some point.  So summer, fall, winter, spring, that'd be cool.  But for now the movement names are a little arbitrary.  It's the third movement, time and tide.  In the abstract that made me write this talk,  I promised I'd do a time travel portion, a look into the future.  So for this section I want to turn it over to Karnak the Magnificent.  This is also in the blocks URL down there.  So this is what Karnak does.  You type a city into the autocomplete form, which is driven off of a Postgres query.  And then based on the city, Karnak tells you if it's going to rain tomorrow.  And this is all done with Postgres and PostGIS.  So the video was taken a couple months ago, so the answers are all wrong.  But if you go to the demo page, you can find a live Karnak.  It is only for the states though.  A live Karnak that should be pretty accurate, or as accurate as a fortune teller could be expected to be.  So let's peel back the covers and see how the trick works.  NOAA, National Oceanic Atmospheric Administration,  is nice enough to publish their forecast data in a web directory.  It's kept constantly up to date so you can see that I created this slide in February.  And for the rain prediction, the file we're interested in is the POP12 file,  probably the precipitation given in 12-hour forecast windows.  And if you download that file, convert it to GeoTiff, and look at it in QGIS, this is what you see.  Which is like totally awesome and trippy.  Because QGIS is defaulting to loading the TIF with band 1 is red, and band 2 is green, and band 3 is blue,  which gives a really cool picture with lots of fun mixing.  Actually, each band is meant to be viewed separately as a forecast period.  So if you stream them together, you can see the forecast pattern of precipitation moving from west to east,  in this case, as you would expect the general direction of weather in the jet stream in North America to run.  So once the data are in GeoTiff, we can put them into PostGIS,  but actually getting an optimal conversion from the NOAA net CDF to GeoTiff using Google is a bit of an adventure and learning experience.  So I thought I'd share it.  The default conversion did preserve all five bands, and included the spatial reference information  and the grid metadata of the input file, which is cool.  But the input file was 1.5 megs, and the output file was 113 megs.  So the first thing you notice when you pop open the output files, the pixel type is double.  So that's 8 bytes per pixel.  But the input data is just integers from 0 to 100, with a NO data at 9999, which basically fits into a single byte.  So there's an eight-fold improvement in storage available if we just change the pixel type, which is pretty easy.  And that gets the output down to 14 megs, so it's no longer 100 times larger than the input, only 10 times larger, which is still pretty terrible.  When you look at the TIF in QGIS, it has some awful imperfections,  where the 999 no-data pixels have been coerced down into the same date range as the 0 to 100.  So we need to explicitly map the NO data values into a slot in the number space where there's no real data.  So since our data run from 0 to 100, we can map it into 255 safely.  But the file is still large.  So what's going on?  It turns out that Google is producing an uncompressed Geo-TIF by default.  So we have to ask for compression.  Dflate does an excellent job, and now we're down to 1.4 megabytes, about the same as the original.  But it turns out actually that Dflate has some extra options we can twiddle.  If we add a higher compression level, which just uses a little more RAM,  and if we add a scan line predictor, we can get it down to just over 1 megabyte,  which is a pretty nice improvement over the 113 megabyte the default conversion gave us.  So the key component of our solution is what Carnac means when he says,  tomorrow, and to figure that out, it helps to look at the output from Google Info.  There's actually five bands in the Geo-TIF, and Google does a great job preserving the original grid format metadata,  which is what you're seeing there.  So we can figure out what each band means.  The first band is good for 10 hours after the forecast is generated.  The second band is good until 22 hours after the forecast.  The third band is good until 34 hours.  The fourth band is good to 46 hours.  Each band has a valid time, which gives the UTC timestamp when the forecast expires.  That could be pretty useful, but there's actually two ways to solve the problem.  There's the right way, which we carefully look at the metadata and figure out which forecast band we need based on the timestamp.  And then there's my way, which was just to average bands three and four together.  That was pretty close to tomorrow, between 12 and 36 hours.  So before I can do any averaging, first I need to load the data, which involves this usual opaque command line syntax.  But there's nothing too crazy here.  We choose that 32 by 32 chips because one byte per pixel times 32 times 32 times five bands  implies a five kilobyte tile, which is slightly smaller than the page size of Postgres, the 8K page size of Postgres.  And once all the data are loaded, we just need two SQL queries to run Karnac.  Oops, got some stuff here.  Two SQL queries to run Karnac.  So first, the query to drive the autocomplete, which just reads the populated places,  ordering results by size of cities, so that's really easy.  And second, the one that generates the probability guess for Karnac, given the city.  So step one is a nice big hunk of SQL.  Step one, we use the selected city to generate a buffer ring, which we'll use to summarize the precip probabilities.  Step two, we apply that buffer to the raster table and find all the rasters that intersect the buffer,  and then mask out just the pixels of those rasters that fall inside the buffer.  And then step three, we take those masked rasters and summarize them, finding the maximum value of the probability precipitation for every pixel.  That becomes the number we use to drive Karnac's gas.  And here's what it looks like visually.  We calculate the blue buffer, and then we use that blue buffer to find the intersecting chips.  You can see the red boundaries of chips.  We just hit two of them barely, and then we mask out the chips to find just the pixels that intersect the buffer.  And then in order to keep Karnac up to date, I have a little process running on my home computer every six hours.  It pulls the precipitation forecast from NOAA, converts it to Geotiff, stuffs it up into an S3 bucket,  and from there, Cardi B automatically syncs it every day using the standard table sync capability of Cardi B.  Basically, every refresh period just slurps the file down and replaces the current data with the new data.  And that's Karnac, the magnificent looking into the future.  I have a little coda to finish it off, but I've used up my time, so I'll say thank you very much for listening to me now.  And I don't know if I have time for like one question. I'll take one question.  Just one question, please. So you're running out of time.  Easiest question ever.  If you're shy, you can have two questions, so you are not the only one.  That's right. I'm not going to hold you to one. Yes.  Would you differentiate between past and future sets on this screen?  I would over-determinate, so I would say that I would let you pick both of them out. Yeah.
PT8-06_0_95.png PT8-06_96_1823.png PT8-06_1824_1979.png PT8-06_1980_2279.png PT8-06_2280_3755.png PT8-06_3756_4115.png PT8-06_4116_4655.png PT8-06_4656_6227.png PT8-06_6228_7079.png PT8-06_7080_9299.png PT8-06_9300_10067.png PT8-06_10068_11123.png PT8-06_11124_13655.png PT8-06_13656_15095.png PT8-06_15096_15995.png PT8-06_15996_16907.png PT8-06_16908_17231.png PT8-06_17232_18359.png PT8-06_18360_18539.png PT8-06_18540_18599.png PT8-06_18600_19535.png PT8-06_19536_20123.png PT8-06_20124_20171.png PT8-06_20172_24119.png PT8-06_24120_24551.png PT8-06_24552_26195.png PT8-06_26196_26387.png PT8-06_26388_27323.png PT8-06_27324_28799.png PT8-06_28800_30131.png PT8-06_30132_30803.png PT8-06_30804_32423.png PT8-06_32424_33659.png PT8-06_33660_34547.png PT8-06_34548_35939.png PT8-06_35940_37859.png PT8-06_37860_38843.png PT8-06_38844_39623.png PT8-06_39624_40415.png PT8-06_40416_45431.png PT8-06_45432_46223.png PT8-06_46224_46583.png PT8-06_46584_47495.png PT8-06_47496_48527.png PT8-06_48528_48635.png PT8-06_48636_48732.png