SQL

To begin with, a quick description of what SQL API is: Postgresql with PostGIS extension (which is the core of Ramani Cloud) are some of the most powerful pieces of mapping and GIS software. It is hard to imagine a spatial vector analysis that can not be done in a PostGIS database. But PostGIS is a piece of server software that requires some technical skills to install and use. It's not just about running SQLs, but you also need to know how to format and display the result. SQL API allows you to query Ramani Cloud's PostGIS database by sending the SQL string via HTTP / HTTPS and receiving the result formatted as GeoJSON ready for viewing on a web map or as Excel/CSV.

The signature of the SQL API is as follows:

https://example.com/api/v2/sql/[database]

Or if a sub-user is used:

https://example.com/api/v2/sql/[subuser@database]

The SQL string and additional parameters can either be sent as URL parameters or in a JSON body. The following examples use the cURL program, but any HTTP client can be used.

URL parameter. Note that the SQL string is URL encoded:

curl -i --header "Content-Type: application/x-www-form-urlencoded" -XGET \
https://gc2.io/api/v2/sql/dk\
?q=SELECT%201

JSON body. The body can be sent as both GET and POST. The latter can be used in clients who can not GET with body. For example, web browsers:

curl -i --header "Content-Type: application/json" -X GET \
https://gc2.io/api/v2/sql/dk --maps \
'{"q":"SELECT 1"}'

By default, the result is returned as GeoJSON. But MS Excel and CSV are also an option. If Excel or CSV is requested, geometries can be outputted as either GeoJSON or WKT strings in a column. If "geoformat" is not set, geometries are not returned.

The geometries are returned by default in EPSG: 3857 (Web mercator), although the source has a different projection. You can choose the project's projection with "srs":

curl -i --header "Content-Type: application/json" -X GET \
https://gc2.io/api/v2/sql/dk --maps \
'{
  "q":"SELECT 1 as id,ST_setsrid(ST_MakePoint(10,56),4326) as geom",
  "srs":"25832",
  "format":"csv",
  "geoformat":"wkt",
  "allstr": "1",
  "lifetime": 0,
  "base64": 0 
}'

The following parameters can be used:

q: SQL string (required)
srs: EPSG code that the result should be in. (Standard: 3857)
format: geojson, excel or csv. (standard geojson)
geoformat: geojson or wkt. Relating only to Excel and CSV. (Default: not set)
allstr: All columns are set as text type. (Default: not set)
lifetime: Cache the result in this number of seconds on the server. (Default: 0)
base64: Flags that the SQL string is base64 encoded. Can be used to "cheat" firewalls with threat detection.
key: The user's API key. Must be used if reading of one or more relationships is secured.

INSERT, UPDATE og DELETE

You can alter maps by INSERT, UPDATE and DELETE. The SQL API will return the number of rows that have been affected by the transaction.

--maps '{
  "q":"DELETE FROM foo.bar",
  "key":"abc123"
}'

Bulk

The bulk API enables you to POST multiple transaction statements, which will be run in the same transaction block. The bulk API requires the Ramani Cloud API key in the header and the Content-Type must be set to text/plain. The SQL statements must be new line separated.

curl -i \
  --header "Content-Type: text/plain" \
  --header "Ramani Cloud-API-KEY: abc123" \
  -X POST http://host.com/api/v2/sql/dk --maps \
'
INSERT INTO my_table (id) VALUES(1)
INSERT INTO my_table (id) VALUES(2)
INSERT INTO my_table (id) VALUES(3)
INSERT INTO my_table (id) VALUES(4)
'

0 Comments

Add your comment

E-Mail me when someone replies to this comment