# Modeling SQL Query to add statistical data to a geometry

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.

• Which database are you using? Commented Nov 29, 2014 at 7:22
• PostgreSQL Version 9.3 Commented Nov 29, 2014 at 7:47
• 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.
– djq
Commented Nov 30, 2014 at 16:04
• 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. Commented Nov 30, 2014 at 16:12

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)
``````