| 11.521: Spatial Database Management and Advanced Geographic Information Systems |
|
11.523: Fundamentals of Spatial Database Management |
Here are the queries that we ran in class on Feb. 22 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.
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 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 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 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 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. SELECT owner, count(*) parcels FROM parcel96 WHERE owner LIKE '%BOSTON%' GROUP BY owner HAVING count(*) > 1 ORDER BY count(*) desc; /* 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. */ update blookup set fix_owner = 'BOSTON REDEVELOPMENT AUTH' WHERE owner LIKE '%BOSTON%' AND (owner LIKE '%REDEV%' or owner LIKE '%REDV%'); 17 rows updated. create index p96owner on blookup(owner); 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(*) > 1 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 ... 133 rows selected.
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 19 Feburary 2009 (jf)