Tutorial: Editable Mapset

Overview

This lesson teaches you how to use an SQL database and forms to allow creating and editing map data.

It is intentionally more challenging. Instead of teaching you every click and mouse gesture, you’ll see instructions like “create the Attachment mapset”. Refer to previous lessons like [[Tutorial: Making a Mapset]] if these still confuse you.

This tutorial is part of the [[Course: Partner Workbench]].

audience

  • system administrators and IT staff
  • power users
  • developers

objectives

  • work through a real-life example of field data collection
  • learn about SQL, forms, and the wheel menu

prerequisites

You need a working Partner installation and basic familiarity with the Workbench. You also need to understand how to create and work with mapsets. [[Tutorial: Making a Mapset]] and its prerequisites are designed with this in mind.

Sharing the Poles

A common problem for our electric utility customers is what’s generally called a joint-use audit. Joint-use refers to the common case where telephone, cable, fiber, or other wires are attached to electric poles. These are called, naturally enough, attachments, and generally there are contracts between the various companies to cover the costs of usage, maintenance, transfer, and other activities related to attachment.

Tracking these sorts of things is a big problem, and often times you start out with little or no idea how many attachments there actually are. The only solution is to send people out into the field to count them.

Our example is simplistic, but captures the nature of a solution to this problem, and can be adapted to almost any data collection task.

Be aware, though, that this sort of application is what Partner’s [[Field Inspection|Field Inspection System]] was designed for, and it would be far easier to build it in the inspection system than by hand like this. But, if nothing else, this will show you some of the issues involved, and it remains a good example of how to interact with databases using the Map Viewer.

Design

We’ll assume a map with some kind of pole numbering system. Our tutorial map set has these. Thus, we can just keep track of the pole number surveyed, rather than x and y coordinates. For simplicity’s sake we’ll just use checkbox entry for each possible attachment type. Our service area includes both BellSouth and AllTel phone companies, so we’ll make separate entries for them.

So, we need an SQL table with the following fields:

  • pole
  • bellsouth
  • alltel
  • catv
  • fiber
  • other
  • comment

We’ll place a large circle over a pole to indicate that it has been surveyed.

Module Configuration

create the module

Create a new module by navigating to the modules/seat directory, choosing the Modules tab and clicking on “New Module”. Name your module “Attachment”.

database choice

For our example, we will use the embedded HSQLDB database. This is included in all Partner installations, does not require a separate server process, and is sufficient for our needs.

create the database

In version 4.4.7 and later, modules can have a “databases” subdirectory containing XML files for named SqlDatabase configurations.

Create the standard directory “databases” in modules/seat/Attachment. Select the directory and create a new file named “Attachment.xml”. Edit the file and go to the “Database” tab for a form-based editor.

Fill in only these two fields: * Database type: HSQLDB * File path: data/Attachment/database

This defines a SQLite SQL embedded database located at data/Attachment/database.

restart workbench

Restart the workbench. This is required to actually load our new module.

create the attachment table

Our database is empty, and we need to create a table for our attachment data. In your module, create scripts/ directory and add the following app script to it:

modules/seat/Attachment/scripts/CreateDatabase.groovy

import com.partnersoft.system.SystemServices;

// get module
module = SystemServices.moduleManager().moduleNamed("Attachment");

// fetch database configuration
database = module.databaseNamed("Attachment");

// connect to the database
connection = database.openConnection();

// create table (multi-line Groovy string)
connection.runScript(
"""
create cached table attachment (
        pole varchar(255),
        bellsouth boolean,
        alltel boolean,
        catv boolean,
        fiber boolean,
        other boolean,
        comment varchar(255)
        )
"""
);

// close
connection.close();

If you know any SQL you’ll see that we’re just creating a new table named “attachment” with the same fields as our design.

[[Image:DatabaseCreation.png|none|frame|Placement of Attachment-CreateDatabase.groovy.]]

Click the run button to run the script and create the table. This will create the database files and the table definition. Another cool feature of the workbench is that you can actually view hsqldb tables. Under the data directory, click Attachment, then click database.tmp, then click database.data to see your empty table. [[Image:DatabaseViewing.png|none|frame|Viewing the contents of an hsqldb table in the workbench.]]

Mapset Configuration

create the mapset

From the workbench, create a new mapset by navigating to the directory config/seat/mapsets and clicking “New Mapset”. Name your mapset “Attachment”.

Connect it to the module by adding a file info/module.txt inside the Attachment mapset directory. Put the name of the module in it (which is also, of course, Attachment):

config/seat/mapsets/Attachment/info/module.txt

