Methodology to find Adjacent Parcels with Same Owner Name
Cesar Yoc 11-20-09 Bureau of Environmental Planning and Analysis
Table of Contents
1. 2. 3. 4.
Introduction: The Project Method Use Unionizing Hospitals and Institution Lots About rails and BBL Duplicates
Introduction
Project: • To create a database that contains adjacent BBLs, tax lots, with the same owner name • To create a column that has unique IDs for adjacent BBLs for water consumption analysis and other projects
ArcGIS 9.3 Microsoft Access 2003 Microsoft Excel 2003
www.microsoft.com
Introduction Method: the adjacent to features tool
• The find adjacent features (Find Adjacent Features tool) • The tools result is data stored in notepad Using the tool • Select a feature layer: the shapefile that has BBL owner name repeating more than once • Select the unique field for list: BBL column or field • Select attribute fields: owner name column or field
Introduction Method : Method use to identify parcels that need to be organized
BBL
Adjacent 2
1007790035
1007790039
1007790039
Adjacent 3 1007790036
Adjacent 4
Count
Records to organize
Sum
1
3
2
0
3
6
-3
1007790036
3
2
0
0
2
5
-3
1007790043
1007790044
1
2
0
0
2
3
-1
1007790045
1007790044
1
2
0
0
2
3
-1
1007790062
1007790063
1
1
0
0
2
2
0
1007840014
1007840013
1
1
0
0
2
2
0
1007840054
1007840033
1
1
0
0
2
2
0
1007870025
1007870059
1
1
0
0
2
2
0
1007870040
1007870044
2
1
0
0
2
3
-1
1007870058
1007870040
1
2
0
0
2
3
-1
1007900009
1007900010
1
1
0
0
2
2
0
1007910030
1007910029
1
1
0
0
2
2
0
1007910068
1007910066
1
1
0
0
2
2
0
1007920039
1007920038
1
2
0
0
2
3
-1
1007920043
1007920038
1
2
0
0
2
3
-1
1007920061
1007920060
2
1
0
0
2
3
-1
1007920062
1007920061
1007920063
1
2
2
0
3
5
-2
1007920064
1007920065
1007920063
1
1
2
0
3
4
-1
Introduction Method: Organizing adjacent parcel data
Use ArcMap to check the record to make sure it contains all the BBLs that are adjacent to each other.
• •
Used the Excel find tool to check if the BBL is repeated in another cell. For example BBL number 2028530045 is found in all highlighted records (top table) The repeated BBL is deleted-not the one in record 5136, BBLs remaining are dragged into the 5136 record (bottom table)
Introduction Method: snapshot of BBLs for record 5136
• Snapshot of the BBLs in record 5136 (previous slide) • 5 BBLs that are adjacent to each other with the same owner name (Parks and Recreation)
Introduction Method: entering UniqueIDs for each record
UniqueID
Unique IDs Adjacency tables – UniqueIDs for records in excel document (records with more than 25 BBLs) – UniqueIDs for SQL Server 2005 tables (SQL QuerySequence)
BBL
Adjacen2
Adjacen3
Adjacen4
1
1000070030
1000070033
1000070037
1000070035
2
1000080039
1000080051
3
1000160003
1000160100
4
1000220020
1000220017
5
1000290019
1000290001
6
1000290046
1000290047
7
1000300005
1000300004
8
1000300033
1000300032
830729
1000070029
= 830729
1000070030 1000070033 1000070035 1000070037
830730
1000080051 1000080039
830731
1000220020 1000220017
Introduction Method: transposing adjacency data • SQL query to transpose data • Final table after appending the Excel document that contains records with BBLs greater than 25
Introduction Method: checking data for duplicates
• SQL Queries to find number of duplicates • Organizing and deleting repeating records • Union steps above
www.microsoft.com
Counting BBLs from create View SELECT Adjacen1, COUNT(Adjacen1) AS countBBL FROM dbo.alladjuniqueIDS_BBL_11_19_09 GROUP BY Adjacen1
BBLS that have Dups2 select BBL from Dups2 where countBBL >1
Introduction Method Universities and Institutions • Universities and Hospitals and its adjacent blocks
• Unionizing data the adjacency tool didn’t select
Universities and Hospitals
UniqueID
BBL
Brooklyn College
25843
3075520100
Brooklyn College
25843
3075560150
City College of New York
25844
1019570200
City College of New York
25844
1019570110
City College of New York
25844
1019570001
City College of New York
25844
1019570105
City College of New York
25844
1019570100
Columbia University
25839
1019730001
Columbia University
25839
1018670023
Columbia University
25839
1018840039
Introduction Method Universities and Institutions BBL Duplicates • These Islands have the same BBL (Owner: Parks and Recreation • 468 BBLs with dups (Total dups: 1402)
Repeating BBL: 3045870012
Introduction Method Universities and Institutions BBL Duplicates Rail Tracks
Adjacency tool does not recognize the Penn Central Company lots as adjacent to each other, unless the lots are connected to each other
Introduction Method Universities and Institutions BBL Duplicates Rail Tracks Next Steps
• Next step – Setting up method to combined universities and hospital during the rearrangement process.
• Future use – Water Rate Study Analysis – Mapping – Other projects that require this data
Any Questions?