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 in modules/seat named "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: SQLite * 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 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 in config/seat/mapsets named "Attachment". Connect it to the module by adding a file info/module.txt with the name of the module in it: 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 :doc:`calculator`. [[Category: Tutorials]]