Search for Features Using SQL Queries

Description

Returns features in a named table in the repository based on a MapInfo SQL query. See the MapInfo SQL Language Reference for detailed information about MI SQL functions.
Note: All MapInfo SQL functions are supported except the MI_GridMinValue and MI_GridMaxValue grid functions.

This operation returns a feature collection JSON that can be passed to a render map or render legend request in the Mapping service.

ACL Authorization Flow

To return features in a named table in the repository based on a MapInfo SQL query, the user (or any roles they belong to) will need Execute permission on the named table in the following manner:

  • “EXECUTE” for entity type “Location Intelligence.Named Resources”
Note: If the SQL query references more than one table then EXECUTE permissions are needed on all of the tables included in the query. (please check with team)

Parameters

For information about 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.
q=MI SQL query String yes The query to perform in MI SQL format.
page=pagenumber String no The page number to return.
pageLength=pagelength String no The number of features returned on each page. If the page parameter is specified in the request, you must define pageLength.

HTTP GET URL Format

The following format is used for HTTP GET requests:


HTTP GET /tables/features.rep;[q=SQL query]
&[page=pagenumber]&[pageLength=pagelength]
			

Returns

Returns specified features in a named table in the repository.


{
"type":"FeatureCollection","Metadata":[{"type":"Geometry","name":"Obj",​
"style":{},"styleColumn":"MI_Style"},{"type":"String","name":"Country"},​
{"type":"String","name":"Capital"},{"type":"String","name":"Continent"},​
{"type":"Integer","name":"Numeric_code"},{"type":"String","name":"FIPS"},​
{"type":"String","name":"ISO_2"},{"type":"String","name":"ISO_3"},​
{"type":"Integer","name":"Pop_1994"},{"type":"Decimal","name":"Pop_Grw_Rt",​
"fractionalDigits":1,"totalDigits":6},{"type":"Integer","name":"Pop_Male"},​
{"type":"Integer","name":"Pop_Fem"},{"type":"Integer","name":"Pop_0_14"},​
{"type":"Integer","name":"Pop_15_64"},{"type":"Integer","name":"Pop_65Plus"},​
{"type":"Integer","name":"Male_0_14"},{"type":"Integer","name":"Male_15_64"},​
{"type":"Integer","name":"Male_65Plus"},{"type":"Integer","name":"Fem_0_14"},​
{"type":"Integer","name":"Fem_15_64"},{"type":"Integer","name":"Fem_65Plus"},​
{"type":"Integer","name":"Pop_Urban"},{"type":"Integer","name":"Pop_Rural"},​
{"type":"Integer","name":"Pop_Urb_Male"},{"type":"Integer","name":"Pop_Urb_Fem"},​
{"type":"Integer","name":"Pop_Rur_Male"},{"type":"Integer","name":"Pop_Rur_Fem"},​
{"type":"Decimal","name":"Arable_Pct","fractionalDigits":1,"totalDigits":6},​
{"type":"Decimal","name":"Literacy","fractionalDigits":1,"totalDigits":4},​
{"type":"Decimal","name":"Inflat_Rate","fractionalDigits":1,"totalDigits":4},​
{"type":"Decimal","name":"Unempl_Rate","fractionalDigits":1,"totalDigits":4},​
{"type":"Decimal","name":"Indust_Growth","fractionalDigits":1,"totalDigits":4},​
{"type":"Integer","name":"ColorCode"},{"type":"Style","name":"MI_Style"}]
}
			

Example

Returns the features for the records where the country is CANADA for the table WorldTable located in the /Samples/NamedTables directory in the Repository.


http://www.<website>.com/rest/Spatial/FeatureService/tables/features.json?q=SELECT * FROM 
"/Samples/NamedTables/WorldTable" WHERE Country='CANADA'
			

HTTP POST URL Format

The following format is used for HTTP POST requests to Search by SQL using bound parameters :


HTTP POST:  /FeatureService/tables/features.rep?
POST Data: [q=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 search 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.

Returns

Returns specified features in a named table in the repository.

Examples

Search for features using bound parameters:


SELECT WORLD.Capital FROM "/WORLD" as WORLD WHERE MI_CONTAINS(Obj, @geomParam)
{
     "parameters": [{
           "name": "geomParam",
           "value": {
                 "type": "Point",
                 "coordinates": [-72, 42],
                 "crs": {
                       "type": "name",
                       "properties": {
                             "name": "epsg:4326"
                       }
                 }
                             }
             }]
}