Archive for the ‘ PostGIS ’ Category

Arch updates Postgresql & PostGIS

Postgresql 9.2.1 and PostGIS 2.0.1 just hit Arch’s repositories.  Going from Postgres 9.1 to 9.2 is a major upgrade and will require your 9.1 databases to be migrated.  For steps on how to upgrade your data see this post in the Arch Wiki.  If you want to postpone updating your databases add postgresql and postgresql-libs to your IgnorePkg line in your /etc/pacman.conf file.

Getting .fit file data into PostGIS

GPSBabel released an update (1.4.4) on Labor Day.  This update fixed GPSBabel’s FIT file compatibility.  Now FIT files created on Garmin Edge and other devices can be converted into formats that can be imported into PostGIS.  Follow along after the jump to see how.

Continue reading

Temporary PostGIS-2.0.1 PKGBUILD

A few months ago I put out a PKGBUILD for PostGIS 2.0 to use while we waited for the official Arch repositories to catch up.  I just updated it to 2.0.1.  This time I changed it so it will build a package called Postgis-2.0.1-0, so when the official repositories add 2.0.1 it will be replaced automatically.  If you currently have 2.0.0 installed you can use this to upgrade but you’ll still have to upgrade your Postgresql extensions by using:

ALTER EXTENSION postgis_topology UPDATE TO "2.0.1";

As always, I only kind of know what I’m doing, use at your own risk.

PostGIS 2.0 in Arch’s official repository!

A  little over a week ago PostGIS 2.0 hit the official Arch repository.  Hooray!


Trying to improve the TIGER Geocoder query for QGIS

In an earlier post I showed how you can use the Fast SQL Layers plugin (available from the QGIS Plugin Repository) to execute a TIGER Geocoder query.  I’ve simplified the query slightly, instead of using a UUID for the unique ID field you can use a window query and the row_number() function instead.

SELECT ROW_NUMBER() OVER (ORDER BY g.rating) as id, 
 g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, 
 (addy).address As stno, (addy).streetname As street, 
 (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip, g.geomout As the_geom
 FROM geocode('5200 Duesner Rd, Evansville, IN 47712') As g;

While simpler, it doesn’t quite work as intended.  If you run the query in Pgadmin or psql it works just fine.  However, running it in Fast SQL Layers the window doesn’t work, the ID for each feature is zero.  This causes a crash when you try to open the attribute table.  The identify tool and exporting to shapefile both work, but as mentioned the ID field will show 0 for all records returned.

How far away are you from beer on a Sunday in Indiana?

Answer:  At most 50 miles.

Inspired by GIS blogger Darren Cope’s analysis of Tim Horton’s locations in Canada I decided to figure out how far you are at any given time from a microbrewery in Indiana.   Instead of Canadian coffee and donuts I mapped brewery locations.  I pulled the Brewer’s Guild of Indiana’s membership list (and took out Bee Creek, I’m pretty sure they’re closed).  This is a vital map because Indiana’s blue laws prohibit alcohol sales on Sundays except at microbreweries and vineyards.   Two things I don’t like on Sundays, wine and crossing state boundaries so this was a vital project for me.  I found that I really don’t want to be in northeast Dubois County on a Sunday. Seriously, 50 miles.  Dang.  At least people in Vincennes and Terre Haute can jump over to Illinois for a 6 pack.

Distances to Indiana breweries

I followed Cope’s steps except to get the lat / long for the breweries I had to geocode them against the TIGER street centerlines and instead of using GRASS directly I used QGIS’s GRASS plugin.  This article was a big help on getting a GRASS workspace set up to create the raster.

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.