1

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.

  • Which database are you using? – BradHards Nov 29 '14 at 7:22
  • PostgreSQL Version 9.3 – Benjamin Nov 29 '14 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 Nov 30 '14 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. – Benjamin Nov 30 '14 at 16:12
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.