Modeling SQL Query to add statistical data to a geometry

Question

Hello I have a problem modelling a SQL Query for the following problem:

I have two tables with geometries from Shapefile der Verwaltungsgrenzen (WGS84). Of interest for me are Kreise and Gemeinden, whereas a Gemeinde is always part of the bigger Kreis. Each Kreis has a unique id called rs which is exactly 5 chars long, where as the unique id of Gemeinden is 12 chars long and the first 5 chars are equal to a Kreis id.

For some Gemeinden in de_commuter_gemeinden I have statistical data as well as for most of the Kreise in de_commuter_kreise. These data describe how many commuters there are. The Table looks like this:

de_commuter_* (
 rs character varying(12 or 5) NOT NULL, 
 name text,
 within integer, 
 home integer, 
 incoming integer, 
 outgoing integer)

For each geometry with statistical data I have to create random points within the geometry. For the Gemeinden it is no problem, since the are a subset of a Kreis and I can select the geometry from the table de_shp_gemeinden.geom. But for the Kreise I have to subtract the already generated points as well as the geometry they were created in. My current SQL for this is:

SELECT 
 k.rs, k.gen, st_difference(k.geom, (
 SELECT ST_Union(geom) AS geom 
 FROM de_shp_gemeinden g, de_commuter_gemeinden c 
 WHERE c.rs ~ ('^' || k.rs) AND c.rs = g.rs)
 ) AS geom 
FROM de_shp_kreise k 

Which looks like this

Kreise without Gemeinden

Now I need a smart way on calculating the statistical data for each Kreis, meaning get the data of commuters for the Kreis and subtracting all the data from it subset Gemeinden.

Could anyone point me in the right directions? If you need more info please ask.

• BradHards: Which database are you using?
• Benjamin: PostgreSQL Version 9.3
• djq: Do you have to do it in SQL? It could be easier to do it in something like R, where you connect to the SQL DB, and have the ability to store intermediate results in dataframes throughout a script. Not saying it can't be done in SQL but long term, it might be more legible in another language.
• Benjamin: No I need to do it in SQL since I use the result to generate random points (gis.stackexchange.com/questions/122567/…). So I need the modified geometry along with the statistical data. But the SQL below seems to work fine.

Answer 1

It seems I have found a good performing SQL

SELECT 
 k.rs,
 k.gen, 
 (ck.incoming-sums.incoming) AS incoming, 
 (ck.outgoing-sums.outgoing) AS outgoing, 
 (ck.within-sums.within) AS within, 
 ST_AsEWKB(ST_Difference(k.geom, geo.geom)) AS geom_b 
 FROM de_commuter_kreise ck 
 INNER JOIN ( 
 SELECT rs,gen,ST_Union(geom) AS geom FROM de_shp_kreise WHERE rs IN (SELECT rs FROM de_shp_kreise GROUP BY rs HAVING COUNT(rs) > 1) GROUP BY rs,gen 
 UNION 
 SELECT rs,gen,geom FROM de_shp_kreise WHERE rs NOT IN (SELECT rs FROM de_shp_kreise GROUP BY rs HAVING COUNT(rs) > 1) 
 ) k ON (ck.rs=k.rs) 
 LEFT OUTER JOIN ( 
 SELECT DISTINCT 
 k.rs AS rs,
 geo.geom AS geom 
 FROM de_shp_kreise AS k 
 LEFT JOIN ( 
 SELECT 
 ST_Union(g.geom) AS geom, SUBSTRING(g.rs FOR 5) AS rs 
 FROM de_shp_gemeinden AS g 
 INNER JOIN de_commuter_gemeinden c ON c.rs = g.rs 
 GOUP BY SUBSTRING(g.rs FOR 5) 
 ) geo ON (k.rs=geo.rs) 
 ) geo ON (geo.rs=ck.rs) 
 LEFT OUTER JOIN ( 
 SELECT
 SUBSTRING(rs FOR 5) AS rs,
 SUM(incoming) AS incoming,
 SUM(within) AS within,
 SUM(outgoing) AS outgoing 
 FROM de_commuter_gemeinden 
 GROUP BY SUBSTRING(rs FOR 5)
 ) sums ON (sums.rs = ck.rs)