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.