XML/SVG outtakes

Node manipulation results in invalid parent/child relationship


21 Mar 2008

Dirk de Wit, dir. BAM

visibility of flemish new media use
commission is to suggest path towards increased legibility accessibility of this database
tight control on editorial view of what's in the database

question of location

converting list of addresses into geographical data (lat/long)

city names
problems of many same street names in same gem.
zip codes, city, sometimes street address

visualisations of database
relationship of keywords to people
views of words from project descriptions
possibly even analyze the text of URLs / pages listed

subsidie by keyword
showing where hosting is occuring


TEST QUERY
first name count:

XX
select first_name, count(*) from persons group by first_name

XX
SELECT first_name, count( * ) FROM persons GROUP BY first_name ORDER BY 'count( * )' ASC 

SELECT first_name, count( * ) FROM persons GROUP BY first_name

SELECT keywords.keyword_nl, SUM(subsidies.bedrag) as sumbedrag 
FROM subsidies,organisations,addresses,
relations_organisations_addresses,keywords,relations_organisations_keywords
WHERE subsidies.organisation_id=organisations.id 
AND subsidies.jaar=2008 
AND relations_organisations_addresses.organisation_id=organisations.id 
AND relations_organisations_addresses.address_id=addresses.id 
AND relations_organisations_keywords.organisation_id=organisations.id 
AND relations_organisations_keywords.keyword_id=keywords.id 
GROUP BY keyword_nl
ORDER BY sumbedrag DESC

-or-

SELECT keywords.keyword_nl, SUM(subsidies.bedrag) as sumbedrag FROM subsidies,organisations,addresses,relations_organisations_addresses,keywords,relations_organisations_keywords WHERE subsidies.organisation_id=organisations.id AND subsidies.jaar=2008 AND relations_organisations_addresses.organisation_id=organisations.id AND relations_organisations_addresses.address_id=addresses.id AND relations_organisations_keywords.organisation_id=organisations.id AND relations_organisations_keywords.keyword_id=keywords.id GROUP BY keyword_nl ORDER BY sumbedrag DESC

PERSON_CATEGORIES
(number of people)

OK
select category_en, COUNT(*) from relations_persons_categories, persons_categories WHERE category_id = persons_categories.id GROUP BY category_id ORDER BY category_en

OK
select category_nl, COUNT(*) from relations_persons_categories, persons_categories WHERE category_id = persons_categories.id GROUP BY category_id ORDER BY category_nl

OK (FINAL)
SELECT category_nl, COUNT( * ) FROM relations_persons_categories, persons_categories WHERE category_id = persons_categories.id GROUP BY category_id LIMIT 0 , 60

XX
select category_nl, COUNT(*) from relations_organisations_categories, organisations_categories WHERE category_id = organisations_categories.id GROUP BY category_id

OK (FINAL) ????
SELECT category_nl, COUNT( * ) FROM relations_organisations_categories, organisations_categories WHERE category_id = organisations_categories.id GROUP BY category_id LIMIT 0 , 60 

????
SELECT category_nl, COUNT( * ) as cnt FROM relations_persons_categories, persons_categories WHERE category.id = relations_persons_categories.persons_categories.id GROUP BY category_id

CORRECTED / GROUPED BY CATEGORY_NL!!!
SELECT category_nl, COUNT( * ) AS cnt
FROM relations_persons_categories, persons_categories
WHERE persons_categories.id = relations_persons_categories.category_id
GROUP BY category_nl
LIMIT 0 , 60

CORRECTED / GROUPED BY CATEGORY_NL + LIMITED TO 50
SELECT category_nl, COUNT(*) AS cnt FROM relations_organisations_categories, organisations_categories WHERE organisations_categories.id = relations_organisations_categories.category_id GROUP BY category_nl LIMIT 0 , 50

PEOPLE PER KEYWORD
XX
select keyword_nl, COUNT(*) from relations_persons_keywords, keywords WHERE keywords.id = keyword_id GROUP BY person_id

XX
select keyword_nl, COUNT(*) from relations_persons_keywords, keywords WHERE keywords.id = relations_persons_keywords.keyword_id GROUP BY  keyword_id

XX (CORRECTED ON BASE OF NICOLAS QUERY)
SELECT keyword_nl, COUNT( * ) FROM relations_persons_keywords, keywords WHERE keywords.id = relations_persons_keywords.keyword_id
GROUP BY keyword_id LIMIT 0 , 60

ORGS PER KEYWORD
XX
select keyword_nl, COUNT(*) from relations_organisations_keywords, keywords WHERE keywords.id = keyword_id GROUP BY organisation_id