Transcript Employing an RDBMS to Integrate and Enhance the Usability

Employing an RDBMS to Integrate and
Enhance the Usability of
Land Record Data
Dan Steen
Steve Yoder
AIMS, Johnson County, Kansas
Employing an RDBMS to Integrate and
Enhance the Usability of
Land Record Data
Dan Steen
Steve Yoder
AIMS, Johnson County, Kansas
Employing an RDBMS to Integrate and
Enhance the Usability of
Land Record Data
Dan Steen
Steve Yoder
AIMS, Johnson County, Kansas
Employing an RDBMS to Integrate and
Enhance the Usability of
Land Record Data
Dan Steen
Steve Yoder
AIMS, Johnson County, Kansas
Our Situation ... 4th Qtr 2000
• County’s GIS operations solely NT based
• Increasing demand for integrated DB/Map Apps
– Recently deployed a number of VB/MO apps
• Johnson County Land Records data resides
on/in various servers/formats
– IT had no plans to build an enterprise Land Records DB
• GIS industry moving toward integrated storage
of spatial and attribute data in commercial DBMS
software
Our Plan
• Move land record attribute data to DBMS
– Before moving spatial data
• Bring together disparate land record data stores
into a single database
• Use the new database as we develop additional
integrated DB/Map apps
• Lay foundation for expanded DBMS use in AIMS
operations and administration
Infrastructure
• Production Server
– Compaq Proliant ML370, 2x866 Mhz, 896 MB RAM
– OS: Windows 2000 Server, SP2
– Mirrored 18.2 GB Drives for OS, Log Files &
Pagefile, 3 36.4 GB Drives in RAID5 for Data
(72Gb’s Usable space)
• Microsoft SQL Server 2000
– Service Pack 2
JOCOLand
Data pertaining to “Land Records” in Johnson County.
JOCOLand is NOT a primary data store; rather, it is a
warehouse of replicated data, derived data, and pointers
to other data.
JOCOLand data comes from a variety of primary data sources
including OASIS , CAMA, miscellaneous Appraiser databases,
Property Spatial Dataset (i.e., property coverages), as well as
data from a number of municipalities and Public Works.
Its value is that it brings together data from a variety of
sources into a single, centralized, enterprise database.
Replicated from Mainframe
OASIS and CAMA
Real Estate Properties/Parcels
ownership, situs address, legal description,
appraisal characteristics, appraised value
Replicated from Property Coverages &
Derived from numerous point-in-polys
Real Estate Properties/Parcels
location (centroid & administrative districts),
size
Replicated from Property Coverages &
Derived from numerous point-in-polys
Real Estate Properties/Parcels
location (centroid & administrative districts),
size
Replicated from INFO files used in
Property Coverage Maintenance process
Transfer Orders
type (e.g., split, plat), properties/parcels
involved, spatial before and after
Replicated from INFO files used in
Property Coverage Maintenance process
pointers to archived property coverages
Transfer Orders
type (e.g., split, plat), properties/parcels
involved, spatial before and after
Replicated from Mainframe OASIS &
Derived through code
Plats and Subdivisions
name, year platted, book/page
Replicated from Mainframe OASIS &
Derived through code
pointer to scanned plat image
Plats and Subdivisions
name, year platted, book/page
Replicated/Massaged from Mainframe OASIS &
Appraiser Access Database
Appraisal Data
Value History, BOTA, Front Elevation,
Certificate of Value, Sales, Permits
Replicated/Massaged from Mainframe OASIS &
Appraiser Access Database
Appraisal Data
Value History, BOTA, Front Elevation,
Certificate of Value, Sales, Permits
pointers to images
Replicated/Massaged from Mainframe OASIS,
Public Works (INFO), Municipalities (DBF), etc.
Situs Addresses
including the atomized pieces of an
address and its location
Code Definitions
look-up tables that translate various codes
into a text description
Under Construction
Condominiums (vertical parcels & common areas),
Leased Land, Mineral Rights &
Underground Warehouses
How is data in JOCOLand
Refreshed?
• Most tables are updated each night
• Typically some pre-processing, for example
– INFO  .dbf
– merge tiled data
– point-in-polygon
• Scheduled Data Transformation Services (“DTS
Packages”)
Development of Land
Records Application
• Promote data sharing
– Eliminate redundancy
• Maximize existing data resources
– Reduces cost & eliminate redundancy
• Utilize existing infrastructure and hardware
– Reduces cost
• Minimize specialized development
– Administration & support more manageable
• Make application accessible
– 24x7 from anywhere
The “JCLR” Application
•
•
•
•
•
•
JCLR – Johnson County Land Records
SQL Server 2000 driven application
One example of a web interface into DBMS
Internet-based land records information access
Incorporates tabular and spatial data
JCLR and IMS tightly integrated but independent of
each other
• Ties data from AIMS, OASIS, CAMA, Appraiser,
Register of Deeds, Planning/Codes, …
Goal of JCLR
JCLR & IMS
•
•
•
•
•
•
•
•
•
•
•
Property Id
Situs Address
Owner Address
Owner Names
Mail Names
Parcel Spatial History
Zip Code
Legal Description
Planimetric Features
Spatial Intersections
Spatial Buffering
•
•
•
•
•
•
•
•
•
•
•
Appr. Characteristics
•
Year Built
•
Tax Value
•
Sales Value
•
Zoning Landuse
•
PLSS / Map Number
•
Taxing Unit
•
Neighborhood Unit
•
Comparables
•
Plotting
•
Hardcopy Map Production
Pictures
Sales Questionnaires
Tax Appeal Documents
Plat
Subdivision Names
Subdivision Scans
Tax Information
Parcel History (splits, etc)
Floorplans
Scanned Documents
Technical Architecture
• JCLR – Intranet Application
– Active Server Page Application
– VBScript (some JavaScript)
– SQL Server 2000
• Internet Map Server (IMS) – Internet App.
–
–
–
–
MapObjects IMS
Visual Basic
SQL Server 2000
Shapefile data
Technical Architecture
• Application distributed between many servers
–
–
–
–
Web Server
Database Server
File Server
Application Server
DBMS Advantages
•
•
•
•
•
•
•
Data access very efficient
Rapid development of applications
High availability
Diverse accessibility – Non-proprietary data formats
More data sharing – Centralized source
Standardization across enterprise
Table driven code – updates get made transparently
Table driven code
- updates get made transparently
Unanticipated Outcomes
•
•
•
•
Easier to examine data quality
Data being used in new & innovative ways
Developing a better understanding of the data
An understanding of how efficient “set”
processing is
• Turf issues with IT Department
Easier to examine data quality
- Example: Land Use Code
Easier to examine data quality
- Example: Voting Precinct
Data being used in new & innovative ways
- Example: Parcels with “Transfer Order Activity”
Developing a better understanding of the data
- Example: Property Change Log
compare
Developing a better understanding of the data
- Example: Property Change Log
PropertyChangeLog (detail)
compare
Developing a better understanding of the data
- Example: Property Change Log
PropertyChangeLog (summary)
compare
An understanding of how
efficient “set” processing is
• In contrast to “cursor” processing
– Deal with one record at a time
• “set” processing
– Deal with groups of records
– Huge performance gain
Turf Issues with IT
Department
• “The GIS Department is operating outside the
scope of its mission.”
• “As they developed the database and the
applications, the GIS Department should have
consulted with IT more.”
The Future of JOCOLand ...
• Implement Situs Address and PropIDRelate
tables
• Incorporate additional land record data stores
• Include historical as well as in-progress data
• Integrate better with JOCOGeog (AIMS spatial
data accessible via SDE)
Beyond Land Records ...
• Economic Data
• Census Data
• AIMS Administration
–
–
–
–
–
Log of AIMS Map & Data Requests
Accounts Receivable, Accounts Payable
Log of IMS Hits
Customers/Contacts
Data License Agreements
• AIMS Application Definition