University of Kentucky Facilities Planning Database Design

1. University of Kentucky UGIS FacilitiesPlan Mapping UK’s Long-term Needs with Effective Enterprise Planning Current Accurate Campus Facilities Information Facilities Information Services

2. Presentation Topics • Database Design • Database Administration • Custom Tools • Products

3. Database Design

4. Database Design • Floor Plan Configurations: • Stacked • 1 FC per level • All levels share same x,y origin (Stacked) • Offset • 1 FC • Contains all levels • Offset alignment • Existing Floor Plan • 1 FC • Contains all levels • Offset alignment • Stacked (Georeferenced) • 1 FC per level • Each FC is projected to KY State Plate Single Zone (Real World Coordinates)

5. Database Design • Stacked_UCS • 1 FC per level • All levels share same x,y origin (Stacked) • Coordinate System Abbreviations • UCS – User Coordinate System • An arbitrary coordinate system located anywhere in a 2D or 3D space (inches) • PCS – Projected Coordinate System • Kentucky State Plane Single Zone (feet) • Offset_UCS • 1 FC, all levels • Stacked_PCS • 1 FC per level • Projected to KY State Plate Single Zone (Real World Coordinates)

6. Database Design Building Datasets Standalone Feature Classes Data Tables

7. Database Design • Building Datasets contain: • Detail FCs (converted CAD polyline layers, 1 FC per building level) • GSF FCs (“Gross Square Footage” polygons, 1 FC per building level) • NSF FCs (“Net Square Footage” polygons, 1 FC per building level)

8. Database Design • Standalone Feature Classes contain: • Detail FCs • “Offset_UCS”, “Stacked_UCS”, “Stacked_PCS” for all buildings • GSF FCs • “Offset_UCS”, “Stacked_UCS”, “Stacked_PCS” for all buildings • NSF FCs • “Offset_UCS”, “Stacked_UCS”, “Stacked_PCS” for all buildings • GRID FC • Extent polygons for all building levels (used for Data Driven Page generation) • Spatial Domain Template FCs • Empty FCs that store spatial domain boundary coordinates (used for creating new feature datasets & feature classes) • 1 UCS Template • 1 PCS Template

9. Database Design • Data Tables contain: • Adjustment Links table • x,y coordinate pairs for each building (used for georeferencing) • Level Offset table • Contains offset distance values for each building

10. Database Design • Feature Class Naming Standard • Know a lot from just looking at the feature class name: Sort Prefix (Sorts Tables in SQL Server) Alignment Type (Stacked or Offset) Suffix (Geometry Type) Sub-Prefix (Specific Feature Type) GIS_FMGT_FP_NSF_Stacked_UCS_py Feature Identifier Prefix (General Feature Type) Coordinate System (UCS or PCS)

11. Database Administration

12. Database Administration • User Account Administration • *All user accounts are “headless” SQL database users • No need to add/remove users • Three user account classes: • Admin/Data Creator – perform administrative tasks and create new feature classes • Data Editor – edit feature classes • Data Viewer – view feature classes *except for a few OS authenticated admin users (currently not in use)

13. Database Administration • User Account Administration (cont.) • One admin user account (ugis_fp_masterdbo) • Each feature category in the database will have it’s own Data Editor and Data Viewer • *Two “special” users have geodatabase-wide access: • ugis_fp_powerreader(view all GDB features) • ugis_fp_powerwriter(edit all GDB features) ugis_fp_ds_datareader EXAMPLE: Building Datasets ugis_fp_ds_datawriter *ugis_fp_masterreader & ugis_fp_masterwriter were also created for viewing/editing “secure” features (currently not in use)

14. Database Administration • Administering Database Connections • Created user level groups • Same naming convention as user account classes • Admin/Data Creator – perform administrative tasks and create new feature classes • Data Editor – edit feature classes • Data Viewer – view feature classes

15. Database Administration • Administering Database Connections (cont.) • User Level Groups

16. Database Administration • Version Structure • Versions created on an as-needed basis when floor plan edits are required (via script) • Naming Standard • QC_<BldgID> • Edits_<BldgID> Example: Database Versions: Database User: Building Dataset ugis_fp_ds_datawriter

17. Database Administration

18. Editing Workflow Production MXDs MXDs used for editing FCs within a specific building dataset (one per bldg.) Database connections are sourced using “ugis_fp_ds_datawriter” (data editor) End-user just needs to switch to a newly created edit verison Helps with data QA/QC Access to MXDs secured by Windows security groups (same as User Level Groups) Database Administration EXAMPLE: User needs to edit the floor plan for building ID “0005”

19. Custom Tools

20. Custom Tools • CAD to GIS Conversion: • Floor Plan Layer Manager • Checks Attribute Table for duplicate room numbers, NULL values, etc. • Compares GIS shape square footages against CAD square footage labels • Compares GIS room labels against CAD room labels

21. Custom Tools • CAD to GIS Conversion (cont.): • Python Scripts • GIS Conversion – generates NSF and GSF polygons from CAD dwg, then attributes NSF polygons with CAD annotation • Stack Building Levels – stacks each building level to a common x,y coordinate origin and creates a new FC per level • Offset Building Levels – takes individual building level FCs and appends to a new FC with the levels offset

22. Custom Tools • Enterprise Management: • Python Script • Update Detail line FCs – converts source CAD dwgs and converts to GIS (w/ schema) and overwrites existing Detail line FCs

23. Custom Tools • Enterprise Management: (cont.) • Python Script • Update Stacked_UCS FCs – migrates building updates from feature datasets to standalone FCs (that contain all buildings)

24. Custom Tools • Enterprise Management: (cont.) • Python Script • Update Offset_UCSFCs – migrates building updates from feature datasets, offsets the levels, and appends to standalone FCs (that contain all buildings)

25. Custom Tools • Enterprise Management:(cont.) • Python Script • Update Stacked_PCSFCs – migrates building updates from feature datasets, georeferences each level, and appends to standalone FCs (that contain all buildings)

26. Custom Tools • Production Tools (cont.): • Generate DDP Floor Plan Maps (Python Script ) • User can choose between three different page sizes: 8.5x11, 11x17, or 24x36

27. Custom Tools • Production Tools(cont.): • Create Production MXD Tool (Python Script ) • Generates an Production MXD for a specified building that contains layer groups for each level and applies custom symbology to each layer

28. Custom Tools • Production Tools (cont.): • Generate Square Footage Indicies(.NET application) • Creates a detailed multi-paged excel spreadsheet of level and room data for a given building

29. Products

30. Products • Web Applications: • BAM (Building Analysis Map) • Application that allows access to campus-wide facilities information (restricted user access) • View the interactive floor plan for a specified building and symbolize data based on room usage code, department usage, etc.

31. Products • Web Applications (cont.): • Floor-Plan Analysis System Tester • Testing application (internal-use only) for visualizing georeferenced floor plans

32. Products • Maps: • Facilities Plan data is used to support a variety of campus-wide needs

33. Questions &Comments Brian Zimmermann, GISP GIS Analyst brian.zimmermann@uky.edu 859-218-3162