Electricity Meter Management

Meters

Meters are installed at the location of each consumer's residence or place of business. Ideally, each meter should be captured by obtaining a GPS location on-site, along with other identifying information including a photograph of each installed meter. Existing installations typically do not have this information, and the locations for each installed meter will initially be determined from street addresses or links to property boundaries in the client authority's GIS.

The following attributes are carried in the PlanetGIS database for each meter^†:

Data Concentrator Units (DCUs)

DCUs are installed at minisubs / transformers and communicate with all nearby meters, collecting their information and sending the information onwards to the MDM database.

The following attributes are carried in the PlanetGIS database for each DCU:

Transformers

A transformer is a component of the electrical network, housed in an enclosure or building. DCUs are installed in the enclosure/building.

The following attributes are carried in the PlanetGIS database for each transformer:

Alert classes

Alert classes are generated from unique combinations of the device type, alert level and message. Originally, it was thought to restrict the API to only accept alert values that are already in such a list, but currently the list is being updated from values received through the API. The downside to this approach is that variations in the spelling of the same alert class will cause additional thematic layers in the GIS, but more importantly, the GIS needs to be updated to have specific styles for the thematic layers generated from these values.

Currently, the collected unique values are as follows. This information is stored in the Alert classes table in PlanetGIS, and here reported directly from it:

Updates

All incoming JSON will be validated against a schema as defined by json-schema.org. PlanetGIS extends JSON schemas to include additional validations and calculations. PlanetGIS' internal attribute codes are also mapped by these schemas. Any request that fails the schema validation will have no effect on the data in the PlanetGIS database. A failure to validate the data of a single entity, will cause the entire request to be discarded. (No partial updates will be done.) The exact reason for a failure will be returned as a plain text response, instead of JSON, with the HTTP 400 Bad Request error code.

All REST endpoints accepting JSON will have the JSON schema available at an endpoint with the same name and /Schema appended. For example, an update endpoint at /MDM/Transformers/Update will have the schema available at /MDM/Transformers/Update/Schema

JSON schemas are defined with regular PlanetGIS pages with a content type of Static JSON. The properties definition must match the attribute names and codes for the relevant class exactly (and case-sensitively). Here is the general form of a JSON schema that will be used below:

{
  "$id": "https://powercom-maquassihills.planetgis.co.za/MDM/Transformers/Update/Schema",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "MDMID": { "type": "integer", "code": "MDMD" },
      "Name": { "type": ["string","null"], "code": "Name" },
      "Longitude": { "type": ["number","null"], "code": "Lngt" },
      "Latitude": { "type": ["number","null"], "code": "Lttd" },
      "Barcode": { "type": ["string","null"], "code": "Brcd" },
      .. other attributes
    },
    "required": ["MDMID"],
    "calculated": {
      "I": { "sql": "Coalesce((SELECT FeatureId FROM f_powercom_transformers WHERE MDMId=:MDMID), CreateEntity(754647404906896970))" },
      "G": { "condition": "KeyExists(\"Latitude\") and KeyExists(\"Longitude\")", "sql": "WGS84Point(:Latitude,:Longitude)" },  
      "L": { "condition": "KeyExists(\"Name\")", "expression": "Name" }
    }
  }
}

The update response

The JSON response contains a member named $Updates which has a value containing an array of feedback information for each object received in the update request. Here is an example of a response to updates to three entities:

{
  "Request": "Update transformers",
  "$Updates": [
    {
      "1": "unchanged"
    },
    {
      "2": "changed"
    },
    {
      "9": "created"
    }
  ]
}

The numbers in the objects inside $Updates are MDMIDs and are followed by a string that is one of three values:

• "unchanged" - the update did not result in any actual changes
• "changed" - the update resulted in at least one attribute's value to be changed
• "created" - the MDMID was not found in the database and a new entity was created for it

ChangeIds

Due to the distributed-sequential nature of a PlanetGIS database, the final ChangeId is not immediately available to the app server to return in the above results. If an update is followed within a few seconds by a single entity request or list request, a negative ChangeId might be observed in the results. This indicates that a final ChangeId is yet to be received by the app server, from the stream server. ChangeIds are discussed further below.

Update schemas for each feature class

The actual schemas currently in use will be loaded from the relevant pages below.

