
About the Authors
Consulting
PostgreSQL
PostGIS
Sunday, February 07. 2010PostGIS 1.5.0 out and PLR working on Windows 8.3-8.4 installsPostGIS 1.5.0 is finally outI'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"
Posted by Leo Hsu and Regina Obe
in 8.3, 8.4, pl programming, PLR, postgis
at
00:58
| Comments (0)
| Trackbacks (0)
Thursday, January 21. 2010Making backups of select tablesEvery 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"
Posted by Leo Hsu and Regina Obe
in 8.2, 8.3, 8.4, 9.0, basics, beginner
at
00:55
| Comments (0)
| Trackbacks (0)
Saturday, January 09. 2010Forcing the planner's hand with set enable_seqscan off WTFUPDATE: 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.
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" Wednesday, January 06. 2010Looking forward to PostgreSQL 8.5Ah a new year, a new PostgreSQL release in the works. Beware -- this post is a bit sappy as we are going to highlight those that have made our lives and lives of many a little easier. These are people we consider the most important because they provide the first impression that newcomers get when first starting off with PostgreSQL. The newcomer that quickly walks out the door unimpressed, is the easy sale you've lost. Make your pitch short and sweet. As always Hubert does a really good job of taste testing the new treats in the oven and detailing how cool they are. I highly suggest his posts if people have not read them already or are looking at PostgreSQL for the first time. You can catch his Waiting for PostgreSQL 8.5 series which is in progress. Surely gives us a list of things to test drive. Then there are those that document, the volumes of PostgreSQL documentation which are just great, up to date and rich with content. Probably too many of these people to call out, and sadly we don't know them by name. Of course its not just enough to announce releases, document them and talk about them, you must make it really easy for people to try them out. If people have to compile stuff, especially windows users, forget about it. You won't hear complaints, you won't hear whispers, you'll hear dust blowing. The biggest audience you have is the one you just lost because you didn't make it easy for them to try your stuff. The apple hit me on the head one day when a very dear friend said to me and here is a slight paraphrase. You don't actually expect me to compile this myself do you? How much time do you think I have? It is not about you, it is about me.. This was especially surprising coming from a guy I always thought of as selfless. This I realized is the biggest problem with many open source projects, that they are lost in the flawed mentality that its about scratching their own itch and the rest will come. It is not. Always concentrating on your own itch and scratching it is a sure way of guaranteeing that no one will scratch your itch for you. Think of it like a pool game. Do you target the aim at the ball you are trying to hit, or balls near by that will knock down the others. So in short don't be a complete wuss that people can walk all over, but look past your nose and choose your balls wisely; make sure all your balls are not focused on software development. Continue reading "Looking forward to PostgreSQL 8.5"
Posted by Leo Hsu and Regina Obe
in 9.0, mysql, new in postgresql, postgis, sql server
at
04:14
| Comments (5)
| Trackbacks (0)
Friday, January 01. 2010Compiling PostGIS 1.5 and installing after Yum PostgreSQL InstallA couple of people have asked this, so thought we would address the common issues people run into with compiling PostGIS. The first question, some will ask is Why don't you just take the packaged PostGIS that comes with the PostgreSQL Yum repository? There are two reasons for not installing PostGIS under using Yum and our general reasons for not.
NOTE: PostGIS 1.3, PostGIS 1.4, PostGIS 1.5 can coexist on the same PostgreSQL daemon service as long as they are installed in different databases. They will all share the same Proj and GEOS. So installing PostGIS 1.5 will not break your PostGIS 1.4 or 1.3 installs. The newer GEOS 3.2.0 C-API is backward compatible with older GEOS C-API and the C-API is what PostGIS uses. Continue reading "Compiling PostGIS 1.5 and installing after Yum PostgreSQL Install"
Posted by Leo Hsu and Regina Obe
in basics, gis, intermediate, postgis, yum
at
13:48
| Comments (2)
| Trackbacks (0)
Thursday, December 31. 2009Year in reviewThis was a truly exciting year for us and the PostgreSQL project and perhaps a bit depressing for MySQL. The following events happened:
Plans for 2010What are our plans for 2010?
What will happen to the database industry in 2010I 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.
Posted by Leo Hsu and Regina Obe
in 8.4, editor note, gis, postgis
at
22:59
| Comments (0)
| Trackbacks (0)
Thursday, December 24. 2009PgAdmin III Plug-in Registration: PostGIS Shapefile and DBF LoaderPgAdmin 1.9+ has a simple Plug-In architecture which makes it relatively simple to introduce new plugins. It is pretty much all controlled by the file plugins.ini. In that file you can register any executable you want in there. If you want the executable to get passed database configuration settings, there is an option for that and you just have to build your executable to accept commandline switches. In PostGIS 1.4, Paul Ramsey created this nice Graphical User Interface to the PostGIS shapefile loader, because well, not everyone likes command-line interfaces, particularly people new to PostGIS. All was great except I couldn't get the thing to compile under windows. After much whining on my part, Paul being the gracious guy he is got off his Mac soapbox and disgusted himself by debugging this thing in his slow and clunky windows VM. After a lot of sweat and tears, we have a GUI for the upcoming PostGIS 1.5 that not only is an easy to use non-threatening to newbies interface, but that also fits in nicely as a Plug-in for PgAdmin III (thanks to my whining). PostGIS 1.5 is eminent once we stop fighting over the whole PostgreSQL PGXS annoyance and how to hack or not hack around it. This whole argument really deserves its own Dilbert comic strip.. Although the GUI is designed for PostGIS 1.5, you can use it to load PostGIS 1.3 and PostGIS 1.4 data as well. Mark Cave-Ayland threw in a nice loading touch to it too. It also adds to the mix, ability to load into the new geography type introduced in PostGIS 1.5. The commandline version also has geography type support. You can download the windows version from http://www.postgis.org/download/windows/experimental.php. If you just want it without the PostGIS 1.5 binaries -- just download the one labeled PostGIS ESRI Shapefile GUI. Continue reading "PgAdmin III Plug-in Registration: PostGIS Shapefile and DBF Loader"
Posted by Leo Hsu and Regina Obe
in beginner, contrib spotlight, pgadmin, postgis
at
14:19
| Comments (0)
| Trackbacks (0)
Saturday, November 28. 2009An almost idiot's guide to Install and Upgrade to PostgreSQL 8.4 with YumIn 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. Updgrading from PostgreSQL 8.* to PostgreSQL 8.4If 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 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"
Posted by Leo Hsu and Regina Obe
in 8.2, 8.3, 8.4, basics, beginner, yum
at
04:48
| Comments (5)
| Trackback (1)
Sunday, November 08. 2009Wink: Making screencast tutorialsDebugmode Wink is a freeware piece of software for both business as well as personal use for doing screencasts and incorporating sound into your screen captures. The main useful format it outputs to is macromedia flash though you can output to PDF for handouts and so forth. While its not a PostgreSQL related item per se, it can be useful for making all sorts of tutorials including PostgreSQL tutorials that involve showing people how to do things on screen. We've started to experiment with tutorials of this format and hope to get into doing more hands on like tutorials. We'll probably be doing a using PostgreSQL in OpenOffice tutorial in this issue since it lends itself well to screen casting. Continue reading "Wink: Making screencast tutorials" Friday, November 06. 2009PostGIS does GeographyThe 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"
Posted by Leo Hsu and Regina Obe
in 8.3, 8.4, gis, new in postgresql, oracle, postgis, sql server
at
06:42
| Comments (3)
| Trackback (1)
Monday, October 26. 2009Lowercasing table and column namesThis 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"
Posted by Leo Hsu and Regina Obe
in 8.2, 8.3, 8.4, basics, beginner
at
02:44
| Comments (6)
| Trackbacks (0)
Thursday, October 22. 2009Beyond Nerds Bearing Gifts: The Future of the Open Source EconomyThis week is a busy week for events. While PostgreSQL is having its PostgreSQL West conference in Seattle, the biggest Open Source GIS conference of the year is happening in Sydney, Australia FOSS4G 2009. Sadly given our schedule and the distances of the commutes, we couldn't make either conference. Mateusz Loskot pointed out that the video is out for Paul Ramsey's FOSS4G 2009 Keynote speech on Beyond Nerds Bearing Gifts: The Future of the Open Source Economy. I think its a very important distinction Paul makes between selling software and selling a product, that a lot of people miss when trying to evaluate the solvency of open source software. For those who don't know Paul, he's one of the co-founders of the PostGIS project and Refractions Research.
Posted by Leo Hsu and Regina Obe
in editor note, gis, postgis
at
04:10
| Comment (1)
| Trackbacks (0)
Wednesday, October 21. 2009Enable and Disable Vacuum per tableVacuuming 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. 2009Allocating People into Groups with SQL the SequelIn 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"
Posted by Leo Hsu and Regina Obe
in 8.4, advanced, cte, postgresql versions, q&a, window functions
at
00:55
| Comments (0)
| Trackbacks (0)
Monday, September 28. 2009Allocating People into Groups with Window aggregationThis 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"
Posted by Leo Hsu and Regina Obe
in 8.4, intermediate, postgis, q&a, window functions
at
15:58
| Comments (2)
| Trackbacks (0)
(Page 1 of 10, totaling 143 entries)
» next page
|
QuicksearchCalendar
CategoriesSyndicate This BlogBlog Administration |
||||||||||||||||||||||||||||||||||||||||||
