Spatial SQL API

The SQL API exposes the power of PostGIS as a web service. You can fire any select query against the database and retrieve the result set as GeoJSON. You can insert, update and delete rows in a safe way using a simple API Key method. Soon we'll introduce our SQL bulk API, which enables you to post a transaction block of queries.

Spatial SQL API

The SQL API exposes the power of PostGIS, the database technology behind Ramani Cloud, as a web service. You can fire any select query against the database and retrieve the result set as GeoJSON. You can insert, update and delete rows is a safe way using a simple API Key authentication method. Soon we'll introduce our SQL bulk API, which enables you to post a transaction block of queries.

Basically you call the API endpoint (check the name of your endpoint in the admin-page) with q=select.... like this: 

/cloud/api/v1/sql/[databaseName]?q=SELECT * FROM public.[layerID]

Where "databaseName" is your Ramani Cloud database, and "layerID" is the name of your layer.

You can enable server side caching of the result by using &lifetime=[seconds]. You can use this on long running queries.

You can use the full disposal of (spatial) SQL, like JOIN, UNION, LIMIT and so on. You can also construct geometies without quering tables like this:

/cloud/sql/[databaseName]?q=SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),4326) as geom

By default the SQL API will transform geometries to Web Mercator. To override this you can use  &srs=[EPSG]

/cloud/api/v1/sql/[databaseName]? q=SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),4326) as geom&srs=4326

Some non-GeoJSON properties are included in the JSON:

forStore Is an object that describes the schema of the result set.

forGrid As above, but with column headings for use the a Extjs grid.

success True or false

message If above is false this will contain the error message.

For use in cross domain scripting, you can "pad" the JSON response with a function (This is know as JSONP): 

/api/v1/sql/[databaseName]?q=SELECT * FROM public.[layerID]&jsonp_callback=myfunction

Insert, update and delete

When doing inserts, updates and deletes you must use your API key.

/api/v1/sql/[databaseName]?q=DELETE FROM public.[layerID]&key=[apiKey]

affected_rows in the JSON response shows how many rows where affected by the query.

You find your API key [apiKey] in Ramani Cloud's administation page (design > service).

The SQL bulk API

Coming soon...


© 2015 Ujuizi Laboratories