Insert Features Using SQL

Description

Inserts features in a table by performing a query on a table using an MI SQL Insert command string. See the MapInfo SQL Language Reference for detailed information on MapInfo SQL functions.

The body JSON representation of the geometries is in GeoJSON format. For more information on GeoJSON, see geojson.org/geojson-spec.html.

Note: You must have View permissions on the named table as well as Create/Modify/Delete permissions on the named table's associated dataset to perform insert, update, and delete operations on writable tables. See Access Control for Datasets in the Administration section for more information.
Note: Insert is supported on PostGIS, Oracle, SQL Server, GeoPackage, and generic JDBC tables. It also supported on MapInfo native TAB and extended native TAB (NativeX) files on Windows operating systems only. Named tables with composite primary keys are not writable.

ACL Authorization Flow

To insert features in a named table by performing a query on a table using an MI SQL Insert command string, the user (or any roles they belong to) will need Execute and Create permissions on the named table in the following manner:

  • “EXECUTE” for entity type “Location Intelligence.Named Resources” for the user
  • “CREATE” for entity type “Location Intelligence.Dataset.DML” respectively

HTTP POST URL Format

The following format is used for HTTP POST requests:


HTTP POST:  /FeatureService/tables/features.rep?
POST Data: [insert=MI SQL query]
POST BODY: Content-Type:application/json {bound parameters}
			

The {bound parameters} is a POST json body (Content-Type: application/json) for the MI SQL insert query containing one or multiple parameters to be included. Null values are supported; however, a type is still required.

Note: The json in the POST is optional; it is only required if the SQL query uses bound parameters.

For information on the parameter types listed below, see Request URL Data Types.

Parameter Type Required Description
rep String yes The representation to be returned. Supported representation is json.
insert=MI SQL query String yes The insert query to perform, in MI SQL format.

Returns

Returns the number of successfully inserted features in a named table in the repository. For native and NativeX TAB files, a SuccessfulInserts response is returned instead that indicates the number of successful rows inserted.

Examples

Inserts features from MyTable using a bound parameter:


http://<hostname>:<port>/rest/Spatial/FeatureService/tables/features.json?
insert= INSERT INTO “/WORLDTABLE”(MI_PRINX, COUNTRY, CAPITAL, CONTINENT, OBJ) 
VALUES (257,'Bahrain', 'Manama', 'Asia', @geomParam) 
{
     "parameters": [{
           "name": "geomParam",
           "value": {
                 "type": "Point",
                 "coordinates": [-72, 42],
                 "crs": {
                       "type": "name",
                       "properties": {
                             "name": "epsg:4269"
                       }
                 }
           }
     }]
}