Transformers:

/MDM/Transformers/Update/Schema

DCUs:

/MDM/DCUs/Update/Schema

Meters:

/MDM/Meters/Update/Schema

PlanetGIS creates a parent-child relationship between DCUs and meters by looking up the internal EntityId for the DCU that has an MDMID matching the supplied DCUId. Meters have a referential integrity requirement in the schema above that will cause updates to fail if a DCU with the specified DCUId does not exist. The current schema will assign a 0 ParentId if an invalid MDMID is supplied for the DCUId. This DCUId will still be stored as an attribute. A thematic query will be required to highlight any such meters (non-zero DCUId, but 0 ParentId).

It is valid to have meters without a parent and it is assumed in such a case that the parent (DCU) for such a meter will be determined at a later stage. In PlanetGIS, unparented entities always have a ParentId of 0, which conceptually means that such entities' parent is an imaginary entity representing the whole project (the root entity). Entities are not allowed to have a NULL (unknown) parent. The API for meters, however, does introduce the concept of an unknown parent. To add meters with unknown parents, either omit the DCUId member in an update, or set it to null. (Do not set it to 0, as that will fail the lookup for a DCU with an MDMID of 0, unless, of course there is one). Internally, PlanetGIS will set the ParentId of such meters (as well as cases where the supplied DCUId was not found) to 0. (Recall that an EntityId or ParentId in PlanetGIS is not the same as the MDMID.)

Single entity requests

Single entity requests are GET requests with the entity's MDMID as a query parameter. The response will be a JSON object describing the request along with the specified MDMID and a member object named $Attributes with all the attributes defined for the class in PlanetGIS.

Transformers

/MDM/Transformers/Query?MDMID=nnnn

This is an example of a request for the attributes of a transformer with an MDMID of 1:

Request: /MDM/Transformers/Query?MDMID=1; response:

{
  "Request": "Transformer query",
  "MDMID": "1",
  "$Attributes": {
    "MDMID": 1,
    "Name": "Transformer 1",
    "Longitude": 25.9975297,
    "Latitude": -27.3163287,
    "Barcode": "Barcode 1",
    "Address": "Transformer 1 address",
    "Power": 10.1,
    "InstallationDate": "2024-01-01",
    "MaintenancePeriod": 1.5,
    "Ownership": "Transformer 1's owner",
    "ContactPerson": "John",
    "ContactPersonTel": "123-456-7890",
    "GPRSSignal": 2.1,
    "EntityId": "754698230901486534",
    "ChangeId": 62189
  }
}

Notice the addition of EntityId and ChangeId as the last members of each attributes object. These are not attributes of the Transformers class, but values that are attached to every entity in a PlanetGIS database, similar to the timestamp of the last change and the user that made such change. EntityId is PlanetGIS' internal, unique identifier for the transformer. The ChangeId, if positive, may be used to request only updated entities. (Further discussed below.)

DCUs

Meters

Fetching all entities in a class

List requests are GET requests returning a JSON array containing all the entities for the specified class. Each entity's attributes are returned in a JSON object that is the same as the result of a single entity request, shown above.

Transformers

/MDM/Transformers/List

The response to a list request will have the following form:

{
  "Request": "Transformer list",
  "Count": 10,
  "LastChangeId": 62222,
  "$List": [
    {
      ..first entity..
    },
    {
      ..second entity..
    },
    ..etc..
  ]
}

The Count member's value corresponds to the number of items in the $List array. LastChangeId is the maximum value of all the ChangeId members in the $List array. This value can be used to request only changes entities. (See below.)

DCUs

/MDM/DCUs/List

Meters

/MDM/Meters/List

Fetching changed entities in a class

ChangeIds

PlanetGIS uses an incremental number to serialize changes that enter the data stream. Planet's database system is distributed and allows offline operation, but once a database becomes online again, the changes made while offline enters the data stream and are returned to the database with a server-assigned, sequential ChangeId for each change. This ensures that all changes are applied to all databases in the exact same order so that all databases remain identical (while online).

While offline, and for a brief moment after making a change (usually at most 5 seconds), an entity's ChangeId may be negative. Such entities will be shown in a /List request, but will not appear in a /Changed request (until the next request, if sufficient time has elapsed).

Changed requests

