PostGIS generate_series tricks
generate_series comes in particularly handy for manipulating geometries in a database. The following examples are done using Postgis 1.3.1. Below are some common use cases
/**break multipolygon geometries into single polygon geometries ***/
INSERT INTO mypoly(the_geom)
SELECT ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom))) AS poly
FROM mymultipoly
/**The ST_ExteriorRing function only takes Polygons. If each of your geometries
is a multipolygon and you wanted use ST_ExteriorRing
to get the exterior line string of
each polygon in each polygon, you would do something like the below. **/
SELECT a.gid, ST_ExteriorRing(ST_GeometryN(a.the_geom,ST_NumGeometries(a.the_geom))) AS a_singlelinenoholes
FROM somegeomtable a
/** If you wanted to do the above but still maintain
the same number of records as before, you would do this - which would give you a
multilinestring geometry where each linestring represents
an exterior of each polygon
**/
SELECT a.gid, ST_Collect(ST_ExteriorRing(ST_GeometryN(a.the_geom,ST_NumGeometries(a.the_geom)))) AS a_multilinesnoholes
FROM somegeomtable a
GROUP BY a.gid
NOTE: Because of the way generate_series works we can't have 2 generate series calls in the SELECT.
So if we need an inner and outer loop then we put one in the SELECT and one in the FROM and have a limiting WHERE clause as shown in the below.
/**Get all interior linestring rings (holes) of a multipolygon into a separate table
with one record per interior ring. **/
SELECT ST_BuildArea(ST_InteriorRingN(ST_GeometryN(a.the_geom,gn.n),generate_series(1,ST_NumInteriorRings(ST_GeometryN(a.the_geom,gn.n))) )) AS a_hole
FROM somegeomtable a,
generate_series(1, (SELECT Max(ST_NumGeometries(the_geom)) FROM somegeomtable)) gn(n)
WHERE ST_NumGeometries(the_geom) >= gn.n AND ST_NumInteriorRings(ST_GeometryN(the_geom,gn.n)) > 0
To a Database programmer all problems look like database problems
Doing a lot of database programming warps your thinking. Normally I would consider thinking in the languages you program in to be a bad thing because to some
extent it limits your thinking to what is supported in said language. In general I think doing a lot of database programming has had positive effects on me.
I find myself thinking about problems in parallel, unencumbered by the optical illusions of step dependency, and instead grouping problems in sets.
It seems that regardless of what language I program in, I see sets and patterns
everywhere. When that happens I can't help but hit that particular nail with a database hammer.
Below is a PostgreSQL example that uses generate_series to generate ASP.NET gridview column markup for a month column cross tab. Originally I used
AutogenerateColumns=true property of a grid view, but I needed to manipulate the formatting of my columns so that was a bit less than satisfying.
The code below generates a markup record for each grid view month column
SELECT '<asp:BoundField DataField="' || trim(to_char(date '2007-01-01' + (n || ' month')::interval, 'Month'))
|| '" HeaderText="' || trim(to_char(date '2007-01-01' + (n || ' month')::interval, 'Mon'))
|| '" ItemStyle-HorizontalAlign="Center" />' as newval
FROM generate_series(0,11) n
If you were to change the code above to add a SUM aggregate function on strings - (definition of a PostgreSQL SUM aggregate for text you can find here)
you would get just one row with all your markup. For this particular one we also created a column that returns the corresponding SQL for the cross tab query.
SELECT '<asp:GridView id="grv" runat="server">' || E'\r\n' || SUM('<asp:BoundField DataField="' || mth.long_mname
|| '" HeaderText="' || mth.short_mname
|| '" ItemStyle-HorizontalAlign="Center" />' || E'\r\n') || '</asp:GridView>' as aspxmarkup,
'SELECT ' ||
SUM('SUM(CASE WHEN report_date BETWEEN \'' || mth.start_date
|| '\' AND \''
|| mth.end_date
|| '\' THEN amount ELSE NULL END) As '
|| mth.long_mname
|| ', ' || E'\r\n') || ' SUM(amount) As total ' || E'\r\n'
|| ' FROM sometable WHERE report_date between \'2007-01-01\' AND \'2007-12-31\'' as sqlcrosstab
FROM
(SELECT (n + 1) As mnum,
trim(to_char(date '2007-01-01' + (n || ' month')::interval, 'Mon')) As short_mname,
trim(to_char(date '2007-01-01' + (n || ' month')::interval, 'Month')) As long_mname,
date '2007-01-01' + (n || ' month')::interval As start_date,
date '2007-01-01' + ((n + 1) || ' month')::interval + - '1 day'::interval As end_date
FROM generate_series(0,11) n) As mth
The gridview markup output of the aspxmarkup column looks like this
<asp:GridView id="grv" runat="server" >
<asp:BoundField DataField="January" HeaderText="Jan" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="February" HeaderText="Feb" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="March" HeaderText="Mar" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="April" HeaderText="Apr" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="May" HeaderText="May" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="June" HeaderText="Jun" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="July" HeaderText="Jul" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="August" HeaderText="Aug" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="September" HeaderText="Sep" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="October" HeaderText="Oct" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="November" HeaderText="Nov" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="December" HeaderText="Dec" ItemStyle-HorizontalAlign="Center" />
</asp:GridView>
And the Sql output of the sqlcrosstab column looks like this
SELECT SUM(CASE WHEN report_date BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 00:00:00' THEN amount ELSE NULL END) As January,
SUM(CASE WHEN report_date BETWEEN '2007-02-01 00:00:00' AND '2007-02-28 00:00:00' THEN amount ELSE NULL END) As February,
SUM(CASE WHEN report_date BETWEEN '2007-03-01 00:00:00' AND '2007-03-31 00:00:00' THEN amount ELSE NULL END) As March,
SUM(CASE WHEN report_date BETWEEN '2007-04-01 00:00:00' AND '2007-04-30 00:00:00' THEN amount ELSE NULL END) As April,
SUM(CASE WHEN report_date BETWEEN '2007-05-01 00:00:00' AND '2007-05-31 00:00:00' THEN amount ELSE NULL END) As May,
SUM(CASE WHEN report_date BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 00:00:00' THEN amount ELSE NULL END) As June,
SUM(CASE WHEN report_date BETWEEN '2007-07-01 00:00:00' AND '2007-07-31 00:00:00' THEN amount ELSE NULL END) As July,
SUM(CASE WHEN report_date BETWEEN '2007-08-01 00:00:00' AND '2007-08-31 00:00:00' THEN amount ELSE NULL END) As August,
SUM(CASE WHEN report_date BETWEEN '2007-09-01 00:00:00' AND '2007-09-30 00:00:00' THEN amount ELSE NULL END) As September,
SUM(CASE WHEN report_date BETWEEN '2007-10-01 00:00:00' AND '2007-10-31 00:00:00' THEN amount ELSE NULL END) As October,
SUM(CASE WHEN report_date BETWEEN '2007-11-01 00:00:00' AND '2007-11-30 00:00:00' THEN amount ELSE NULL END) As November,
SUM(CASE WHEN report_date BETWEEN '2007-12-01 00:00:00' AND '2007-12-31 00:00:00' THEN amount ELSE NULL END) As December,
SUM(amount) As total
FROM sometable WHERE report_date between '2007-01-01' AND '2007-12-31'
