Software by Jeff Bourdier

DumpRows

DumpRows (Database Utility Map-Producing Read-Only Web Service) is a Common Gateway Interface (CGI) program that allows a geospatial database to be queried using a web browser. It is a native application (written in C), and its only dependency is the C standard library. It is free, open-source software (FOSS), distributed under the MIT License. (In a nutshell, you can do whatever you want with it, so long as it retains the copyright notice.)

DumpRows leverages the abilities of command-line database utilities to produce HTML output by serving as a wrapper that processes the utility's I/O as needed to respond to an HTTP request. If the resultset contains geospatial features, they are shown on an interactive map in the resulting web page. Use cases may include:

Usage

dumprows [OPTION]... COMMAND

COMMAND is a command line (which may be quoted) that invokes a database utility. It should include the arguments needed to:

If the QUERY_STRING environment variable is empty, DumpRows outputs a web page that prompts for a query. The query can be generated using a query template (parameterized SELECT statement), or can be entered directly into the Query text area. (The web page includes a few built-in query templates, and several more sample templates are available in the GitHub repository. To see the web page that prompts for a query, click here.)

When non-empty, the query string should contain a single parameter, query, whose value is an SQL SELECT statement. The query is validated, and COMMAND is executed with the query as input. If the resulting table has a geometry column with spatial data in GeoJSON format, a Leaflet map (with base data from OpenStreetMap) showing the geospatial features is added to the HTML output. (Coordinates are assumed to be geographic (longitude, latitude), using the WGS 84 spheroid.)

Options

-h, --help
Output a help message and exit.
-k, --keep-files
Do not delete temporary files created for I/O processing. (By default, they are deleted, but keeping them can be helpful for debugging purposes.)
-l, --log
Write a message to the default log file (C:\ProgramData\dumprows.log on Windows, /var/log/dumprows.log on Linux).
-t, --template=FILE
Load query templates from FILE. These templates are included in the web page that prompts for a query (if said page is needed).

Examples

These examples were tested on Ubuntu running Apache HTTP Server (httpd). CGI was enabled as follows:

sudo ln -s /etc/apache2/mods-available/cgi.load /etc/apache2/mods-enabled/

Because the directory /var/log/ was owned by root (whereas the httpd process was owned by www-data), the log file was bootstrapped as follows (in order to be writable by the CGI process at run-time):

sudo touch /var/log/dumprows.log
sudo chown www-data:www-data /var/log/dumprows.log

