In this article, we are going to show how to get started with Geospatial data in PostgreSQL. More specifically, we are going to learn how to store spatial information (e.g. Coordinates in a map) and how we can index and perform searches on this data (e.g. Find all coordinates inside a given area).

Installing PostGIS

PostGIS is an extension of standard PostgreSQL that allows us to work with geospatial data. This means, if the extension is not present, we won’t have these capabilities.

To check if our database has PostGIS, we can use this query:

1
SELECT PostGIS_Version();

If PostGIS is not enabled, we will get a message similar to this one:

1
ERROR:  42883: function postgis_version() does not exist

If PostGIS is enabled, we will get something like this:

1
2
3
4
postgres=# SELECT PostGIS_Version();
            postgis_version
---------------------------------------
 3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

If PostGIS is not installed on our system, we can follow the installation instructions based on our system.

Points

One of the most common use cases for PostGIS is storing coordinates in a map. For this, we can use the POINT type.

Let’s say we want to have a table that stores information about places, including their coordinates. We can create this table:

1
2
3
4
5
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location GEOMETRY(POINT, 4326)
);

You might be wondering what 4326 means. EPSG:4326 is a standard coordinate reference system (CRS) used in geographic information systems (GIS). This is the standard used by most systems that work with geographic information (Google Maps, Leaflet, etc.), so we use it too.

Since we want to perform geospatial queries against this field, we need to create an index:

1
CREATE INDEX idx_places_location ON places USING GIST (location);

We can insert a new place with this query:

1
2
INSERT INTO places(name, location)
VALUES('Central Park', ST_GeomFromText('POINT(-73.9654 40.7829)'));

In this case, we inserted a record for Central Park, in New York. Note that the first value (-73.9654) is the longitude and the second is the latitude.

The data is saved in a binary encoding, so it won’t be easy to read if we just query it:

1
2
3
4
5
SELECT * FROM places;

 id |     name     |                      location
----+--------------+----------------------------------------------------
  1 | Central Park | 0101000020E6100000BDE3141DC97D52C0EA04341136644440

If we want to see the point representation, we can use a query like this one:

1
2
3
4
5
SELECT id, name, ST_AsText(location) FROM places;

 id |     name     |        st_astext
----+--------------+-------------------------
  1 | Central Park | POINT(-73.9654 40.7829)

Areas

If instead of just a point, we want to store an area (i.e. the polygon that delimits a location), we need to use a POLYGON instead of a POINT:

1
2
3
4
5
CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 4326)
);

To insert Central Park, we can use this query:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO areas (name, boundary)
VALUES (
    'Central Park',
    ST_GeomFromText('POLYGON((
        -73.9731 40.7644,
        -73.9819 40.7681,
        -73.9580 40.8006,
        -73.9498 40.7968,
        -73.9731 40.7644
    ))')
);

Note that we call the POLYGON function with a list of points that delimit the area we are interested in. Also note that the first and last points must be exactly the same.

We can query the date similarly:

1
2
3
4
5
SELECT name, ST_AsText(boundary) FROM areas;

 id |     name     |                                         boundary_text
----+--------------+------------------------------------------------------------------------------------------------
  1 | Central Park | POLYGON((-73.9731 40.7644,-73.9819 40.7681,-73.958 40.8006,-73.9498 40.7968,-73.9731 40.7644))

Geojson

Geojson is a standard format for encoding geographic data using JSON. The format is easy to read and write, and it’s supported by many tools, including PostGIS.

We can, for example, use this query to see the Central Park area as Geojson:

1
2
3
4
5
SELECT name, ST_AsGeoJSON(boundary) FROM areas;

     name     |                                                            st_asgeojson
--------------+------------------------------------------------------------------------------------------------------------------------------------
 Central Park | {"type":"Polygon","coordinates":[[[-73.9731,40.7644],[-73.9819,40.7681],[-73.958,40.8006],[-73.9498,40.7968],[-73.9731,40.7644]]]}

This gives us the benefit of being able to use the output in other tools. For example, we can visualize the area in Geojson.io:

We can also use Geojson when inserting data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO areas (name, boundary)
VALUES (
    'Dolores Park',
    ST_GeomFromGeoJSON('{
        "type": "Polygon",
        "coordinates": [
          [
            [ -122.42836549322851, 37.76123463001613 ],
            [ -122.42806139698865, 37.75811926165099 ],
            [ -122.42592638797277, 37.758254497406654 ],
            [ -122.42620514285912, 37.76137987710844 ],
            [ -122.42836549322851, 37.76123463001613 ]
          ]
        ]
    }')
);

Geo queries

Now that we have Geographic information in our database, we can perform all kinds of queries against it.

It’s often useful to figure out if a point is inside an area. For example, we can use this query to check if our Central Park point is inside our Central Park area:

