One of my favorite tools and I think that of many folks working with GIS and other kinds of Multimedia is the GDAL/OGR suite.
Though I started using it to conquer GIS ETL activities, I found myself using it for problems that are inherently not GIS at all. I talked
about the GDAL OGR2OGR component a while ago in GDAL OGR2OGR for Data Loading
and this time I'll talk tangentially about its raster capabilities. It is a fantastic tool for converting between various raster formats and applying various raster operations.
In PostGIS world the new 2.0 raster functionality puts an SQL wrapper around much of its power. I'm not going to talk about that though except as a fleeting comment to explore later (we've got cool 2 band Map Algebra in PostGIS 2.0 to flaunt its stuff).
So what does this have to do with XPathing XML data with PostgreSQL? Well that's what I'm going to talk about what to do with machine generated data that comes at you in XML format.
GDAL exposes another kind of machine generated data in XML format which turns out to be very useful for all kinds of things. This is Exchangeable image file format (EXIF) data. There are all kinds
of random text information embedded in pictures and this varies depending on what camera is taking it. Newer cameras like the ones you have built into your iphone or android
embed location based information into them sometimes like where you were standing when you took the picture. Most cameras these days embed the time the picture was taken.
This information is important because if you are taking electronic notes while you are snapping your pictures, it provides an easy way to match up your notes with the picture about the object. So what does this EXIF info look like when you point GDAL at it? We'll see.
As mentioned in Lessons Learned Packaging PostGIS extensions, I am working on PostGIS 2.0.0 extension packaging. One of the things I wanted to know was what objects, types, functions etc were installed by my extension. The new packaging system allows for cataloging this relatively easily, but I couldn't find a function or view for this and didn't see one mentioned in the manual, so I created this query which seems to work pretty well as far as I can tell. The basic idea being that any object that an extension depends on
that is not an extension is part of the extension package.
SELECT c.relname As item_type,
COALESCE(proc.proname,typ.typname, cd.relname, op.oprname,
'CAST('|| cs.typname ||'AS'|| ct.typname ||')', opcname, opfname)As item_name,
COALESCE(proc.proisagg,false)As is_agg, oidvectortypes(proc.proargtypes)As arg_types
FROM pg_depend As d INNERJOIN pg_extension As e
ON d.refobjid = e.oid INNERJOIN pg_class As c ON
c.oid = d.classid
LEFTJOIN pg_proc ASprocONproc.oid = d.objid
LEFTJOIN pg_type AS typ ON typ.oid = d.objid
LEFTJOIN pg_class As cd ON cd.oid = d.objid
LEFTJOIN pg_operator As op ON op.oid = d.objid
LEFTJOIN pg_cast AS ca ON ca.oid = d.objid
LEFTJOIN pg_type AS cs ON ca.castsource = cs.oid
LEFTJOIN pg_type AS ct ON ca.casttarget = ct.oid
LEFTJOIN pg_opclass As oc ON oc.oid = d.objid
LEFTJOIN pg_opfamily As ofa ON ofa.oid = d.objid
WHERE d.deptype ='e'and e.extname ='postgis'ORDERBY item_type, item_name;
Ever have the need to create a holding table say spreadsheet data with say 100 columns. You need to create a table to hold this stuff. Or perhaps you were feeling in a sadist mood and wanted to abuse your PostgreSQL database to see how many columns you can create in a table of a specific data type.
Here is a quick script to do it: