Friday, July 01. 2016
Continue reading "regexp_split_to_table and string_to_array unnest performance"
Whenever you need to split a text into multiple records breaking by some delimeter, there are two common options that PostgreSQL provides. The first is
regpexp_split_to_table and then next popular is using the unnest function in combination with string_to_array.
Here is an example using regexp_split_to_table:
FROM regexp_split_to_table('john,smith,jones', ',') AS a;
You can achieve the same result by using the construct:
FROM unnest(string_to_array('john,smith,jones', ',')) AS a;
With short text you won't notice much perfomance difference. But what happens if we pass in a humungous text?
Saturday, December 06. 2014
Oracle FDW is a foreign data wrapper PostgreSQL extension that allows you to read and write to Oracle database tables from a PostgreSQL database. You can get it via the PGXN network or the main website http://laurenz.github.io/oracle_fdw/.
What is new about the latest 1.1.0 release is that there is now support for the Oracle SDO_GEOMETRY type that allows you to map the most common geometry types POINT, LINE, POLYGON, MULTIPOINT, MULTILINE and MULTIPOLYGON to PostGIS geometry type. Much of the spatial plumbing work was done by Vincent Mora of Oslandia. If we have any Windows Oracle users out there, yes there are binaries available for windows for PostgreSQL 9.1- 9.4 for both 32-bit and 64-bit. The FDW does have a dependency on the OCI.dll which I think comes shipped with Oracle products. Unfortunately, we are not Oracle users so can't kick the tires.
Saturday, November 09. 2013
Continue reading "Migrating from SQL Server to PostgreSQL"
Alexander Kuznetsov on SQLblog.com
has an interesting series going entitled with Learning PostgreSQL. In the series he focuses on what it takes to move
a SQL Server database and App to PostgreSQL and highlights some of the key differences between the two platforms that you should watch out for.
I recommend it to any SQL Server developer planning to make the switch to PostgreSQL or any PostgreSQL consultant tasked with the job
and not familiar with the intricacies of SQL Server.
His PostgreSQL feed can be found here here
So far on his list of articles in the series:
Monday, September 09. 2013
Continue reading "PostGIS 2.1 windows bundle"
PostgreSQL 9.3 came out today and we are excited to start using the new features. PostGIS 2.1.0 came out about 2 weeks ago and pgRouting just cut the RC 3 release. For windows PostGIS users who are impatient to try the new suite, binaries
can be found on the Unreleased versions of PostGIS.net windows page.
We are planning an official release sometime probably next week on StackBuilder. We are waiting for release of pgRouting 2.0 before we do which should be out next week. This new 2.1 release will be dubbed the PostGIS 2.1 Bundle since it will have more than just PostGIS. It will include postgis extensions (postgis which includes geometry,raster, geography) , postgis_topology, postgis_tiger_geocoder), address_standardizer extension (a companion to tiger geocoder), and pgRouting 2.0.
For those people running PostGIS 2.0 and PostgreSQL 9.0+, especially (raster and geography) users, I highly recommend you jump to PostGIS 2.1. PostGIS 2.1 is a soft upgrade from 2.0. For raster there are enormous speed improvements and new functions. The ones we are most excited about in raster are the much much much faster ST_Clip and ST_Union functions (which now does multi-band in addition to being faster). These two functions are highly important since they are generally the first step in many raster workflows. Geography has speed improvements for point in poly and a ST_Segmentize function done on the spheroid (important for long range). Geometry has a couple of new functions. The Enhanced 3D functionality provided by SFCGAL is brand new and probably won't be distributed by many package maintainers until PostGIS 2.2 where it will garner a few more features and stability improvements.
Saturday, August 17. 2013
Continue reading "PostGIS 2.1.0 released"
The 2.1.0 release of PostGIS is now available.
The PostGIS development team is proud to release PostGIS 2.1.0.
As befits a minor release, the focus is on speed improvements, more features, and bug fixes.
If you are currently using PostGIS 2.0+, you can go the soft upgrade path:
ALTER EXTENSION postgis UPDATE TO "2.1.0";
If you are running 2.1.0 of beta or an unreleased version
(and the above step fails with error “extension postgis has no update path from version x.x.x to version 2.1.0”)
, you need to first copy the file
in share\extensions\postgis—2.0.3—2.1.0.sql and change the 2.0.3 to the x.x.x noted in the error you are running.
Then follow the above upgrade step again.
Users of 1.5 and below will need to go the hard-upgrade path
documented in manual:
Best served with a bottle of GEOS 3.4.1 and
PostgreSQL 9.3beta2 (planned release September).
Tuesday, April 09. 2013
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;
Sunday, March 17. 2013
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:
General details on what is included:
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.
Saturday, January 19. 2013
We discussed a while back the Text array foreign data wrapper that allows you to register and query a delimited file as if it were a regular table with one array column.
It's probably the FDW we use most often and reminded of that recently when I had to query a 500,000 record resident list tab delimited file to prep for geocoding.
When we upgraded to 9.2 and we could no longer compile, I wrote to Andrew Dunstan about this and he kindly created a 9.2 version.
Unfortunately there are still quite a few FDWs broken as a result of the 9.2 changes and I was hoping to try to apply similar patches to them that I saw Andrew do, but
haven't had the patience or time yet. Anyway we've compiled these for 9.2 under our mingw64-w64 and mingw64-w32 chains using Andrew's 9.2 GitHub stable branch.
and we've tested them using the PostgreSQL EDB windows VC++ compiled versions. We hope you find them as useful as we have.
I'm hoping to add more FDWs to these 9.2 bags once
we have those working again. If you want to compile yourself or compile others, we have instructions in the packaged README.txt.
Friday, January 04. 2013
Continue reading "Unpivoting data in PostgreSQL"
A while ago we demonstrated how to create cross tabulation tables using tablefunc extension aka (Pivot Tables) (basically collapsing
rows into columns).
Recently someone asked me how to do the reverse (convert columns to rows). He found a solution to the problem here: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql
using a combination of array and unnest. That approach is very similar to SQL Server's built-in Pivot SQL predicate.
The solution seemed
nice enough except similar to the SQL Server Unpivot, it required knowing the column names beforehand so very hard to genericize.
So would it be possible to accomplish this feat without knowing the columns names (except for the key) and be able to do it with one SQL statement. I realized that the
PostgreSQL hstore extension fit the bill nicely. In this article I'll demonstrate both approaches by creating a view
Monday, December 31. 2012
We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this:
http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.
As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.
SELECT * into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype = 'T';
DELETE from pg_default_acl WHERE defaclobjtype = 'T';
If anyone else has further input on this, I'd be interested.
Thursday, December 13. 2012
Continue reading "PLV8 1.3 windows binaries for PostgreSQL 9.2"
As Hitoshi Hirada mentioned already in PL/v8 is now the richest procedural language , PL/V8 1.3 has been recently released and some of the great new features are automatic conversion of json objects, and ability to write window functions. Not only does PL/V8 allow you to make the most of the native JSON support in PostgreSQL 9.2, but in many cases particularly numeric processing, the speed is much better than what you get with plpgsql. Someone asked us a while back about this and we are hoping to do a demonstration of pl/v8 for numeric processing where you can see the marked difference in speed and perhaps compare with something like PL/R that is also often used for array numeric processing.
For those who are on windows we've compiled PLv8 1.3 for windows 32-bit and windows 64-bit that work with PostgreSQL EDB installs. We haven't had a chance to thoroughly test them yet, so let us know if you find issues. We've saved the last versions we compiled under a different name since some things are not backward compatible between the PLv8 1.1 and 1.3 releases.
We are also hoping to making our windows binary download links more visible. For stop gap fix, we are tagging all the articles concerning things like FDW extensions, PostGIS, plv8 we've compiled for windows under winextensions and accessible from http://www.postgresonline.com/winextensions.php
Sunday, September 23. 2012
Continue reading "Using PLV8 to build JSON selectors"
Unfortunately we missed Postgres Open this year, but we did catch some of the slides. One of them was Embracing the web with JSON and PLV8 by Will Leinweber of Heroku. He had a great slide deck with a lot of interesting points. One surprising for us was that even in tasks that both PL/PgSQL and PL/V8 can do, PL/V8 is sometimes faster as demonstrated in his slides:
#51 thru #54
In one article we demonstrated a JQuery app with PLV8 and one of JQuery's foundations is the CSS like selector syntax it provides for JSON and HTML document elements which allows you to drill down a document using CSS3 style referencing, much like what xpath does for xml. One of the glaring features missing in PostgreSQL 9.2 basic JSON support is a function to navigate a JSON document comparable to the PostgreSQL built-in xpath function for xml.
So how do we get this json selector goodness available to us in the database? Like all good monkeys, we copy/emulate it.
Saturday, September 22. 2012
We've recompiled our PL/V8 for windows against PostgreSQL 9.2.0 (both 32-bit and 64-bit) with latest PL/V8 code. This includes the additional LiveScript language, which you can use by doing:
CREATE EXTENSION plls;
New release also includes some bug fixes such as Crash case with non-array in array returning function.
We have also put up binaries and installers for PostGIS 2.0.1 for PostgreSQL 9.2 (32-bit and 64-bit) downloadable from PostGIS website. We haven't deployed the installers to Application StackBuilder yet since we are doing some testing before we release to the masses.
Monday, September 17. 2012
A couple of people have expressed concern that we have not released a PostGIS 2.0.1 for Windows 9.2. I guess 9.2 really makes people want to jump and scream for joy. Even if they don't deploy on Windows, Windows is a popular development platform to kick the tires. We originally were planning to not release one and were hoping people would not notice and just start using the PostGIS 2.1.0SVN. That plan evidently did not work. So Yes, we will be releasing a PostGIS 2.0.1 for 9.2 both 32-bit and 64-bit probably late this week. We've got some regression kinks showing in the 9.2 64-bit chain that we are troubleshooting before we release to the masses.
We've been busy beefing up the PostGIS testing and interim build infrastructure to replace the old PostGIS Hudson CentOS build bot. If you are in a super rush and really need a 2.0 micro, you can use the PostGIS 2.0.2SVN binaries, generated by Winnie the PostGIS windows build bot. If you hang around the PostGIS IRC channel, you may have stumbled on Debbie, Winnie's younger and more vocal sister (Debian 64-bit hosted on GoGrid) that does all the document builds and tar balls currently. We are hoping to have Debbie eventually pull PostgreSQL source directly from Git PostgreSQL 9.3, so we can make sure we don't introduce issues for 9.3 along the way and can catch them early.
Friday, August 24. 2012
Continue reading "Creating GeoJSON Feature Collections with JSON and PostGIS functions"
If you do a lot of web-based GIS applications, a common desire is to allow a user to
draw out an area on the map and then do searches against that area and return back a FeatureCollection
where each feature is composed of a geometry and attributes about that feature. In the past the format
was GML or KML, but the world seems to be moving to prefer JSON/GeoJSON. Normally you'd throw
a mapping server that talks Web Feature Service
, do more or less with a webscripting glue, or use a Webservice
such as CartoDb that lets you pass along raw SQL.
In this article we'll demonstrate how to build GeoJSON feature collections that can be consumed by web mapping apps.
the built in JSON functions in PostgreSQL 9.2 and some PostGIS hugging.
Even if you
don't use PostGIS, we hope you'll come away with some techniques for working with
PostgreSQL extended types and also how to morph relational data into JSON buckets.