Support for Geography Data Type in Personify

The geography spatial data type represents data in a round-earth coordinate system. This type is implemented as a .NET common language runtime (CLR) data type in SQL Server. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

As of 7.4.1, the geography type is predefined and available in each database. You can create table columns of type geography and operate on geography data in the same manner as you would use other system-supplied types. In Personify, there are fields that store longitude and latitude coordinates related to addresses. By implementing the “geography” data type for use with LONGITUDE and LATITUDE, Personify will be able to take advantage of the special geography methods and properties to create mapping apps, apps that calculate distances between locations, etc.

Implementation Approach

The process of converting a street address to a latitude/longitude pair is called address geocoding. Personify Universal Address Handler provides geocoding as a service and Latitude and Longitudes are currently stored in the Personify database.

Hence, TMA Resources has simplified the implementation by creating a computed column of type geography, constructed from existing latitudes and longitudes of customer addresses. Since, calculated columns properties are read-only in Personify APIs, they do not participate in Inserts or Updates, but the user can still bind the property to a control on the screen.

One concern with computed columns is unawareness of such types by the TRS APIs. They are expected to cause issues when these columns participate in insert/updates. However, TMA Resources has circumvented this issue by creating the geography type computed column in a new child table which is not exposed to TRS applications. This table has latitude & longitude data columns which are kept in synchronization with the parent table using database triggers and the computed column uses them to construct geography data.

Also, the Database Designer has been modified to provide ability to create computed columns, so that users can create geography columns in the custom tables. For more information, please see Adding a Computed Column.

A check constraint needs to be added to the table restricting the Latitude between -90  & +90.

New Table – CUS_ADDRESS_GEO_LOCATION

GEO_LOCATION computed column expression:

CASE  WHEN LATITUDE IS NULL OR LONGITUDE IS NULL

OR (LATITUDE = 0 AND LONGITUDE = 0)

THEN NULL

            ELSE

geography::Point(LATITUDE, LONGITUDE, 4326)

      END