| 11.521: Spatial Database Management and Advanced Geographic Information Systems |
|
11.523: Fundamentals of Spatial Database Management |
Here are the queries that we are running in class on today to study land use and ownership patterns in the parcel database for Boston. The queries track those in this paper: Ferreira, Joseph Jr., "Information Technologies that Change Relationships between Low-Income Communities and the Public and Non-profit Agencies that Serve Them." This paper is Chapter 7 of High Technology and Low-Income Communities: Prospects for the Positive Use of Advanced Information Technology," Edited by Donald A. Schön, Bish Sanyal, and William J. Mitchell, MIT Press, 1997. These original queries were run in an Oracle database engine but the SQL is sufficiently standard that they work unchanged in PostgreSQL.
To access the 'parcel96' table using our class Postgres instance, choose the 'eboston' database that we have used for recent exercises and look at the tables in the 'boston96' schema. Since the queries do not specific the schema in the FROM clause (e.g., FROM boston96.parcel96) you will want to run this first command to include the 'boston96' schema in your search path:
SET search_path TO boston96,public;/** Let's look at those owners in the Boston parcel database who own the largest number of parcels. **/SELECT owner, count(*) as parcels FROM parcel96 WHERE owner LIKE '%BOSTON%' GROUP BY owner HAVING count(*) > 1 ORDER BY count(*) desc; OWNER PARCELS ------------------------------ ---------- CITY OF BOSTON 2876 CITY OF BOSTON BY FCL 1258 BOSTON REDEVELOPMENT AUTH 231 BOSTON HOUSING AUTHORITY 215 BOSTON KENMORE REALTY CORP 131 BOSTON UNIVERSITY TRSTS OF 111 CITY OF BOSTON FCL 98 BOSTON WHARF CO GPS 88 BOSTON REDEVELOPMENTAUTH 83 BOSTON REDEVELOPMNT AUTH 82 ROMAN CATH ARCH BOSTON 63 BOSTON REDVLPMNT AUTH 60 BOSTON UNIVERSITY TRSTS 56 BOSTON HOUSING AUTH 46 BOSTON UNIVERSITY TRS OF 44 ... (143 rows)/** ---- Here is a similar query that focuses on owners with 'BOSTON' and with 'UNIV' in their name. This query also total the assessed value (in 000s) of the properties and the acreage. [Beware: public and non-profit owned properties are often assigned unrealistic assessed values since they do not pay taxes.] ------- **/select owner, count(*) as parcels, sum(totalval)/1000 as totval_k, sum(lotsize)/43560 as acres from parcel96 where owner like '%BOSTON%' and owner like '%UNIV%' group by owner order by count(*) desc; OWNER PARCELS TOTVAL_K ACRES ------------------------------ -------- ---------- --------- BOSTON UNIVERSITY TRSTS OF 111 85,824 14.5 BOSTON UNIVERSITY TRSTS 56 89,008 32.8 BOSTON UNIVERSITY TRS OF 44 29,654 6.2 BOSTON UNIVERSITY TRST OF 12 6,847 2.1 BOSTON UNIVERSITY 10 38,978 25.6 BOSTON UNIVRSTY TRSTS OF 8 18,186 2.0 BOSTON UNIVERSITY TRST 7 3,180 0.6 BOSTON UNIV TRUSTEESOF 5 1,192 0.0 BOSTON UNIVERSITY TRS 5 8,547 2.9 BOSTON UNIV TRSTS MTGEE 3 968 0.2 BOSTON UNIVERSITY TS OF 3 1,276 0.2 BOSTON UNIVERSITY TRUSTEE OF 3 4,090 0.6 BOSTON UNIV TRST OF MASS 2 679 0.2 TRUSTEES OF BOSTON UNIV 2 755 0.2 TRSTS OF BOSTON UNIVERSITY 2 2,791 2.1 BOSTON UNIVERSKTY TRSTS OF 2 402 0.1 BOSTON UNIV TRSTS OF 1 1,337 0.2 TRUSTEES OF BOSTON UNIVERITY 1 71 0.2 BOSTON UNIVSTY TRST OF 1 1,544 0.1 BOSTON UNIV TRSTS OF MTGEE 1 326 0.1 BOSTON UNIVERSITY TRSTS THE 1 580 0.1 BOSTON UNIVERSITY TRSTS OF. 1 333 0.1 BOSTON UNIVERSITY TR OF 1 1,774 0.6 BOSTON UNIV TRUSTEES 1 1,800 0.2/** Here is a similar query for owner names that also contain 'REDEV'... **/SELECT owner, count(*) as parcels, sum(totalval)/1000 as totval_k, sum(lotsize)/43560 as acres FROM parcel96 WHERE owner LIKE '%BOSTON%' AND (owner LIKE '%REDEV%' or owner LIKE '%REDV%') GROUP BY OWNER; OWNER PARCELS TOTVAL_K ACRES ------------------------------ ------- ---------- --------- BOSTON REDEVELOMENT AUTH 1 327 0.2 BOSTON REDEVELOPMENT 5 157 0.5 BOSTON REDEVELOPMENT AUTH 231 106,863 123.9 1BOSTON REDEVELOPMENT AUTHRTY 3 61 0.1 BOSTON REDEVELOPMENTAUTH 83 0 11.9 BOSTON REDEVELOPMENTAUTHRTY 1 0 0.1 BOSTON REDEVELOPMNT AUTH 82 32,975 23.3 BOSTON REDEVELPMENT AUTH 41 1,418 3.0 BOSTON REDEVELPMNT AUTH 22 5,073 2.4 BOSTON REDEVELPOMENT AUTH 1 33 0.2 BOSTON REDEVLPMNT AUTH 28 7,488 3.2 BOSTON REDEVLPMNT AUTHOR 29 2,955 3.3 BOSTON REDEVLPMNT AV 1 0 0.0 BOSTON REDVLPMNT AUTH 60 2,472 7.8 BOSTON REDVLPMNT AUTHOR 15 3,416 1.8 BOSTON REDVLPMNT AUTHORITY 1 1,095 0.5 BOSTON REDVLPMNT CORP 1 662 0.1 PARCELS TOTVAL_K ACRES ------------------------------ ------- ---------- --------- BOSTON REDEVELOPMENT AUTH 605 164,991 182.3 /* -------- Nov, let's create a lookup table to accumulate 'fixes' to the owner names. (Unless your table is very large, you can omit the storage settings.) ---------- */ CREATE TABLE blookup STORAGE (initial 1M NEXT 250K PCTincrease 50 MAXEXTENTS 500) AS SELECT DISTINCT owner, owner fix_owner FROM parcel96; SELECT count(*) FROM blookup; COUNT(*) ---------- 103281 SELECT count(*) from parcel96; COUNT(*) ---------- 138001 /* There are 138K records with 103k distinct owner names. Here's the one example that we used in class to illustrate the use of a UPDATE statement to enforce a 'rule' that we construct to make some spelling corrections. By saving these update statements, we can accumulate useful and re-usable 'knowledge' about how to categorize the parcel owners. */ /** ---- The 'rule' will change 'fix_owner' for records where the owner appears to be the Boston Redevelopment Authority -------**/ update blookup set fix_owner = 'BOSTON REDEVELOPMENT AUTH' WHERE owner LIKE '%BOSTON%' AND (owner LIKE '%REDEV%' or owner LIKE '%REDV%'); 17 rows updated. -- Let's look at all the spellings that got changed SELECT * FROM BLOOKUP WHERE owner LIKE '%BOSTON%' AND (owner LIKE '%REDEV%' or owner LIKE '%REDV%'); OWNER FIX_OWNER ------------------------------ ------------------------------ BOSTON REDEVELOMENT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMENT BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMENT AUTHRTY BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMENTAUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMENTAUTHRTY BOSTON REDEVELOPMENT AUTH BOSTON REDEVELOPMNT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELPMENT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELPMNT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVELPOMENT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVLPMNT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDEVLPMNT AUTHOR BOSTON REDEVELOPMENT AUTH BOSTON REDEVLPMNT AV BOSTON REDEVELOPMENT AUTH BOSTON REDVLPMNT AUTH BOSTON REDEVELOPMENT AUTH BOSTON REDVLPMNT AUTHOR BOSTON REDEVELOPMENT AUTH BOSTON REDVLPMNT AUTHORITY BOSTON REDEVELOPMENT AUTH BOSTON REDVLPMNT CORP BOSTON REDEVELOPMENT AUTH 17 rows selected. -- for performance, we could index on owner name. -- with 130k rows in the table it will help a little. create index p96owner on blookup(owner); -- rerun the original query with a cutoff of 50 SELECT owner, count(*) parcels FROM parcel96 WHERE owner LIKE '%BOSTON%' GROUP BY owner HAVING count(*) > 40 ORDER BY count(*) desc; OWNER PARCELS ------------------------------ ---------- CITY OF BOSTON 2876 CITY OF BOSTON BY FCL 1258 BOSTON REDEVELOPMENT AUTH 231 BOSTON HOUSING AUTHORITY 215 BOSTON KENMORE REALTY CORP 131 BOSTON UNIVERSITY TRSTS OF 111 CITY OF BOSTON FCL 98 BOSTON WHARF CO GPS 88 BOSTON REDEVELOPMENTAUTH 83 BOSTON REDEVELOPMNT AUTH 82 ROMAN CATH ARCH BOSTON 63 BOSTON REDVLPMNT AUTH 60 BOSTON UNIVERSITY TRSTS 56 BOSTON HOUSING AUTH 46 BOSTON UNIVERSITY TRS OF 44 BOSTON EDISON CO 42 CITY OF BOSTON MUNICIPAL CP 42 BOSTON REDEVELPMENT AUTH 41 18 rows selected./** Once we have created an index (to speed the table join), we can use this query to re-group the parcels owned by the various BRA spellings so the earlier list of multi-parcel owners reflects the change categorization: **/ SELECT fix_owner, count(*) as parcels FROM parcel96 p, blookup b WHERE p.owner = b.owner and p.owner LIKE '%BOSTON%' GROUP BY fix_owner HAVING count(*) > 40 ORDER BY count(*) desc; FIX_OWNER PARCELS ------------------------------ ---------- CITY OF BOSTON 2876 CITY OF BOSTON BY FCL 1258 BOSTON REDEVELOPMENT AUTH 605 BOSTON HOUSING AUTHORITY 215 BOSTON KENMORE REALTY CORP 131 BOSTON UNIVERSITY TRSTS OF 111 CITY OF BOSTON FCL 98 BOSTON WHARF CO GPS 88 ROMAN CATH ARCH BOSTON 63 BOSTON UNIVERSITY TRSTS 56 BOSTON HOUSING AUTH 46 BOSTON UNIVERSITY TRS OF 44 CITY OF BOSTON MUNICIPAL CP 42 BOSTON EDISON CO 42 14 rows selected./** Instead of having BRA appear 5 times with different spellings in the
'biggest owner' list, BRA now appears once with 605 properties. **/
Home | Syllabus | Lectures | Labs | CRON | MIT
For more information about this page, please contact the 11.521 Staff Mailing List <11.521staff@MIT.EDU>.
Last Modified 23 Feburary 2017(jf)