Monday, May 17. 2010
Printer Friendly
Pierre Racine has been diligently working on PostGIS WKT Raster development. He was recently creating an sql function that
uses output parameters.
That was all nice and well, except he couldn't figure out how to output the output parameters as columns.
The function looked something like this:
CREATE FUNCTION somefunction(rast raster, OUT field1 integer, OUT field2 sometype, etc.) AS
$$ blah blah blah $$
LANGUAGE 'sql';
Continue reading "Output parameters, custom data type gotchas"
Saturday, April 17. 2010
Printer Friendly
We just finished the first draft of the last chapter of our book: First look at PostGIS WKT Raster. This completes our hard-core writing and now on to more drafting,
polishing all the chapters.
In Chapter 13 we demonstrate how to use PostGIS WKT Raster functions by example and cross breed with PostGIS geometry functionality. I was pleasantly surprised to see how nicely the raster and geometry functions play together.
We had intended this chapter to be short about 20 pages in length, because how much can one say about pixels and pictures. As it turns out, a lot.
Rasters are more versatile than their picture portrayal on a screen. Rasters are a class of structured storage suitable for representing any numeric,
cell based data where each cell has one or more numeric properties (the bands). This covers quite a bit of data you collect with remote sensing and other electronic instrumentation. We had to stretch to over 30 pages; even then we felt we were missing some critical examples.
There is a lot of useful functionality in PostGIS WKT Raster
already and should make a lot of people looking for raster support in PostgreSQL very happy. Although the chapter may portray some scenes of violence and torture inflicted on elephants, you can rest assured
that it is pure illusion and no real elephants or blue elephant dolls were harmed in the making of this chapter.
As a side note -- our book is now listed on Amazon PostGIS in Action.
It is not available in hard-copy yet,but you can pre-order and of course you can order from PostGIS in Action from Manning directly
to get the chapter drafts we have posted, updates as we polish them, and the final book when it comes out in hard print.
The Amazon listing would have been so much more exciting, had they not stripped me of my last name or had Leo married to himself.
UPDATE: It appears I now have a last name again
In hind sight, I suppose OBE is more commonly seen as a title of honor rather than a last
name, so its only fitting that I should be stripped of mine and Tim Berners-Lee gets it tacked on at the end of his name.
To find out more about PostGIS WKT Raster, we encourage you to check out these links.
Now we'll itemize 10 things you can do now with PostGIS WKT Raster. In order to use PostGIS WKT Raster, you need PostGIS 1.3.5 or above. Preferably 1.4 or 1.5 or 2.0 alpha.
PostGIS WKT Raster is currently packaged as a separate library and we have windows binaries available.
Continue reading "PostGIS Raster its on: 10 things you can do NOW with raster"
Sunday, February 14. 2010
Printer Friendly
Every programmer should embrace and use regular expressions (INCLUDING Database programmers).
There are many places where regular expressions can be used to reduce a 20 line piece of code into a
1 liner. Why write 20 lines of code when you can write 1.
Regular expressions are a domain language just like SQL. Just like SQL they are embedded in many places. You have them in your program editor.
You see it in sed, grep, perl, PHP, Python, VB.NET, C#,
in ASP.NET validators and javascript for checking correctness of input. You have them in PostgreSQL as well where
you can use them in SQL statements, domain definitions and check constraints. You can mix
regular expressions with SQL. When you mix the two domain languages, you can do enchanting things with a flip of a wrist that
would amaze your less informed friends. Embrace the power of domain languages and mix it up. PostgreSQL
makes that much easier than any other DBMS we can think of.
For more details on using regular expressions in PostgreSQL, check out the manual pages Pattern Matching in PostgreSQL
The problem with regular expressions is that they are slightly different depending on what language environment you are
running them in. Different enough to be frustrating. We'll just focus on their use in PostgreSQL, though these lessons
are applicable to other environments.
Continue reading "Regular Expressions in PostgreSQL"
Sunday, February 07. 2010
Printer Friendly
PostGIS 1.5.0 is finally out
I'm happy to report that after a long haul, we have finally released PostGIS 1.5.0.
Two months late, but there it is, and its a really great release I think; Perhaps the best release ever.
Details on what makes this release so special. The geodetic support.
Summary excerpted from Paul's slightly premature announcement
February 4, 2010
The PostGIS development team has, after a long course of reflection
and a detailed self-examination of our various personal failings,
decided to release PostGIS 1.5.0 to the public.
http://postgis.org/download/postgis-1.5.0.tar.gz
This new version of PostGIS includes a new "geography" type for
managing geodetic (lat/lon) data, performance-enhanced distance
calculations, GML and KML format readers, an improved shape loading
GUI, and other new features as well.
Especial thanks to:
* Dave Skea for algorithms and mathematics necessary to support
spherical geometry
* Nicklas Avén for the new performance enhanced distance calculations
and other distance-related functions
* Sandro Santilli for new buffering features (end caps and style options)
* Olivier Courtin for GML/KML input functions
* Guillaume Lelarge for support for the upcoming PgSQL 9.0
* George Silva for an example implementation of history tables
* Vincent Picavet for Hausdorff distance calculations
* The maintainers of GEOS, Proj4, and LibXML, without whom we would
have less of a spatial database
Love, the PostGIS project steering committee,
Mark Cave-Ayland
Kevin Neufeld
Regina Obe
Paul Ramsey
Continue reading "PostGIS 1.5.0 out and PLR working on Windows 8.3-8.4 installs"
Thursday, January 21. 2010
Printer Friendly
Every once in a while, especially if you have a fairly large database, you may find the need to do select backups of certain tables.
Your criteria might be based on name or how relatively recently data has changed in the table.
Below are some of the tricks we use. Some use our favorite hack of scripting command line scripts with SQL.
Continue reading "Making backups of select tables"
Saturday, January 09. 2010
Printer Friendly
UPDATE: Thanks all for the suggestions. For now we ended up increasing the
seq_page_cost from 1 to 2 in the database. That has gotten us back to our old much much faster speeds without change in code and seems to have
improved the speeds of other queries as well, without reducing speed of any.
ALTER DATABASE mydb SET seq_page_cost=2;
As Jeff suggested, we'll try to come up with a standalone example that exhibits the behavior. The below example was more to demonstrate the construct. Table names and fields were changed to protect the innocent so that is why we didn't bother showing explain plans. The behavior also seems to do
with the distribution of data and gets worse when stats are updated (via vacuum analyze). Didn't see this in PostgreSQL 8.3 and this was a system recently upgraded from 8.3 to 8.4
---ORIGINAL ARTICLE HERE --
This is a very odd thing and I think has happened to us perhaps once before.
Its a bit puzzling, and we aren't particularly happy with our work around because its
something that looks to a casual observer as a bit bizarre. The hack is setting the enable_seqscan setting
off for a particular query to force the planner to use indexes available to it.
What is particularly troubling about this problem, is that it wasn't always this way.
This is a piece of query code we've had in an application for a while, and its worked shall
I say really fast. Response times in 300 ms - 1 sec, for what is not a trivial query against a not
so trivially sized hierarchy of tables.
Anyrate, one day -- this query that we were very happy with, suddenly started
hanging taking 5 minutes to run. Sure data had been added and so forth, but that didn't
completely explain this sudden change of behavior. The plan it had taken had changed drastically.
It just suddenly decided to stop using a critical index it had always used. Well it was still using it but just on
the root table, not the children. Though querying a child directly proved that it still refused to use it,
so it didn't seem to be the hierarchy at fault here.
Continue reading "Forcing the planner's hand with set enable_seqscan off WTF"
Thursday, December 31. 2009
Printer Friendly
This was a truly exciting year for us and the PostgreSQL project and perhaps a bit depressing for MySQL.
The following events happened:
- PostgreSQL 8.4 was released which had blow away features like Common Table Expressions (CTE) , Recursive CTEs, and Windowing Functions. This meant we could finally get some of our hard-core Oracle and SQL server friends really excited about PostgreSQL.
- This is the first year we got out of our shy mode and actually presented at conferences. We presented at PGCon 2009 and OSCON 2009.
- The PostGIS project steering committee was formed with Regina as one of the founding members
- We started writing our PostGIS in Action due out sometime in 2010. Sadly we are a bit behind schedule, but on the bright side, you can buy the book now and it will probably be a bit heftier than the 325 pages we had planned. To celebrate our upcoming book, we have launched our book promo site PostGIS in Action: The Book where the adventure begins. There you will find source code downloads, data, presentations as we put each together. You will also see a brief description of chapters , our progress with each chapter, what you can expect from each chapter, and related links to the chapter content. We are currently at what we hope is our last quarter sprint.
- We wrote a DZone cheatsheet which was confronted with mixed emotions.
- 2009 was also the year Oracle threatened to buy Sun and engulf MySQL in the process. Interestingly this was predictable in someone's wildest dreams. Is this the end of Open source databases as we know it? Only time will tell.
Plans for 2010
What are our plans for 2010?
- Get PostGIS 1.5 out the door some time in January 2010
-
We hope in 2010 to present at at least one PGCon conference and hopefully make FOSS4G 2010 in September 2010. Our book better be written by then.
- Finish our book and hopefully soon.
- Increase the adoption of PostgreSQL and PostGIS significantly. To paraphrase our favorite Larry's famous words our strategy is to Get big very fast.
- Get PostGIS 2.0 out the door sometime in late 2010.
What will happen to the database industry in 2010
I usually try to keep my mouth shut on these topics. I must say that I have noticed a bit of animosity from some PostgreSQL people toward the whole MySQL/Oracle affair, comments like He lives by the sword, he should die by the sword. Other interesting conjectures as to what this means for Open Source databases, Is Monty right that the apparent rape of MySQL by Oracle is only bad and will cause countless pain and suffering for many. All I can say is "What..ever".
Some argue that Monty's fight is all about money and some don't that he is earnestly trying to save the world from Oracle. To me its a fight about a man who has spent half his life nurturing this child MySQL named after his own son. Of course he has quite a bit of emotional attachment to it, as many in the PostgreSQL clan have an equal attachment to PostgreSQL and we have an equal non-economic (as well as economic) attachment to PostGIS and PostgreSQL. Equally so I'm sure Larry Ellison has perhaps a stronger attachment to the namesake Oracle database named after a CIA project he spent more than half his life nurturing.
So in short the motives on all side are clear and irrelevant to all except said people with said motives. In the end, what is relevant is what is relevant.
Saturday, November 28. 2009
Printer Friendly
In this exercise, we'll go thru our steps for upgrading a Redhat Enterprise Linux 64-bit PostgreSQL
box from PostgreSQL 8.3 to PostgreSQL 8.4. If you don't have any kind of PostgreSQL installed on your box,
you can skip the Upgrade step.
UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use
this one instead since it seems more up to date than the other. http://yum.pgrpms.org/
UPDATEWe have instructions for installing PostgreSQL 9.0 via yum.
Updgrading from PostgreSQL 8.* to PostgreSQL 8.4
If you are starting from scratch -- just skip this section.
If you are upgrading from 8.4.0 to 8.4.1 you can get away with a simple
yum update postgresql and skip the rest of this article.
If you are upgrading from PostgreSQL 8.3 to 8.4, in theory you can use PgMigrator, but in practice,
particularly with a Yum install, you are bound to run into obstacles. If you are running an older version, you must dump and restore.
Continue reading "An almost idiot's guide to Install and Upgrade to PostgreSQL 8.4 with Yum"
Friday, November 06. 2009
Printer Friendly
The upcoming version of PostGIS - PostGIS 1.5 will be an exciting one. It has native geodetic support in the form of the new
geography type, similar in concept to SQL Server's geography support. For windows users, we have experimental binary builds hot off the presses for PostgreSQL 8.3 and 8.4
Continue reading "PostGIS does Geography"
Monday, October 26. 2009
Printer Friendly
This is an unfortunate predicament that many people find themselves in and does cause a bit of frustration. You bring in some tables into your PostgreSQL
database using some column name preserving application, and the casings are all preserved from the source data store. So now you have to quote all the fields
everytime you need to use them. In these cases, we usually rename the columns to be all lower case using a script. There are two approaches we have seen/can think of for doing this
one to run a script that generates the appropriate alter table statements and the other is to update the pg_attribute system catalog table directly.
Continue reading "Lowercasing table and column names"
Wednesday, October 21. 2009
Printer Friendly
Vacuuming and analyzing is the process that removes dead rows and also updates the statistics of a table.
As of PostgreSQL 8.3, auto vacuuming (the process that runs around cleaning up tables), is on by default. If you are
creating a lot of tables and bulk loading data, the vacuumer sometimes gets in your way. One way to get around that is to
disable auto vacuuming on the tables you are currently working on and then reenable afterward.
You can also do this from the PgAdmin III management console.
Continue reading "Enable and Disable Vacuum per table"
Monday, October 05. 2009
Printer Friendly
In our prior story about allocating people with the power of window aggregation, we saw our valiant hero and heroine trying
to sort people into elevators
to ensure that each elevator ride was not over capacity. All was good in the world until someone named Frank came along and spoiled the party.
Frank rightfully pointed out that our algorithm was flawed because should Charlie double his weight, then we could have one elevator ride over capacity.
We have a plan.
Continue reading "Allocating People into Groups with SQL the Sequel"
Monday, September 28. 2009
Printer Friendly
This is along the lines of more stupid window function fun and how many ways can we abuse this technology in PostgreSQL. Well actually we were using this approach to allocate geographic areas such that each area has approximately the same population
of things. So you can imagine densely populated areas would have smaller regions and more of them and less dense areas will have larger regions but fewer of them (kind of like US Census tracts).
So you have to think about ways of allocating your regions so you don't have a multipolygon where one part is in one part of the world and the other in another etc. Using window aggregation is one approach in conjunction with spatial sorting algorithms.
The non-spatial equivalent of this problem is how do you shove people in an elevator and ensure you don't exceed the capacity of the elevator for each ride. Below is a somewhat naive way of doing this.
The idea being you keep on summing the weights until you reach capacity and then start a new grouping.
Continue reading "Allocating People into Groups with Window aggregation"
Sunday, September 20. 2009
Printer Friendly
A while ago we wrote about DZone RefCards cheatsheets and how its a shame there isn't one for PostgreSQL. They are a very attractive and useful vehicle for learning and brushing up on the most important pieces
of a piece of software or framework. Since that time we have been diligently working on one for PostgreSQL to fill the missing PostgreSQL slot. The fruits of
our labor are finally out, and a bit quicker than we expected. The cheatsheet covers both old features and new features introduced in PostgreSQL 8.4. We hope its useful to many old and new PostgreSQL users.
The Essential PostgreSQL Refcard can be downloaded from Essential PostgreSQL http://refcardz.dzone.com/refcardz/essential-postgresql?oid=hom12841
Wednesday, September 09. 2009
Printer Friendly
One of the small little treats provided in PostgreSQL 8.4 is the new pg_terminate_backend function. In the
past when we wanted to kill runaway postgresql queries issued by a database or user or hmm us, we would call the pg_cancel_backend function.
The problem with that is it would simply cancel the query in the backend process, but often times the offending application would simply launch the same query again.
In PostgreSQL 8.4 a new function was introduced called pg_terminate_backend. This doesn't completely replace pg_cancel_backend, but basically does
what you do when you go into say a Windows Task manager and kill the offending postgres process or on Linux, you call a kill command on a postgres process. Its nicer
in the sense that you can do it all within PostgreSQL and you can use the pg_stat_activity query to help you out a bit. Also you don't run the risk as easily of
killing the root postgres process and killing the postgres service all together.
Continue reading "Terminating Annoying Back Ends"
|