REST API CRUD: Updating a spatial database
Published: 2022-03-13 10:06:22

In this tutorial, we are going to build a REST API that can be used to update a geospatial database.
<!--more-->
Using Flask-Restful, we will perform basic CRUD (create, read, update and delete) operations on spatial data. It is recommended that readers know how to create a geospatial database.

### Table of Contents
- [Prerequisites](#prerequisites)
- [Setup](#setup)
- [Post Request](#post-request)
- [Get Request](#get-request)
- [Get coordinate by ID](#get-coordinate-by-id)
- [Update Coordinate](#update-coordinate)
- [Delete coordinate](#delete-coordinate)
- [Conclusion](#conclusion)

### Prerequisites
To follow along with this tutorial, you are required to have some knowledge on:
- How to create a Flask app
- How to use Postman
- How to create a geospatial database.

### Setup
First, install Flask-Restful by running the command below in your terminal.

```bash
pip install flask_restful
```

Then import the API object from `flask_restful` and store it in a variable called `api`.

```python
from flask_restful import Api

api = Api(app)
```

After creating the `api` variable, we will need to create our API endpoints. The `api` object and the `add_resource()` method will be used to create the endpoints.

### POST request
Flask-Restful resources give us access to different HTTP methods. One resource can contain multiple HTTP methods such as: `get`, `post`, `put`, and `delete`.

Each method will return a value and a response code after execution.

Using the code snippet below, let's create our first class coordinate using the `flask_restful` resource.

The route will use the `post` request method for saving coordinates to the database.

```python
from flask_restful import Api, Resource

class Coordinate(Resource):
    def post(self):
        response = {
            "status": 400,
            "message": "Coordinate not saved"
        }

        aoi = request.form.get('aoi')

        file = request.files['coordinate']
        read_file = file.read()
        file_json = json.loads(read_file)

        aoi_coordinate = file_json["features"][0]['geometry']
        coordinate = AoiCoordinate(aoi=aoi, coordinate=json.dumps(aoi_coordinate))

        db.session.add(coordinate)
        db.session.commit()

        response['status'] = 201
        response['message'] = "Coordinate saved successfully"

        return response, 201
```

We imported `Resource` from `flask_restful` and used it in creating the class `Coordinate`. This enables us to add the `post` method to the class. This means that it will accept only a post request.

The `response` variable will be returned to the users after each submission. If the form is not saved successfully, the default response will be returned with a status code of 400 (bad requested) which means the request was not fulfilled.

If the form was saved successfully the updated response will be  `Coordinate saved successfully` and the status code changed to 201 (created) which means the request has been fulfilled. 

To make the resource accessible, we will create an endpoint using the `api` object that was created earlier and the  `add_resource()` method from `flask_restful`.

The `add_resource()` method accepts some parameters such as the route and the endpoint.

Let's create our first endpoint.

```python
api.add_resource(Coordinate, "/api/coordinate")
```

We passed the `Coordinate` class and the endpoint as parameters to the `add_resource()` method. 

### GET request
A GET request is used for retrieving data from the database.

In this method, we will retrieve all the data that has been saved in the geodatabase using the `get` request method.

```python
class Coordinates(Resource):
    def get(self):
        response = {
            "status": 204,
            "message": "No coordinate available"
        }

        coordinates = AoiCoordinate.query.all()

        if coordinates:
            all_cord = []

            for location in coordinates:
                location_details = {
                    'id': location.id,
                    'location_coordinate': str(to_shape(location.coordinate)),
                    'location_aoi': location.aoi
                }

                all_cord.append(location_details)

            response['status'] = 200
            response['message'] = all_cord

            return response, 200

        return response, 200
```

We created a new class called `Coordinates` that will retrieve all the coordinates that are saved in the database.

The naming convention is important in Python because it represents what the class entails.

We only used one method because we want to retrieve the saved data only. If coordinates are fetched from the database, the `get` method will return the updated response message and 200 status code but if none is available it returns 204.

To create the endpoint, we will also pass the class and endpoint name as parameters to the `add_resource` method.

```python
# ...
api.add_resource(Coordinates, "/api/coordinates")
```

### Get coordinate by ID
We can retrieve a specific coordinate using its id. It will be passed as a parameter to the URL.

```python
class CoordinateId(Resource):
    def get(self, coordinate_id):
        response = {
            "status": 204,
            "message": "Coordinate not available"
        }

        aoi_details = AoiCoordinate.query.filter_by(id=coordinate_id).first()

        if aoi_details:
            details = {
                "id": aoi_details.id,
                "location_coordinate": str(to_shape(aoi_details.coordinate)),
                "location_aoi": aoi_details.aoi
            }

            response['status'] = 200
            response['message'] = details

            return response, 200

        return response, 204
```

We created a new class `CoordinateId`. ID was added to its name because we will retrieve coordinates using their ID. 

We added a new parameter `cordinate_id` to the `get` method because it will display the ID that is passed to the method. The ID is also passed to the search query to retrieve it from the database.

If the ID is available, it will be displayed and the status code 200 will be returned. If it's not available, the default response message will be displayed and the status code 204 is returned.

The `coordinate_id` will be passed as a parameter to the URL.

```py
# ...
api.add_resource(CoordinateId, '/api/coordinate/<int:coordinate_id>')
```

We can only pass an integer to the URL because we specify our variable type as an integer. The `coordinate_id` will be passed from the URL to the get method, which will be used to fetch the coordinate from our database.

### Update coordinate
After retrieving a coordinate using its ID, we can update it by sending a put request from Postman with the updated fields.

```python
 def put(self, coordinate_id):
        response = {
            "status": 204,
            "message": "AOI is not available"
        }

        aoi_details = AoiCoordinate.query.filter_by(id=coordinate_id).first()

        if aoi_details:
            file = request.files['coordinate']

            aoi = request.form.get('aoi')

            read_file = file.read()
            file_json = json.loads(read_file)

            aoi_coordinate = file_json["features"][0]['geometry']
            aoi_details.coordinate = aoi_coordinate
            aoi_details.aoi = aoi

            db.session.commit()

            response['status'] = 200
            response['message'] = "aoi updated successfully"

            return response, 200

        return response, 204
```

Just like the `get` method, the `put` method also accepts the `coordinate_id` that was passed from the URL, and it will use the same endpoint.

### Delete coordinate
This method is used for deleting the selected coordinate by sending a delete request from Postman. We will add the method to the `CoordinateId` class so that it will receive the `coordinate_id`.

```python
def delete(self, coordinate_id):
    response = {
        "status": 204,
        "message": "AOI is not available"
    }

    aoi = AoiCoordinate.query.filter_by(id=coordinate_id)

    if aoi:
        db.session.delete(aoi)
        db.session.commit()

        response['status'] = 200
        response['message'] = 'Aoi deleted successfully'
        
        return response, 200
```

If the coordinate is deleted successfully, it will return the updated response code and status 200. If not, the default response will be returned.

### Conclusion
APIs play a great role in web development because they allow programs to communicate easily and multiple users can have access to the program. It is also a source of income for the developers because users can be charged for using the API.

Building an API that can update spatial records can serve as a spatial data bank that allow users to perform CRUD operations on spatial data accessible from mobile, web, and desktop apps by sending requests to our server. All we need to do is to write documentation on how they can use it.

The code in this tutorial is available on [this GitHub repo](https://github.com/isaiaholadapo/api-geodatabase).

Happy coding!