Attachment

[[Image:AttachmentFileTree.png|none|frame|At this point your filetree should have both the mapset and module directories set up as shown.]]

survey action

This action adds an attachment survey record to our mapset. Since we have a database now, we can also edit any existing records, instead of just appending. We allow for this by pre-querying the database for existing data, and setting the dialog accordingly.

Create an action named “Survey Pole”, in the Attachment mapset(you can use the make link just like the previous tutorials), and put the following in it:

config/seat/mapsets/Attachment/actions/Survey Pole.groovy

import com.partnersoft.gui.forms.FormBuilder;
import com.partnersoft.gui.forms.FormDialog;

// idiot proof
if (selected == null ||
        !(selected.getDataType().equalsIgnoreCase("arc:Support_Structure") || selected.getDataType().equalsIgnoreCase("Attachment"))) {
    app.getLogic().showError("You must select a support structure!");
    return;
}

// get pole number from selection - either a background map pole or an attachment
pole = selected.getData().get("GPS Tag Number");
if (selected.getDataType().equalsIgnoreCase("Attachment"))
        pole = selected.getData().get("pole");

// build the dialog
builder = new FormBuilder();
builder.layoutForm();
builder.addLabel("Pole Number");
builder.addReadOnlyStringField("pole");
builder.addLabel("BellSouth");
builder.addCheckboxField("bellsouth");
builder.addLabel("AllTel");
builder.addCheckboxField("alltel");
builder.addLabel("CATV");
builder.addCheckboxField("catv");
builder.addLabel("Fiber");
builder.addCheckboxField("fiber");
builder.addLabel("Other");
builder.addCheckboxField("other");
builder.addLabel("Comment");
builder.addTextField("comment");
form = builder.finishForm();

// open connection to database
connection = module.databaseNamed("Attachment").openConnection();

// create editable object - fill in with data from database if it exists
data = connection.oneRowQuery("select * from attachment where pole=?", pole);

// this takes care of empty result set - sets the pole # at least
data.put("pole", pole);

// set data in form
form.setEditedObject(data);

// now open a dialog
dialog = new FormDialog(app.getWindow(), "Survey Pole Attachments", form);
dialog.pack();
dialog.setVisible(true);

// SCRIPT STOPS HERE UNTIL DIALOG IS CLOSED

if (!dialog.getCancelled()) {
        // handy method that works for inserting or updating database using table name, key field, and values
        connection.insertOrUpdate("attachment", "pole", form.getEditedObject());
}

// always close your database connections!
connection.close();

// refresh the mapset to show the new or changed data
app.getLogic().getSpaceLogic().refresh(mapset);

frontend script

Create a frontend script named Attachment.groovy:

config/seat/mapsets/Attachment/translator/frontends/Attachments.groovy

// open database
database = module.databaseNamed("Attachment");
connection = database.openConnection();

// read all records
source = connection.query("select * from attachment");
for (record in source) {
        // we'll use the pole number as our find item lookup value
    poleNumber = record.get("pole");

    // this looks up the pole number in the "Pole Tag Number" find item and sticks an Attachment point there with our data
    translator.processFindItem("Pole Tag Number", poleNumber, "Attachment", "Attachment", record);
}

// be sure to close your DataRecordSource and connection
source.close();
connection.close();

report action

Create an action named “Attachment Report.groovy”:

config/seat/mapsets/Attachment/actions/Attachment Report.groovy

// imports
import java.io.StringWriter;
import com.partnersoft.formats.xml.XmlBuilder;
import com.partnersoft.data.TimeLib;

// build report
report = new StringWriter();
builder = new XmlBuilder(report, false);

// report header
builder.startElement("html");
builder.startElement("body");
builder.writeElement("h1", "Attachment Report");
builder.writeElement("p", TimeLib.humaneTimestamp());

// table header
builder.startElement("table", "cellpadding", "10");
builder.startElement("tr");
builder.writeElement("th", "Pole");
builder.writeElement("th", "BellSouth");
builder.writeElement("th", "AllTel");
builder.writeElement("th", "CATV");
builder.writeElement("th", "Fiber");
builder.writeElement("th", "Comment");
builder.endElement("tr");

// open database
database = module.databaseNamed("Attachment");
connection = database.openConnection();

// run through all rows
source = connection.query("select * from attachment");
for (record in source) {
        builder.startElement("tr");

        builder.writeElement("td", record.get("pole"));
        builder.writeElement("td", record.get("bellsouth"));
        builder.writeElement("td", record.get("alltel"));
        builder.writeElement("td", record.get("catv"));
        builder.writeElement("td", record.get("fiber"));
        builder.writeElement("td", record.get("other"));
        builder.writeElement("td", record.get("comment"));

        builder.endElement("tr");
}

