ogr_fdw   

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 16 Windows 64-bit.

To use these, copy the files into your PostgreSQL 16 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

These were compiled against PostgreSQL 16rc1, but should work fine against EDB PostgreSQL 16beta3.

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 12 Windows 64-bit.

To use these, copy the files into your PostgreSQL 12 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.

To use these, copy the files into your PostgreSQL 11 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

I gave a talk at PGConf US 2019 on some of the many ways you can load data into PostgreSQL using open source tools. This is similar to the talk I gave last year but with the addition of the pgloader commandline tool and the http PostgreSQL extension.

HTML slides PDF slides

Even though it was a talk Not much about PostGIS, but just tricks for loading data, I managed to get a mouthful of PostGIS in there.

PostGIS 2.5.1 was released on November 18th 2018 and I finished off packaging the PostGIS 2.5.1 windows builds and installers targeted for PostgreSQL EDB distribution this weekend and pushing them up to stackbuilder. This covers PostgreSQL 9.4-11 64-bit and PostgreSQL 95-10 (32bit).

Note that PostGIS 2.5 series will be the last of the PostGIS 2s. Goodbye PostGIS 2.* and start playing with the in-development version of PostGIS 3. Snapshot binaries for PostGIS 3.0 windows development are also available on the PostGIS windows download page. These should work for both BigSQL and EDB distributions.

At PGOpen 2018 in San Francisco, we gave a talk on 10 ways to load data into Posgres. This is one of the rare talks where we didn't talk much about PostGIS. However we did showcase tools ogr_fdw, ogr2ogr, shp2pgsql, which are commonly used for loading spatial data, but equally as good for loading non-spatial data. Below are the slide links.

Leo and I attended PostgresVision 2018 which ended a couple of days ago.

We gave a talk on spatial extensions with main focus being PostGIS. Here are links to our slides PostgresVision2018_SpatialExtensions HTML version PDF.

Unfortunately there are no slides of the pgRouting part, except the one that says PGRouting Live Demos because Leo will only do live demos. He has no fear of his demos not working.

Side note, if you are on windows and use the PostGIS bundle, all the extensions listed in the PostGIS box of the spatial extensions diagram, as well as the pointcloud, pgRouting, and ogr_fdw are included in the bundle.

We've updated our binaries for PostgreSQL 10 windows, both 32 and 64-bit. The 64-bit should work fine with EnterpriseDb windows as well as BigSQL.

A while ago when Foreign Data Wrappers in PostgreSQL was a fairly new thing, we talked about the ODBC_FDW foreign data wrapper. Since then, people have been asking us how to get the ODBC FDW to work on newer PostgreSQL. Sadly the ODBC_FDW was stuck in time not having updated to newer FDW API standards. Our recommendation was just to use OGR_FDW, which many distributions both Linux and Windows have compiled OGR_FDW with ODBC support. True that OGR_FDW is coined as a spatial data wrapper, but the reality is spatial data rarely lives apart from regular attribute data so a good spatial vector driver supports both vector data and bread and butter data types. OGR_FDW is still our go to for working with spreadsheets and folders of CSV files.

Recently the fine folks at Carto patched the ODBC FDW to work with PostgreSQL 9.5. I do hope they accept my modest patch to make it work with PostgreSQL 9.6 as well. So now 2 FDWs to choose from for connecting to ODBC datasources. Which one is better? The answer as most always is IT DEPENDS.

We gave a PostGIS Intro Training and a PostGIS talk at PGConfUS 2016 in Brooklyn, New York and just got back. A number of people asked if we'd make the slides and material available. We have these posted on our presentation page: http://www.postgis.us/presentations and will be putting on the PostgreSQL Wiki as well in due time. There will be a video coming along for the talk, but the training was not recorded.

We also have two more talks coming up in North Carolina in Early May at FOSS4G NA 2016 - one on PostGIS Spatial Tricks which has more of a GIS specialist focus than the top 10 talk we gave, but there will be some overlap. The other talk is a topic a couple of people asked us in training and after our talk, on routing along constrained paths. If you are attending FOSS4G NA 2016, you won't want to miss our talk pgRouting: A Crash Course which is also the topic of our upcoming book.

Just like FOSS4G NA 2015, there is a pgDay track which is PostgreSQL specific material, useful to a spatial crowd, but not PostGIS focused.

Someone reported recently on PostGIS mailing list, that they were unable to install PostGIS 2.2.1 bundle or PostGIS 2.2.2 binaries on a clean PostgreSQL 9.5.2 install. Someone also complained about PostgreSQL 9.3 (though not clear the version) if that is a separate issue or the same. I have tested on PostgreSQL 9.5.2 Windows 64-bit and confirmed the issue. The issue does not affect PostgreSQL 9.5.1 and older. I haven't confirmed its an issue with the 32-bit installs, but I suspect so too. This issue will affect OGR_FDW users and people who used our compiled WWW_FDW.

