Archive for October, 2012
I migrated my existing Arch installation to a standalone systemd by following the steps laid out in this wiki post. It was a quick and easy process, hopefully it will be easy to administer down the line.
If you wanted to create a view with spatial capabilities with previous versions of PostGIS all you needed to do was manually register the view in the geometry_columns table. This is no longer possible since from PostGIS 2.0 forward geometry_columns is a view, not a table. However, if you use typemods to define the geometry column your view the geometry_columns view will pick it up. For example, I wanted to take some of the data I loaded from my .fit files and convert the metric data to imperial units, and reproject the data from WGS84 to UTM. Here’s the view that does that for me:
CREATE OR REPLACE VIEW rides.ride_utm AS
SELECT id, no
, utility.units_from_to('meter', 'feet', altitude) AS edge_elev
, 32+(temperature*(1.8)) AS temp_f, speed*2.2369 AS mph, cadence
, ride_date, ride_time, elev_dem
, ST_Transform(the_geom, 26916)::geometry(Point, 26916) As the_geom FROM rides.ride;
The units_from_to function is something I picked up from PostGIS in Action. The geometry column uses ST_Transform to reproject the data, and the typemod after defines the column as having point geometry with my UTM projection.
With the geometry column defined in that manner the geometry_columns view is properly populated. When you want to use this view in QGIS it is visible as a loadable layer. However, before you can use it you must tell QGIS which field is the unique row identifier. To the left of the layer’s SRID number there will be a drop box on the PostGIS layer load screen. This dropbox will give you a list of possible fields to use as your ID column. Just select the column that contains unique values and you’re good to go.
A few weeks ago Regina Obe posted that work has begun on the 2nd edition of PostGIS in Action. I can’t wait until it is released, the first edition is an amazing resource and I’m sure the new edition will be better at covering topics covered in the first edition, plus be the best resource for all the new stuff that PostGIS 2.0 brought us such as topology and raster support.
Understandably it’s going to take some time to put all this information together, so until the new edition drops keep an eye on the BostonGIS page, the BostonGIS blog, and everything else Regina Obe and Leo Hsu do in the world of PostGIS and Postgresql. It’s really too much to keep track of!
I’ve put together a Python script that uses pyqgis to add elevation data from a DEM to the attribute table of a point layer. I’m going to use it to compare the elevations recorded by my Edge 500 to elevations derived from local DEMs. I pieced the script together with tons of help from the PyQGIS Cookbook.
Here’s what I came up with. It requires both the point layer and DEM to be in the same projection.
Boring Nerdy Stuff 