Geolocation data, constituting the position of a geographic point, can be written to an SQL-compatible data source table. This data must be written to a Geography-type column, and be formatted as a comma-separated list containing a single value each for latitude, longitude, and Spatial Reference Identifier (SRID). The SRID is a special value required by SQL to uniquely identify the co-ordinate system from which the other two values are derived. The SRID for planet Earth is 4326, and as such this is the value you will need to use when writing geolocation data related to Earth.
The following example shows a correctly formatted coordinate string for the city of Edinburgh:
"55.948188,-3.201189,4326"
When this string is passed into a Geography-type column in an SQL table, it will be encoded as a new Geography Data String. The following table shows how the three values are encoded for the cities of Edinburgh and London, respectively:
| Latitude | Longitude | SRID | SQL Geography Data String |
|---|---|---|---|
| 55.948188 | -3.201189 | 4326 | 0xE6100000010CD63A71395EF94B4001857AFA089C09C0 |
| 51.512028 | -0.116740 | 4326 | 0xE6100000010C72512D228AC14940FB912232ACE2BDBF |
The Geography data string is only visible in SQL Server - when rendered in KnowledgeKube, the value will be displayed as a string containing the data type of the column and the values for longitude and latitude. Note that the values for longitude and latitude will be in reverse order to how they were originally written, and the SRID will not be included.
The Geography data string for Edinburgh rendered in a data source grid.
The example below shows an expression to assign the latitude, longitude and SRID for the city of Edinburgh to the variable GeogCol1, which is the alias of a geography column in the MyGeolocation data source. The WriteData function can then write the string value to a new row in the table:
GeogCol1:="55.948188,-3.201189,4326";
WriteData("GeogCol1","","MyGeolocation","","");