Versions Compared

Old Version 2

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 synchronized with EAS addresses - regardless in near real time. Regardless of how we  we build this it 's 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 need to insert into and ESRI SDE oracle table. While there are a number of ways to do this, it seems like the simplest approach is to use the ArcGIS rest API for query, add, and update.

Implementation

I have written most of that code which you can see here. I do not have access to an arcgis server yet - see below for log file output.

Next Steps

Before we can continue with 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 user credentials for EAS user
  •  limit EAS user access to APO table

...

I'd also like to do the following.

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

...

JSON Example

...

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.  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. 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": {
         "y   "EAS_UNIT_ADDRESS_ID": 2110391.1163551975725576,
        "x": 6005506.037626389    "BASE_ADDRESS_NUM": 10000,
       },     "attributesOBJECTID": {null,
            "YUNIT_ADDRESS": null,
          "UNIT_TYPE_DESCRIPTION  "ADDRESS_X_PARCEL_CREATE_TMS": null1352061116477.258,
            "AXPSTREET_CREATENAME": null,"ROOSEVELT",
            "LOT_NUMUNIT_ADDRESS_CREATE_TMS": null,1352061116477.258,
            "ZIPCODEADDRESS_X_PARCEL_RETIRE_TMS": null,
        "DISPOSITION_DESCRIPTION    "LONGITUDE": null,-122.44280195814189,
            "STREETPARCEL_BLOCK_TYPELOT": 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,
        "BLK_LOT    "LATITUDE": 37.763860355079515,
            "UNIT_ADDRESS_RETIRE_TMS": null,
            "EAS_ADDRESS_BASE_CREATEID": null483702,
            "BASE_ADDRESS_CREATE_SUFFIXTMS": null,1352044837254.029,
           "AXP_RETIRE": null,
        "ADD_RETIRE_CHANGE_REQUEST_ID": null, "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
      "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_CHANGE  "X": null6000111.418789571
        },
        "OBJECTID"attributes": {
            "EAS_UNIT_ADDRESS_ID": null725576,
            "BASE_ADDRESS_NUM": null10000,
            "CNNOBJECTID": null273623,
         "X   "UNIT_ADDRESS": null,
            "ADDRESS_BASE_FLG_X_PARCEL_CREATE_TMS": 1352061116477.258,
            "STREET_NAME": null"ROOSEVELT",
            "UNIT_NUMADDRESS_CREATE_TMS": 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,
            "AXPBASE_ADDRESS_RETIRE_CHANGE_REQUEST_IDTMS": 1352064082020.226,
            "STREET_TYPE": null"WAY",
            "SUBEAS_ADDRESS_X_PARCEL_ID": 725566563580,
            "ADDBASE_UPDATE_CHANGE_REQUEST_IDADDRESS_SUFFIX": null,
            "SUBLATITUDE": 37.763860355079515,
            "UNIT_ADDRESS_RETIRE_TMS": null,1352064082020.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