Monday, May 13. 2013
Printer Friendly
Now that PostgreSQL 9.3 beta1 has been released we've started to jump start our experimentation by compiling our favorite extensions. First on the list is PL/V8 js.
This was compiled against 9.3beta1 for 64-bit and 32-bit and plv8 version 1.4.0. We briefly tried with the EDB windows builds which we downloaded from: http://www.enterprisedb.com/products-services-training/pgbindownload and seems to work fine.
We hope windows users find these useful.
Sunday, May 12. 2013
Printer Friendly
PostGIS 2.1.0 beta2 is out. Details on what's new in it are in official news release: http://postgis.net/2013/05/11/postgis-2-1-0beta2.
This is the first version of PostGIS to work with PostgreSQL 9.3, so if you are planning to experiment with PostgreSQL 9.3 coming out soon, use this one. Also check out the documentation in new ePUB offering format if you have an ereader and let us know how it looks. It seems to vary alot depending on what ePub reader used.
For windows users, we've got binary builds available compiled against PostgreSQL 9.3beta1 (and also available for 9.2 9x32,64) and 9.0,9.1 (x64). Details on windows PostGIS downloads page: http://postgis.net/windows_downloads. It does not yet have the new Advanced 3D offering (provided by SFCGAL https://github.com/Oslandia/SFCGAL), but we hope to have that compiled and packaged with the binaries before release time.
Tuesday, April 30. 2013
Printer Friendly
PostgreSQL 9.3 will be coming out in beta soon and with that, some who want to experiment with both PostGIS and PostgreSQL 9.3 have asked if they can use PostGIS 2.0. The answer is NO. A lot of major changes happened in PostgreSQL 9.3 that required us to patch up upcoming PostGIS 2.1. These changes were not backported to 2.0 and I personally do not plan to back-port them unless lightning strikes me and I escape unscathed, a big wad of cash falls from the sky, or for some reason we can't make the 2.1 cut before 9.3 comes out. So if you are planning to experiment with PostgreSQL 9.3, PLEASE use PostGIS 2.1 development branch. I will try to make sure we release 2.1 before PostgreSQL 9.3 comes out even if I have to resort to hitting some people over the head with a rubber bat :).
If ever in doubt what versions of PostGIS works with what versions of PostgreSQL /GEOS / GDAL, please refer to the matrix that we try to keep up to date.
http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS.
Now some people might say "Isn't it cruel not to support PostGIS 2.0 for 9.3", and my answer is "it's crueler to". The reason is simple. We have limited bandwidth for testing permutations of things and the more permutations of things we support, the dirtier our code base becomes making it harder to maintain and also the less time we can devote to properly testing each permutation. I'd rather say we don't support something than to do a half-hearted job of supporting all.
On a slightly different, but also pragmatic note, package maintainers (except for windows maintainers :)) generally only carry one version of PostGIS per version of PostgreSQL, and I'd rather users getting from packages see our best foot than a two year old aging foot.
Note: that going from PostGIS 2.0 to 2.1 is a soft upgrade so you can install 2.1 on your existing PostgreSQL 9.2 without dump restore and then you should be able to pg_upgrade over to 9.3 if your database is too big to dump restore.
Sunday, April 21. 2013
Printer Friendly
In Happy Valentine PostGIS we demonstrated how to use PostGIS raster
to decipher letters from a raster, vectorize them and then reuse this vectorized letters to form new words. Admittedly the letters were a bit grainy since they were
vectorizations of low res rasters and I didn't bother smoothing them. Bruce Rindahl offered a script to SVG to PostGIS geometry and using Batik to convert a font file to SVG format and gave me a hi-res converted kankin fontset. I still haven't figured out how his script works.
Bborie Park thought that was all too complicated and thought (as I have always) that we need an ST_GeomFromSVG function for PostGIS of which he is on a mission to create when he's less busy.
He also suggested I wrap my letter writer function as an extension. Taking all these ideas, I formulated an extension you install with
CREATE EXTENSION postgis_letters;
postgis_letters (http://www.bostongis.com/postgisstuff/postgis_letters_extension.zip) is an sql / data extension containing mostly data, but as the name suggests relying on PostGIS. The data are geometry vectors of the kankin font. I plan to add in more free fonts later once I figure out how to use Bruce's script or Bborie comes up with a better way and also more positioning logic and handling of spaces. So its a little rough at the moment.
The purpose of the extension is so I can write words on my images in reports e.g. state names or overlay labels on geometry features like roads and land. Using the power of both geometry/raster you can have a fully functioning report image writer that would return a fully formed image for use in LibreOffice (or for my ASP.NET web apps Active Reports.NET). This wouldn't rely on any mapping server to draw images (just pure PostGIS/PostgreSQL). Hopefully with new and improved binary features coming in PSQL for (looks like 9.4), outputting these raster images from psql will also be trivial. While on my mission to do something useful, I got distracted by something more entertaining: describing spatial processes with words. Here it goes.
Continue reading "Word Play with Spatial SQL"
Saturday, April 13. 2013
Printer Friendly
One of the features of PostGIS (pain to some however you look at it), is that PostGIS library file is versioned by minor version. The library will have for example a postgis-2.0 or postgis-1.5.dll / .so to denote the version. Each version of PostGIS can be compiled to run on usually about 3 or 4 versions of PostgreSQL.
Since PostGIS is not part of PostgreSQL proper and has to be installed separately, it is possible to run a different version of PostGIS in each database of a cluster. While this is a great feature for PostGIS developers and also great for users who want to keep their old legacy PostGIS apps, while testing or creating new apps with the PostGIS 2.0 or experiment with 2.1 development series, it does pose some obvious challenges.
For example you can't simply just upgrade your cluster to a new version of PostgreSQL. You need to make sure the new cluster has the various versions of PostGIS compiled and available. One step to that end is figuring out exactly what version of PostGIS each database in your cluster is running. Here is a quick psql script I wrote up to help with that.
Continue reading "Determine which version of PostGIS each database is running"
Tuesday, April 09. 2013
Printer Friendly
We've got experimental pgRouting windows binaries available for windows PostgreSQL 9.2 32-bit and 64-bit for pgRouting 1.0.7 development branch. More details on our Boston GIS blog page.
The final versions we plan to release with upcoming PostGIS 2.1 PostgreSQL 9.2 on stackbuilder as part of the PostGIS install. Barring no difficulties we'll also have experimental binaries for PostgreSQL 9.3 releases once 9.3 reaches beta.
This version and upcoming pgRouting versions support the PostgreSQL extension model, so if you have postgis already installed, its just an additonal simple step:
CREATE EXTENSION pgrouting;
Friday, April 05. 2013
Printer Friendly
The 3rd MEAP update of PostGIS In Action, 2nd Edition will be going out very shortly
to Early Action purchasers. Keep your eyes peeled. Lots of errata corrections in previous chapters and appendix, and one
very VERY new chapter on Raster functions which took a ton of time to write, so hopefully it will be well received. Our progress on the chapters is listed on PostGIS In Action 2nd Edition Chapters and all the ones marked as completed you will find in the MEAP. The ones with paperclips have downloadable code and data which you can click on the paperclip to download.
Regarding Raster, the Raster Function chapter is just merely the tip. You'll see a lot more raster usage in upcoming Relating two or more spatial objects and Raster Processing chapter which we are still fleshing out.
We are immensely grateful to all the early action subscribers who have posted errata or general comments about what can be clarified or examples that don't work. General comments about what specific kinds of examples you'd like to see are also welcome. Your opinions
really influence what we write and make for a better book.
Continue reading "PostGIS In Action 2nd Edition MEAP 3 Update"
Monday, April 01. 2013
Printer Friendly
Forget Linux, Forget Unix, Forget MacWhatever, forget Windows, and any other OS you may be using.
Say hello to PostOS. PostOS is built on PostgreSQL technology and fits you like a glove.
The built-in planner watches you type (or stare in confusion) and creates a composite image of what kind of person you are and
what behavior it should emulate. It's integrated monitor display and speakers are used to provide information as well as to watch and hear your every move. The built-in image recognition system (an extension to the PostGIS extension), can distinguish between members of your family and can impersonate them as well and change behavior accordingly.
Continue reading "PostOS"
Thursday, March 28. 2013
Printer Friendly
Today was the last day of the Boston OSGeo code sprint we hosted. Several OSGeo project tribes were represented.
PostGIS had a big showing with core PostGIS developers and related working on PostGIS core, PostGIS 3D, PostGIS raster, pgRouting, geocoding, and point clouds.
Leo and I with lots of help from Steve Woodbridge, spent a good chunk of time working out kinks of PostGIS pgRouting packaging for Windows and address normalizer replacement for the one packaged with tiger geocoder.
A special shout-out thanks to all the Code Sprint sponsors:
More details Paul's PostGIS Summary and Paul's MapServer Summary and our Boston OSGeo Code Sprint Synopsis.
Sunday, March 17. 2013
Printer Friendly
We have all the setup executables for PostgreSQL 8.4-9.2 32-bit and
PostgreSQL 9.1-9.2 x64-bit for PostGIS 2.0.3. We'll have the 9.0x64-bit
ones up shortly but do have the binaries.
We are planning to release these to stackbuilder sometime next week, but
would like some feedback first with any issues people run into.
One person already reported a serious bug in our original packaging which we
fixed. If you already installed 2.0.3 the x64-bit version or any of the 9.2
versions (both x32 and x64) that was not marked as -2.zip, -2.exe, you'll
want to reinstall. The 8.4-9.1 32-bits were not affected so just have a -1.
Packages can be downloaded from:
http://download.osgeo.org/postgis/windows
General details on what is included:
http://postgis.net/windows_downloads
As always if you are installing for PostgreSQL x64 make sure to pick a zip
or installer with 64 in the name. The unmarked or marked with 32 are just
for PostgreSQL 32-bit.
Wednesday, March 06. 2013
Printer Friendly
Should I install using PostGIS extension? is an FAQ that comes up quite a bit in PostGIS circles and unfortunately askers get mixed answers.
In How to upgrade your database to PostGIS 2.0
we covered WHY you should use extensions. In this article I'll use my Lincoln-Douglas debate skills to argue why you shouldn't.
Keep in mind that I put a great deal of effort in fitting PostGIS extensions
into the existing PostGIS build structure and I eat my own dogfood, so I might be a little biased and a poor defender of the counter argument.
Continue reading "Should I install using PostGIS extension"
Friday, February 22. 2013
Printer Friendly
Just recently saw Michael Paguier's article on the recent commit of postgres_fdw so I was excited to try this out for myself. Questions we wanted to answer/experience were:
- Can I connect against different versions of PostgreSQL? Yes to test I tried connecting from my PostgreSQL 9.3 dev instance to my 9.2 instance on another box.
- Can I use a foreign type like -- you guessed it PostGIS if we have same version of PostGIS installed on both databases? Yes. I didn't try with different versions so not sure if that would work especially since the structure changed a bit between 1.5 and 2.0. I suspect 2.0 and 2.1 would work fine and might be a good way to cheat run 2.1 on 9.3 but creating derivatives of my 2.0 data with functions only available in 2.1.
- Will it use my spatial index? No or at least we couldn't figure out how and I don't see how it is possible, but would be nice if it could be
- Can I have a table defined with dynamic SQL? Nope or at least the docs don't mention it. This is a feature I really liked about the ODBC_FDW
because often times I want the table to be filtered especially if I need my filter to use an expensive index like a spatial one or utilize functions only available on the foreign server. If that were part of the definition of the foreign table, then it could in theory process that part on the foreign server. So this makes postgres_fdw not a good replacement for dblink in many cases.
So here are the tests we did to experiment with:
Continue reading "PostgreSQL 9.3 postgres_fdw: a Test Drive"
Thursday, February 14. 2013
Printer Friendly
A while back I mentioned to Bborie (aka dustymugs) and Sandro (aka strk): We're missing ability to label our images with text.
Picture this: What if someone has got a road or a parcel of land and they want to stamp
the parcel id or the road name on it and they don't want to have to depend on some piece of mapping software. Many report writers and databases can talk to each other directly
such as we demonstrated in Rendering PostGIS raster graphics with LibreOffice
and we've got raster functionality in the database already. Can we cut out the middleman?
Some ideas came to mind. One we could embed a font lib into PostGIS thus having the ultimate bloatware minus the built-in coffee maker.
strk screeched. He's still recovering from my constant demands of having him upgrade his PostgreSQL version. Okay fair enough.
Bborie suggested why don't you import your letters as rasters and then vectorize them. So that's exactly what we are going to demonstrate in this article
and in doing so we will combine some of the new features coming in PostGIS 2.1 we've been talking about in Waiting for PostGIS 2.1 series.
Warning this article will have a hint of Rube Goldbergishness in it.
If you are easily offended by seeing stupid tricks done with SQL, stop reading now.
We are going to take a natural vector product and rasterize it just so
we can vectorize it again so we can then rasterize it again. Don't think about it too much. It may trigger activity in parts of your brain you didn't know were there
thus resulting in stabbing pains similar to what you experience by quickly guplping down a handful of Wasabi peas.
So here are the steps for creating your own font set you can
overlay on your geometries and rasters.
See if you can spot the use of window functions and CTEs in these examples.
Continue reading "Saying Happy Valentine in PostGIS"
Friday, February 08. 2013
Printer Friendly
PostgreSQL allows you to customize statement logging in numerous ways. You can globally set the level of logging you want at both the postgresql.conf (that will affect all databases) or at the database level using the various log_statement* variables. Most of these
are documented in runtime config logging.
For this brief article, we'll talk about the log_line_prefix variable. By default this variable is very minimalistic and just prefixes the log lines with the date time of the statement. If you have just one database (like the way Oracle runs) this is not a big deal,
but if you are like us and run several databases on one PostgreSQL instance, it would be nice to as part of the prefix include the database name.
Continue reading "How to add database name to log output"
Monday, January 28. 2013
Printer Friendly
This exercise is a continuation of our How to bulk export tables from MS Access and How to recreate MS Access primary keys and indexes in PostgreSQL. As mentioned in the first article, when you use the built-in export feature of MS Access, it exports autonumber fields as integers instead of the more appropriate PostgreSQL pseudo serial type.
The serial type in PostgreSQL is not really a type though, so this behavior is expected. The serial type is really short-hand for defining an integer column and a corresponding sequence object
with a specific naming convention and setting the default value to the next value of the sequence. So this is what we will do in this exercise after we have already exported our data to PostgreSQL. The script we are about to demonstrate will generate an sQL script containing all the CREATE SEQUENCE , and ALTER TABLE ALTER COLUMN SET DEFAULT .. needed to convert our integer column to a serial column.
As a side note, Mark mentioned a similar approach to what we've been describing here, but builds all the table , key structures utilizing python. You can check out the python script
at http://code.activestate.com/recipes/52267-reverse-engineer-ms-accessjet-databases
Continue reading "How to map MS Access auto number to PostgreSQL serial"
|