CGI scripts were created in /usr/lib/cgi-bin/. The examples below demonstrate different command lines and options when using DumpRows with various database providers and geometry types.

  1. Oracle

    This example applies to Oracle Spatial (version 12.2 or later, with SDO_UTIL.TO_GEOJSON). The CGI script would invoke SQL*Plus® as follows:

    #!/bin/sh
    dumprows -lk --template=oracle.json "sqlplus -F -M 'HTML ON' -S username/password@//host:port/service_name"

    with username, password, host, port, and service_name populated appropriately. (Note that if the 64-bit version of the Oracle client is installed, the command would be sqlplus64 instead of sqlplus.)

    Let's say the following query returns a set of point features representing school buildings in Brockport, New York:

    SELECT DESCRIPTION AS "Description", ADDRESS AS "Address", SDO_UTIL.TO_GEOJSON(GEOM) AS "Location"
    FROM BROCKPORT.SCHOOL
    ORDER BY ADDRESS;

    This query would equate to the following URL:

    http://host/cgi-bin/script?query=SELECT+DESCRIPTION+AS+"Description"%2C+ADDRESS+AS+"Address"%2C+SDO_UTIL.TO_GEOJSON%28GEOM%29+AS+"Location"%0D%0AFROM+BROCKPORT.SCHOOL%0D%0AORDER+BY+ADDRESS

    where host is the name or IP address of the host, and script is the filename of the CGI script. (This URL would be generated by the web page that prompts for a query.)

    Click here to see the results.

  2. PostgreSQL

    This example applies to PostgreSQL with the PostGIS extension. The CGI script would invoke psql as follows:

    #!/bin/sh
    dumprows --log -tpostgresql.json "psql -H 'host=hostname dbname=dbname user=username password=password'"

    with hostname, dbname, username, and password populated appropriately.

    Let's say the following query returns a set of polygon features representing the coterminous United States:

    SELECT fips_code AS "FIPS", usps_code AS "USPS", name AS "Name", ST_AsGeoJSON(shape) AS "Location"
    FROM states
    WHERE (fips_code::integer < 60) AND (usps_code NOT IN ('AK', 'HI'))
    ORDER BY fips_code;

    This query would equate to the following URL:

    http://host/cgi-bin/script?query=SELECT+fips_code+AS+"FIPS"%2C+usps_code+AS+"USPS"%2C+name+AS+"Name"%2C+ST_AsGeoJSON%28shape%29+AS+"Location"%0D%0AFROM+states%0D%0AWHERE+%28fips_code%3A%3Ainteger+<+60%29+AND+%28usps_code+NOT+IN+%28%27AK%27%2C+%27HI%27%29%29%0D%0AORDER+BY+fips_code

    where host is the name or IP address of the host, and script is the filename of the CGI script. (This URL would be generated by the web page that prompts for a query.)

    Click here to see the results.

  3. SQLite/SpatiaLite

    This example applies primarily to SpatiaLite (version 5 or later, with the GPKG (GeoPackage) extension module), though it should also work with plain SQLite, albeit without the geospatial functionality. The CGI script would invoke spatialite (or sqlite3) as follows:

    #!/bin/sh
    dumprows --keep-files "spatialite -header -html -silent -batch FILENAME"

    with FILENAME populated appropriately. (For plain SQLite (instead of SpatiaLite), substitute sqlite3 for spatialite.)

    Let's say the following query returns a set of linestring features from a GeoPackage produced by the PipelineML GeoPackager:

    SELECT 'Linepipe' AS "Component", CAST(code AS INTEGER) AS "Code", AsGeoJSON(GeomFromGPB(geom)) AS "Location" FROM Linepipe WHERE (CAST(code AS INTEGER) > 1712042) UNION ALL
    SELECT 'Valve'    AS "Component", CAST(code AS INTEGER) AS "Code", AsGeoJSON(GeomFromGPB(geom)) AS "Location" FROM Valve    WHERE (CAST(code AS INTEGER) >    9276) UNION ALL
    SELECT 'Tee'      AS "Component", CAST(code AS INTEGER) AS "Code", AsGeoJSON(GeomFromGPB(geom)) AS "Location" FROM Tee
    ORDER BY 2;

    (For plain SQLite (instead of SpatiaLite), omit the "Location" column, since AsGeoJSON and GeomFromGPB are unavailable.)

    This query would equate to the following URL:

    http://host/cgi-bin/script?query=SELECT+%27Linepipe%27+AS+"Component"%2C+CAST%28code+AS+INTEGER%29+AS+"Code"%2C+AsGeoJSON%28GeomFromGPB%28geom%29%29+AS+"Location"+FROM+Linepipe+WHERE+%28CAST%28code+AS+INTEGER%29+>+1712042%29+UNION+ALL%0D%0ASELECT+%27Valve%27+AS+"Component"%2C+CAST%28code+AS+INTEGER%29+AS+"Code"%2C+AsGeoJSON%28GeomFromGPB%28geom%29%29+AS+"Location"+FROM+Valve+WHERE+%28CAST%28code+AS+INTEGER%29+>+9276%29+UNION+ALL%0D%0ASELECT+%27Tee%27+AS+"Component"%2C+CAST%28code+AS+INTEGER%29+AS+"Code"%2C+AsGeoJSON%28GeomFromGPB%28geom%29%29+AS+"Location"+FROM+Tee%0D%0AORDER+BY+2

    where host is the name or IP address of the host, and script is the filename of the CGI script. (This URL would be generated by the web page that prompts for a query.)

    Click here to see the results.

Download

DumpRows can be downloaded from its GitHub repository, where there are instructions on how to build it from source code.