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

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.

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)