// table footer
builder.endElement("table");

// report footer
builder.endElement("body");
builder.endElement("html");
builder.close();

// be sure to close your connection
source.close();
connection.close();

// launch dialog
app.logic.showHTMLReport("Attachment Report", report.toString());

At this point we have created the following in the mapset:

  • actions/Attachment Report.groovy
  • actions/Survey Pole.groovy
  • translator/frontends/Attachments.groovy

And in the module:

  • scripts/Create Database.groovy

[[Image:AttachmentFileTree2.png|frame|none|Your filetree should look like this now.]]

enter some data

Start the Map Viewer from the Workbench menu.

Click on a pole in the map. Be sure you have a pole selected - in the standard demo, these are called “arc:SUPPORT_STRUCTURE”.

Click the Actions menu. Notice that there is now an Attachment submenu. If you click that you’ll see the two actions that we just created are now available: [[Image:SurveyPoleAction.png|frame|none|You can now see your actions in the map viewer.]]

Click “Survey Pole”. A form should pop up that looks like this:

[[Image:SurveyPole.png|frame|none|Any data you enter here should be added to your new database table.]]

Fill it in and press OK. Nothing will appear on the map... yet.

legendary

You should see the following in your Workbench log:

warn : Invalid point graphic type: point-Attachment - you probably need to add it to your legend.

This means that the translator saw your attachment data, and fed it to the map viewer, but the map viewer didn’t have a legend entry for your points and discarded them.

Go the Workbench window, and edit config/seat/mapsets/Attachment/legends/default.xml. This is the default legend for your mapset.

The Workbench auto-detects new graphic types and adds them to the Legend form. You should see an entry for point-Attachment, and a drop-down picklist to the right of it. Click the picklist and pick “default”. This is the default point style, a red dot.

Then click Apply, go back to your Map Viewer window, and select View/Refresh All. A dot or dots should appear, one for each survey you completed. Survey a few more poles and watch more appear.

Test

OK, now do some real testing. Add a bunch of attachment records, and see how they get added to your database file and show up on the map. View them in the report as well. Edit a few by selecting a pole that already has an attachment and change the values.

Once you have enough to be interesting, try the Actions/Attachment/Attachment Report menu item. It should pop open an HTML view of your attachment data. Ugly, eh? Grab an HTML book and fix it! [[Image:AttachmentReport.png|frame|none|The Attachment Report.]]

Keep in mind that whenever your restart the Map Viewer, you’ll have to run View/Refresh All from the Map Viewer menu to see your results. Or, you can check the “Visible on startup” option for your mapset in the Preferences editor (menu Platform/Preferences...).

Wheel Menu

How about wheel menu support?

Wheel menus are nigh-essential to any modern 4.4 mapset. Unfortunately, they use an inherently object-oriented method to determine which buttons and actions are appropriate for a given context; the object responsible is called a Map Edit Actor and must implement the interface com.partnersoft.maps.model.MapEditActor, generally by extending AbstractMapEditActor.

Essentially your actor is responsible for answering yes/no questions about whether specific features are supported (edit, move, rotate, etc.) for a given selection and what the available actions are. The MapEditContext object provided to these methods gives you pretty much anything you could want for that determination.

If you’re not a Java programmer that probably sounds like gibberish. Luckily, copy-from-example still works fine, it’s just a little more complicated than the usual do-this-in-order script.

Add a file named “Map Edit Actor.groovy” (note spacing) to the scripts/ subdirectory of your mapset.

config/seat/mapsets/Attachment/scripts/Map Edit Actor.groovy

import com.partnersoft.maps.model.AbstractMapEditActor;
import com.partnersoft.maps.model.MapEditContext;
import com.partnersoft.maps.app.MapApp;

/**
 * Class definition for our Map Edit Actor implementation. We extend AbstractMapEditActor
 * so that we only have to override the methods we actually support - the others (isMoveSupported(), etc.) will
 * behave appropriately as disabled.
 */
public class MyActor extends AbstractMapEditActor {

    public boolean isEditSupported(MapEditContext context) {
        // only allow editing for "Attachment" objects
        return context.getSelected().getDataType().equalsIgnoreCase("Attachment");
    }

    public void edit(MapEditContext context) {
        // fire the "Survey Pole" action when they click the Edit (E) wheel menu button
        context.getApp().getLogic().getSpaceLogic().doAction("Attachment", "Survey Pole");
    }

