Queries

[sabinem]

Discuss about the queries:

Questions:

• should we make the spatial join even if there is just country data in the table?
• should we name the result field in a meaningful way?
• should we also output other data fields such as year in the example below?
• column names: ü is frequently turned to u instead of ue by R tools: should keep it as such or rather rename with ue, similar for the other german special characters.

Query Nr. 1
-- Wieviele Abstimmungsvorlagen gab es im Jahr 2000?
SELECT COUNT(*) as anzahl_abstimmungsvorlagen_im_jahr_2000 FROM abstimmungsvorlagen_seit_1971 as T JOIN spatial_unit as S on T.spatialunit_uid = S.spatialunit_uid WHERE S.name_de='Schweiz' AND S.country=TRUE AND T.jahr=2000;
=======================================================
# A tibble: 1 × 1
  anzahl_abstimmungsvorlagen_im_jahr_2000
                                    <int>
1                                      12

[sabinem]

Answers:

• yes always make the spatial joins
• use the as in the way that makes sense, kind of like a natural answers
• only one value with the exact answer is the preferred way
• special characters go with ue or u : does not matter, the go with the u

[nooralahzadeh]

Hi @cmdoret @sabinem : Thanks for the great work. I've observed some inconsistency in the SQL queries when it comes to filtering by Canton or Municipalities names. For instance: S.name LIKE '%Aargau%' S.name="Canton of Bern" S.name_de LIKE '%Bern% Could we ensure consistency in all SQL queries? Considering the English and German dataset!

[cmdoret]

Thanks Farhad, we'll homogenize to with S.name LIKE '%Aargau%' AND S.canton = TRUE

[cmdoret]

Update from Kurt and Raphael: variety is good in this scenario, so they would rather keep it heterogenous