Inserting Well Known Text (WKT) into a spatial database

In spatial webservices, the geographical data is usually offered in Well Known Text format according to OGC specifications. Well Known Text aka WKT is a pretty nifty way to transport spatial data through ascii-based channels like XML or JSON. In some situations you might want to store this WKT as spatial data in your local database.

The data you want to process might be the result of a spatial webservice and may look something like this example.
<Object>
 <Guid>0799a8b2-191d-9a55-9438-861d61a2fba7</Guid>
 <Date>06-06-2013</Date>
 <Username>tester11</Username>
 <Type>Dangerous situation</MeldingType>
 <Wktstring>LINESTRING(150715.029 402570.26,150803.524 402729.473)</Wktstring>
</Object>

As you can see, the <Geometry> placeholder contains the WKT definition of a simple polyline. We can convert all information in this example to a SQL statement, that would look something like this.

INSERT INTO <schema>.<table> (guid,date,username,type,wktstring)
VALUES ('0799a8b2-191d-9a55-9438-861d61a2fba7'
        ,'06-06-2013'
        ,'tester11'
        ,'Dangerous situation'
        ,'LINESTRING(150715.029 402570.26,150803.524 402729.473)');

However, this way the spatial information will be stored as plain text into the database. To create proper geometry objects in the database's spatial geometry type format, some more configuration is required.

You can configure your database table to automatically convert the WKT ascii to proper spatial objects. In the next few examples I'll show you how you can accomplish this in Oracle, MS SQL Server and PostGIS.

For the example, we will set up a table called  'mylinestringtable' in each of the three database platforms. The table contains two fields:

1. The first field, named WKTSTRING, is a VARCHAR type field used to store WKT string provided by the webservice's <Geometry> placeholder. 
2. The second field, named SHAPE, is defined as a geometry type field. In this field we will generate proper spatial features. The geometry type definition will vary depending on the database platform.

On all database platforms, the table 'mylinestringtable' will also get some additional administrative fields for storing Guid, Date, User and Type.

#1: Oracle

The RDBMS Oracle can be extended with geospatial properties by installing either Oracle Locator (free) or Oracle Spatial (additional licensing needed).

First, we create the database table with the necessary fields:
CREATE TABLE <schema>.MYLINESTRINGTABLE
  ( SHAPE         MDSYS.SDO_GEOMETRY ,
    GUID          VARCHAR2(40),
    WKTSTRING     VARCHAR2(8000),
    DATE          DATE,
    USERNAME      VARCHAR2(2000),
    TYPE          VARCHAR2(2000),
);

Then we register the table in Oracle Spatial's metadata table. This is required for Oracle to recognize the table as a spatial data table:
INSERT INTO USER_SDO_GEOM_METADATA
    VALUES ('MYLINESTRINGTABLE',
            'SHAPE',
            MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 300000, 0.005),
            MDSYS.SDO_DIM_ELEMENT('Y', 300000, 600000, 0.005)),
            '28992');

The metadata record stores the information about what coordinate system is being used in the table for the geometry type. The parameters used are for the Dutch coordinate system (28992) and will vary for your specific situation.

To ensure the conversion to proper spatial features, we design a trigger on the table that automatically translates WKT to the SDO_GEOMETRY column type upon inserting or updating a record.
CREATE OR REPLACE TRIGGER <schema>.MYLINESTRINTABLE_TRG
  BEFORE INSERT OR UPDATE
  ON <schema>.MYLINESTRINGTABLE
  FOR EACH ROW
BEGIN
  IF (sdo_util.VALIDATE_WKTGEOMETRY(:new.WKTSTRING) = 'TRUE' and :new.SHAPE is null) THEN 
    :new.SHAPE := sdo_util.FROM_WKTGEOMETRY(:new.WKTSTRING);
    :new.SHAPE.SDO_SRID := '28992';  -- RD New Coordinate system (Netherlands)
  END IF;
END;

The trigger performs two actions before the insertion of a new record, on the conditions that valid WKT is provided and that the SHAPE field is still empty:

1. Populate the SHAPE field with SDO_GEOMETRY based on the WKTSTRING field by means of the Oracle-specific spatial procedure 'sdo_util.FROM_WKTGEOMETRY'.
2. Setting the Spatial Reference ID (SRID) of the geometry item to the right coordinate system. The coordinate system for the geometry item must match with the coordinate system for the table, stored in the metadata table.

This way, when a record is inserted into or updated in the table, the well known text is automatically converted to true spatial geometry type information.

#2: MS SQL Server

