Wednesday, October 23. 2019
Continue reading "PostgreSQL 12 64-bit for Windows FDWs"
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
Saturday, September 07. 2019
Continue reading "PostgreSQL 11 64-bit Windows FDWs"
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
Monday, March 25. 2019
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.
Sunday, December 09. 2018
Continue reading "PostGIS 2.5.1 Bundle for Windows"
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.
Sunday, September 09. 2018
Continue reading "PGOpen 2018 Data Loading Presentation Slides"
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.
Saturday, June 09. 2018
Continue reading "PostgresVision 2018 Slides and Impressions"
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
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.
Thursday, February 15. 2018
Continue reading "FDWS for PostgreSQL Windows 32 and 64-bit"
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.
Wednesday, November 23. 2016
Continue reading "ODBC FDW now supports 9.5 and 9.6"
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.
Thursday, April 21. 2016
Continue reading "PGConfUS 2016 PostGIS slides and tutorial material"
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.
Saturday, April 02. 2016
Continue reading "PostGIS 2.2 Windows users hold off on installing latest PostgreSQL patch release"
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.
Thursday, January 21. 2016
Continue reading "Foreign Data Wrappers for PostgreSQL 9.5 and 9.6 windows"
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
Thursday, January 07. 2016
Continue reading "Import Foreign Schema hack with OGR_FDW and reading LibreOffice calc workbooks"
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.
UPDATE: If you are using PostgreSQL 9.5+, you can use the IMPORT FOREIGN SCHEMA feature which is available in ogr_fdw 1.0.1+. We demonstrate this in: ogr fdw IMPORT FOREIGN SCHEMA
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.
Thursday, December 31. 2015
Continue reading "Import Foreign Schema for ogr_fdw for PostgreSQL 9.5"
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.
UPDATE - ogr_fdw 1.0.1+ now includes the IMPORT FOREIGN SCHEMA functionality discussed here.
Sunday, November 22. 2015
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/.
Sunday, September 27. 2015
Continue reading "Connecting to SQL Server from Linux using FDWs"
There are two PostgreSQL FDWs (currently maintained) I know of for connecting to SQL Server from a Linux/Unix PostgreSQL box. There is the TDS Foreign Data wrapper (tds_fdw driver) which
relies on the Free TDS driver. This is a fairly light-weight FDW since it just relies on TDS which is commonly already available on Linux installs or an easy install away. Unfortunately when I tried to use it on windows (compiling my usual mingw64 way), while it compiled and installed, it crashed when I attempted to connect to my SQL Server 2008 R2 box table, so I gave up on it for the time being as a cross-platform solution. One thing I will say about it is that it accepts ad-hoc queries from what I can see, as a data source, which is pretty nice. So we may revisit it in the future to see if we can get it to work on windows.
I'm not sure if tds_fdw would support SQL Server spatial geometry columns though would be interesting to try.
The second option, which as you may have noticed, we spent much time talking about is the ogr_fdw foreign data driver. ogr_fdw utilizes UnixODBC on Linux, iODBC on MacOSX and Windows ODBC on windows for connecting to SQL Server. The ogr_fdw big downside is that it has a dependency on GDAL, which is a hefty FOSS swiss-army knife ETL tool that is a staple of all sorts of spatial folks doing both open source and proprietary development. The good thing about ogr_fdw, is that since it is a spatial driver, it knows how to translate SQL Server geometry to it's equivalent PostGIS form in addition to being able to handle most of the other not-so spatial columns.