1
2
3
4
5
6
7
8
SELECT ST_Contains(
  ST_GeomFromText('POLYGON(( -73.9731 40.7644, -73.9819 40.7681, -73.9580 40.8006, -73.9498 40.7968, -73.9731 40.7644))', 4326),
  ST_GeomFromText('POINT(-73.9654 40.7829)', 4326)
);

 st_contains
-------------
 t

Or, using the values already in our database:

1
2
3
4
SELECT ST_Contains(
  (SELECT boundary FROM areas WHERE name = 'Central Park' LIMIT 1),
  (SELECT location FROM places WHERE name = 'Central Park' LIMIT 1)
);

We might also want to get all locations inside a polygon. We can do it with this query:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT id, name
FROM places
WHERE ST_Contains(
  ST_GeomFromText(
    'POLYGON((
      -73.9731 40.7644,
      -73.9819 40.7681,
      -73.9580 40.8006,
      -73.9498 40.7968,
      -73.9731 40.7644
    ))', 4326),
    location
);

There are many other possible operations, but we are not going to cover those in this article.

Conclusion

Handling geospatial data can seem intimidating, but there are many common use cases that can be easily achieved with PostGIS.

If you want to try the commands in this article, you can find a ready to use docker image in my examples repo.

In this article, we are going to learn how to write software that uses configuration files using ESP-IDF.

Kconfig

Kconfig is the configuration system used by the Linux kernel and by ESP-IDF. It allows management of project settings through a structured and hierarchical menu system.

It allows developers to write configuration files that specify the available configuration options for a piece of software.

In this article, we are going to explore how the clipboard works when using different tools inside a terminal and how we can configure these tools so we get and intuitive experience.

Linux

The most basic example of using the clipboard would be using it directly from the operating system.

Most Linux distributions come with a built-in clipboard. This clipboard works as a temporary data storage that can hold a single item. To add an item to the clipboard, we can select it, right click it and select “Copy” or we can use the shortcut Ctrl-C.

There are different tools that can be used to inspect the clipboard. We will use xclip. To install:

1
sudo apt install xclip

Internationalization is the process of making a website that is adaptable to different locales (regions or languages). Translation is a large part of it, but there are other aspects, like date formatting, for example.

There is no single standard way to do internationalization. In this article we’ll learn the approach I chose, but there are other ways to achieve a similar result.

URLs

To make an internationalized app SEO friendly, it’s recommended to have locale-prefixed URLs. This means, if we have a dashboard page, the URL for it will be something like: /<lang>/dashboard.

This, of course, leaves the possibility of someone visiting our pages without a prefix, for example: /dashboard. In this scenario, we will use locale detection to redirect users to the correct page for their language.

In a previous post, we learned about asynchronous programming with Tokio. This time, we are going to use Axum to build a server that uses Tokio as runtime.

Hello world

Creating a simple server with Axum only requires a few lines:

1
2
3
4
5
6
#[tokio::main]
async fn main() {
    let app = Router::new().route("/", get(|| async { "Hello, World!" }));
    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

If we run this code, we will see a Hello, World! message if we visit http://localhost:3000.

I’m working a project that uses Firestore, so I’m using the firestore crate to help me interact with my database.

There are two examples showing the use of transactions in the source code:

• transactions.rs
• read-write-transactions.rs

There are some parts of those examples that are a little confusing, so I’m writing this article to try and shed some light.

I was not the only one confused by this, and luckily someone brought this up in a Github issue before I had to.

Unrecoverable errors

These errors are a sign that the developer made a mistake. An example of this, could be trying to access an index that is out of bounds. For example:

1
2
3
4
5
6
7
8
fn get_number() -> usize {
    return 5;
}

fn main() {
    let numbers = [1, 2];
    println!("{}", numbers[get_number()]);
}

I’m building a little web server with Rust and as part of it, I’m going to need to send some e-mails to my users. I found a few services that offer an e-mail API with a free tier, and decided to go with Brevo.

Authenticating our domain

In order for our e-mails to reach our users’ inboxes, we need to correctly configure our DKIM and DMARC records so they can be used by Brevo.

We can’t authenticate e-mails from free email providers like Gmail. So, before we can authenticate our domain, we need to own a domain. I’m going to use my blog’s domain (ncona.com).

I’m building a project for an ESP32 microcontroller (which works with 3.3v logic), and my project needs to get information from a 5v sensor.

Connecting the 5v directly to a GPIO (General Purpose Input/Output) pin would probably damage the chip. To prevent this, we can use a voltage divider to lower the voltage.

Voltage dividers

A voltage divider is a simple circuit that given an input voltage, it produces a lower output voltage. A simple representation looks like this:

I’m working on a circuit where I need to use a sensor that mentions using an open collector (Also known as: open drain, open emitter or open source) output. In this post, we are going to learn what this is and how to use open collector components.

The term open in open collector refers to an “open” digital circuit. Which means that a pin in our component is not connected to a HIGH or a LOW signal. It is, effectively, undefined.

When we have an open collector output, the output will toggle between LOW and undefined. This happens, because of the way the component is connected internally, which often looks like this: