Home

SDPG_Part3_Programmers Guide_v1_0

1. 12 Figure 4 Migrating from Conceptual to Logical Model 13 Figure 5 Logical Model of Schedule Calendar Date Concept 14 Figure 6 Physical Model of the Schedule Calendar Date 15 Figure 7 SDP Database Application Directory Structure 21 Figure 8 SDP Startup Macro Initialization Directory Brommt 22 Figure 9 SDP Startup Macro Initialization Directory Results 23 Figure 10 XMLSpy SDP Project File at Startup cisnienia rnini 21 Figure 11 XMLSpy Showing Validation Forge 28 Figure 12 MSXSD Application Showing a Validation Report 29 Figure 13 MSXSD Application Showing Validation Error Report 30 Figure 14 Schedule Data Processing Data Flow Dageram 34 Figure 15 SDP Directory Structure Hierarchy essere 36 Figure 16 High Level SDP Metadata XML Schema 43 Figure 17 Metadata SDP XML Schema Model from SM Sp 44 Fteure I5 SDP Metadata Attribute eet 45 Figure 19 SDP Metadata XML Schema Fragment of Identification Element 46 Figure 20 SDP Metadata XML Schema Fragment of Description Element 47 Figure 21 SDP Metadata XML Schema Fragment of Status Element 48 Figure 22 SDP Metadata XML Schema Fragment of Distribution Information Element 49 Figure 23 SDP Metadata XML Schema Fragment of Data Quality Element 49 Figure 24 SDP Metadata XML Schema Fragment of Spatial Data Element 50 Figure 25 SDP Met
2. Figure 10 on the following page shows XMLSpy after startup of the sdp spp an XMLSpy project file The XMLSpy project file contains a list of schema files and XML documents A portion of the sdp MNR Base April2008 xml file is shown in the center At the bottom of the figure and part of the XMLSpy application 1s a panel containing a Validation Report that indicates that the sdp MNR Base April2008 xml document is valid This means that the XML document conforms to all the schema rules including e Sequence and order of elements e All mandatory elements present e All data content within the value constraints SDPG Part3 Programmers Guide v1 Opdf 25 Part 3 SDP Programmer s Guide 30 June 2008 If we change one of the data values to an incorrect value for example the effectiveDate attribute of the lt Agency gt element to 2008 13 01 and run the validation the XMLSpy application will provide a description of the error in the Validation Report panel at bottom highlight the location of the error in the document and print the xPath location of the error found This is shown in Figure 11 SDPG Part3 Programmers Guide vl Opdf 26 Part 3 SDP Programmer s Guide 30 June 2008 Validate Document Button Altova XMLSpy sdp_MNR_Base_April2008 xml Tel File Edit Project ML DTD Schema Schema design ZGlfOuerg thentic Convert View Browser WSDL SOAP Tools Window Help _ qx aM T Me fr ap E T eme EHE Lom mir
3. activationDate deactivationDate placementDate dayOfWeek holiday Q M ScheduleRevision PK FK1 scheduleVersionID PK revisionNumber Schedule Calendar Date PK FK1 date PK FK2 dayType placementDate activationDate deactivationDate activationDate deactivationDate placementDate scheduleVersionType E revisionNumber histor se FK3 scheduleVersion D eee FK3 routeDepotVersion RouteDepotVersion PK FK1 revisionNumber Day Type scheduleVersionID eS routeDepotVersion Gay lype routelD E m depotID ey activationDate Imecn deactivationDate Figure 5 Logical Model of Schedule Calendar Date Concept 3 3 3 Example of the Physical Database Implementation The physical model is similar to the logical model except the data types are defined by the database management system The physical database also supports procedures that enforce referential integrity triggers primary and foreign keys when data are added changed or deleted from the database A generic physical model for the Schedule Calendar Date concept 1s illustrated in Figure 6 As is shown in the figure this is similar to the logical model shown in Figure 5 except for defining specific data types and showing the procedures For organizations with specific database management systems the logical and physical representations are somewhat redundant since logical and physical models will use the same data typ
4. 2 3 Using the SDP Template Spreadsheet The SDP Template Spreadsheet provides a useful cheat sheet for developing translations between native data and SDP data concepts The spreadsheet includes 9 worksheets Notes on How to Use the Template includes notes on how to use the worksheets CodeList includes the SDP element name description XML code fragment and guidance on how to use the code values Guidance includes how to classify special real world objects For example a subway is a heavy rail HR mode Glossary defines the data concepts and branch where it 1s placed Each SDP Branch has its own worksheet Each worksheet is organized similarly The worksheet contains five Columns o Required describes whether the high level element is mandatory M or optional O o Element Name lists the SDP data concept high level element o Type lists the XML type or referenced type This field also includes referential integrity rules such as unique identifier 1dentifying key A nested element will list the name of another Element Name either on the same worksheet or on another one o Questions to Ask describes the guidance related to a high level embedded or nested element o Native Format a placeholder for recording notes on which native data match the SDP element AgencyRegistration branch describes the elements nested and embedded elements in the AgencyRegistration branch of the SDP XML Schema This page also includes
5. have been written for a user interested in understanding how to use an SDP application without the need to understand the details of the processing involved These are listed below SDP Application LIBus2Sdp Conversion Setup and User s Manual SDP Application RTIF2Sdp Conversion Setup and User s Manual SDP Application STIF2Sdp Conversion Setup and User s Manual SDP Application SDP Csv2Xml Conversion Setup and User s Manual SDP Application SDP Xml2Csv Conversion Setup and User s Manual SDP Application SDP Csv2Gtfs Conversion Setup and User s Manual 6 2 SDP Application System Architecture The SDP Application System Architecture shown in Figure 14 describes the relationship of SDP applications and system interfaces inputs and outputs SDPG Part3 Programmers Guide vl Opdf 33 Part 3 SDP Programmer s Guide 30 June 2008 Schedule Data Profile Processing Data Flow Diagram LiBus LIRR MNR Source Trapeze gt gt E gt SDP XML Native Dat Flat Files LIBus2Sdp EE Csv2Xml Xml2Csv e NOTE MTABus Sdp2Gtfs uses the following subset of SDP CSV files Source SDP CSV sdp_agency csv WDMS Query sdp scheduleVersion csv Outputs sdp_route csv sdp location csv sdp_patternEventList csv sdp_trip s csv I sdp_tripTimes csv RTIF2Sdp STIF2Sdp WDMS CSV Wdms2Sdp GTFS CS
6. the SDP Document attribute group for the ScheduleVersion data concept Service branch describes the elements nested and embedded elements in the Service branch of the SDP XML Schema Transit Network branch describes the elements nested and embedded elements in the Service branch of the SDP XML Schema Transit Gazetteer branch describes the elements nested and embedded elements in the Transit Gazetteer branch of the SDP XML Schema Transit Facility branch describes the elements nested and embedded elements in the Transit Facility branch of the SDP XML Schema AddressStructure describes the data structure for an address The AddressStructure is nested in the Agency and TransitStop elements The Excel Template may be found at the following link http www consvstec com tsdea rstwg documents SDPxml template vl O xls SDPG Part3 Programmers Guide vl Opdf 9 Part 3 SDP Programmer s Manual 30 June 2008 3 Guidance on Building a Physical Database from the SDP 3 1 Conceptual Data Reference Model as a Framework for Implementation Methods As described in Guidance Part 2 volumes the SDP Project used a system engineering approach for developing user driven requirements for schedule and related data A set of Use Cases on Integrated Trip Planning Dynamic Generation and Presentation of Public Timetables and Generation of Ad Hoc Scheduling were developed to identify the specific schedule data requirements for these down
7. Guide v1 Opdf 4 Part 3 SDP Programmer s Manual 30 June 2008 A Category Requirements tepln html o Default is NAD 83 and UTM 18 o Default spherical coordinates units is decimal degrees with 6 decimal places Linear reference units e g feet meters decimeters include precision resolution and accuracy for measurement o Default is feet Distribution List of available interfaces Information Contact Information o Name telephone email URL When the TSDEA provides a capability to document and store the metadata many of the requirements may be automatically or manually entered into a metadata document The schedule calendar requirement was separated from the metadata and will be submitted as a separate document Guidance information on the schedule calendar XML schema may be found in the SDP Guidance Part 2 Chapter 9 7 4 3 Metadata XML Schema Model The Metadata XML Schema is described in this section The high level SDP Metadata schema is depicted in Figure 16 High Level SDP Metadata XML Schema The depiction includes the categories described in the Requirements Description Each node of the schema includes the details related to the required information SDPG Part3 Programmers Guide v1 Opdf 42 Part 3 SDP Programmer s Manual 30 June 2008 Metadata Identification The conventions used to depict the XML Schema requirements are as Description follows Each node branching from the root node Metadata has
8. document is Part 3 SDP Programmer s Guide 1 3 Programmer s Guide Organization The SDP Programmer s Guide is divided into six stand alone sections Following this chapter the Programmer s Guide contains five sections including Chapter 2 A Guide to Translating Native Data to SDP Chapter 3 Guidance on Building a Physical Database Chapter 4 Using the SDP XML Schema for XML Document Validation Chapter 5 Application Design Reference Manuals Chapter 6 SDP and Metadata Each section may include hyperlinks to software database or other large document In addition embedded files may be included in some chapters e g the Application Design Reference Manual embeds six reference manual for six applications developed for the SDP SDPG Part3 Programmers Guide vl Opdf 6 Part 3 SDP Programmer s Manual 30 June 2008 2 A Guide to Translating Native Data to SDP The Guide to Translating Native Data to SDP describes the Guidance documents and tools as well as a straight forward process to map native schedule data to the SDP data concepts and formats This manual 1s written for a technical reader who has XML programming or database experience and who has knowledge of the procedures used to translate one format to another 2 1 Templates Quick Start and User Requirements Guidance several Guidance documents and tools were developed to support the translation of native data to SDP The most useful documents for th
9. exactly one 1 one to many 1 or zero to many 0 number of records included in the node This notation is only depicted in the top level schema Status DistributionInformation model Figure 1 m m m Bolded attributes elements and nodes are mandatory that is at least DataQuality one value must be included in the 1 element or record Note the use of italics in this convention list refers to terms used by the XML Schema or XML standards SpatialDataset c EN CodeSet 0 SpecialConventions 0 Figure 16 High Level SDP Metadata XML Schema Using the XMLSpy software see Appendix A for description of the notation the high level SDP Metadata XML Schema is depicted in Figure 17 The Metadata document attribute group 1s depicted in Figure 18 Figures 19 26 describe the high level elements in the schema The schema is composed of three files similar to the SDP XML Schema The files include Main Schema Document SDP Metadata XML Schema V0O 1 xsd Complex Type Descriptions SDPM common VO 1 xsd Simple Type Descriptions SDPM domain VO 1 xsd These documents may be found on http www consystec com tsdea rstwg docs html Although a white paper describing the requirements was circulated during the TSDEA Project the schema and requirements have not yet been implemented SDPG Part3 Programmers Guide v1 Opdf 43 Part 3 SDP Programmer s Manual ee Iden
10. in Visual Basic for Access that runs under the Windows Operating System Table 2 outlines the operating system and version requirements for the database instance example SDPG Part3 Programmers Guide v1 Opdf 20 Part 3 SDP Programmer s Manual 30 June 2008 Table 2 SDP Access Database Operating System and Version Requirements System Version SDP XML Schema 2003 SP2 MS Windows Windows XP Prerequisites Microsoft Windows and Access 2003 Database The TSDEA web site http www consystec com tsdea rstwg docs html contains a copy of the Microsoft Access 2003 database content file but not the database software itself 3 5 2 Data Files 3 5 2 1 Set Up the SDP Directory Structure The figure below shows where to install the SDP instance database called sdp db mdb You may choose any SDP root path directory We have labeled this SDP root as SDP in the figure below and use this label in the remainder the user s manual If you are copying files from the CD the directory structure 1s set up as described in Figure 7 SDP Database Application Example Directory Structure SDP is a directory of your choosing SDP data Application Input Files SDP CSV files Application Software Module Cdp dd CRnr 5 Car Cunn Cup D Oe Goachusi Figure 7 SDP Database Application Directory Structure The Sample SDP Database Instance is located in the SDP daa sub directory of the SDP directory We will use a forward slash notation to indica
11. processing Below these directories that contain native data processing applications is a directory called Native data that contains sub directories for each an agency s native format data To date Native data directory contains the native data files for Long Island Bus and the RTIF and STIF files of New York City Transit The LIB processing RTIF processing and STIF processing directories contain applications that convert the individual agency data from there native formats to the SDP CSV format The CSV files are written to a specific sub directory of SDP data one for each agency native data source 6 4 6 2 SDP Data Processing and Data The SDP processing sub directory contains the applications that quality check and convert the CSV outputs of the native data processing to SDP format files This includes applications that convert SDP CSV to XML and SDP CSV to the Google Transit Feed Spec format The SDP_xml2csv sub directory contains the applications necessary to perform an XML schema validation of agency specific XML documents 6 5 Appendix A LIBus2Sdp Conversion Setup and User s Manual This manual contains guidance on the application LIBus2SDP Conversion of Long Island Bus Native Data Files to SDP See http www consystec com tsdea rstwg docs html to download the User Manual See http www consystec com tsdea rstwg applications html to download the application code and user manual 6 6 Appendix B RTIF2Sdp Conversion Set
12. the SBP aacument testLogSet type TestLogStructure 1 o testPlan type Figure 21 SDP Metadata XML Schema Fragment of Status Element SDPG Part3 Programmers Guide v1 Opdf 48 Part 3 SDP Programmer s Manual 30 June 2008 notNullString min maxLen 1 Describes how the content of the SDP Document may be distributed and to whom requests for access should be sent Describes the processes and procedures applied to meste the SDP document P type notNulString min maxLen 1 Figure 23 SDP Metadata XML Schema Fragment of Data Quality Element SDPG Part3 Programmers Guide v1 Opdf 49 Part 3 SDP Programmer s Manual 30 June 2008 Describes the spatial attributes and location referencing conventions and standards used by the SDP document pr 1 0 2 Lists additional local use codes used in enumerated Figure 25 SDP Metadata XML Schema Fragment of Code Set Element SDPG Part3 Programmers Guide vl Opdf 50 Part 3 SDP Programmer s Manual 30 June 2008 Describes special conventions used to extend or constrain the semantics of the elements in the SDP elements For example it might describe naming conventions for identifiers Figure 26 SDP Metadata XML Schema Fragment of Special Conventions Element SDPG Part3 Programmers Guide v1 Opdf 51 Part 3 SDP Programmer s Manual 30 June 2008 7 5 Appendix A XMLSpy Schema Notati
13. 1 Open Source Open Platform The TSDEA is an open source open platform project All the software developed under the project is subject to an adapted Berkeley Open Source license 6 4 2 Operating Systems The SDP demonstration is an open platform project SDP applications were developed and tested under the Microsoft WindowsXP operating system Several applications early during development were tested under the Linux operating as a proof of concept of the open platform philosophy 6 4 3 Data Encoding Formats All data processed by the SDP applications is ASCII text data No binary formats are specified and therefore files are cross platform The following summarized the data encoding formats of file content of the SDP applications e Native Data Formats The TSDEA SDP demonstration processes native ASCII format files for Long Island Bus and New York City Transit RTIF and STIF e CSV Comma Separated Values a k a Comma Separated Variables is a standardized ASCII format for describing tabular information CSV files optionally contain a header record listing column names following by rows containing values corresponding with the column names e XML eXtensible Markup Language uses a tag based notation for representing hierarchically organized 1 e tree structure data SDPG Part3 Programmers Guide vl Opdf 35 Part 3 SDP Programmer s Guide 30 June 2008 6 4 4 Databases No databases were used during this demonstratio
14. 2 6 3 6 4 6 5 6 6 6 7 6 8 6 9 7 1 7 2 Ju 7 4 USME ACO X MLS PY uos eeseesie eeto eer ea dida eas peeee vacui ee oae eeeus eon ion PE ege 25 Microsoft MSXML2 Windows Scripting Host Application eee e eee e eee eene eene eeu 29 4 7 1 DOM Ware MSA lA OM BEE 29 4 71 2 AJDplicadon EXECU ON ere 29 APPENDIX A MSXSD JS SOURCE AND BATCH FILES 31 APPLICATION DESIGN REFERENCE MANUALS 32 rte ode o nhu 32 6 1 1 Intended Ee ee 32 6 1 2 SDP Application User s Manuals esee e eto ert er c re nents 33 SDP Application System Architecture cc eeee eee e eee ee eee eee eee eee eee eee e eese eese esses sss ee ee teet eee 33 SDP Application Module Descriptions ci0s ccccssecesssesssvesssssssessescoonnessessensecssessesesssecscecoesseseessesses 35 Application Development Environment ccccccccccccssssssssssssssssssssssccccccccccccccsscsssssssscsscsscsoooes 35 6 4 1 Open Source Open Plattform 35 6 4 2 ODeraumne EE 35 6 4 3 Data Encoding FORMA octies mice to Ratte Mn aqua E 35 6 4 4 RE 36 6 4 5 Programming Lano naGes cy tud bed ntu UNDE 36 6 4 6 Direccion ICC NE ETT 36 Appendix A LIBus2Sdp Conversion Setup and User s Manual cccccssssssssssssssssssssssssssees 37 Appendix B RTIF2Sdp Conversion Setup and User s Manual eee eeeeeeeene 37 App
15. NYSDOT Task 2 A Extension Transit Schedule Data Exchange Architecture TSDEA SDP Guidance Documentation PART 3 SDP Programmer s Guide Version 1 0 June 30 2008 By consysTec Oem Consensus Systems Technologies Inc 617 983 3364 Prepared for New York State Department of Transportation Part 3 SDP Programmer s Guide 30 June 2008 1 1 1 2 1 5 2 1 2 2 5 3 1 3 2 3 3 3 4 3 5 4 1 4 2 4 3 4 4 4 5 Table of Contents OVERVIEW eege 5 Purpose OF DOCUHIOH o ioi anaE EEe 5 Document Structure and ODJeCctives cccccccccsssssssssssssoccccccccecscssssseseessssssovscsccconccessssseseeessssooossoses 5 Programmer s Guide OrganiZatiOn ccccssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss 6 A GUIDE TO TRANSLATING NATIVE DATA TO SDP 7 Templates Quick Start and User Requirements Guidance cc eeee ecce eere e eene neue 7 A Simple Process for Translating Native to SDP sssssssssssscccccccccccccssssssssssccscccsccccssssssseees 7 Using the SDP Template Spreadsheet eere eee eee ee eee eee u ener rore rr eee eee eere ee eo e eee ee erre ee 9 GUIDANCE ON BUILDING A PHYSICAL DATABASE FROM THE SDP 10 Conceptual Data Reference Model as a Framework for Implementation Methods 10 Differences between the SDP CDRM
16. V P agency txt RTIF STIF calendar txt route txt Legend shapes txt Data Store data flow stop_times txt stops txt NYCT trips txt Agency Source Sink GOOGLE Interface lt lt Source HASTIS gt gt Last Update June 19 2008 Figure 14 Schedule Data Processing Data Flow Diagram SDPG Part3 Programmers Guide vl Opdf 34 Part 3 SDP Programmer s Guide 30 June 2008 6 3 SDP Application Module Descriptions Each SDP application 1s comprised of one or more software modules A software module as used here is a single file containing software code This design document contains a complete description of the software modules used by all the SDP applications A module is described completely by the following e Module Name The software module name e Module Descriptions A high level description of the module s processing and functions e Inputs The names of the files opened for the purposes of reading by the module e Outputs The names of the files opened for the purposes of writing by the module e Startup Parameters List of any parameters that may be passed into the file at startup for example command line arguments e Programming Language and Version The programming language and version of the module The Application User s Modules contain a description of each software module 6 4 Application Development Environment 6 4
17. Validation x Se Dmm alalal x SI Ca File Program Fleczu pnache Group pacheintdocs sDPSDP schema alidatiomMiNR Edp MNP Baze April2008 xml ig valid lil A Validation Find in Files Path ML pv v2U07 spl Registered to Manny Insignares ConsysTec Corp 1998 2006 Altova GmbH Ln 1 Col 1 Figure 10 XMLSpy SDP Project File at Startup SDPG Part3 Programmers Guide v1 Opdf 27 Part 3 SDP Programmer s Guide 30 June 2008 X Altova XMLSpy sdp MNR Base April2008 xml l ex File Edit Project XML DTD S5chema Schema design XSL KQuerv Authentic Convert View Browser WSDL SOAP Tools Window Help Il ie i E E El at Project SE lt xml version 1 0 7 gt Elements ux CL SDP H lt SUPIOO xmlns xsi http fawn wo org 2001 IL Schema Instance 1 fa XML Files S xmlns xed http aww org UD MI Schema a sdp LlBus 208 xml 4 activatianDate 2008 04 06 m sch MNA Base And 5 deactivatio nDate 2008 10 04 XSL Files b picKNo Query Files H placementDate 2000 04 Ob HTML Files z scheduleVersianiID baseGraup El schedule versianDescriptionz April 2006 Base 4 5 2008 10 4 2006 DTD Schemas CH GML_geomety xsd 10 xmlns htto Awww tsdea com schemafSsDP100 GA SDP_common_v6 xsd 11 xsi schemaLacation http www tsdea com schema SDP10U schema SDP XML Schema v6 xsd SDP domain vb xsd 12 l d Tj SDP KML Schema vE q3 e zAgencyRegistratians ee Gg Entities 14 CO lt Agency effect
18. adata Attribute Group SDPG _ Part3 Programmers Guide vl Opdf 45 Part 3 SDP Programmer s Manual Identification type Identification Structure Describes the identification information about the submission and standards used to define an SDP document Figure 19 SDP Metadata XML Schema Fragment of Identification Element contact notNullString min maxLen contactTel type TELEPHONE min maxLen notNullString min maxL en 1 approval Staff notNullString min maxLen sdpSchemaVersion type min maxL en 1 KE emmmer a approvalCode registrationDate type ed date 1 publicationDate L type xsd date SDPG Part3 Programmers Guide vl Opdf schemaVersionlD id ENCRYPTTION KEY min maxLen 16 30 June 2008 46 Part 3 SDP Programmer s Manual Identifies the SDP Document s to which this mestedata applies type notN min maxLen M SDPG Part3 Programmers Guide v1 Opdf 30 June 2008 47 Part 3 SDP Programmer s Manual 30 June 2008 level updateF reg tvpe updateFreq cd 7 next pdate type xsd date TestStructure ei testPlan testPlan_id LA E ype Teststructure testStatus ype testStatus cd Status Status d type StatusStructure 1 3 Defines the validation and verification tests and procedures applied to
19. adata XML Schema Fragment of Code Set Element 50 Figure 26 SDP Metadata XML Schema Fragment of Special Conventions Element 51 Figure 27 Example of the XMLSpy Diagram Notation nnnn0o0noooooooeeennnennesnssssssssssesee 53 SDPG Part3 Programmers Guide v1 Opdf 4 Part 3 SDP Programmer s Guide 30 June 2008 1 Overview 1 1 Purpose of Document This SDP Programmer s Guide is intended to assist developers and technical users in understanding how to convert agency schedule information into SDP format to allow the efficient exchange of schedule information between agencies in the New York City region Part 3 SDP Programmer s Guide contains several manuals that include tools applications physical databases and XML Schema descriptions for implementation Each Chapter is developed as a self contained unit Hyperlinks and file objects are embedded in the document for quick access All the applications and user manuals included in this document were tested however in the event of a problem please send a bug report to tsdea consystec com 1 2 Document Structure and Objectives The SDP Programmer s Guide is meant to provide technical information on the SDP to technical users System Integrators and Application Developers In order to meet the needs of the varied set of stakeholders a series of four Guidance documents were developed as shown in Figure 1 1 Overview why what and map of docu
20. and Implementation Methods 10 An Example of Migrating the CDRM to a Logical Physical and XML Schema Representation 11 3 3 Example of the Conceptual Data Reference Model 11 3 522 Example of the Logical Entity Relationship Representation 12 3 3 9 Example of the Physical Database Implementation 14 Database Scripts and Referential Integrity SSUeG csssssssscsccccccccccccccsssssssssscccscccsssooes 16 3 4 1 Re lerential Inte Prrty SSW SS 5 tede aolet iode o noie debe oret do acts 16 3 4 2 Tempotal Integrity 59068 cqui tup eu ees dnx eternum ioo praes ie 19 Implementation of a SDP Database in MS A ccess eee e eee e eee eee ee eee eee e eate e eee eese e eae 20 S RS E SEI El E e EEN 20 3 52 DOE MIS Sao EIC PE M LUDUM eS DE 21 3 5 3 JXDDItCaton EEN 22 USING THE SDP XML SCHEMA FOR XML DOCUMENT VALIDATION 24 nterne 24 Structure of the SDP XML eher EES 24 Structure of the Schedule Calendar Date SCD XML Schema eee e eee eee nn nu 25 Elements of the XML Schema ioruiieiesacesu ue gan egene eege deeg 25 Validating XML Documents Using the XML Schema eee e eee ee e eee eee eee etes eese ee eeeouo 25 SDPG Part3 Programmers Guide v1 Opdf 2 Part 3 SDP Programmer s Guide 30 June 2008 4 6 4 7 6 1 6
21. c com tsdea rstwg docs html to download the User Manual See http www consystec com tsdea rstwg applications html to download the application code and user manual 6 10 Appendix F SDP Csv2Gtfs Conversion Setup and User s Manual This manual contains guidance on the application Csv2GTFS which converts SDP CSV Files to the Google Transit Feed Specification GTFS files The application uses the GTFS February 2008 version See http www consystec com tsdea rstwg docs html to download the User Manual See http www consystec com tsdea rstwg applications html to download the application code and user manual SDPG Part3 Programmers Guide v1 Opdf 38 Part 3 SDP Programmer s Manual 30 June 2008 7 The SDP and Metadata 7 1 Introduction This chapter introduces the metadata for the Schedule Data Profile Metadata 1s used to describe one or more SDP Documents that form the collection of a schedule version including all revisions and route depot versions or schedule version revision 7 2 What is SDP Metadata Metadata is often defined as data about data It summarizes the who what when where why and how of the data set Metadata helps people find data that 1s appropriate for their use SDP metadata will help e preserve the data history so that it can be re used or adapted e assess the age and character of your data set e provide a place for agencies to document extensions to their data sets for internal or spec
22. cal Model Figure o Note the attributes are specified with specific data types that reference the specific database management system specifications 3 3 1 Example of the Conceptual Data Reference Model The CDRM is expressed as an Entity Relationship Diagram ERD Figure 3 shows an example for the basic representation of the schedule calendar date concept SDPG Part3 Programmers Guide vl Opdf 11 Part 3 SDP Programmer s Manual 30 June 2008 Route_Depot_Version SDP routeDepotVersionID pi M activationDate M deactivationDate M Schedule Calendar Date data type SDP Day Type calendarDate pi M SDP dayType pi lt M gt daylypeDescription M placementDate M agencyID activationDate lt M gt timeBegin deactivationDate lt M gt timeEnd E so rae pi M is defined by defines Calendar Date date pi M day OfW eek lt M gt holiday Figure 3 Conceptual ER Model of Schedule Calendar Date Concept The following paragraph describes the requirements of Figure 3 Transit service 1s scheduled for each day of operation Service components may be scheduled to operate on different dates depending on a number of factors These factors may be schedule based for example special trips are designated when there is an event at Shea Stadium or service to evacuate workers from the city during a s
23. cesID featureType cd trackNo Platform Track stopID portalID locationID Portal effectiveDate endDate SDPG Part3 Programmers Guide vl Opdf 17 Part 3 SDP Programmer s Manual 30 June 2008 IDENTIFYING KEY IDENTIFYING ENTITY NAME RELATED KEY RELATED KEYS NAME S relativeLocationID locationID i Relative Location routeID routeBeginDate mode Route routeEndDate scheduleVersionID effectiveDate endDate routeDepotVersionID revisionNo day Type Route Depot Version scheduleVersionl effectiveDate endDate D scheduleVersionID Route Direction routeGroupingID scheduleVersionID Route Grouping effectiveDate endDate routeGroupingCode Route Grouping Type calendarDate Schedule Calendar Date scheduleVersionI D scheduleVersionID activationDate agencyID Schedule Version deactivationDate scheduleVersionType Schedule Version Type identifier locationD Service Area revisionNo plantCompID activationDate Status deactivationDate placementDate modificationDate creationDate statusTypeCode Status Cod Type timepointID locationID Timepoint scheduleVersionID effectiveDate endDate routeDirection endDate transferClusterName setoflocationID Transfer Cluster effectiveDate endDate transitFacilityID locationID Transit Facility effectiveDate endDate tranPathID locationID Ordered list of Tran
24. ch data set organization In theory the TSDEA will support multiple transit agency schedules it may support several schedule versions and revisions for a single transit operator Managing these data sets will provide information on the SDP Document submission s identity status extensions customizations data quality and other information needed to describe the purpose fitness for use and distribution policies Management will be List adapted from Why bother with Metadata http www fgdc gov metadata metadata business case SDPG Part3 Programmers Guide vl Opdf 39 Part 3 SDP Programmer s Manual 30 June 2008 accomplished through a SDP Metadata XML Schema submission and metadata application programming interface API 7 4 SDP Metadata XML Schema 7 4 1 Overview The SDP Metadata XML Schema is composed of several packages These packages are based on best practices identified by several metadata standards including e ASTM E2468 05 Standard Practice for Metadata to Support Archived Data Management Systems e IEEE 1489 1488 ITS Data Dictionary and Message Set e FGDC Content Standard for Digital Geospatial Metadata e ISO 11179 Metadata Registries The ASTM references seven categories of information that should be included in a metadata They include Identification Information Data Quality Information Spatial Data Organization Information Spatial Reference Information Entity and Attribute Information Dis
25. d Includes a definition of identifiers and code enumerations used in the SCD 4 4 Elements of the XML Schema The SDP Reference Data Model is an implementation neutral representation of the static design that fulfills the SDP user requirements The SDP data model describes the static data structure data relationships and valid value rules for the information used in SDP applications The SDP data model specifies the following e Sequence and order of data e Multiplicity or number of times a data element can be represented e Defines re usable types for example longitude and latitude are used together so a re usable point type may be defined e Specifies mandatory 1 of more required and optional 0 or more required data elements e Specifies data value ranges and other constraints for example only the floating point numbers 90 000000 through 90 000000 may be used to define a latitude value only the 29 66 29 66 29 following valid text codes may be used to describe a dayType weekday mon tue ch e Key References 4 5 Validating XML Documents Using the XML Schema The SDP demonstration project used the Altova XMLSpy software and a Microsoft Windows Scripting Host application developed as part of the demonstration project to validate both the XML schemas and XML documents How to use these two applications to validate XML documents are the topics of Section 4 6 and Section 4 7 below 4 6 Using Altova XMLSpy
26. developers and data modelers interested in creating valid SDP XML data and e System managers responsible for setting up the run time environment for SDP applications 4 2 Structure of the SDP XML Schema The SDP XML Schema is comprised of four files These are listed below e SDP XML Schema vl O xsd This is the base schema which imports the SDP Common and SDP Domain schemas It defines the structure of the SDP at the highest level The SDP100 root element contains the following o AgencyRegistration Service TransitNetwork TransitGazatteer TransitFacilities e SDP common vl O xsd Include a definition of complex elements included by the highest level elements of the SDP e SDP domain vl O xsd Includes a definition of identifiers and code enumerations used in the SDP O O O O SDPG Part3 Programmers Guide vl Opdf 24 Part 3 SDP Programmer s Guide 30 June 2008 e GML geometry xsd Contains definitions of the Geography Markup Language GML used in the SDP 4 3 Structure of the Schedule Calendar Date SCD XML Schema A second schema that is part of the demonstration project is the Schedule Calendar Date schema which is made up of two files These are listed below e SDP Schedule Calendar Date vl xsd This is the base schema which imports the SDP domain scd schema It defines the structure of the Calendar and Schedule Calendar Date The Calenday root is comprised of Schedule Calendar date elements e SDP domain scd VI O xs
27. ding script The physical database should be optimized for its use Because primary and foreign keys are not specifically identified 1n the conceptual model tables are not optimized for queries and multiple agencies or versions are not assumed in the CDRM the script will need to be revised and augmented For inquiries on obtaining a script to generate a SDP physical database please contact tsdea consystec com 3 4 1 Referential Integrity Issues Referential integrity in a database ensures that tables ensures the identity and relationships among data concepts are unique consistent and unambiguous The utility to ensure these characteristics are the assignment of primary and foreign keys in the physical database management system Although the CDRM does not use the terms primary and foreign keys it classifies one or more identifying keys primary keys for each entity which becomes a table in the physical database The model also inserts related identifying keys in some entities and in some cases there are non identifying keys in an entity The CDRM entity their identifying keys related identifying keys and non identifying keys are listed in Table 1 CDRM Entity with its Identifying and Non Identifying Keys Table 1 CDRM Exntity with its Identifying and Non Identifying Keys IDENTIFYING KEY IDENTIFYING ENTITY NAME RELATED KEY RELATED KEYS NAME S agencyID effectiveDate Agency endDate blockID Amenity Type scheduleVersionID Bloc
28. e uninitiated SDP translator are the following SDP Quick Start Guide http www consystec com tsdea rstwg SDP QS web htm This web document provides a high level overview of the requirements for translating native data to the mandatory elements of the SDP XML Document Each mandatory element is described along with its format and an example of its usage In addition conventions and code values are included in the document SDP Template Spreadsheet This MS Excel spreadsheet contains several pages that describe the entire SDP XML Schema mandatory and optional The template includes a comprehensive data concept glossary as well as a description of all the codes and their values The template will be described in Section 2 3 below SDP Guidance Documents As described in Section 1 2 the Guidance Documents consist of four parts Ed Note part 4 will be implemented when the TSDEA is operating Part 2 User Requirements Manual may be used as a comprehensive reference o SDP Guidance Document Part 2 User Requirements Part 2 may be used as a reference manual to view detailed examples of how different organizations map their native data to the SDP In addition the context and requirements related to the SDP data concepts are explained in this document Chapters 4 through 10 describe how to map native data to the SDP data concepts by each branch in the SDP XML Schema Appendix A Considerations for Rail Transit discusses how to apply th
29. e 71 location begin date Public Description 5 1 03 12 31 9999 Union amp 5th Example Table 72 Public Description 5 TM 30 03 Union amp 5th l5 7 1 03 12 31 9999 MLK amp 5th In this manner historical data can be matched accurately Alternatively if the location changes eo nearside to farside a new ID and new record is created and the old ID is retired from FTA NJ 26 7044 2003 1 Best Practices for Using Geographic Data in Transit A Location Referencing Guidebook April 2005 pf 79 free download from http www fta dot gov documents LRG FinalPublication pdf Many of the principles for managing temporal databases may be found in Richard T Snodgrass Developing Time Oriented Database Applications in SQL Morgan Kaufmann Publishers San Francisco CA 2000 free download at http www cs arizona edu people rts tdbbook pdf 3 5 Implementation of a SDP Database in MS Access The SDP demonstration contains a sample Microsoft Access database instance of the SDP Sample SDP database The referential integrity procedures and triggers are not installed in this database The database executes a macro at startup that reads a set of SDP csv file format files at run time and loads the data into the database The user may then view table data and create new queries to examine the SDP data and structure 3 5 1 Software Installation The Sample SDP database contains a startup macro application written
30. e SDP concepts to rail concepts particularly those that differ from bus transit A glossary is included in Appendix B of this document 2 2 A Simple Process for Translating Native to SDP The process described in this section is similar to one used by many software developers to map one data format to another As depicted in Figure 2 there are four major steps needed to map native data to SDP SDPG Part3 Programmers Guide vl Opdf I Part 3 SDP Programmer s Manual 30 June 2008 3 Know native 1 Familiarize e lee ve Schedule and SDP Data document TY Concepts i formats EALE sources k I 4 Map native dese Develop script for translating data relationships and formats to SDP XML Schema requirements Figure 2 SDP Translation Process Steps to build a native data to SDP XML document translator l Familiarize yourself with SDP data concepts codes and schema elements The SDP Quick Start web document will provide the basic overview To see more detail on non mandatory element see the User Requirements Manual Part 2 Guidance document Check your skills and experience with key technical areas to develop script and code Do you understand XML and SDP Schema organization Do you have experience programming or developing modules for DBMS Do you understand how to validate SDP XML documents Review your understanding of the seman
31. e definitions SDPG Part3 Programmers Guide vl Opdf 14 Part 3 SDP Programmer s Manual 30 June 2008 ScheduleVersion scheduleVersionID CHAR 10 scheduleVersionDescription CHAR 10 pickNo CHAR 10 activationDate CHAR 10 deactivationDate CHAR 10 placementDate CHAR 10 CalendarDate pk ame ang E dayOfWeek CHAR 10 10 holiday TEXT 10 Ohh ScheduleRevision scheduleVersionID CHAR 10 revisionNumber CHAR 10 activationDate CHAR 10 deactivationDate CHAR 10 placementDate CHAR 10 scheduleVersionType CHAR 10 AN Schedule Calendar Date date DATETIME dayType CHAR 10 placementDate DATETIME activationDate DATETIME deactivationDate DATETIME revisionNumber CHAR 10 scheduleVersionID CHAR 10 routeDepotVersion CHAR 10 i A history CHAR 10 9 Oh RouteDepotVersion revisionNumber CHAR 10 scheduleVersionID CHAR 10 routeDepotVersion CHAR 10 Day_Type PK dayType CHAR 10 dayTypeDescription TEXT 10 timeBegin DATETIME routelD CHAR 10 depotID CHAR 10 activationDate CHAR 10 deactivationDate CHAR 10 timeEnd DATETIME Figure 6 Physical Model of the Schedule Calendar Date SDPG Part3 Programmers Guide vl Opdf 15 Part 3 SDP Programmer s Manual 30 June 2008 3 4 Database Scripts and Referential Integrity Issues Using specialized data modeling software the CDRM may be used to generate a physical database loa
32. endix C STIF2Sdp Conversion Setup and User s Manual eee e eee eee eene 37 Appendix D SDP Csv2Xml Conversion Setup and User s Manual e ee 37 Appendix E SDP Xml2Csv Conversion Setup and User s Manual ee 38 Appendix F SDP Csv2Gtfs Conversion Setup and User s Manual ee eee 38 THE SDP AND METADATA onsu 39 rte ELON ec S 39 Whatis SDP Metadata NEE 39 Why SDP IVC tA at ais c eite eee DE Eee ENEAK EE V E VENE VAEA VENICE Te RSEN 39 SDP Metadata x hee oboe rio bendi tu e e 40 7 4 DV EE 40 7 4 2 SDPMetadata Regut Me MS s eau di aq OM od i e 40 7 4 3 Metadata XML Schema Model 42 SDPG Part3 Programmers Guide vl Opdf 3 Part 3 SDP Programmer s Guide 30 June 2008 7 5 Appendix A XMLSpy Schema Notation ecce eee eee eee eee eee ee eee ee eee eee ee eee eee eee eee ee eee 52 Table of Tables Table 1 CDRM Entity with its Identifying and Non Identifying Keys 16 Table 2 SDP Access Database Operating System and Version Requirements 21 Table 3 IS DPA DPIC AU OTe SU E 32 Table 4 Requirement Description for SDP Metadata esse 40 Table of Figures Figure 1 Structure of SDP Four Guidance Documents 5 Pure SDP Translalon PrOGeSS ee 8 Figure 3 Conceptual ER Model of Schedule Calendar Date Concept
33. f JT Ts KL Project m i lt xml versianz 1 0 7 A Elements ax 2 lt oDP100 xmlns xsi http Ji w3 org 2001 xL Schema instance ey SBP1DD 3 xmlnas xed http www w3 arg zDD1 LE Schema AML mem 4 activationDate 2008 04 06 Documents sse Apr 5 deactivationDate 20D8 10 D4 b pickMaz xQuerv Files E placementDate 2D 08 D4 D5 HTML Files B schedule ersionlDi baseGroup g schedule s ersianDiescription April 2005 Base 4 5 2008 10442008 10 xmlns http Au tsdea cam schema sS DP 100 XML 11 xsiischemaLocation http www tsdea cam schema SDP1UL schema SOP XML Schema wve xsd 12 gt Schema l3 lt AgencyRegistration gt 14 C sAgency effectiwveDate 2006 11 01 endDate 9999 12 31 gt 15 lt agencylD gt 100 2 lt agencylD gt 16 lt agencyAcronym MNR lt agencyAcronym gt 17 lt agencyName hletro North Railroad lt agencyName gt 18 web droe http wwe mta info mnrAndex htmlz veb amp ddressz 13 e zheadguarter amp ddressz A lt addressiD 1 lt addressiD gt 2 zaddressMumberz347 addressNumber Attributes nx ER lt typePretix ES lt streetName gt MADISON lt streetName gt 24 zctypesuftixz AVE lt tyoesutix 25 lt completeName s4 MADISON AVE NEVV YORK MY 10017 3739 lt completeMame gt Jh zunitTypezafficec unit Type E lt unitDesignation gt Jn lt secondLine gt a a e z wer EE a Text Grid Schema SOL Authentic Browser Validation Entities nx misdp MNR Base April2008 ml 4b Report
34. grammers Guide vl Opdf 30 Part 3 SDP Programmer s Guide 30 June 2008 5 Appendix A MSXSD JS Source and Batch Files File msxsd js Validate XML Document Against Schema validate parameters if WScript Arguments length 3 WScript Echo msxsd takes three arguments datafile namespace schema eg WScript Echo msxsd books xml books xsd else var cache new ActiveXObject Msxml2 XMLSchemaCache 4 0 cache add WScript Arguments 1 WScript Arguments 2 var xmldoc new ActiveXObject Msxml2 DOMDocument 4 0 xmldoc async false xmldoc preserveWhiteSpace true xmldoc schemas cache xmldoc load WScript Arguments 0 if xmldoc parseError errorCode 0 WScript Echo There is a problem xmldoc parseError errorCode xmldoc parseError reason else WScript Echo no problems File validate MNR SdpXml bat msxsd SDP MNR Base April2008 xml http www tsdea com schema SDP1 00 J schema SDP XML Schema vi O xsd SDPG Part3 Programmers Guide v1 Opdf 31 Part 3 SDP Programmer s Guide 30 June 2008 6 Application Design Reference Manuals 6 1 Introduction The Transit Schedule Data Exchange Architecture TSDEA project describes the exchange requirements for schedule and related data These data requirements are incorporated into Schedule Data Profile SDP reference data model and implemented into the SDP XML Schema Version 0 posted May 23 2008 This SDP Applicati
35. ial projects and applications e instill data accountability by requiring you to state what you know about the data and realizing what you don t but should know about your data e limit data liability by explicitly designating the effective and administrative limits of use of the data e monitor data development by regular review of the process steps completed and recorded within the metadata e access the lineage and content of the data production process In theory metadata is a best practice In practice metadata is time consuming and tedious The geospatial industry has learned over the years that metadata 1s an important tool in order to effectively manage and re use data resources To this end an SDP Document submitted for use should be accompanied by a metadata document A data repository that stores a SDP Document should use the metadata document enable Data Consumers to discover the data resources available at the site To aid in the collection of metadata the data repository when it registers the SDP Document should support the documentation and importation of metadata components of the SDP Document submissions 7 3 Why SDP Metadata The TSDEA Data Repository will serve as a portal in which consumers of schedule data may find resources submitted by downstate NY regional Operators This portal environment requires a registry of information about the quality fitness for use dissemination policies and interfaces supported by ea
36. ication is included on the application CD The msxsd is a javascript application what accesses the MSXML2 DLL to validate an XML document against an XML Schema The table below outlines the operating system and version requirements for the database instance example SDP XML Schema Windows Scripting 5 6 Host Microsoft XML DLL MS Windows Windows XP Prerequisites Microsoft Windows Scripting Host The attached CD contains a copy of the javascript but not the Windows Scripting Host software nor the MSXML2 DLL Appendix A of this chapter contains a copy of the Javascript source and batch file to execute the code under the Windows Scripting Host 4 7 2 Application Execution To start the msxsd application navigate to the SSDP SDP schemaValidation MNR directory Then double click on the file called validate MNR SdpXml bat to start the application If the file is valid then the following prompt will display see Figure 12 Windows Script Host E no problems Figure 12 MSXSD Application Showing a Validation Report An example showing an error report message is in Figure 13 SDPG Part3 Programmers Guide v1 Opdf 29 Part 3 SDP Programmer s Guide 30 June 2008 Windows Script Host There is a problem 1072897661 Error parsing 2006 13 01 as date datatype The attribute efFectiveDate has an invalid value according to its data type Figure 13 MSXSD Application Showing Validation Error Report SDPG Part3 Pro
37. irectory Results SDPG Part3 Programmers Guide vl Opdf 25 Part 3 SDP Programmer s Guide 30 June 2008 4 Using the SDP XML Schema for XML Document Validation 4 1 Introduction The Transit Schedule Data Exchange Architecture TSDEA project describes the exchange requirements for schedule and related data These data requirements are incorporated into Schedule Data Profile SDP reference data model and implemented into the SDP XML Schema This tutorial provides a brief overview of SDP XML Schema elements and then describes two applications that use the SDP XML Schema to validate XML documents A thorough treatment of the XML schema implementation and conceptual data reference model is contained in Chapter 2 of SDP Guidance Documentation Part 2 User Requirements One of the advantages of XML is the relative ease of verification of conformance with an XML schema specification Furthermore software tools that are easy to use are readily available Two applications that validate XML documents are discussed in this chapter Finally XML which 1s written 1n ASCII 1s portable across system platforms One disadvantage of using XML documents is the relative large size of the document routinely ranging between 5 to 15 megabytes in size based on a representative sample of SDP XML documents References SDP Guidance Documentation Part 2 User Requirements Version 1 0 June 2008 The intended audience f this manual includes e System
38. iveDate MNAE endDate 9999 12 31 gt Ten zagencylDi2100 2 agencylDz 16 zagencycronym2 MINE z agency amp cronymz 17 lt agencyhame gt hetro North Railroad lt agencyName gt 18 zwebAddress http vww rmta infa mnrindex htmlz webAddress 15 CH lt headquarterAddress z0 zaddresslD71 lt addressID gt 41 zaddressMumberz347 lt addressNumber gt Attributes nx ae lt typePretix 23 lt streetName MADISON streetName gt E oo 2 EE F endDate C i gt Text Grid Schema w SDL Authenti use 4 b x Yanan EN LEE Entities ax Error Report EEN 3 File C Program Filesv amp pache Groupi amp pachehtdocs SDPYSDP schemaveldettorgkdkbscdp P Base April2008 xrml is not valid EI aue 2005 1 3 01 is not allowed for attribute effectiveDate AH Sein Hint A valid value would be 967 08 13 i Pe Error location 3DP100 AqgencyReqistration Agency ettectiveDate E Details er 2 fy Sj ay 3 TIT Validation Find in files XPath sMLSpy 2007 spl Registered to Manny Insignares ConSysTec Corp 1998 2006 Altova GmbH Ln 14 Col 28 Figure 11 XMLSpy Showing Validation Errors SDPG Part3 Programmers Guide v1 Opdf 28 Part 3 SDP Programmer s Guide 30 June 2008 4 7 Microsoft MSXML2 Windows Scripting Host Application 4 7 1 Software Installation A sample application msxsd based on the MSXML2 an XML processor dynamic link library DLL that comes with the Microsoft Internet Explorer appl
39. k effectiveDate endDate blockTime blockID scheduleVersionID Block Event Time seqNo tripID scheduleVersionID Block Trip Sequence routeID date Calendar_Date From locationID effectiveDate Connection Seg To locationID endDate day Type date TimeBegin Day Type date TimeEnd scheduleVersionID Be depotID ransitFacilityID amenityID locationID Amenity effectiveDate endDate SDPG Part3 Programmers Guide vl Opdf 16 Part 3 SDP Programmer s Manual 30 June 2008 IDENTIFYING KEY IDENTIFYING ENTITY NAME RELATED KEY RELATED KEYS NAME S locationID effectiveDate endDate connectionNum To tripI ime scheduleVersionID Event Connection tripID routeID From tripTime tripID routeID facPCID transitFacilityID Facility Plant Component locationID featureType cd Location effectiveDate endDate pmode J Moe noteAssociationID noteID trip Time Note Association tripID scheduleVersionID effectiveDate endDate noteID scheduleVersionID Note Entry effectiveDate endDate organizationUnitID agencyID Organizational Unit effectiveDate endDate passAccessID locationID en Access Compone effectiveDate endDate pem EE NE Passenger Access Type ETE routel D routeDirection Pattern scheduleVersionID effectiveDate endDate plantCompID componentID effectiveDate Plant Component amenityID endDate transitFacilityID stopID trackID passAc
40. mena The model is driven by a set of requirements described by current local practice and by best practices advocated by the information technology and transit industries The CDRM uses an abstract ER diagram ERD modeling method to represent these real world phenomena Although a similar notation 1s used to describe the Logical and Physical models they do not include the same information or serve the same purpose Differences between the CDRM and Logical CDRM and Physical and CDRM and XML Schema models are described below Difference between a CDRM and SDP Logical Entity Relationship Model A CDRM shows the relationship between entities but does not carry related keys to related entities For example in a system that supports more than one schedule version per agency the schedule version identifier must be included in every entity in the logical model A logical model expressed as an ERD shows these primary and foreign keys and thus describes key storage requirements related to the data set The CDRM makes no assumptions about how a model is applied rather it describes real world relationships SDPG Part3 Programmers Guide vl Opdf 10 Part 3 SDP Programmer s Manual 30 June 2008 Difference between a CDRM and SDP Physical Implementation Similar to the relationship between a conceptual and logical model the physical implementation supports primary and foreign keys the procedures that validate these relationships and specific format
41. mentation 2 Requirements Manual what is approach and requirements 3 Programmer s Manual how and other technical guidance 4 Troubleshooting Manual lessons and support for issues Figure 1 Structure of SDP Four Guidance Documents The four sets of documents provide increasing levels of detail in understanding SDP and performing conversion of agency data to the SDP standardized format Part 1 is intended for Program Managers Analysts Developers and System Integrators It provides an overview of SDP including a high level overview of SDP model as well as a discussion of resources and a glossary SDPG Part Programmers Guide vl Opdf 5 Part 3 SDP Programmer s Guide 30 June 2008 Part 2 is intended for Analysts and provides a more detailed framework amp approach for the SDP as well as a summary of the requirements that drove the development of the SDP Part 3 is intended for Application Developers It includes the data mapping approach detailed conversion programs and user manuals transformations algorithms cheat sheets as well as implementation guidance on developing a physical database and XML validation Part 4 1s intended for System Integrators and Developers It answers implementation questions addresses integration issues 1 e FAQ and suggests solutions to commonly encountered problems Note Part 4 will be implemented as part of the ongoing SDP Operations and Maintenance This
42. n phase of the project 6 4 5 Programming Languages The programming languages used in the TSDEA SDP demonstration are summarized below e PHP Pre Hypertext Processing PHP is a C like scripting language with support for Web processing PHP is open source and runs under a variety of operating systems PHP is implemented under the Apache Microsoft IIS and other web servers e VBScript A Microsoft Windows Scripting Host language It is used to process XML formatted information into CSV xml2csv SDP application e Jscript A Microsoft Windows Scripting Host language It is used to validate XML documents against the SDP XML schema e Command line Processing PHP supports command line operation of PHP scripts This feature is utilized under WindowsXP 6 4 6 Directory Structure Figure 15 shows the directory tree hierarchy for location of data content SDP application software and documentation SDP Directory Structure Including Future SDP is a directory of your choosing Figure 15 SDP Directory Structure Hierarchy The SDP directory at the top of the tree in figure is a directory of an implementation s choosing Several sub directories are branches off the top node or root node SDPG Part3 Programmers Guide v1 Opdf 36 Part 3 SDP Programmer s Guide 30 June 2008 6 4 6 1 Native Data Processing and Data There are directories for the applications that process native data LIB processing RTIF porcessing and STIF
43. now storm The Schedule Calendar Date associates the relevant schedule components designated by the Route Depot Version and an index related to the appropriate trips designated by the day type into a table which is used as a reference A Schedule Calendar Date is created for each set of schedule version components and the trips that operate on the specific dayType In some cases the schedule version components are scheduled for only part of a day for example the schedule components vary when the Mets play games that begin at 5 p m versus at 7 p m from SDP Functional Requirements p 102 3 3 2 Example of the Logical Entity Relationship Representation The logical model is driven by application requirements related to how the data are stored and accessed In the example illustrated in Figure 4 the Schedule Calendar Date entity inherits related keys designating the schedule version when more than one schedule version 1s present When an organization changes its schedule mid version the entity is required to include a revision number and when a transit agency issues their schedule by route or by route and depot the route depot version is also included in the entity When this model is extended to a regional repository each entity must designate the authority that issued the data as such the functional entities Route Depot Version Schedule Calendar Date and Day Type include the agency SDPG Part3 Programmers Guide v1 Opdf 12 Par
44. of a sub directory where SDP csv files are located for example libus rtif stif mnr lirr mtabus etc An example of the prompt display 1s shown in Figure 8 SDP Refresh Links to CSV Files Dir Extension e g LIBus or STIF M104 Figure 8 SDP Startup Macro Initialization Directory Prompt Once the data files are loaded the application will display Done SDPG Part3 Programmers Guide v1 Opdf 24 Part 3 SDP Programmer s Manual 30 June 2008 To launch the MS Access database navigate to the SDP SDP _ data directory If you are using a graphical interface to navigate to the SSDP SDP data directory then double click on the file called sdp db mdb to start the application After startup MS Access will contain a list of tables that correspond with the csv files imported This 1s shown in Figure 9 Microsoft Access m jew Insert Tools window Help 4 AE A E Fe a r EI x Create table in Design view Create table by using wizard ae Create table by entering data sdp agency ils sdp direction Reports sdp_location Pages sdp Note eeneg sdp MateTimeAssac sdp NoteTripassoc HERE sdp pattern sdp patternEventLisk Favorites sdp relativeLacatian sdp route sdp rkDepatversian sdp RtDirection sp scheduleRevisian sp scheduleversion sp TimeEwentTvpe sdp Limepaoink sdp trips sp kripTimes tbl4tbachedDatabase BAE Type a question For help e EISE Figure 9 SDP Startup Macro Initialization D
45. on The XML Schema notation as extracted from the XMLSpy application is used to describe the organization and format of the SDP XML Schema The Schema is based on a hierarchical organization where parent nodes or elements may contain child elements which may in turn be a parent element to child elements The XML Schema format and document instance are based on the standard notation of an XML Schema and instance document Figure 27 below illustrates the different levels of the XML Schema and key notation using Transit Facility as the example In addition the figure shows the type description for each element A type reference may have one of the following prefixes or suffixes e Prefix of xsd asserts the type is native to the XML standard e Suffix of id implies the type is defined as an SDP identifier domain e Suffix of cd implies an enumerated code type A Structure in the type name implies that the element is a complex type An element also includes the constraint on the number of times it is allowed An element enclosed by a dotted lined box indicates that the element 1s optional Elements that may be repeated will include a notation of the minimum and maximum eg 0 00 under the right hand corner of the element enclosure plantComponentList is an example of an element that is optional but may be repeated One element is required when the element is enclosed with a solid line and does not contain a min max val
46. on Design Reference Manual describes the design elements and application development environment of SDP applications developed under the TSDEA demonstration project The appendices describe the design details of the application software The SDP Applications developed under this project fall into one of three categories 1 Applications that convert from an agency s native schedule data to an SDP format 2 Applications that convert between the two SDP file formats XML and CSV 3 Applications that convert from the SDP CSV format to formats used by open source third party initiatives The SDP Applications described in this design document are listed in Table 3 SDP Application List Table 3 SDP Application List SDP CSV Format to XML and SDP XML Format to CSV Csv2 Xml Converts SDP CSV Files to XML Xml2Csv Converts SDP XML Files to CSV SDP CSV Format to Third Party Open Source Initiatives Csv2Gtfs Converts SDP CSV Files to Google Transit Feed Spec GTFS 6 1 1 Intended Audience The intended audience of this manual includes e System developers interested in maintaining the existing SDP applications or creating new SDP applications e System managers responsible for setting up the run time environment for SDP applications SDPG Part3 Programmers Guide vl Opdf 32 Part 3 SDP Programmer s Guide 30 June 2008 6 1 2 SDP Application User s Manuals SDP Application User s Manuals each developed as a separate document
47. phic Data in Transit A Location Referencing Guidebook The Location Table cannot lose the old record keyed to the Location ID therefore a method to manage the updated records must be implemented Adding one attribute to the unique identifier primary key of the Location Table called location end date can achieve this consistency When a location ID is first introduced the location begin date is set to the date of first use see Example Table 1 below 5 1 03 is the new date The location end date is set to a date in the far off future in Example Table 1 location end date is 12 31 9999 If a change is made to one of the Location Table attributes for example if the Public Description is changed the location end date for the existing row 1s set to the date of closure in Example Table 2 the date is now 6 30 03 In addition a new row or record may be introduced for the SDPG Part3 Programmers Guide vl Opdf 19 Part 3 SDP Programmer s Manual 30 June 2008 Location ID On the new record the location begin date value 1s set for the original location end date plus one day later 7 01 03 and the location end date is set to a date in the far off future 12 31 9999 Using this approach a consistent set of values 1s provided for the Location ID with the referring transit feature matching the Location ID and inclusive of location begin date and location end date Example of the Public Description changing Example Tabl
48. s and syntax related to each data type described in the model Specifically these rules and procedures are defined for a specific database management system such as Oracle 91 MS Access 2003 etc Difference between a CDRM and SDP XML Schema Implementation The SDP XML Schema s primary purpose is to facilitate the sharing of data across different information systems particularly via the Internet The SDP XML Schema uses the CDRM to describe schedule and related data concepts and preserve the relationship requirements among data concepts for one schedule version and for a single transit operator A set of rules were used to migrate the data concepts from the CDRM to the XML Schema implementation 3 3 An Example of Migrating the CDRM to a Logical Physical and XML Schema Representation As described above there are rules for implementing the CDRM from the conceptual framework to its logical physical and XML schema formats The following sections show how the CDRM for the same data concept Schedule Calendar Date 1s transformed to a logical physical and XML Schema model Each model depicted in Figures 1 through 4 is summarized in the list below e Conceptual Data Reference Model Figure 3 Note the CDRM Logical and Physical models are all represented using ERD notation e Logical ERD Model Figure 4 and Figure 5 Note the key identifiers become primary keys pk and related entities include related or foreign keys fk e Physi
49. sit Path locationID SDPG Part3 Programmers Guide vl Opdf 18 Part 3 SDP Programmer s Manual 30 June 2008 IDENTIFYING KEY IDENTIFYING ENTITY NAME RELATED KEY RELATED KEYS NAME S endDate EP low patternID seqNo locationID first amp Ordered list of Transit Point Event last locationID patternID stopID locationID Transit Stop effectiveDate endDate tripID routeID scheduleVersionID Trip dayType effectiveDate endDate tripEventTypeCode Trip Event Type trip Time triplID scheduleVersionID Trip Time routeID 3 4 2 Temporal Integrity Issues Transit schedule and related data are composed of sets of data with differing overlapping and temporary data versions and revisions in different states To that end temporal data principles are applied to the major entities using two fields for time effectiveDate and endDate The principles for preserving the history while enabling so called redundant data to remain in the database necessitate including the two dates as primary keys In some cases like ScheduleVersion Route Day Type Plant Component Status additional temporal keys are included e g activationDate deactivationDate routeBeginDate routeEndDate in order to allow for temporary data with similar identifying keys to remain in the For example TriMet implemented this approach in their database The description of their use of dates is described in a passage from the FTA Best Practices for Using Geogra
50. stream applications The effort wanted to ensure that the data meaning and relationships were well defined A Conceptual Data Reference Model CDRM was developed to meet that need The CDRM is meant to be used as a framework to unambiguously describe the SDP data concepts and their relationship to each other Different technical methods may be used to physically represent and store schedule data The three methods include logical data model physical database and XML Schema The major objective of the project was to implement the XML Schema and validate the requirements in one or more downstream applications related to the use cases The SDP logical and physical methods provide alternative approaches for the storing the data Different needs may exist for storing the data using a different method yet ensuring that there 1s a seamless automated way to transfer the data from format to format In fact the csv2xml application described in Chapter 5 of this document uses a comma separated value csv representation which may be described as a logical model of the CDRM to convert native data using the interim format to an XML document A physical database 1s a more efficient way to store data sets of different versions and multiple agencies together then file server containing multiple SDP XML Documents 3 2 Differences between the SDP CDRM and Implementation Methods The SDP CDRM uses an entity relationship ER method to represent real world pheno
51. t 3 SDP Programmer s Manual 30 June 2008 identifier agencyID The actual implementation of the conceptual to logical model may be seen in Figure 5 7 7 Calendar Date l I date lt pi gt lt M gt i dayOfWeek lt M gt holiday I defines is defined by i I lt lt data type gt gt Schedule Calendar Date Route Depot Version f SDP u SDP calendarDate i M Relatiogiship_47 HSH xpi xwv 53 xx routeDepotVersionID pi M gt I sgt day Type lt pi gt lt M gt O l day TypeDescription lt M gt activationDate lt M gt placementDate lt M gt timeBegin deactivationDate lt M gt activationDate M timeEnd deactivationDate M day Type pi M E Del l Use to distinguish different schedule L or route depot versions When When storing multiple schedule versions table multiple agencies and versions are will include foreign keys scheduleVersionID present the table will include foreign revisionNo and or routeDepotVersionID too Add keys scheduleVersionID revisionNo foreign key agencyID to all tables when and agencyID integrating with multiple agencies Figure 4 Migrating from Conceptual to Logical Model SDPG Part3 Programmers Guide v1 Opdf 13 Part 3 SDP Programmer s Manual 30 June 2008 ScheduleVersion PK scheduleVersionID CalendarDate scheduleVersionDescription pickNo
52. te sub directory relationships of directory tree for example SSDP SDP processing SDP CSV data files which can be imported to the database at run time are located in an agency specific directory of SSDP SDP data for example SSDP SDP data LIBus for Long Island Bus and SSDP SDP data RTIF for New York City Transit Rail files 3 5 2 2 List of SDP CSV Data Input Files The Sample SDP database reads CSV data from an agency specific sub directory of SDP SDP data agency The list of files read depends on the agency and whether the agency SDPG Part3 Programmers Guide v1 Opdf 21 Part 3 SDP Programmer s Manual 30 June 2008 provides bus commuter rail or subway etc service The list of files using the LIBus as an example 1s listed below e sdp agency csv sdp direction csv sdp location csv sdp Note csv sdp NoteTimeAssoc csv sdp NoteTripAssoc csv sdp pattern csv sdp patternEventList csv sdp relativeLocation csv sdp route csv sdp rtDepotVersion csv sdp RtDirection csv sdp scheduleRevision csv sdp scheduleVersion csv sdp stop csv sdp TimeEventType csv sdp timepoint csv sdp trips csv sdp tripTimes csv 3 5 2 3 List of SDP Tables The output of the initialization macro is a series of tables with the same names as the csv files imported 3 5 3 Application Execution AutoExec Macro Application Execution e AutoExec Macro At startup the sdp db mdb database executes a software module that prompts the user for a name
53. tics relationships and formats of your organization s native schedule and related data set s that cover the SDP data requirements Map Native Data to SDP concept Based on your knowledge of your schedule and related data meanings identify the native table and field that maps to each SDP mandatory and optional element descriptions Using the SDP Template Spreadsheet to record your findings or other tool Map native types flags and codes to SDP codes Map and document the obvious native data and transformations to SDP Identify missing identifiers strategize on approaches for completing them while ensuring identifier uniqueness constraints Identify missing mandatory data fields strategize on approaches for completing them Identify native data that is not represented and should be in the SDP 5 Develop scripts and modules to translate codes data types and extract data from native sources and load the data into the SDP format SDPG Part3 Programmers Guide vl Opdf 8 Part 3 SDP Programmer s Manual 30 June 2008 You can use the Data Mapper software csv2xml application to generate the XML Document from comma separate value csv files For information on applying this method see Chapter 5 You can develop embedded procedures in your database to generate an XML Document You can reuse procedures from any of the applications developed for this project See Chapter 5 for user manuals and design documents
54. tificationstructure Describes the identification information about the submission and standards used to define an SDP document Description DescriptionStructure Identities the SDP Document s to which this meatadata applies Metadata Schema for Status registering SDP Document to the TSIP StatusStructure_ PE 1 3 Defines the validation and verification tests and procedures applied to the SDP document Distributioninformation DistributionIn formationStructure Describes how the content of the SDP Document may be distributed and to whom requests for access should be sent DataQuality DataQualityStructure Describes the processes and procedures applied ta create the SDP document spatialData spatialDataStructure Describes the spatial attributes and location referencing conventions and standards used by the SDP document Lists additional local use codes used in enumerated e M o CK CE CHE ML py es ey ee ee a c ME Describes special conventions used to extend or constrain the semantics of the elements in the SDP elements For example it might describe naming conventions for identifiers Figure 17 Metadata SDP XML Schema Model from XMLSpy SDPG Part3 Programmers Guide vl Opdf 30 June 2008 44 Part 3 SDP Programmer s Manual 30 June 2008 lo MetadataAttributes Metadata Schema for registering SDP Document to the TSIP Figure 18 SDP Met
55. tribution Information Reference Information oe ee DS des Many of the elements from the ASTM reference are incorporated in the requirement description for the SDP Metadata 7 4 2 SDP Metadata Requirements This section describes the requirements that drive the SDP Metadata XML Schema The needs are listed in Table 4 Table 4 Requirement Description for SDP Metadata A Category Requirements Identification e SDP shall contain identification information about the Information submission and standards used to define an operator s set of Schedule and Related Data The information should include o Originator submitter name telephone and email Original registration date Approval staff person name signature date Publication date List of SDP Files covered by this metadata only one Schedule Version per metadata SDP Schema version used to produce SDP files SDP Metadata Schema version SDPG Part3 Programmers Guide vl Opdf 40 Part 3 SDP Programmer s Manual 30 June 2008 A Category Requirements o Online linkages e g maps pdf timetables 2 Description and Abstract general description of SDP files Time Period of Schedule Version Content o revision Activation date deactivation date or 12 12 9999 Data sources e g Scheduling Application RTIF including version numbers of applications or file descriptions 3 Phase registered Levels 1 3 Date entered phase Estimated date for next schedule
56. ue SDPG Part3 Programmers Guide v1 Opdf 52 Part 3 SDP Programmer s Manual 30 June 2008 o T ab o D ER TransitFacility Structure 2 T 5 Su Blettributes uoc 4r S S ro transitFacilitylD 6 6 Type description of SDP OBS type transitFacilitvID ki domain type C O65 D Ess oO mm mmm ee W Type description of E TransitFacility B native XML type type TransitRaciityStructure S agen aD E TCI TEE D mm emm beem mmpgm m mmm ccc D 8 facilityDescription D isPartOf type xsd boolean t i partOf itype transitFaciltyiD_i type plantComplD id plantComponentList i ze re re oom ee ee ee mm E es Ce EXE type PlantComponentStructure rf gu Pate as RR E plantCompDescription itype xsd string Child Complex Element Lill D q 0 E Embedded Child Complex Ele Figure 27 Example of the XMLSpy Diagram Notation SDPG Part3 Programmers Guide vl Opdf 53
57. up and User s Manual This manual contains guidance on the application RTIF2SDP Conversion of New York City Transit Rail Transit Interface Format Native Data Files to SDP See http www consystec com tsdea rstwg docs html to download the User Manual See http www consystec com tsdea rstwg applications html to download the application code and user manual 6 7 Appendix C STIF2Sdp Conversion Setup and User s Manual This manual contains guidance on the application STIF2SDP Conversion of New York City Transit Surface Transit Interface Format Native Data Files to SDP See http www consystec com tsdea rstwg docs html to download the User Manual See http www consystec com tsdea rstwg applications html to download the application code and user manual SDPG Part3 Programmers Guide v1 Opdf 37 Part 3 SDP Programmer s Guide 30 June 2008 6 8 Appendix D SDP Csv2Xml Conversion Setup and User s Manual This manual contains guidance on the application Csv2SDP which converts SDP CSV Files to a SDP XML Document See http www consystec com tsdea rstwg docs html to download the User Manual See http www consystec com tsdea rstwg applications html to download the application code and user manual 6 9 Appendix E SDP Xml2Csv Conversion Setup and User s Manual This manual contains guidance on the application XML2CSV which converts a SDP XML Document format to SDP Comma Separated Value CSV files See http www consyste
58. version update Update frequency schedule change frequency brweekly quarterly semi annually Processing and Change Logs o Tests conducted e Type who what why activation date disposition of updated record s o Revision History Lineage actual processes used to convert data o Transformation processes needed to convert data o Date recorded o change start date change stop date 4 Schedule Calendar Calendar List of holidays and special days Schedule Calendar Days Data Quality Procedures instructions Information o Process of loading data set s o Transformations required to pass testing levels 1 3 List of Exceptions or Constraints o Exception descriptions o Date recorded Special Conventions Schedule day based on up to a 36 hours clock which may include a start time from the day before and end at a time the next day Naming convention for indexes may be included in XML schema annotation KMS 7 Code Set Extensions e Based on codes supported by SDP XML Schema Spatial Dataset e Description of each location reference with respect to its measurement quality and datum or coordinate system e g 5 GPS map coordinates using SP NY e g x coordinate y coordinate pair is New York State Plane Accuracy of measurement or map base may include its own metadata o For example the New York State Plane may reference the State GIS metadata link http www nysgis state ny us gisdata metadata nysogs sta SDPG Part3 Programmers

SDPG_Part3_Programmers Guide_v1_0

Contents

Download Pdf Manuals

Related Search

Related Contents