Versions Compared

Old Version 12

changes.mady.by.user Paul McCullough

Saved on

compared with

New Version Current

changes.mady.by.user Paul McCullough

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

Table of Contents

Introduction

Accela APO records must be kept synchronized with EAS addresses in near real time. Regardless of how we build this it is convenient to think of this as a sort of custom replication service. We've already done this successfully with DBIs AVS as described here and here. With Accela however, we are using an ESRI SDE table in an Oracle DB. While there are probably a number of ways to do this, it seems like the simplest approach is to . That implementation has been used in production since February 15, 2012. 

Implementation

As of November 4, we have a working Accela implementation. Addresses that are created or changed on our EAS DEV should replicate in near real time to a table named EAS_ADDRESSES_FLAT on Accela GIS DEV. To accomplish this we use the ArcGIS rest API. I think we can do everything we need with query, add, and update.

...

 When an address is created we call addFeatures. When an address is updated we use query to get the primary key from Accela (OBJECTID) and then use updateFeatures to update the other fields. The code is here. You can also get some sense of how this works by examining the insert and update log file output. 

Learning Curve

After some discussion, Mike and Paul have decided to try the simplest thing that could possibly work, To that end, I have written most of this code which you can see here. I do not have access to an arcgis server yet so I still have lots of testing to do . However, I have tested a fair bit of the code. You can see the log file output below.

Next Steps

Before we can continue with any serious testing we need to do the following.

  •  clean up APO data model on arcgis server
    •  fix timestamp fields
    •  fix field name widths
    •  include address_x_parcel_id
    •  remove unnecessary fields
    •  rename some fields to improve clarity
  •  reload APO data using refactored model
  •  enable arcgis feature service
  •  provide web server user credentials for EAS user
  •  limit EAS user access to specific tables
  •  decide how to handle retired versus unretired addresses

...

I'd also like to do the following before we get to much further down the road.

  •  design review
  •  code review
  •  discuss strategy for cut-over from AVS to Accela (see this line) (currently hard coding timestamp)

...

. In this case, this means using a flattened representation of EAS addresses. This should allow us to better understand Accela and how addresses work in Accela. We expect that we'll make some changes as we learn.

Next Steps

Here are the remaining issues that will go into subsquent Accela related releases.

Before we get too much further down the road, I'd like to have a code review.

Anchor
insert_example
insert_example
Log File Output (Insert)

