Saturday, September 09. 2023
Paul Ramsey and I recently had a Fireside chat with Path to Cituscon. Checkout the Podcast Why People care about PostGIS and Postgres. There were a surprising number of funny moments and very insightful stuff.
It was a great fireside chat but without the fireplace. We covered the birth and progression of PostGIS for the past 20 years and the trajectory with PostgreSQL. We also learned of Paul's plans to revolutionize PostGIS which was new to me. We covered many other side-line topics, like QGIS whose birth was inspired by PostGIS.
We covered pgRouting and mobilitydb which are two other PostgreSQL extension projects that extend PostGIS.
We also managed to fall into the Large Language Model conversation of which Paul and I are on different sides of the fence on.
Continue reading "Why People care about PostGIS and Postgres and FOSS4GNA"
Monday, March 07. 2016
We'd like to give a big thanks to Olivier Courtin and Oslandia for organizing this year's OSGeo Code Sprint in Paris. It was quite memorable
and I walked away feeling energized.
Leo and I attended the Paris OSGeo Code Sprint for 1.5 days. I'm really glad we were able to attend at least part of it.
It would have been nicer to attend all of it. Though we didn't get too much done while there,
we did have some interesting conversations and learned about what others were doing.
I walked out with a TO DO list, of which I'm happy to say I've accomplished some of now.
While we were there Leo spent time cleaning up my Mingw64 compile scripts and starting to test PostGIS against PostgreSQL 9.6
in preparation for parallelization testing of PostGIS 2.3 features specifically targetted for PostgreSQL 9.6.
Continue reading "Paris OSGeo Code Sprint 2016 PostGIS Highlights"
Thursday, February 26. 2015
There will be two PostGIS workshops at FOSS4G NA 2015 on March 9th. Signup if you haven't already.
PostGIS Up and Running 9AM - 12 PM. This is an introductory workshop where we'll cover the basics of configuring PostGIS and using the PostGIS geometry and geography types. We also plan to demonstrate some new features coming in PostGIS 2.2, particularly of the 3D kind. If time permitting, we'll do a quick coverage of pgRouting as well.
Someone asked on IRC if we will be handing out certificates of completion to folks who complete the workshop. Some people need this because they are allowed to attend workshops on company time, but not conferences. The thought hadn't crossed our mind, but we like the idea a lot. So yes you can have a certificate if you stay thru the whole session complete with Regina and Leo's seal of approval. We might even have some door prizes.
Advanced spatial analysis with PostGIS. Pierre Racine will be leading this workshop. Expect to be blown away by images of rasters dancing on legs of geometries. He'll also have some other cool advanced spatial analysis stuff beyond raster. Expect a lot of geometry processing tricks in this one.
Sadly I think our PostGIS In Action 2ed is going to be released a little after conference time and probably won't be ready until mid March, so probably just a wee bit too late for FOSS4G NA 2015, but just in time for PGCon.US New York 2015 March 25th-26th . Final book proofing is like getting our teeth pulled. I really hope it's worth the wait. We'll have coupons but no book. We will have some copies of our PostgreSQL: Up and Running 2nd Edition available though. If you've already bought one of our books and want it autographed, bring it along on your trip.
Friday, January 02. 2015
When ogr_fdw came out, I was very excited to try ogr_fdw out on windows. To start with I used the default GDAL that we package with PostGIS, which is built under mingw-w64 (both 32-bit and 64-bit versions). Then I thought imagine how much more I can get by compiling with more drivers. A big one on my list was ODBC. Sadly this did not work out of the box under mingw-w64.
For windows folks who want to try out the ogr_fdw, I have binaries for PostgreSQL 9.4 FDWs which includes ogr_fdw and some of my other favorites. I do have the 9.3s as well, but haven't written up an article about them until I test them on my production instances. The links to those are the same as 9.4, just replace 94 with 93 in the download link. These include ODBC support which should allow you to query a lot of ODBC datasources.
Normally when I try to compile something that depends on ODBC using mingw, the configure scripts or make files are setup to assume you are using UnixODBC (which works but seems to use a different ODBC Manager and drags in all these extra dependencies). The way I normally fix this to use the native ODBC support is to replace all references to -lodbc in the configure script with -lodbc32 . Yes even the windows 64-bit ODBC is called odbc32 but put in the system32 folder for 64-bit and SysWow64 for 32-bit. I imagine Microsoft came up with this confusing convention for backward compatibility. So I did that for GDAL, and to my horror that did not work.
What was the problem? GDAL had this extra dependency on odbcinst library. Although the odbcinst header is packaged with mingw-w64, there is no associated library. Searched thru my windows/system32 folder and my mingw-w64 libodbc*.a files and couldn't find such a thing. So I removed this thing from the configure script, and GDAL at least got past configure and happily started compiling, and then failed when linking. So I searched for the function it couldn't find, and discovered this was in a library called odbccp32. So to compile with odbc support, as noted in my ogr_fdw_build gist
- edit the configure script in gdal source tree and replace all references to -lodbc with -lodbc32, and -lodbcinst with -lodbccp32
- Then add in your configure
--with-odbc=/mingw/${MINGHOST} where MINGHOST is set to x86_64-w64-mingw32 for the 64-bit mingw-w64 chain and i686-w64-mingw32 for the 32-bit mingw-w64 chain.
With this I was able to build libgdal with native windows ODBC support that just depends on the windows packaged odbc system dlls (so no extra dependencies). I tested by querying one of my clients SQL Server databases via ogr_fdw. One issue I ran into is it didn't handle SQL Server datetime right and kept on giving error ERROR: timestamp(62258384) precision must be between 0 and 6 . So I had to change the ogr_fdw_info generated definition to bring in datetimes as varchar instead of timestamp and then just cast it to timestamp as part of my PostgreSQL query. This may be an idiosyncracy with how lengths have changed in windows ODBC that I have to patch or something with the ogr_fdw. I haven't tried the MSSpatial driver, but that suddenly showed up as an option after compiling with ODBC support. Also had some issues with UTF encoding which I was able to work thru by stripping high-byte characters in my PostgreSQL query.
One of my plans coming PostGIS 2.2 is to package a GDAL with more drivers - next on my list being SQLite family. I also need to figure out a way to have PostGIS gdal be swappable with the Visual C++ built ones. I can do that with for example curl and libxml, but the VC gdal last I looked seemed to expose weird named symbols so never hooks up. If I can get this going, then people can just swap out the GDAL we package with PostGIS with their own to get proprietary drivers like MrSID and so forth. Why don't I just try to build with it with VC++? Because then I've got to ship a VC++ runtime if I don't build with the same one EDB is using, and they use different flavors for each version of PostgreSQL. I also feel uncomfortable around Visual Studio for anything other than web development.
Thursday, November 20. 2014
This year's PostGIS day, I decided to celebrate with a little Conway's Game of Life fun inspired by Anita Graser's recent blog series Experiments with Game of Life.
The path I chose to simulate the Game of life is a little
different from Anita's. This variant exercises PostGIS 2.1+ raster mapalgebra and PostgreSQL 9.1+ recursive queries. Although you can do this with PostGIS 2.0, the map algebra syntax I am using is only supported in PostGIS 2.1+. My main disappointment is that because PostGIS does not yet support direct generation of animated gifs I had to settle for a comic strip I built unioning frames of rasters instead of motion picture. Hopefully some day my PostGIS animated gif dream will come true.
Continue reading "PostGIS Day Game of Life celebration"
Tuesday, September 30. 2014
One of the new features coming in PostGIS 2.2 is ST_ClipByBox2D (thanks to Sandro Santilli's recent commits
funded by CartoDB ). However to take advantage of it, you are going to need your PostGIS compiled with GEOS 3.5+ (very recent build) which has not been released yet.
Windows folks, the PostGIS 2.2 9.3 and 9.4 experimental binaries are built with the latest GEOS 3.5 development branch, so you should be able to test this out with Winnie's experimental builds.
Since the dawn of PostGIS, PostGIS users have needed to mutilate their geometries in often painful and horrific ways.
Why is ST_ClipByBox2D function useful, because its a much faster way of mutilating your geometries by a rectangular mold than using ST_Intersection. Why would you want to mutilate your geometries?
There are many reasons, but I'll give you one: As your geometry approaches the area of your bounding box and as your bounding box size decreases, the more efficient your spatial index becomes.
You can consider this article, Map dicing redux of the article I wrote (eons ago) - Map Dicing and other stuff which describes the same approach with much older technology. Though I will be using more or less the same dataset Massachusetts TOWNSSURVEY_POLYM (its newer so you can't really compare) and I tried to simplify my exercise a bit (not resorting to temp tables and such), my focus in this article will be to compare the speed between the new ST_ClipByBox2D approach and the old ST_Intersection approach. The spoiler for those who don't have the patience for this exercise is that using ST_ClipByBox2D at least on my sample data set on my puny Windows 7 64-bit desktop using PostgreSQL 9.4beta2 64-bit
was about 4-5 times faster than using ST_Intersection. There was a downside to this speedup. With the ST_Intersection approach, I had no invalid polygons. In the case of ST_ClipByBox2D, I had one invalid polygon. So as noted in the docs, use with caution. We'd be very interested in hearing other people's experiences with it.
One other benefit that ST_ClipByBox2D has over ST_Intersection which I didn't test out is that although ST_Intersection doesn't work with invalid geometries, ST_ClipByBox2D can.
For these exercises, I'm going to also abuse the PostGIS raster function ST_Tile for geometry use, cause
for some strange reason, we have no ST_Tile function for PostGIS geometry. For those who missed the improper use of ST_Tile for raster, refer to Waiting for PostGIS 2.1 - ST_Tile.
Continue reading "Waiting for PostGIS 2.2 - ST_ClipByBox2D - Map dicing Redux in concert with ST_Tile"
Thursday, September 12. 2013
The DZone Essential PostGIS reference card got released yesterday. The PDF version is a free download, though does require DZone registration. This reference card is focused on PostGIS 2.1 but does showcase some features present in older versions. Its way too hard to stuff everything on a 9 page reference so we had to leave out some of our favorites that we thought would not be as appreciated by a developer community just starting off with GIS and PostGIS.
We are working on the second edition of Essential PostgreSQL which hopefully will be out in the next 2 months. That one will be advertising our upcoming second edition of PostgreSQL: Up and Running..
On a slightly related topic, we did our Intro to PostGIS talk. We had a good live demo with very few glitches followed by food and spirits. The group of folks was a good cross sectional. Web developers working with geodjango or ruby on rails new to PostgreSQL and PostGIS, some mainstream GIS (urban planing) with little familiarity with relational database spatial (but familiar with ESRI), retired professor, high-end PostgreSQL folks with limited knowledge of PostGIS, IT consultants both GIS and mainstream etc. We promise to have the code posted probably early next week (and possibly a video too if the video turns out okay).
Friday, August 30. 2013
One of the new functions in PostGIS 2.1 is the ST_ColorMap function
which converts a 1-band raster into 3 or 4 band 8BUI raster. It's main use is for visualizing otherwise non-visual floating point rasters such as digital elevation models, temperature, weather, and even rasters you create from your own data. For this next example, I'm going to
take the fairly uninteresting topic of land area using tiger 2013 state boundaries to demonstrate.
This is similar to the ST_SetValues
example I demonstrated except using ST_Union and ST_ColorMap.
The final output is a PNG image which looks like:
.
Continue reading "Using ST_ColorMap for Thematic Maps"
Thursday, May 09. 2013
We had this big raster that we needed to chop up into tiles and only extract a portion of for load into PostGIS. raster2pgsql doesn't currently have an option to pull just a portion of a raster and also we don't have the windows raster2pgsql compiled with MrSID support. Luckily
GDAL commandline gdal_translate has this switch that allows you to specify a chunk of a raster to grab based on a projected or unprojected box window.
We wanted to grab just that portion that is part of boston and chunk it into bite size pieces. What we needed was a grid generator similar to
what we described a while back in Map Dicing and other stuff
that would chop our neighborhood into bite sized tiles we could then use to generate the relevant gdal_translate command.
Instead of using temp tables and all that stuff, we decided to try with the ST_Tile raster function. Creating an empty raster and then tiling it.
Note the repurposing: Creating a raster with no bands to accomplish a task that has nothing to do with rasters, so that we can then apply it to something to do with rasters. Gridding is a surprisingly common step in a lot of spatial processing workflows.
Here is the SQL to do it and we'll explain in a separate article in more detail.
In a nutshell, we're using PostGIS raster technology (ST_Tile function introduced in PostGIS 2.1) that we demonstrated in Waiting for PostGIS 2.1 ST_Tile to create a grid because PostGIS geometry doesn't have cool gridding function like SpatiaLite has :).
SpatialLite tesselation. Perhaps in PostGIS 2.2
we'll see some of these SpatiaLite niceties. However ST_Tile does the trick fairly nicely and quickly. For this example took under 600 ms to generate 1524 rows of GDAL commands.
Continue reading "Chopping rasters with gdal_translate"
Monday, April 01. 2013
We have a confession to make. We're not GIS analysts; we just play one at parties. Truth is the bread and butter of our business involves pretty
boring stuff like e-Commerce, pricing (venture capital, private equity, travel, pension management), project management, work force management and all that other stuff that would bore a real GIS analyst to tears. Somehow we've got a lot of pictures to deal with particularly with project management and e-commerce work. So I was elated when Bborie checked in this new function ST_FromGDALRaster. With this function you can do all resizing and other manipulations
right in the database with standard type images like PNGs, bitmaps and anything else users will throw at you.
As mentioned before, we like keeping our work related pictures in the database, but every once in a while, we'd like to manipulate them and it would be nice not to have to keep many sizes of one image in the database. Having to drag them out of the database to do stuff with them is kind of a pain or to keep extra sizes is also a pain. We'd like to keep the original format we were given intact, but all other custom sizes people ask for do on the fly.
For these operations, I'm using:
POSTGIS="2.1.0SVN r11230" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER
PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit
Using the buildbot builds generated by Winnie PostGIS buildbot.
Continue reading "Waiting for PostGIS 2.1: ST_FromGDALRaster free those images"
Thursday, March 28. 2013
The Boston OSGeo Code Sprint ended today. Overall I think it was a great success just in terms of what was accomplished
and the fact that it was the very first conference we had ever set up. We were all nerves so couldn't enjoy it as much as we wanted to. It was also the first conference we were able to sorta attend all days for.
Paul already gave a great cap on what was going on in the PostGIS and MapServer camps. I still felt like there was way too much coding going on
and would be nice to have a bit more talking and a bit less typing.
I want to thank EnterpriseDb for being a Gold Sponsor and it would have been nice if they gave a talk. Maybe I can drag them in some other day. Rich Grady, of AppGeo gave a wonderful and humorous talk about the metamophisis AppGeo is going thru coming from a predominantly ESRI shop, now focused on the prize of finding the best solutions to solve spatial IT problems; which surprisingly does not always involve ESRI and is more and more requiring Open Source GIS technologies. They did show off their ESRI medals of honor and how well they kept papers from flying around.
For those who contributed to our pgRouting campaign, a BIG THANKS. We'll be circling back later this week or early next to try to finish collecting and charging credit cards.
If you already wrote us asking, but how do I pay? and we haven't responded with details, don't worry about it. We'll get to you next week. More concerned about those who haven't contacted us.
Continue reading "Boston OSGeo Code Sprint Synopsis"
Saturday, March 09. 2013
With all the cool new raster functions coming in postGIS 2.1, it's really hard to pick a favorite. One of the functions I feel like I've been waiting eons
for is ability to burn a set of geometry values in a raster with one SQL statement. My dream came true with ST_SetValues. Check this out:
A side note, people have asked how we visualize these things. We used the ad hoc web viewer we had created that we described in Minimalist Web based ASP.NET PostGIS 2+
and brother version PHP viewer version.
We do a lot of ASP.NET development and for using the ASP.NET viewer, we just set the credentials, in web.config, install the helper function in our test database running PostGIS, and then right-click View in Browser the postgis_viewer.htm with Visual Studio or Visual Web Express. The PHP version is setup much the same and in fact the postgis_viewer.htm are identical except the php version calls a PHP handler file and the ASP.NET one calls an ASP.NET http handler. The client side has some jquery magic that feeds that posts the query to the handler. The handler does the query and outputs the results which are then injected via jquery call.
Continue reading "Waiting for PostGIS 2.1: ST_SetValues - The road to thematic maps"
Saturday, February 09. 2013
We've done a preliminary generation of function cheat sheets for upcoming PostGIS 2.1. We'll regenerate again before PostGIS 2.1 gets released.
We've also updated the PostGIS 2.0 cheatsheets with updated doc links and errata. The html versions of these are generated from the PostGIS documentation xml files using the:
make cheatsheets
build command. Then we just do some massaging to it and print it to PDF.
Pay careful attention to the superscripts on the functions:
Here is list of 2.1 sheets.
Continue reading "Waiting for PostGIS 2.1: Cheatsheets"
Tuesday, February 05. 2013
In a previous article
Waiting for PostGIS 2.1 ST_Resize
we demonstrated a new raster function ST_Resize that is available in upcoming 2.1. In this article we'll talk about another function called ST_Tile, which similar to ST_Resize can work with
both georeferenced and non-georeferenced rasters. As the name suggests, ST_Tile can be used to cut up rasters right in the database, similar to what the raster2pgsql -t Tilesize commandline switch does
when you import. In this article we'll demonstrate the misuse of ST_Tile. ST_Tile can also be used for tiling out of db rasters and what it does when it does that is not to actually tile, but instead mark off the areas where it would cut if it were to actually cut.
Another thing I'd like to mention here which can not be summed up in a single function is that a lot of work has gone into PostGIS 2.1 in improving the performance
and robustness of out of database rasters (rasters stored externally but queried as if they were inside the database), and more is planned before 2.1 hits the street. All this work
has made me reconsider where out dbs play a role and how their speed profiles compare to in-db rasters.
We'll demonstrate the speed profile differences in this article for ST_Tile as well for this small sampling.
If you are not afraid of chewing the fat a little and you are on Windows, there are always fresh windows binaries packaged with all these goodies you can get from:
Winnie's fresh baked windows PostGIS binaries corner.
Continue reading "Waiting for PostGIS 2.1 - ST_Tile"
Sunday, December 02. 2012
I have a confession to make. I'm one of those folk who likes keeping my pictures in the database. File paths are just too annoying
and when some network guy/gal decides to do some spring cleaning, all the pictures referenced in your database, associated with important projects are misplaced. On top of that paths aren't as easily accessible across firewall connections as database connections, vary depending on OS/network and are just ancient. I can go on and on about why I like
my pictures in the database along side the data they pictorially describe, and some wise ass will tell me how wrong I am, so I'll stop here.
One thing I have always wanted to do is do all my cropping and resizing with the terseness and beauty that is SQL. With latest changes in PostGIS I can enjoy the same features with non-georeferenced pictures that I can with georeferenced ones. Thanks Bborie. We now have an ST_Resize function that doesn't require a known spatial reference system and many of the other gdal functions e.g. ST_ReSample have been changed in a similar fashion.
For these operations, I'm using:
POSTGIS="2.1.0SVN r10777" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER
PostgreSQL 9.2.0, compiled by Visual C++ build 1600, 64-bit
Using the buildbot builds generated by Winnie PostGIS buildbot.
Continue reading "Waiting for PostGIS 2.1: ST_Resize not just for GIS"
|