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 database utilities to produce HTML output by serving as a wrapper that chooses command line options, processes input, and augments output as needed to respond to HTTP requests. Use cases may include:

seeing the results of a spatial query on a map
saving URLs as bookmarks/favorites in your browser (convenient for viewing the results of frequently run queries)
sending URLs via email (very useful for sharing queries/resultsets with colleagues)

DumpRows also provides the ability to generate queries using query templates (parameterized SELECT statements), which are handy for queries that are essentially the same but have minor differences (e.g., they might query different tables).

Usage

DumpRows is similar to other applications commonly used with CGI (e.g., perl) in that it is hosted by a web server, where it functions as the interpreter of a "script" file, which in this case consists of exactly four lines:

an interpreter directive (i.e., a "shebang" line)
one of the following indicators of which database engine/utility to use:
- Oracle
- PostgreSQL
- SQLite
- SpatiaLite
connection information (depends on database engine/utility)
a relative path to a query template file, or empty

When the web server receives a request for a CGI script, its filename is passed as an argument to the interpreter (in this case, the DumpRows executable). The query string of the URL determines what is output.

If the query string is empty, DumpRows outputs a web page that prompts for a query, which can be generated using a query template (and then edited if/as desired) or entered directly into the Query text area. (The web page includes a few built-in templates, and several more are available in the GitHub repository. To see the web page that prompts for a query, click here.)
Otherwise, the query string should contain a single parameter, q, whose value is an SQL SELECT statement. The query is validated¹, and the appropriate database utility 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.

¹ DumpRows implements a crude form of validation that simply checks to make sure the query appears to be a SELECT statement (and not an INSERT, UPDATE, or DELETE statement), thus preventing any data modification. It does not check for valid SQL syntax; the database utility will do that.
² Coordinates are assumed to be geographic (longitude, latitude), using the WGS 84 spheroid.

DumpRows can also be run via command line (e.g., for testing purposes) by setting the QUERY_STRING environment variable as follows.

On Windows:

CMD /C "SET QUERY_STRING=string& dumprows file"

On Linux:

QUERY_STRING=string sh -c "dumprows file"

where string is the query string, and file is the pathname of the CGI script. (Note that on Windows, string cannot be empty.)

Examples

The examples below demonstrate different CGI scripts when using DumpRows with various operating systems, databases, and geometry types. It is assumed that DumpRows is hosted on a machine called GISA.

Oracle (on Windows)

This example applies to Oracle Spatial (version 12.2 or later, with SDO_UTIL.TO_GEOJSON). Consider the following script file, called NY_mapper.dumprows:
```
#!C:\Program Files (x86)\dumprows.exe
Oracle
MAPPER/maps_20@GISNY
oracle.json
```
If the server were to receive the following request, DumpRows would output the web page that prompts for a query, with query templates loaded from the file oracle.json:
```
http://GISA/cgi-bin/NY_mapper.dumprows
```
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://GISA/cgi-bin/NY_mapper.dumprows?q=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
```
(This URL would be generated by the web page that prompts for a query.) If the server were to receive this request, DumpRows would execute SQL*Plus®, connecting to a database called GISNY as user MAPPER with a password of maps_20 (with the query as input). Click here to see the results.
PostgreSQL (on Windows)

This example applies to PostgreSQL with the PostGIS extension. Consider the following script file, called fpm.dumprows:
```
#!C:\Program Files (x86)\dumprows.exe
PostgreSQL
"host=GISDB1 dbname=footprint user=mobile password=abc123"
postgresql.json
```
If the server were to receive the following request, DumpRows would output the web page that prompts for a query, with query templates loaded from the file postgresql.json:
```
http://GISA/cgi-bin/fpm.dumprows
```
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://GISA/cgi-bin/fpm.dumprows?q=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
```
(This URL would be generated by the web page that prompts for a query.) If the server were to receive this request, DumpRows would execute psql, connecting to a database called footprint (hosted on a machine called GISDB1) as user mobile with a password of abc123 (with the query as input). Click here to see the results.
PostgreSQL (on Linux)

(This example does not require PostGIS.) Consider the following script file, called sysadmin.dumprows:
```
#!/usr/local/bin/dumprows
PostgreSQL
postgresql://admin:vgm2u@DBPG2:10987/sysapps?ssl=true
```
If the server were to receive the following request, DumpRows would output the web page that prompts for a query (without loading any extra query templates):
```
http://GISA/cgi-bin/sysadmin.dumprows
```
Let's say the following query returns a resultset indicating how many users of an application live in each state:
```
SELECT states.name AS "State", count(*) AS "Count"
FROM users LEFT OUTER JOIN states ON (users.state_usps = states.usps_code)
GROUP BY states.name
ORDER BY 2 DESC, 1;
```
This query would equate to the following URL:
```
http://GISA/cgi-bin/sysadmin.dumprows?q=SELECT+states.name+AS+"State"%2C+count%28*%29+AS+"Count"%0D%0AFROM+users+LEFT+OUTER+JOIN+states+ON+%28users.state_usps+%3D+states.usps_code%29%0D%0AGROUP+BY+states.name%0D%0AORDER+BY+2+DESC%2C+1
```
(This URL would be generated by the web page that prompts for a query.) If the server were to receive this request, DumpRows would execute psql, connecting to a database called sysapps (hosted on a machine called DBPG2, via port 10987, requiring an SSL connection) as user admin with a password of vgm2u (with the query as input). Click here to see the results.