Code Block
2012-11-04 20:32:02,494 - XMIT_ACCELA - INFO - posting JSON
2012-1011-3104 1420:0432:3402,340494 - XMIT_ACCELA - INFO - host:accelaGis.sfgov.orgpath:/ArcGIS/rest/services/EAS_Accela_Test/FeatureServer/2/addFeatures
2012-1011-3104 1420:0432:3402,340494 - XMIT_ACCELA - INFO - port connectionString :10.19.51.238:80
2012-1011-3104 1420:0432:3402,340494 - XMIT_ACCELA - INFO - json path:/easToAccelaDev/featureService/
2012-10-31 14:04:34,340 - XMIT_ACCELA - INFO - url:/addFeatures
2012-10-31 14:04:34,341 - XMIT_ACCELA - INFO - user:TODO
2012-10-31 14:04:34,341 - XMIT_ACCELA - INFO - password:TODO
2012-10-31 14:04:34,341 - XMIT_ACCELA - INFO - json:
2012-10-31 14:04:34,341 - XMIT_ACCELA - INFO - {features: [
    {
        "GEOMETRY": {
            "Y": 2106429.8391464953,
            "X": 6000111.418789571
        },
        "geometryattributes": {
            "yEAS_UNIT_ADDRESS_ID": 2110391.1163551975,725576,
            "xBASE_ADDRESS_NUM": 6005506.03762638910000,
       },     "attributesOBJECTID": {null,
            "YUNIT_ADDRESS": null,
            "UNIT_TYPE_DESCRIPTIONADDRESS_X_PARCEL_CREATE_TMS": null,1352061116477.258,
            "AXPSTREET_CREATENAME": null,"ROOSEVELT",
            "LOT_NUMUNIT_ADDRESS_CREATE_TMS": null1352061116477.258,
         "ZIPCODE   "ADDRESS_X_PARCEL_RETIRE_TMS": null,
        "DISPOSITION_DESCRIPTION    "LONGITUDE": null-122.44280195814189,
        "STREET_TYPE    "PARCEL_BLOCK_LOT": null"2618029",
            "UNIT_ADDRESS_BASE_IDFLG": 483692true,
            "AXPACTIVATE_CHANGE_REQUEST_IDSTREET_CNN": null11145000,
            "BASE_ADDRESS_BASERETIRE_CHANGETMS": null,
            "FLOORSTREET_DESCRIPTIONTYPE": null,"WAY",
            "ADDEAS_ACTIVATEADDRESS_CHANGEX_REQUESTPARCEL_IID": null563580,
        "BLOCK_NUM    "BASE_ADDRESS_SUFFIX": null,
            "LATITUDE": 37.763860355079515,
        "BLK_LOT    "UNIT_ADDRESS_RETIRE_TMS": null,
            "EAS_ADDRESS_BASE_CREATEID": null483702,
            "BASE_ADDRESS_SUFFIXCREATE_TMS": null1352044837254.029,
        "AXP_RETIRE"    "ZIPCODE": "94114"
        }
    }
]
2012-11-04 20:32:02,790 - XMIT_ACCELA - INFO - response meta data: status: 200
reason: OK
version: 11
headers
        date: Mon, 05 Nov 2012 04:30:51 GMT
        content-length: 121
        x-powered-by: ASP.NET
        content-type: text/plain;charset=utf-8
        server: Microsoft-IIS/7.5
2012-11-04 20:32:02,790 - XMIT_ACCELA - INFO - response content: {^M
  "addResults" : [^M
    {^M
      "objectId" : 273623, ^M
      "globalId" : null, ^M
       "ADD_RETIRE_CHANGE_REQUEST_ID": null,"success" : true^M
    }^M
  ]}

 

Anchor
update_example
update_example
Log File Output (Update/Retire)

Code Block
2012-11-04 21:21:27,184 - XMIT_ACCELA - INFO - connectionString: 10.19.51.238:80
2012-11-04 21:21:27,184 - XMIT_ACCELA - INFO - urlString: /ArcGIS/rest/services/EAS_Accela_Test/FeatureServer/2/query?where=eas_address_x_parcel_id=563580&returnIdsOnly=true&f=pjson
2012-11-04 21:21:28,726 - XMIT_ACCELA - INFO - posting JSON
2012-11-04 21:21:28,726 - XMIT_ACCELA - INFO - path:/ArcGIS/rest/services/EAS_Accela_Test/FeatureServer/2/updateFeatures
2012-11-04 21:21:28,726 - XMIT_ACCELA - INFO - connectionString :10.19.51.238:80
2012-11-04 21:21:28,727 - XMIT_ACCELA - INFO - json features: [
    {
        "GEOMETRY": {
            "Y": 2106429.8391464953,
            "AXP_LAST_CHANGEX": null, 6000111.418789571
        },
        "attributes": {
         "OBJECTID   "EAS_UNIT_ADDRESS_ID": null725576,
            "BASE_ADDRESS_NUM": null10000,
            "CNNOBJECTID": null273623,
            "XUNIT_ADDRESS": null,
            "ADDRESS_BASE_FLG_X_PARCEL_CREATE_TMS": 1352061116477.258,
            "STREET_NAME": null"ROOSEVELT",
            "UNIT_ADDRESS_CREATE_NUMTMS": null1352061116477.258,
          "MAILABLE_FLG": null,  "ADDRESS_X_PARCEL_RETIRE_TMS": 1352064082020.226,
            "LONGITUDE": -122.44280195814189,
            "BASEPARCEL_STREETBLOCK_NAMELOT": null,"2618029",
            "UNIT_ADDRESS_BASE_RETIREFLG": nulltrue,
            "SUBSTREET_ADDRESS_CREATECNN": null11145000,
        "AXP    "BASE_ADDRESS_RETIRE_CHANGE_REQUEST_IDTMS": 1352064082020.226,
            "STREET_TYPE": null"WAY",
            "SUBEAS_ADDRESS_X_PARCEL_ID": 725566563580,
            "ADDBASE_UPDATE_CHANGE_REQUEST_IDADDRESS_SUFFIX": null,
            "LATITUDE": 37.763860355079515,
            "SUBUNIT_ADDRESS_RETIRE_TMS": null1352064082020.226,
            "STREETEAS_ADDRESS_SEGMENTBASE_ID": null483702,
            "COMPOSITE_ADDRESS": null
    }
}BASE_ADDRESS_CREATE_TMS": 1352044837254.029,
            "ZIPCODE": "94114"
        }
    }
]
2012-11-04 21:21:28,922 - XMIT_ACCELA - INFO - response meta data: status: 200
reason: OK
version: 11
headers
        date: Mon, 05 Nov 2012 05:20:17 GMT
        content-length: 124
        x-powered-by: ASP.NET
        content-type: text/plain;charset=utf-8
        server: Microsoft-IIS/7.5
2012-11-04 21:21:28,922 - XMIT_ACCELA - INFO - response content: {^M
  "updateResults" : [^M
    {^M
      "objectId" : 273623, ^M
      "globalId" : null, ^M
      "success" : true^M
    }^M