To stay in synchronization with changes made to a PlanetGIS database, a system like MDM could issue a Changed request at high-frequency intervals of 10 seconds to a few minutes.

The LastChangeId value in the list request above can be used for fetching information of only the entities that have been modified since that request. This is done by passing the LastChangeId to a ../Changed request:

/MDM/Meters/Changed?LastChangeId=nnnnn

Note that this fetches all entities (in the specified class) with a ChangeId of at least one more than the supplied LastChangeId. Passing ?LastChangeId=0 will retrieve the same contents in the $List array as a regular /List request (except as noted above).

This is an example of a returned result:

{
  "Request": "List of changed meters",
  "ChangedSince": "62256",
  "Count": 2,
  "LastChangeId": 62289,
  "$List": [
    {
      "MDMID": 1,
      ..etc..
    },
    ..etc..
  ]
}

First, the ChangedSince member echos back the request. Then, the LastChangeId member is the maximum ChangeId in the $List array. This value should be used for the next request (the ?LastChangeId= query parameter) to fetch any new changes after this request.

Deleting entities

The PlanetGIS data stream, which is the backbone of the database, is append-only which means that data is never replaced or removed (unless the stream is directly edited to remove harmful changes). This allows for deletions to be undone. However, when an entity is deleted, all its attributes are removed from the main database. Only a row in the entities table remains, and the Track column contains the value -1, indicating that the entity has been deleted.

The endpoints for deleting entities are as follows:

• /MDM/Transformers/Delete
• /MDM/DCUs/Delete
• /MDM/Meters/Delete

Since a delete is a type of update, there are also update schemas, located at an endpoint with /Schema appended. These schemas are a lot simpler than regular update schemas. Here is the one for transformers:

/MDM/Transformers/Delete/Schema

As can be seen from the schema, to delete entities, a JSON array containing objects with MDMID members must be POSTed to the delete endpoint. For example, the following will delete a transformer with MDMD 1:

[
  {
    "MDMID": 1
  }
]

The reponse to a delete request will be a JSON object, for example:

{
  "Request": "Delete transformers",
  "$Updates": [
    {
      "1": "deleted"
    }
  ]
}

Responses in the $Updates array will be either deleted or not found.

Deleted entities and the /Changed API

Since rows from attributes tables are removed when an entity is deleted, the MDMID becomes unavailable for further queries through the API. For this reason, Planet's internal EntityId is included when returning information via the /List, /Changed and /Query endpoints.

The /Changed endpoint, unlike /List returns deleted entities, though all normal attributes (including MDMID) will be null. If a system using the API needs to synchronize itself from changes to the PlanetGIS database, it will have to maintain the PlanetGIS EntityId in its own database.

Alerts

Update schema

The current update schema for alerts is as follows:

The schema allows for MDMIDs that are unique only to the device type. Application logic in the calculated member provides the ability to do MDMID lookups in different feature classes. The lookup for PlanetGIS' internal code for the unique combination of alert values, as well as the creation of new combinations, can also be seen in the schema.

Creating an alert

The endpoint for creating and updating alerts is /MDM/Alerts/Update. An example request for creating an alert looks as follows:

[
  {
    "MDMID": 1,
    "Date": "2024-01-31 12:30:00", 
    "DeviceType": "DCU",
    "DeviceId": 1,
    "Level": "Critical",
    "Alert": "DCU outage",
    "Cleared": null
  }
]

Responses are the same as for other updates discussed above.

Updating an alert

Once an alert is cleared, an example of setting the Cleared attribute is as follows:

[
  {
    "MDMID": 1,
    "Cleared": "2024-01-31 12:30:00"
  }
]

It is recommended that unchanged attributes be omitted as in the example above, but not required. If any other attribute need to be changed for whatever reason, simply include it in this request.

Thematics are updated in the GIS, immediately upon receiving these updates.

Querying existing alerts

(Todo)

Uploading attachments

To upload an attachment, or more accurately, a BLOB for which an attachment entity will be created if necessary, the endpoint is:

/MDM/BLOBs/Update

