Saturday, September 19. 2015
Continue reading "Compiling and installing ogr_fdw on CentOS after Yum Install PostgreSQL PostGIS"
After installing PostgreSQL 9.4 and PostGIS following An Almost Idiot's guide to installing PostgreSQL, PostGIS, and pgRouting, on my CentOS 6.7 64-bit except replacing 9.3 references with equivalent 9.4 reference, I then proceeded to install ogr_fdw. To my disappointment, there are no binaries yet for that, which is not surprising, considering there aren't generally any binaries for any OS, except the windows ones I built which I will be packaging with PostGIS 2.2 windows bundle. Getting out of my windows comfort zone, I proceeded to build those on CentOS. Mainly because I have a client on CentOS where ogr_fdw I think is a perfect fit for his workflow and wanted to see how difficult of a feat this would be. I'll go over the steps I used for building and stumbling blocks I ran into in this article with hope it will be of benefit to those who find themselves in a similar situation.
UPDATE pgdg yum now has ogr_fdw as an offering. If you are on PostgreSQL 9.4, you can now install with :
yum install ogr_fdw94
Monday, August 03. 2015
Continue reading "PostgreSQL 9.5 Grouping Sets with PostGIS spatial aggregates"
One of the features coming in PostgreSQL 9.5 is the triumvirate
ROLLUP nicely covered in Bruce's recent slide deck. The neatest thing about PostgreSQL development is that when improvements happen, they don't just affect the core, but can be taken advantage of by extensions, without even lifting a finger. Such is the case with these features.
One of the things I was curious about with these new set of predicates is Would they work with any aggregate function?. I assumed they would, so decided to put it to the test, by using it with PostGIS ST_Union function (using PostGIS 2.2.0 development). This feature was not something the PostGIS Development group planned on supporting, but by the magic of PostgreSQL, PostGIS accidentally supports it. The grouping sets feature is particularly useful if you want to aggregate data multiple times, perhaps for display using the same dataset. It allows you to do it with a single query that in other PostgreSQL versions would require a UNION query. This is a rather boring example but hopefully you get the idea.
Monday, June 29. 2015
Continue reading "PostgreSQL OGR FDW update and PostGIS 2.2 news"
PostGIS 2.2 is planned to reach feature freeze June 30th 2015 so we can make the September PostgreSQL 9.5 curtain call with confidence. Great KNN enhancements for PostgreSQL 9.5 only users. I've been busy getting all my ducks lined up. A lot on tiger geocoder and address standardizer extension to be shipped with windows builds, story for later. One other feature we plan to ship with the windows PostGIS 2.2 builds is the ogr_fdw ogr_fdw Foreign data wrapper extension. I've been nagging Paul Ramsey a lot about issues with it, this in particular https://github.com/pramsey/pgsql-ogr-fdw/issues/25, and after some prodding, he finally put his nose in and fixed them and pinged Even Rouault for some help on a GDAL specific item.
Needless to say, I've been super happy with the progress and support I've gotten with ogr_fdw development and really enjoying my ogr_fdw use. The XLSX reading a file saved after the connection was open required a fix in GDAL 2.0 branch (which missed GDAL 2.0.0 release, so because of this, this new package contains a GDAL 2.0.1ish library. Hopeful GDAL 2.0.1 will be out before PostGIS 2.2.0 comes out so I can release without guilt with this fix.
Saturday, May 23. 2015
Continue reading "PostGIS 2.2 leveraging power of PostgreSQL 9.5"
Things are shaping up nicely in PostGIS 2.2 development. We are going to hit feature freeze around June 30th 2015, and plan to ship late August or early September to be in line with PostgreSQL 9.5 release.
So far we have committed a couple of neat features most itemized in PostGIS 2.2 New Functions.
Many of the really sort after ones will require PostgreSQL 9.5 and GEOS 3.5. The geography measurement enhancements will require Proj 4.9.0+ to take advantage of.
Things I'd like to highlight and then later dedicate full-length articles in our BostonGIS Waiting for PostGIS 2.2 series once they've been stress tested.
Monday, May 04. 2015
Continue reading "Adding properties to existing JSON object with PLV8"
Lately I've been experimenting with building semi-schemaless apps. These are apps
where much of the data may never be used for reporting aside from story telling and also that as time goes by some of these may be revisited
and converted to more structured fields for easier roll-up and reporting. For the front-end UI, I'm using AngularJS which naturally spits out data as JSON and can autobind to JSON data of any complexity.
My stored functions in PostgreSQL take JSON blobs as inputs spit it out into various tables and throws the whole thing in a jsonb field for later consumption (it's a bit redundant). Similarly they return JSON back. One of the things I wanted to be able to do was take this jsonb blob and tack on additional properties from well-structured fields or even a whole set of data like sub recordsets to feed back to my app in JSON.
While there are lots of functions in PostgreSQL 9.3/9.4 that can easily build json objects from records, aggregate rows, etc. I couldn't find a function that allowed me to just add a property to an existing JSON object, so I went to my tried and true old-pal PL/V8 for some comfort. Here is a quickie function I created in PL/V8 that did what I needed. Hopefully it will be of use to others or others might have other ideas of doing this that I missed.
Sunday, March 01. 2015
Continue reading "LATERAL WITH ORDINALITY - numbering sets"
One of the neat little features that arrived at PostgreSQL 9.4 is the
WITH ORDINALITY ANSI-SQL construct. What this construct does is to tack an additional column called
ordinality as an additional column when you use a set returning function in the
FROM part of an SQL Statement.
Tuesday, February 10. 2015
Continue reading "Querying MS Access and other ODBC data sources with OGR_FDW"
If you have the OGR_FDW we discussed in OGR FDW Windows first taste built with ODBC support,
then you can access most any ODBC datasource from PostgreSQL. This is especially useful for Windows users. Two of the data sources I've been experimenting with are SQL Server
and MS Access. In this article, I'll demonstrate how to connect to MS Access with PostgreSQL running on a windows box. I think there is an Access driver for Unix/Linux most robust utilizes java. I won't go there.
Registry hack should no longer be needed for PostGIS 2.2 ogr_fdw bundle and up since these come packaged with newer GDAL libraries that have the fix.
Saturday, January 24. 2015
Continue reading "Using SSL https connections with www_fdw on windows"
One of the foreign data wrappers I included in the PostgreSQL 9.3 Windows FDW bag and PostgreSQL 9.4 Windows FDW bag is the www_fdw extension used for querying web services. Someone asked that since I didn't build curl with SSL support,
they are unable to use it with https connections. The main reason I didn't is that the EDB installs come with ssleay32.dll and libeay32.dll (even the 64-bit) which
are dependencies of curl when built with SSL support. I wanted to minimize the issue of distributing dlls that are packaged with Windows PostgreSQL installers already.
Though this article is specific to using www_fdw on Windows systems, many of the issues are equally applicable to other platforms, so may be worth a read if you are running into similar issues with using specialty SSL certificates on Linux/Unix/Mac.
Thursday, January 22. 2015
Continue reading "Installing PostGIS packaged address_standardizer on Ubuntu"
One of the changes coming to you in PostGIS 2.2 are additional extensions. Two ones close to my heart are the address_standardizer (which was a separate project before, but folded into PostGIS in upcoming 2.2) and the SFCGAL extension for doing very advanced 3D stuff (was just an sql script in older versions, but made an extension in 2.2 and new functions added). We had a need to have address standardizer running on our Ubuntu box,
but since PostGIS 2.2 isn't released yet, you can't get it without some compiling. Luckily the steps are fairly trivial if you are already running PostGIS 2.1.
In this article, I'll walk thru just building and installing the address_standardizer extension from the PostGIS 2.2 code base. Though I'm doing this on Ubuntu,
the instructions are pretty much the same on any Linux, just replacing with your Linux package manager.
Tuesday, January 06. 2015
Continue reading "PLV8 binaries for PostgreSQL 9.4 windows both 32-bit and 64-bit"
I'm still in the middle of building packages for our clients for the packages that aren't normally distributed for windows to make upgrading to PostgreSQL 9.4 smooth. One of those is PL/V8 which we use for some custom functions.
I had mentioned how I build PL/V8 for PostgreSQL windows, and the instructions are a bit out of date., but I put more up to date instructions on my gist page. I tend to use gist a lot as a public scrap book with hopes someone else can learn from my toils and save them some trouble. At some point I should probably get more organized with my scrapbooks.
Saturday, January 03. 2015
Continue reading "Updated Foreign Data Wrappers for PostgreSQL 9.3 Windows"
As stated in last article, I've packaged FDW binaries for PostgreSQL 9.3 windows 32-bit and 64-bit and added in the ogr_fdw one. These we've tested with the standard EDB Vc++ built PostgreSQL windows installs and work fine with those.
This package is an updated list from ones we've distributed before that includes ogr_fdw and recompiled with latests source from www_fdw and file_textarray
Sunday, December 28. 2014
Continue reading "Foreign Data Wrappers for PostgreSQL 9.4 Windows"
As stated in last article, I've packaged FDW binaries for PostgreSQL 9.4 windows 32-bit and 64-bit and added in the ogr_fdw one. These we've tested with the standard EDB VS built PostgreSQL windows installs and work fine with those.
Saturday, December 27. 2014
Continue reading "OGR foreign data wrapper on Windows first taste"
This christmas I received something very special from Paul Ramsey and Even Roualt
as detailed in Foreign Data Wrappers for PostGIS.
It's been something I've been patiently waiting for for 4 years. I think it has a few issues I'm working to replicate, but overall it's much faster than I expected and pretty slick.
So why is ogr_fdw so special, because GDAL/OGR is an avenue to many data sources, NOT JUST GEOSPATIAL. It's the NOT JUST that I am most excited about. Though the focus is geospatial you can use it with non-geospatial datasources,
as we described a long time ago in OGR2OGR for data loading
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.
Friday, November 21. 2014
Continue reading "PostGIS Day synopsis"
Yesterday was PostGIS day or as some may call it, Post GIS day and a couple of interesting things happened this day:
- PostgreSQL 9.4 RC1 came out.
- There were parties and unconferences, many summarized on http://2014.postgisday.rocks
- I managed to entertain myself with a Conway's game of life PostGIS raster Map algebra style and pondered how wonderful PostGIS would be if it could generate animated gifs with some sort of aggregate function; to which I was politely called crazy by some of my fellow PSC friends.
But what was greatest of all and took the cake were these pictures: