Archive for the ‘ PostGIS ’ Category

Reprojecting data with Postgis 2.0

There are so many awesome new features in PostGIS 2.0 but one of my favorites is the UpdateGeometrySRID function.  If you needed to reproject data in old versions you had to use a combination of Transform and SetSRID functions.  Now all you need to do is:

SELECT UpdateGeometrySRID('schema', 'table','geometry_column', SRID);

Easy peasy!

If this was available before 2.0 please travel back and time and tell me.  It will save me a few headaches.

Using the TIGER Geocoder from QGIS


Not long after I posted this I found an easier method, not sure why I didn’t update this post.  Anyway rather than mess around with UUIDs, just use Postgres’ window function.  In this case the row_number function will generate the unique integers that QGIS needs to display your dynamic layer.  Replace the uuid_generate_v4() with:

SELECT row_number() OVER () As id,

Easy peasy.


I’ve been trying to use the Fast SQL Layers plugin with QGIS to map results from TIGER Geocoder queries.  The query below works, it will place point(s) but trying to open the attribute table crashes QGIS.  The identify tool works, but you will get an error stating the cursor has been lost.  I think it has something to do with the UUIDs, but I’m not positive.    Anyway, the query is after the jump.

Continue reading

PostGIS 2.0 PKGBUILD for Arch Linux

PostGIS 2.0 is now in the official repository.  Ignore all this nonsense.

PKGBUILD linked below now builds PostGIS 2.0.1.  See this post for details.

I’ve put together a PKGBUILD file you can use if you want to install PostGIS 2.0 on your Arch system.  I’m not going to put it on AUR because it would conflict with the existing package, and also because I’ve never done this before so use this at your own risk.  You can download the PKGBUILD from here:

I didn’t tarball the PKGBUILD so you can just download it to an empty directory and run

makepkg -s

to build the package and then

pacman -U postgis-2.0.0-1-x86_64.pkg.tar.xz

to install it.

Once the package is installed look at the PostGIS manual to see how to set up or upgrade a spatial database.

This build includes the GUI shapefile loader. If you don’t want it or don’t need it edit –with-gui out of the ./configure statement and remove gtk2 from the dependencies.

PostGIS 2.0 released!

PostGIS 2.0 is finally out!  Full details here.

Typmod in PostGIS 2.0

One of my favorite new features in the upcoming 2.0 release of PostGIS is typmod.  This blog post breaks down how it will simplify the creation and modification of spatially enabled tables.

Installing pgRouting in Arch

Installing pgRouting in Arch is a very straightforward process.  The latest version is in AUR, you can install it with yaourt.  If you’re going to need pgRouting’s travelling salesperson (TSP) and/or driving distance (DD) functions you’re going to need to install the Genetic Utility Algorithm Library (GAUL) for TSP and/or Computational Geometry Algorithms Library (CGAL) for DD.  Both libraries are also available from AUR.  Once they are installed  edit pgRouting’s PKGBUILD file and uncomment out the cmake line that activated the DD and TSP flags.

Once pgRouting is compiled and installed you’ll need to add it’s functions to a PostGIS enabled database.  Personally I like to keep pgRouting’s functions in their own schema.  So when I’m adding pgRouting to a database I do the following in psql:

CREATE SCHEMA 'pgrouting';
SET SEARCH_PATH TO 'pgrouting', 'postgis';
\i /usr/share/postlbs/routing_core.sql
\i /usr/share/postlbs/routing_core_wrappers.sql 
\i /usr/share/postlbs/routing_topology.sql 
\i /usr/share/postlbs/routing_dd.sql 
\i /usr/share/postlbs/routing_dd_wrappers.sql
\i /usr/share/postlbs/routing_tsp.sql
\i /usr/share/postlbs/routing_tsp_wrappers.sql

Like pgRouting’s functions I like postGIS’s functions to live in their own schema, most installations put them in the public schema.  If that’s the case in your database replace postgis with public in the set search_path statement above.


Batch Geocoding with TIGER Geocoder 2010

I’m compiling a database of all the microbreweries in my state.  So far I’ve got a list of 43 breweries and their addresses.  I’ve geocoded them against the 2010 TIGER street centerlines.  I used a batch geocoding example from PostGIS in Action the basis for this query.

Continue reading

TIGER Georeference 2010 Loader Works

A few posts ago I went through the steps to get the script that the 2009 TIGER Georeference Loader to work under Linux.  Regina Obe was nice enough to comment on that post, pointing out that the 2010 Loader that’s included in PostGIS 2.0 works just fine under 1.5.  It does work right out of the box and it downloads the 2010 TIGER data instead of the 2009.  Sweet!

One small tip, I used the Pgadmin3 query editor tool and copied the results of the Loader function to a blank text file and executed that text file as a script.  When doing so, remember to delete the first and last quotation marks you’ll get when you copy the query results.

PostGIS & Qgis updated on Arch

PostGIS 1.5.3 has been recompiled for the Arch package repository, it now works out of the box with Postgres 9.1.

QGIS 1.7.1 has been added to AUR, but it is not compiling correctly for me.  It tried adding a flag to the PGKBUILD file that specified where the Python 2 libraries were held, but the compile still bombed out.  There are a few more tricks to try, hopefully I’ll stumble upon the right combination.  If I do maybe I’ll submit my first PGKBUILD to AUR.

Well, that was quick

Compile from source, problem solved.  Plus now I’ve got the shp2pgsql GUI to play with.  Huzzah!

I should have used the silent option when restoring the database dump.  2 GB worth of insert successful messages take forever to scroll by.