2.2. Notes

2.2. Notes
Prev	2. Assignment: Creating a thematic map with QGIS using non-spatial table with statistical data.	Next

Table MaCrimStat was derived from the table macrimstat_orig (which consists of the original data for the year 1999) with the following query:

SELECT "TOWN_ID", "TOWN", "YEAR", "MVEH_TOT_R" AS numVeh, "POP2000"
FROM "macrimestat_orig"
GROUP BY "TOWN_ID", "TOWN", "YEAR", numveh, "POP2000"
ORDER BY "TOWN"

The aid of this query is to aggregate data for each town as well as to select fields required for the analysis.

Table “macrimestat” consists (among other fields/attributes)

Field “TOWN_ID” – id of town
Field “numveh” – vehicle theft rates per 100,000 population. If there were no reports on vehicle theft, this field consists of "-1".
Field POP2000 - population in the year 2000.
Refer to metadata (http://www.mass.gov/mgis/crime_statistics.htm) for detail information on the table structure/attributes.
As field names are in CAPITAL letters you need to use quotes around field names to perform queries, e.g.

SELECT "TOWN_ID"
FROM macrimestat;

Otherwise, PostgreSQL will convert field names to lower case (by default), and it will cause an error, as the table has the field "TOWN_ID", not the "town_id". Please remember use quotes if field names of a table are in UPPER CASE.