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"
Sunday, April 21. 2013
Printer Friendly
In Happy Valentine PostGIS we demonstrated how to use PostGIS raster
to decipher letters from a raster, vectorize them and then reuse this vectorized letters to form new words. Admittedly the letters were a bit grainy since they were
vectorizations of low res rasters and I didn't bother smoothing them. Bruce Rindahl offered a script to SVG to PostGIS geometry and using Batik to convert a font file to SVG format and gave me a hi-res converted kankin fontset. I still haven't figured out how his script works.
Bborie Park thought that was all too complicated and thought (as I have always) that we need an ST_GeomFromSVG function for PostGIS of which he is on a mission to create when he's less busy.
He also suggested I wrap my letter writer function as an extension. Taking all these ideas, I formulated an extension you install with
CREATE EXTENSION postgis_letters;
postgis_letters (http://www.bostongis.com/postgisstuff/postgis_letters_extension.zip) is an sql / data extension containing mostly data, but as the name suggests relying on PostGIS. The data are geometry vectors of the kankin font. I plan to add in more free fonts later once I figure out how to use Bruce's script or Bborie comes up with a better way and also more positioning logic and handling of spaces. So its a little rough at the moment.
The purpose of the extension is so I can write words on my images in reports e.g. state names or overlay labels on geometry features like roads and land. Using the power of both geometry/raster you can have a fully functioning report image writer that would return a fully formed image for use in LibreOffice (or for my ASP.NET web apps Active Reports.NET). This wouldn't rely on any mapping server to draw images (just pure PostGIS/PostgreSQL). Hopefully with new and improved binary features coming in PSQL for (looks like 9.4), outputting these raster images from psql will also be trivial. While on my mission to do something useful, I got distracted by something more entertaining: describing spatial processes with words. Here it goes.
Continue reading "Word Play with Spatial SQL"
Tuesday, April 09. 2013
Printer Friendly
We've got experimental pgRouting windows binaries available for windows PostgreSQL 9.2 32-bit and 64-bit for pgRouting 1.0.7 development branch. More details on our Boston GIS blog page.
The final versions we plan to release with upcoming PostGIS 2.1 PostgreSQL 9.2 on stackbuilder as part of the PostGIS install. Barring no difficulties we'll also have experimental binaries for PostgreSQL 9.3 releases once 9.3 reaches beta.
This version and upcoming pgRouting versions support the PostgreSQL extension model, so if you have postgis already installed, its just an additonal simple step:
CREATE EXTENSION pgrouting;
|