contrib spotlight - 3   

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.

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.

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.

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

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.

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

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.

I'm not a big proponent of schemaless designs, but they have their place. One particular place where I think they are useful is for archiving of data where even though the underlying table structure of the data you need to archive is changing, you want the archived record to have the same fields as it did back then. This is a case where I think Hstore and the way PostgreSQL has it implemented works pretty nicely.

Side note: one of the new features of PostgreSQL 9.4 is improved GIN indexes (faster and smaller) which is very often used with hstore data (and the new jsonb type). We're really looking forward to the GIN improvements more so than the jsonb feature. We're hoping to test out this improved index functionality with OpenStreetMap data soon and compare with our existing PostgreSQL 9.3. OpenStreetMap pbf and osm extract loaders (osm2pgsql, imposm) provide option for loading tagged data into PostgreSQL hstore fields, in addition to PostGIS geometry and other attribute fields. So 9.4 enhancements should be a nice gift for OSM data users. More on that later.

In this exercise, we'll go thru installing PostgreSQL 9.3 on a CentOS 6 64-bit box. We'll cover upgrading in a later article. For the rest of this article, we'll go over configuring yum to use the PostgreSQL PGDG Yum repository found at http://yum.postgresql.org , which has the latest and greatest of 9.3. It's been a while since we wrote step by step instructions for installing with Yum.

In PostgreSQL 9.3 Postgres_FDW: A Test Drive we talked about taking Postgres Foreign Data Wrapper for a test drive. One downside of the Postgres FDW and actually most PostgreSQL FDWs is that the foreign table structure can't be inspected from the source and the fields have to be explicitly stated in the foreign table definition. If you have a lot of tables to script, this can quickly become tedious. For our planned used cases, we plan to script Foreign tables from a source database for tables that are bulky and rarely change and then build materialized views against those for faster performance where needed. To help in this end, we wrote a quick SQL function that you install on the source database and when run will generate foreign table creation statements to install on a target database.

One thing that was important to us was that the function properly handle views and typmod types since PostGIS now uses typmod heavily and many of our databases have spatial data and complex views we'd like to link in as foreign tables.

We have PostGIS intro presentation we did in September in 4 parts at https://www.youtube.com/playlist?list=PLHPJPLnP-bUWZShKrUIdbJM5dig8oavzQ.

In the Intro we covered PostGIS geometry, tiger geocoder, and raster. Sadly we didn't have time to cover geography or topology.

PostGIS 2.1 bundle for PostgreSQL 9.3 Windows is now available on StackBuilder for windows users. You will find both the 32-bit and 64-bit versions. The bundle includes the following features:

• PostGIS 2.1 with extensions support for postgis (which has geometry/geography/raster), postgis_tiger_geocoder, and postgis_topology.
• pgRouting 2.0.0 with extension support -- just do CREATE EXTENSION pgRouting
• address_standardizer, which is needed for better tiger geocoding normalizing. Installed with CREATE EXTENSION address_standardizer

One thing that is different with this packaging is that we are no longer providing a template_postgis database. However if you want your database created for you, you can check the Create spatial database option which will create a regular old database with all the packaged extensions under the sun created for you.

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.

One of the new features in PostgreSQL 9.3 is CREATE SCHEMA IF NOT EXISTS someschema;. We were so excited about this new feature that we started using it in the tiger geocoder loader routine. For some reason we thought it was available since 9.1 which gained CREATE TABLE IF NOT EXISTS sometable; which we noted in Sweat the small stuff, it really matters

Two of the big changes in PostGIS 2.1 raster are the improved speed and functionality of the raster ST_Union function and ST_Clip. Aside from speed, the big thing with ST_Union in 2.1 is that it applies operations to all bands by default. These are our most favorite funcitons of all. This is a continuation of Word Play with spatial SQL, except we'll be generating rasters instead of geometries and exercising some raster functions in addition to geometry functions.

Although these SQL statements look long and somewhat complicated, they are easily wrappable in an SQL function. We have, for example, an sql function to write letters on parcels that just takes as input the parcel id and the words to write.

This uses the postgis_letters extension, which we've finally put up on github postgis_letters. For rendering the images, we used our quickie viewer which relies on ASP.NET or PHP and JQuery. We'll be putting that up on github as well once we've dusted it off a bit.

If you are interested in the aerial and parcel geometry data we are using here, we grabbed it from MassGIS for Cambridge, Massachusetts area. You might recognize the base query in our upcoming DZone PostGIS refCard.

So here it goes. An exercise in raster expression.