Unlike other endpoints, the content of the POST request is not JSON, but the unencoded binary data of the BLOB. The only other information sent will be optional query parameters and HTTP headers. An example HTTP header for generic binary data is Content-Type: application/octet-stream. Photographs should have the header Content-Type: image/jpeg but this is not required. PlanetGIS will determine the type of the content by examining the first few bytes, and classify it as something known (JPEG, PNG, etc) or a document if the content cannot be identified. PlanetGIS will also create a thumbnail from the received content and extract location data from the Exif data contained in JPEGs.

Also unlike the other endpoints, a request is needed for every BLOB that is uploaded. PlanetGIS can receive multiple BLOBs if the data is sent as an HTML form (encoded multipart/form-data), but this endpoint currently requires the method described above, which is more practical for BLOBs with sizes in excess of 500KB.

Query parameters are optional and as follows:

• ClassId: to specify the PlanetGIS attachment class. If omitted, JPEGs will be placed in the Photographs class, PNGs in the Images superclass and anything else in the Documents class.
• DeviceType: one of Meter, DCU or Transformer; needed to find the PlanetGIS EntityId for the supplied MDMID (next).
• DeviceId: MDMID of device to which attachment should be linked. This may add an additional link if the device already has attachments.

For example, the following request will upload the content as a BLOB, create an attachment in the Images → Photographs class and link it to the specified feature:

/MDM/BLOBs/Update?ClassId=9&DeviceType=DCU&DeviceId=1

ClassIds for attachment classes are as follows, and new classes may be created:

6. 6 - Images
7. 7 - Documents
8. 8 - Sketches
9. 9 - Photographs
10. 10 - Diagrams
11. 11 - Spherical images
12. 12 - Artwork

Optional information

Application specific headers can be passed in the request, along with Content-Type, to provide additional information about an attachment:

• X-Planet-FileName: "file name"
• X-Planet-Date: "ISO8601 date"
• X-Planet-Description: "a description of the attachment"
• X-Planet-Tags: "tag1;tag2;tagN"

The above information will be stored as attributes of the attachment, only if the BLOB belonging to the attachment is new. To change the attributes of existing attachments, see below. There is one exception: tags are added to any existing tags without creating duplicate tags.

The response

Here is an example of a response:

{
  "AttachmentId": "764776831876510483",
  "$AttachedTo": [
    {
      "DeviceType": "DCU",
      "MDMID": 40
    }
  ],
  "$Details": {
    "HashKey": "-7TnumCsWBglGgw8x6-5jDm_p2w",
    "URL": "https://cloud2.planetgis.co.za/powercom-maquassihills/blobs/content/-7TnumCsWBglGgw8x6-5jDm_p2w"
  }
}

AttachmentId is PlanetGIS' internal EntityId for the attachment. It is returned as a string because some JSON parsers cannot handle 64 bit integers (all modern web browsers need special handling in their JSON parsers for large integers).

$AttachedTo is an array identifying all MDM devices that the attachment is linked to.

$Details is an object containing the HashKey (unique SHA1 identifier) of the BLOB on the BLOB server (e.g. the name of the object in an Azure container) and the URL that can be used to retrieve the BLOB.

Attachment attributes

Attachments, like any other type of entity, have attributes that describe the attachment. Once such attribute is HashKey which links the attachment entity to its content. When an attachment's content is modified (replaced), it will get a new HashKey that identifies a new BLOB. The basic attributes of attachments are as follows:

Querying an attachment's attributes

To retrieve the attributes of an attachment, the attachment's unique EntityId must be known. This, like all entity Ids in PlanetGIS, is a 64 bit integer that is unique in the project's database. This Id is the value returned as the value of the AttachmentId member in the JSON response when uploading a BLOB. The following API endpoint retrieves attributes for an attachment:

/MDM/Attachments/Query?AttachmentId=nnnn

Example response:

{
  "Request": "Attachment query",
  "AttachmentId": "764776831876510483",
  "$Attributes": {
    "Ordinal": null,
    "FileName": "WIN_20230420_01_13_36_Pro.jpg",
    "Date": "2024-06-10",
    "Tags": "ATag",
    "Description": "This is just a test upload",
    "Reference": null,
    "HashKey": "-7TnumCsWBglGgw8x6-5jDm_p2w",
    "ChangeId": 70324
  }
  "$AttachedTo": [
    "762502092637940929"
  ],
  "$AttachedToMDM": [
    {
      "DeviceType": "DCU",
      "MDMID": 40
    }
  ]
}