FDW binaries for PostgreSQL 9.5 windows 64-bit/32-bit

As you may have noticed we've been building our favorite extensions which we'll use in our PostgreSQL 9.5 installs. Next on our list are the FDWS

Below are the zip files that contain the binaries and dependency files for our favorite FDWs (that aren't normally available for windows)

These packages contain www_fdw and file_textarray_fdw

PostgreSQL 9.4 and below doesn't support importing whole set of tables from a FOREIGN server, but PostgreSQL 9.5 does with the upcoming Import Foreign Schema. To use will require FDW wrapper designers to be aware of this feature and use the plumbing in their wrappers. IMPORT FOREIGN SCHEMA for ogr_fdw come PostgreSQL 9.5 release is on the features ticket list.

The ogr_fdw comes with this to die for commandline utility called ogr_fdw_info that does generate the table structures for you and will also list all the tables in the Foreign data source if you don't give it a specific table name. So with this utility I wrote a little hack involving using PostgreSQL COPY PROGRAM feature to call out to the ogr_fdw_info commandline tool to figure out the table names and some DO magic to create the tables.

Though ogr_fdw is designed to be a spatial foreign data wrapper, it's turning out to be a pretty nice non-spatial FDW as well especially for reading spreadsheets which we seem to get a lot of. This hack I am about to demonstrate I am demonstrating with LibreOffice/OpenOffice workbook, but works equally well with Excel workbooks and most any data source that OGR supports.

PostgreSQL 9.5RC1 got released recently, and as with PostgreSQL 9.5beta2, the FDW API changed just enough so that the ogr_fdw I compiled for PostgreSQL 9.5beta2 no longer worked for PostgreSQL 9.5RC1. While patching up ogr_fdw to make it work with PostgreSQL 9.5RC1, I took a study of postgres_fdw to see how much effort it would be to implement this new PostgreSQL 9.5 Import Schema functionality for my favorite fdw ogr_fdw. Took me about a day's work, and if I was more experienced, it would have been probably only an hour to graft the logic from postgres_fdw and the ogr_fdw_info that Paul Ramsey had already done, to achieve Import Foreign Schema nirvana. Here's hoping my ogr_fdw patch gets accepted in some shape or form in time for PostgreSQL 9.5 release and in time to package for Windows PostGIS 2.2 Bundle for PostgreSQL 9.5.

We just pushed out installers for PostGIS 2.2.0 for PostgreSQL 9.5beta2 windows both 32-bit and 64-bit on Application Stackbuilder. These installers are also available as standalone listed on PostGIS windows page. This is the first PostGIS 2.2.0 release for the PostgreSQL 9.5 32-bit and a rerelease for PostgreSQL 9.5 x 64-bit (this time compiled against beta2 instead of beta1).

On quick testing the PostGIS 2.2 beta1 release and pgRouting 2.1.0 worked fine on 9.5beta2, however you may want to reinstall anyway just to be safe. You can just reinstall over your existing install, no need to uninstall first. Similarly just upgrading a PostgreSQL 9.5beta1 to 9.5beta2 seemed to not require pg_upgrade or dump/restore, so safe to just upgrade from 9.5beta1 to 9.5beta2. Other notes about this 9.5beta2 PostGIS 2.2.0 release:

• The FDW API changed between PostgreSQL 9.5beta1 and PostgreSQL 9.5beta2, so the OGR_FDW, if you don't reinstall the bundle, will crash and burn in PostgreSQL 9.5beta2 (using PostGIS 2.2. beta1 executables). Similarly this newly compiled OGR_FDW will not work on PostgreSQL 9.5beta1 (so upgrade to 9.5beta2 first).
• The PostgreSQL 9.5betas (that includes both beta1 and beta2), are compiled against the pointcloud 1.1 master branch. This was required because the released pointcloud 1.0.1, does not compile against PostgreSQL 9.5
• The PostgreSQL 9.5beta2 PostGIS 2.2.0 release comes packaged with SFCGAL 1.2.2 (instead of 1.2.0 like the others versions) which fixes a crasher with ST_StraightSkeleton as noted in ticket - https://trac.osgeo.org/postgis/ticket/3324. Newer SFCGAL will be packaged with upcoming PostGIS 2.2.1, but if you are on an older edition and are using SFCGAL, you can always copy latest SFCGAL.dll binaries from the 2.2.1dev packages on PostGIS windows page http://postgis.net/windows_downloads/.