Tuesday, February 10. 2015
Printer Friendly
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.
Continue reading "Querying MS Access and other ODBC data sources with OGR_FDW"
Saturday, January 24. 2015
Printer Friendly
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.
Continue reading "Using SSL https connections with www_fdw on windows"
Thursday, January 22. 2015
Printer Friendly
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.
Continue reading "Installing PostGIS packaged address_standardizer on Ubuntu"
Saturday, January 03. 2015
Printer Friendly
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
Continue reading "Updated Foreign Data Wrappers for PostgreSQL 9.3 Windows"
Sunday, December 28. 2014
Printer Friendly
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.
Continue reading "Foreign Data Wrappers for PostgreSQL 9.4 Windows"
Saturday, December 27. 2014
Printer Friendly
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
Continue reading "OGR foreign data wrapper on Windows first taste"
Saturday, December 06. 2014
Printer Friendly
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, April 25. 2014
Printer Friendly
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.
Continue reading "Using HStore for Archiving"
Wednesday, March 26. 2014
Printer Friendly
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.
Note: PostGIS 2.1.2 release is eminent, so you might want to wait till we release and Yum is updated before you install/upgrade.
Continue reading "An almost idiot's guide to install PostgreSQL 9.3, PostGIS 2.1 and pgRouting with Yum"
Saturday, November 02. 2013
Printer Friendly
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.
Continue reading "Generating Create Foreign Table Statements for postgres_fdw"
Thursday, October 03. 2013
Printer Friendly
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.
Continue reading "Boston PUG Introduction to PostGIS videos"
Saturday, September 28. 2013
Printer Friendly
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.
Continue reading "PostGIS 2.1 Bundle for PostgreSQL 9.3 Windows Stackbuilder"
Monday, September 09. 2013
Printer Friendly
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.
Continue reading "PostGIS 2.1 windows bundle"
Friday, August 23. 2013
Printer Friendly
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
Continue reading "CREATE SCHEMA IF NOT EXISTS in 9.3 and tiger geocoder"
Sunday, August 18. 2013
Printer Friendly
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.
Continue reading "Raster Words using PostGIS 2.1"
|