SpatiaLite (on Linux)

This example applies to SpatiaLite (version 5 or later, with the GPKG (GeoPackage) extension module). Consider the following script file, called pml.dumprows:

#!/usr/local/bin/dumprows
SpatiaLite
/home/jeff/Downloads/PML-1.0-Official-Sample-Files/PML_Valid_Large01.gpkg
sqlite.json

If the server were to receive the following request, DumpRows would output the web page that prompts for a query, with query templates loaded from the file sqlite.json:

http://GISA/cgi-bin/pml.dumprows

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;

This query would equate to the following URL:

http://GISA/cgi-bin/pml.dumprows?q=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

(This URL would be generated by the web page that prompts for a query.) If the server were to receive this request, DumpRows would execute spatialite, connecting to a database called PML_Valid_Large01.gpkg (with the query as input). Click here to see the results.

SQLite (on Linux)

This example is similar to the previous, but applies to plain SQLite (without geospatial functionality). The script file could be identical, except for the database utility (line 2):

#!/usr/local/bin/dumprows
SQLite
/home/jeff/Downloads/PML-1.0-Official-Sample-Files/PML_Valid_Large01.gpkg
sqlite.json

The query could be similar as well, returning instead the total number of features in each table:

SELECT 'Linepipe' AS "Component", COUNT(*) AS "Count" FROM Linepipe UNION ALL
SELECT 'Valve'    AS "Component", COUNT(*) AS "Count" FROM Valve    UNION ALL
SELECT 'Tee'      AS "Component", COUNT(*) AS "Count" FROM Tee
ORDER BY 2 DESC;

This query would equate to the following URL:

http://GISA/cgi-bin/pml.dumprows?q=SELECT+%27Linepipe%27+AS+"Component"%2C+COUNT%28*%29+AS+"Count"+FROM+Linepipe+UNION+ALL%0D%0ASELECT+%27Valve%27+AS+"Component"%2C+COUNT%28*%29+AS+"Count"+FROM+Valve+UNION+ALL%0D%0ASELECT+%27Tee%27+AS+"Component"%2C+COUNT%28*%29+AS+"Count"+FROM+Tee%0D%0AORDER+BY+2+DESC

If the server were to receive this request, DumpRows would execute sqlite3, again connecting to the database PML_Valid_Large01.gpkg (with the query as input). Click here to see the results.

Setup

DumpRows has been tested and is known to work with the following combinations of OS and web server.

Windows and IIS

Ensure that CGI is installed. (Exactly how to do that is beyond the scope of this document, however this article explains it pretty well.)
Launch Internet Information Services (IIS) Manager.
In the Connections pane, click the machine node (the server name).
In the Home pane, double-click ISAPI and CGI Restrictions.
In the Actions pane, click Add....
On the Add ISAPI or CGI Restriction dialog:
1. For ISAPI or CGI path, enter the path to the DumpRows executable, followed by %s (e.g., "C:\Program Files (x86)\dumprows.exe" %s).
2. Check the box for Allow extension path to execute.
3. If desired, enter a Description.
4. Click OK.
In the Connections pane, under the server name, expand Sites.
Right-click Default Web Site.
On the context menu, click Add Virtual Directory....
On the Add Virtual Directory dialog:
1. For Alias, enter cgi-bin.
2. For Physical path, enter C:\inetpub\wwwroot\cgi-bin.
3. Click OK.
In the Connections pane, under Default Web Site, click the new virtual directory (cgi-bin).
In the Home pane, double-click Handler Mappings.
In the Actions pane, click Edit Feature Permissions....
On the Edit Feature Permissions dialog:
1. Check the box for Execute.
2. Click OK.
In the Handler Mappings pane, double-click CGI-exe.
On the Edit Script Map dialog:
1. For Request path, enter *.dumprows.
2. For Executable, enter the path to the DumpRows executable, followed by %s (e.g., "C:\Program Files (x86)\dumprows.exe" %s).
3. Click OK.

CGI scripts should be stored in C:\inetpub\wwwroot\cgi-bin\.

Linux and Apache

Enable CGI:

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

Restart Apache:
```
sudo apachectl graceful
```
If using with SQLite or SpatiaLite, ensure that the httpd process owner (e.g., www-data) has write access to the directories where the databases are stored.

CGI scripts should be stored in /usr/lib/cgi-bin/.

Download

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