Again, we create the database table with the necessary fields:
CREATE TABLE [<schema>].[MYLINESTRINGTABLE]
  ( [SHAPE]             [geometry] NULL,
    [GUID]              [varchar](40) NULL,
    [WKTSTRING]         [varchar](8000) NULL,
    [DATE]              [datetime] NULL,
    [USERNAME]          [varchar](2000) NULL,
    [TYPE]              [varchar](2000) NULL
);

The statement looks fairly similar. Only the geometry type is defined with a different syntax. After this step, we immediately can define the trigger because MS SQL Server doesn't require registration of geometry objects in a metadata table. Unfortunately, MS SQL Server doesn't support 'before insert' triggers that will execute on a row-by-row basis. We can solve this by defining an 'instead of' trigger with the following statement.
CREATE TRIGGER [<schema>].[MYLINESTRINGTABLE_TRG] 
            ON [<schema>].[MYLINESTRINGTABLE]
INSTEAD OF INSERT
AS
BEGIN

     INSERT INTO MYLINESTRINGTABLE (
        [SHAPE]
       ,[GUID]
       ,[WKTSTRING]
       ,[DATE]
       ,[USERNAME]
       ,[TYPE]
     ) SELECT
        geometry::STGeomFromText ( WKTSTRING , 28992 )
       ,[GUID]
       ,[WKTSTRING]
       ,[DATE]
       ,[USERNAME]
       ,[TYPE]
      FROM
         INSERTED
END;

The trigger performs the same actions before the insertion of a new record:

1. Populate the SHAPE field with GEOMETRY based on the WKTSTRING field by means of the MS SQL Server-specific spatial procedure 'STGeomFromText'.
2. Setting the Spatial Reference ID (SRID) of the geometry item to the right coordinate system. The coordinate system for the geometry item must match with the coordinate system for the table, stored in the metadata table.

There are some minor differences.

• In the MS SQL Server trigger we have to define all fields that must be inserted by the trigger, while in the Oracle-trigger from example #1 we only had to define the fields that needed to be altered. This approach might require some more maintenance, as the MS SQL Server trigger needs to be updated when fields are added to or deleted from the base table.
• The Oracle trigger will also run when a record is updated, while the MS SQL Server trigger will only work upon first insertion of a record.
• The MS SQL Server trigger will not check for valid geometry yet.

#3 PostgreSQL/PostGIS

The RDBMS PostgreSQL can be extended with geospatial properties by installing the PostGIS extension. Again, we create the new table in the proper schema.
CREATE TABLE <schema>.MYLINESTRINGTABLE
(   SHAPE     geometry(LINESTRING,28992),
    GUID      character varying(40), 
    WKTSTRING character varying(8000),
    DATE      date,
    USERNAME  character varying(2000),
    TYPE      character varying(2000)
);

In PostgreSQL, triggers can't execute SQL statements directly. Instead, we first have to design a trigger function that will convert the inserted WKTSTRING to geometry
CREATE OR REPLACE FUNCTION MYLINESTRINGTABLE_function()
RETURNS trigger AS $body$
    BEGIN
      IF NEW.WKTSTRING IS NOT NULL THEN
        NEW.shape = ST_GeomFromText(NEW.WKTSTRING,28992);
      END IF;
      RETURN NEW;
    END;
$body$ LANGUAGE plpgsql;

After the function is built, we add a trigger to the table that will execute the function when a new record is inserted:
CREATE TRIGGER MYLINESTRINGTABLE_TRG
BEFORE INSERT ON <schema>.MYLINESTRINGTABLE
FOR EACH ROW
EXECUTE PROCEDURE MYLINESTRINGTABLE_function();

The combination of function and trigger performs the same actions before the insertion of a new record as the previous examples:

1. Populate the SHAPE field with GEOMETRY based on the WKTSTRING field by means of the PostGIS-specific spatial procedure 'ST_GeomFromText'.
2. Setting the Spatial Reference ID (SRID) of the geometry item to the right coordinate system. The coordinate system for the geometry item must match with the coordinate system for the table, stored in the metadata table.

Additionally, the PostGIS extension for PostgreSQL is able to process well-known-text that is inserted into a geometry field automatically. If we insert the contents of the LINESTRING directly into the SHAPE column, PostGIS will translate it to geometry type information. However, we can't define the correct SRID this way; the inserted geometry will get a SRID value of  '0'. So while there's no need to design a trigger where PostGIS is concerned, it is recommended to follow the procedure above to ensure the data is in the right spatial reference.

In conclusion

So there you have it! It is fairly easy to process well-known-text into your spatial database based on Oracle, MS SQL Server and PostgreSQL with the use of triggers and functions. Feel free to use above sample code to try it out yourself!