$Attributes will be an empty object {} if the attachment was not found or an id was passed that belongs to an entity that is not an attachment. Otherwise, attributes are returned as for other /Query endpoints, with a ChangeId that can be used to detect whether any attribute was changed since a previous query.

$AttachedTo is an array of all entity ids linked to the attachment. $AttachedToMDM will contain references to MDM features, if the attachment was linked to any such feature.

Updating attachment attributes

The endpoint for updating attachment attributes is /MDM/Attachments/Update and the update schema is located at /MDM/Attachments/Update/Schema

Like other ../Update endpoints, an array is used that may contain updates for several attachments, but unlike other update schemas, the PlanetGIS internal AttachmentId must be used to identify an attachment in the JSON array.

Listing an entity's attachments

To list attachments linked to an MDM feature, use the /MDM/Attachments/For endpoint:

/MDM/Attachments/For?DeviceType=DCU&MDMID=40

Example response:

{
  "Request": "Attachments for MDM feature",
  "DeviceType": "DCU",
  "MDMID": "40",
  "EntityId": "762502092637940929",
  "$Attachments": [
    "764776831876510483"
  ]
}

DeviceType must be one of Meter, DCU or Transformer. If a feature of the specified class and MDMDID is not found, the EntityId member will have a value of null. If the feature was found, the $Attachments member will be an array of attachment ids linked to the feature.

Generic variant

Instead of specifying DeviceType and MDMID, you can instead pass an EntityId to list attachments for any type of entity in the PlanetGIS database:

/MDM/Attachments/For?EntityId=762502092637940929

Replacing an attachment's content

An additional HTTP header is available to specify the AttachmentId for a BLOB when uploading it with the /MDM/BLOBs/Update endpoint:

X-Planet-AttachmentId: nnnn where nnnn is the Id of the attachment entity that must reference the uploaded BLOB.

The BLOB must be new, or more accurately, not already referenced by an attachment entity (it may already be on the BLOB server, but its attachment entity was deleted). If another attachment is already referencing the BLOB (or another identical copy of it), the API request will fail with the error message Identical content already loaded for another attachment.

Only replace an attachment's content if it is the same document that has been updated. The next section describes how to remove an attachment from an entity, which might be a more appropriate way of changing an entity's attachments.

Deleting an attachment

It is important to remember that an attachment may be linked to multiple entities, especially because PlanetGIS enforces avoidance of duplicates based on the stored bytes of content BLOBs. I.e. the API will accept a duplicate attachment for another entity by linking the BLOB already stored to a new entity for which an attachment is being uploaded. The only way to know that this has happened, is by examining the $AttachedTo member of the response to the /MDM/BLOBs/Update API for multiple entries. When an attachment is deleted, it is removed from all entities that referred to it.

The API for deleting an attachment is similar to the endpoints for other entity classes:

/MDM/Attachments/Delete

The update schema is /MDM/Attachments/Delete/Schema, indicating that an array of AttachmentId values must be POSTed to the endpoint. As with other ../Delete endpoints, the array items must be objects, but with an AttachmentId member instead of MDMID.

Unlinking an attachment

In the case where an attachment may be linked to multiple entities, but only needs to be removed (unlinked) from one, the following endpoint can be used:

/MDM/Attachments/Unlink?FromEntityId=nnnn&ToEntityId=mmmm where nnnn is the Id of the entity (e.g., feature) that references the attachment and mmmm is the attachment's Id.

The response will be either {"Success":0} or {"Success":1}, the former if the reference was not found.

This will create orphaned attachments when the last link to an attachment is removed, but that is much less of a problem than inadvertently removing an attachment from another entity. Orphans can be detected and deleted during periodic maintenance on the PlanetGIS database, using a simple script.

Re-linking an attachment

The following endpoint will create a link between an entity and an attachment, if one doesn't already exist:

/MDM/Attachments/Link?FromEntityId=nnnn&ToEntityId=mmmm where nnnn is the Id of the entity (e.g., feature) that should reference the attachment and mmmm is the attachment's Id.

The response is the same as for Unlink to indicate whether or not the operation was successful, but do note that the passed Ids are not checked to see if they exist in the database. Success simply means that the reference was added, i.e., a reference between the two passed Ids did not exist prior to the request.