Using MATLAB with PostgreSQL database

4 minute read

Overview

I was doing some tests with reading data using MATLAB with PostgreSQL relational database and also using the PostGIS module.

In the database we have the advantage of creating our procedures and views and returning the data practically ready to analyze them, I’m saying this in the case of large structures.

I will describe below how to enable the PostgreSQL drive for MATLAB version R2017a and how to connect to the database and make three simple graphs with geographic coordinates and aircraft altitude.

1. Configuring the driver in MATLAB

Attention! I am using PostgreSQL version 10 but the only JDBC driver that worked for me was 8.4-703 JDBC 4 file postgresql-8.4-703.jdbc4.jar using a Windows-7 64 installed in a virtual machine.

  • After downloading the file copy the same to the installation directory of MATLAB: C:\Program Files\MATLAB\R2017a
  • Now edit the file as administrator: C:\Program Files\MATLAB\R2017a\toolbox\local\classpath.txt
  • At the end of the file add the path to load the driver: C:\Program Files\MATLAB\R2017a\postgresql-8.4-703.jdbc4.jar

Now start the MATLAB within Apps you do tests with the Database Explorer, obviously you need to have a database set up for testing.

There is a document on the MATLAB website to make the installation you find useful by clicking here.

2. Creating a test class for the databases

MATLAB has some functions to access the database, you can see clicking here. I created a simple class called Database that connects to the database and returns a method containing my table of routes.

% File: Database.m
classdef Database < handle
    properties (Access = private)
        m_datasource = 'avionics';
        m_username = 'jack';
        m_password = 'jackdaniels';
        m_driver = 'org.postgresql.Driver';
        m_url = 'jdbc:postgresql://localhost:5432/avionics';
        m_schema = 'public';
        m_conn = 0;
    end
    
    properties (Access = public)
        record = 0;
    end
    
    methods (Access = protected)
        
        function connect(obj)
            obj.m_conn = database(obj.m_datasource, obj.m_username, obj.m_password, obj.m_driver, obj.m_url);
            
            switch isopen(obj.m_conn)
                case 1
                    disp('Connection with database is OK.')
                otherwise
                    error('Failed to connection with database.')
            end
            fprintf(1, '\n')
        end
        
    end

    methods (Access = public)
        
        function obj = Database()
            disp('Initialize database...')
            obj.connect();
        end
        
        function disconnect(obj)
            disp('Closing connect with database...')
            close(obj.m_conn)
        end
        
        function sqlGetAircraftRoute(obj, aircraft)
            try
                tic
                fprintf('Running query to get (aircraft route). Please wait...\n')
                q = "SELECT * FROM " + obj.m_schema + ".func_gis_route(0) WHERE aircraft=" + aircraft;
                fprintf('\tSQL> %s\n', q)
                obj.record = select(obj.m_conn, q);
                toc
                fprintf(1, '\n')
            catch
                warning('Database:sqlGetAircraftRoute',...
                    'Failed to execute query: %s', q)
            end
        end
        
    end
end

The above class will connect to the database and fetch the data in the procedure I want to view. Create another file named Avionic and add the code below.

% File: Avionic.m

% Instance the class of the database.
db = Database();

% Search the 747-8F data of the last flight of the aircraft.
db.sqlGetAircraftRoute('747-8F')
route = db.record;

% Close connection with database.
db.disconnect()

% Geo show plot.
figure('name', 'Plot 747-8F Route - Geo', 'NumberTitle', 'off')
geoshow(route.latitude, route.longitude, 'DisplayType', 'line')
colorbar()
wmline(route.latitude, route.longitude)

% Write KML projections.
kmlwriteline('747-8F.kml', route.latitude, route.longitude, route.altitude)

% Set map and ellipsoid.
figure('name', 'Plot 747-8F Route - Globe', 'NumberTitle', 'off')
axesm('globe', 'geoid', wgs84Ellipsoid)

% Create background.
load topo
meshm(topo, topolegend, size(topo)); 
demcmap(topo);
colorbar()

% Plot route.
plot3m(double(route.latitude), double(route.longitude), double(route.altitude), 'r-')

In the code above we created three graphs to plot the route of the aircraft. Analyzing the information:

Screen1

Press the F5 key to run the code, then click on your workspace to view the route data.

Screen2

The first graph to show is the map of the geoshow() function plotting the route of the aircraft.

Screen3

The next chart shows the scala without the map, the routes are written in KML format that can then be viewed in google maps or google earth.

Screen4

The last chart uses the plot3d() function and takes into account the altitude of the aircraft.

Screen5

3. Final considerations

IMHO, MATLAB is a very powerful, objective and easy-to-learn tool, it’s good for testing concepts and getting things done quickly, but not for scalar use.