    public List listSupportedActions(MapEditContext context) {
        List actions = new ArrayList();

        // only add Survey Pole for support structures
        if (context.getSelected().getDataType().equalsIgnoreCase("arc:SUPPORT_STRUCTURE")) {
            actions.add("Survey Pole");
        }

        return actions;
    }
}

// this is where the script actually starts (and ends)
return new MyActor();

Restart the Map Viewer from the Workbench menu and try clicking on a pole. If you don’t immediately see the wheel menu, there may be multiple items at the location you have selected, click the data tab and make sure arc:SUPPORT_STRUCTURE is the selected item. The wheel menu should appear with just the A on it; clicking this gives you the Survey Pole action. Refresh your mapset using View/Refresh All/ and click on one of your Attachment points. The wheel menu should appear with just the E on it; clicking this pops up the Survey Pole dialog so you can edit that attachment record.

Oh No!

But we’re using a weird embedded database... how do we get to our precious data we’ve collected? Here’s one option: export it.

Create an action named “Export.groovy”, and stick this in it:

config/seat/mapsets/Attachment/actions/Export.groovy

import com.partnersoft.formats.csv.CsvFileBuilder;
import com.partnersoft.system.OSLib;

// set the table
file = module.getDataDirectory().fileFor("export.csv");
builder = new CsvFileBuilder(
        file,
        "pole",
        "bellsouth",
        "alltel",
        "catv",
        "fiber",
        "other",
        "comment"
);
connection = module.databaseNamed("Attachment").openConnection();

// eat
source = connection.query("select * from attachment");
for (record in source) {
        builder.write(record);
}

// do the dishes
source.close();
connection.close();
builder.close();

// open the file in Excel or whatever
OSLib.open(file);

You’ll have to restart the Map Viewer for it to recognize this new action and let you run it. When you do run it, it should generate a file in data/Attachment/export.csv and pop it up in your default application for CSV files (generally a spreadsheet). [[Image:SurveyExport.png|none|frame|The export file will be created in the data/Attachment directory, and open in the default csv application.]]

config/seat/mapsets/Attachment/actions/Import.groovy

This does the reverse, importing records from a CSV file.

import com.partnersoft.formats.csv.CsvDataRecordSource;

// set the table
file = module.getDataDirectory().fileFor("export.csv");
source = new CsvDataRecordSource(file);
connection = module.databaseNamed("Attachment").openConnection();

// eat
recordCount = 0;
for (record in source) {
        connection.insertOrUpdate("attachment", "pole", record);
        recordCount++;
}

// do the dishes
source.close();
connection.close();

log.info("Imported " + recordCount + " records.");

Google Map Export

This action exports your data to Google Map HTML format.

config/seat/mapsets/Attachment/actions/Export to Google.groovy

import com.partnersoft.formats.google.*;
import com.partnersoft.system.OSLib;

centerX = 1992000;
centerY = 358725;
projection = app.getSpace().getProjection();
centerLL = projection.reverseFeet(centerX, centerY);

log.info("Center = " + centerLL);

poleFind = app.getSpace().findItemNamed("Pole Tag Number");
backgroundRover = app.getSpace().roverSetNamed("Background");

// set the table
file = module.getDataDirectory().fileFor("export.html");
builder = new GoogleMapBuilder(file);
builder.setCenter(centerLL.getX(), centerLL.getY());
builder.setMapWidth(400);
builder.setMapHeight(400);
builder.setTitle("Attachments");
builder.setStyle("Pole", "dot-large-red.png");

// open database
connection = module.databaseNamed("Attachment").openConnection();

// eat
source = connection.query("select * from attachment");
for (record in source) {
        pole = record.get("pole");
        dataId = poleFind.find(pole);
        data = backgroundRover.dataFor(dataId);
        log.info("data = " + data);
        log.info("location = " + data.getLocation());
        poleLL = projection.reverseFeet(data.getLocation().toXyPoint());
        log.info("poleLL = " + poleLL);
        builder.addMarker(poleLL.getX(), poleLL.getY(), "Pole", pole);
}

// do the dishes
source.close();
connection.close();
builder.build();

// open the file in your web browser
OSLib.browse(file);

Ideas for Expansion

  • add find items
  • build a summary report, counting totals for all attachments
  • merge results into a central database
  • change the graphic types to distinguish different kinds of attachments
  • track violations
  • convert it to use x,y coordinates instead of find item lookups, then allow drawing in missing poles
  • automatically generate invoices to the attachment companies

Moving On

Get your hands dirty with <no title>.

[[Category: Tutorials]]