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 the role they belong to) needs EXECUTE permission on the Named table.

Note: If the SQL query references more than one table, then EXECUTE permissions are needed on all of the tables included in the query.

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. The query statement can also be specified in the POST body instead. For details, see HTTP POST URL 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://hostname:port/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 query and bound parameters:

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

The {bound parameters} is part of 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.

The query allows specifying a query statement in the POST body in addition to the q parameter in URL. This is useful when the query statement is very long that may extend the size limitation of an URL. When both q and query are specified in the body of the request, then only q is used.

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 and query parameters:

{
   "query":"SELECT WORLD.Capital, WORLD.Country FROM \"/Samples/NamedTables/WorldTable\" WORLD WHERE MI_Contains(Obj,@geomParam)",
   "parameters":[
      {
         "name":"geomParam",
         "value":{
            "type":"Point",
            "coordinates":[
               -86,
               32
            ]
         },
         "crs":{
            "type":"name",
            "properties":{
               "name":"epsg:4326"
            }
         }
      }
   ]
}

Search for features using bound parameters for a parameterized view (a view table that contains bind parameters):

{
    "query":"SELECT Country, Capital FROM \"/NamedTables/ViewTableWithParameters\"",
	
	"parameters": [
		{ 
			"name": "param", 
			"value": [
				"United States", "Canada", "France", "Germany"
			],
			"type": "String"
		}
	] 
}

Note: Parameters defined as part of the request override the bind parameters within a view table. The response contains the candidates specified as parameters in the request. If the parameters specified as part of the request (such as “name”:”param”) do not match the parameters defined as part of the view table, then the default bind parameters defining part of the view table are used. A named table (not a view table or without bind parameters) could include bind parameters within the request. In this case, you can pass bind parameters for the query is part of the request.