Stan Grochowski, GIS Systems Analyst Programmer Pierce County GIS 615 S. 9th St.; Suite 220 Tacoma, WA 98405 Tel: (206)596-2916 Fax: (206)596-6622 E-mail: sgrocho@co.pierce.wa.us
Figure 1.
The Pierce County GIS and the Surface Water Management Division are jointly developing a Service Request System to handle storm related problems calls in a more efficient manner. ArcView and PowerBuilder have been chosen as the tools build the application. ArcView is the county standard for GIS display, while PowerBuilder is the front-end for the county's Sybase RDBMS. The integration of these tools involves a series of decisions and choices to most efficiently perform the tasks of the application.The following are a few of the questions which need to be answered before implementing the application. Will the most effective system be developed and run under the UNIX operating system or on a PC platform? If on a PC will it run under Windows NT or Win95? Which platform is the most stable? Which runs the most efficiently? Which best fits with the customers existing hardware or future hardware budget? Which is the most easily expandable? How will PowerBuilder and ArcView communicate witheach other? What additional hardware and software will be needed to communicate and run both tools simultaneously? This paper will discuss these and other issues related to this integrated application and will outline the decisionmaking process involved in implementing the Service Request System application.
The Service Request System (SRS) is bringing together two of Pierce County's most widely used data processing tools. PowerBuilder 4.0, a database front-end form builder and ArcView 2.1a the county's GIS data viewer. ArcView though powerful at displaying and analyzing GIS data is not particularly adept at form building and manipulating tabular data. PowerBuilder handles the tabular data well but has little or no means of handling geographical or spatial data. Today's sophisticated client however is looking for ways of getting at their data utilizing both tabular and graphical data sources. The Service Request System was devised with the intention of meeting these needs. The Surface Water Management and GIS Divisions along with the Development Engineering and Road Maintenance Divisions proposed a system which would handle storm and other water related problem calls. The purpose being to eliminate the occurrence of multiple agency response to one problem. A central database of all the calls would be available to each of the Agency's and calls would be directed to the division best suited to handle that particular call. The GIS portion of the system would give the call takers a quick means of determining where the problem areas are and if multiple calls are being taken concerning one event. It was proposed that GIS along with IS staff, who are skilled in developing PowerBuilder applications work jointly to produce an integrated PowerBuilder and ArcView application to meet the special needs of the SRS system.
Integration comes with a price especially when two divisions which have not previously undertaken a joint project come together and work under one deadline. Differences in programming style and management styles make for conflicting viewpoints at many key points along the road to completion. GIS and Systems & Programming though all part of the same IS department, rarely have the opportunity to really see how each other works and what type of results they expect from their work. These problems though interesting and at times frustrating are not the real focus of this paper. The many hardware and software issues, which arose during the length of this project, are the focus of this paper and will be discussed in detail.
Unix vs. PC
The Unix vs PC question was the first issue we endeavored to answer. Pierce County's GIS data has traditionally resided on a Unix server because of the fact that the primary GIS applications were and are running under ArcInfo on a Unix platform. Typically Unix boxes had larger hard drives and the ability to handle the large data sets more efficiently than did PC's. This is all changing rapidly now as we came to see during the project. Since we wanted the two software packages to communicate with each other easily it seemed unlikely that a hybrid PC/Unix system was the answer. This proved to be true. PowerBuilder has recently released a Unix version of their software and Pierce County had already invested in a large number of Unix licenses for ArcView. Data access was no problem since the data was already on a Unix server. The Unix version of ArcView was comparatively stable and robust. Therefore Unix solution seemed like it would be the easiest and simplest way to go. However such things are often deceiving. When we tested the Unix version of PowerBuilder 4.0 we found two glaring deficiencies. First PowerBuilder had not given their product any RPC capability, which meant we had no easy way for ArcView and PowerBuilder to talk to each other. Secondly most all of our GIS clients work on Unix workstations from a PC through the use of Hummingbird's Exceed X emulation software. PowerBuilder had not written their software to use X emulation. Since PowerBuilder would not run on the hardware that was in place. Short of purchasing Unix workstations for each client it seemed the Unix solution was not viable.
PC ArcView - which OS?
The PC solution posed a whole different set of problems and challenges. First of all the operating system questions needed to be answered. ArcView under Windows 3.1 seemed very unstable and was generally such a resource hog that without an inordinate amount of tinkering and fine tuning it seemed to be totally unusable as a production system. However most county employees were running Windows 3.1 and no support was yet in place for Windows 95 or Windows NT. It was with an evaluation copy of Windows NT that the first test run of the SRS was performed. Immediately we noticed how much more stable NT was as an operating system. It was almost a challenge to get ArcView to crash when running under NT. Application faults did not bring the machine to a halt. ArcView would crash but all one had to do was restart it again, this was looking more like what we saw and liked about Unix. The problem with Windows NT was the amount of "horse power" needed to run it well. Our testing and others within the county working with NT suggested that 32 megabytes of memory and a Pentium processor or equivalent was needed to run and get good performance out of it. Our first tests were run on a Pentium machine with 16 megabytes of memory and it spent a lot of time swapping out to disk much of its work, which significantly slowed down applications like PowerBuilder. Since most county employees had access to machines that in the 486-66 range and had about 16 megabytes of memory. We had to come up with a workable solution that would run well on that size of machine that would not break everyone's budget in upgrading or replacing machines. We next tested Windows 95 with this same configuration. Although it was certainly no screamer of an application it did run with some stability under Windows 95. Unlike Windows 3.1 we could run multiple applications at the same time. It also performed a little better than Windows NT running with the same configuration. Our Client Services Division was better prepared to provide support for Windows 95 than for NT, since they had already had some experience with it. For these reasons Windows 95 was chosen as the OS for the first release. We expect that as the application matures these base machines will be upgraded and replaced by more powerful machines and Windows NT will eventually become the operating system of choice. Even now most of the ArcView development in Pierce County is being done on NT and then ported to Windows 95. This has worked quite smoothly thus far.
Where is the Data?
A major dilemma faced by this project was where to put the data. Pierce County's GIS data currently resides on a Unix server. It was hoped that this would continue to be the case with this application. However when the decision was made to develop SRS on the Win95 platform data location become an issue. Various NFS products were tested to see if they were workable for this application. None proved to be workable for a production system. The speed at which data was drawn on the screen was much too slow to be used when taking calls. Other solutions proposed would not work with Pierce County's current network configuration. Samba, a public domain product, highly touted by those using it, which also gives PC users access to Unix data would not work with our Novell network. Other software products we looked had similar problems such as not being written for Token Ring nets. In the end we had to make our data available on the Novell net directly and had to work out strategies for keeping the data updated and in sync with the GIS data on the Unix server.
DDE Communication
The integration of this application takes place through the use of Dynamic Data Exchange (DDE) which allows applications to instruct each other to perform specific tasks(execute commands). The Arcview Server only allows for a client to make requests to it through Avenue scripts. This way full access to the ArcView's object model and all values is available within the application. The SRS initiates the DDE conversation by means of a button from within a PowerBuilder window. The window as displayed below in Figure 2. shows a PowerBuilder form for entering new address information. This form also acts as a pre-processor for geocoding that will later take place in ArcView. The search button will return a valid unique street name, type and direction with zipcode from a Sybase table derived from the GIS street centerline file. This pre-processing assures us that the address passed to ArcView will conform to the naming conventions used in the centerline file. This of course gives us a much higher percentage of "hits" when geocoding is performed in ArcView. The middle |^| button transfers the selected choice from the candidate list to the address fields, insuring that at least the street name is valid within the given zipcode. Finally if necessary the GIS button is pushed. This is an optional step at this point because pushing the select button actually enters this address into the SRS database and the user need not go to ArcView at this point if data is only being entered. The GIS button gives the user the ability to see whether or not other service requests are located nearby and if they are of interest to the current problem
Figure 2.
The Power Builder Side
The PowerBuilder script behind the GIS button performs several tasks. First of all it starts ArcView if it is not already started as a DDE Server(See Script 1. below). When ArcView initializes its Startup file it sends the request DDEServer.StartNamed ("cv") to start a DDE server named "cv" therefore the program knows that ArcView is started as CountyView and not as a generic ArcView session. The first OpenChannel request in Script 1. is the check to see if the "cv" server is started. This is a system DDE request which is the only kind allowed by ArcView. If it returns a number less than 0 then we know ArcView is not yet started and it opens a message box asking if the user if he wants to start arcview. The user can refuse by pressing the Cancel button and not have to wait the 3 or 4 minutes that ArcView takes to start and respond to the DDE requests. If the answer is OK another message box displays asking for patience as the DDE requests run.
Script 1.
int rtndde, i_str_nbr, avstart, av, avaddr, i_count
string scrpt, addr, zone, s_str_pre_dir_code, s_str_name, s_str_style_type_code,
s_str_sfx_dir_code
string s_us_zip_code, s_str_nbr, avrtn
int avisrun = 0
i_str_nbr = dw_address_selection.GetItemNumber(1, "str_nbr")
s_str_pre_dir_code = dw_address_selection.GetItemString(1, "str_pre_dir_code")
s_str_name = dw_address_selection.GetItemString(1, "str_name")
s_str_style_type_code = dw_address_selection.GetItemString(1,"str_style_type_code")
s_str_sfx_dir_code = dw_address_selection.GetItemString(1, "str_sfx_dir_code")
s_us_zip_code = dw_address_selection.GetItemString(1, "us_zip_code")
s_str_nbr = string(i_str_nbr)
if isnull(s_str_pre_dir_code) then s_str_pre_dir_code = ""
if isnull(s_str_style_type_code) then s_str_style_type_code = ""
if isnull(s_str_sfx_dir_code) then s_str_sfx_dir_code = ""
if isnull(s_us_zip_code) then s_us_zip_code = ""
addr = (s_str_nbr + " " + s_str_pre_dir_code + " " + s_str_name + &
" " + s_str_style_type_code + " " + s_str_sfx_dir_code)
zone = s_us_zip_code
scrpt = 'av.run("Addr.Loc1Offsetnew" , {"' + addr + '", "' + zone + '"} )'
// This is the DDE Client code to pass address info to ArcView
// "cv" is the name of the application "system" is the topic
// The following line checks if server "cv" is already running if it is it
//returns a positive number
// If less than 0 sends request to start ArcView, then does the
//OpenChannel.
avstart = OpenChannel ( "cv", "system" )
if avstart < 0 then
av = MessageBox("ArcView","Start up ArcView ?",StopSign!,OKCancel!)
if av <> 1 then
return
end if
MessageBox("CountyView", "Starting CountyView Please Be Patient...",
Exclamation!)
Run("C:\WIN32APP\ARCVIEW\BIN\ARCVIEW.EXE
c:\win32app\avdata\proj3.apr")
avstart = OpenChannel ( "cv", "system" )
rtndde = GetRemote ( scrpt, avrtn, avstart )
if rtndde < 1 then
CloseChannel(avstart)
avstart = OpenChannel ( "cv", "system" )
rtndde = GetRemote ( scrpt, avrtn, avstart )
MessageBox("AVSTART value after AV is started",rtndde)
end if
avisrun = 1
end if
// Uses GetRemote request to pass the data to ArcView after listing it in
// a message box so that the user can stop the process if he wants
// GetRemote also returns a value from
// ArcView as 'avrtn' this value is the match score obtained in ArcView.
avaddr = MessageBox("Address Passed to AV", (addr + " " + zone), Exclamation!,
OKCancel!)
if avaddr <> 1 then
CloseChannel(avstart)
return
end if
if avisrun > 0 then
rtndde = GetRemote ('av.run("Addr.ReadSQLData",nil)', avrtn, avstart)
CloseChannel(avstart)
else
rtndde = GetRemote ( scrpt, avrtn, avstart )
CloseChannel(avstart)
end if
A second OpenChannel request is made followed by a GetRemote request. The GetRemote sends a request to the ArcView Server for data using the name of the ArcView script as the location of the data, the avrtn variable is the target of the return value, and avstart is the handle identifying the channel opened from ArcView. ArcView returns either an object from a return statement in the script or in the absence of a return statement the last object created in the script.
The next few lines of code were written to take into account differences in the way in which some slower machines respond to the same instructions. In development on the faster NT machines described above in the Unix vs PC section the first Open Channel request was sufficient to make a good DDE connection. However on the client's slower machines this request seems to be being made before ArcView has had a chance to initialize, which caused the Open Channel request to return a negative number and the script to fail. The solution was to check if in fact it had failed to connect and if so try a second OpenChannel request. Fortunately repeating the process once has been sufficient to establish a connection.
After a connection is made the first ArcView script is run. Finally if we know from the avisrun variable that ArcView was started in this script we run the second of the two ArcView scripts, also from a GetRemote DDE request. Both of these scripts will be discussed below. A CloseChannel request is needed at the end of this process. We have found that leaving the Channel open after the script finishes can cause unexpected and unwanted results.
If ArcView had already been running when the GIS button was pressed only the first of the two ArcView scripts will run. Also no attempt will be made to restart ArcView.
The ArcView Side
Once ArcView is started the first script to run is called addr.Loc1offset. This script locates a single address at a time and places a house symbol at the location with an offset of 50. The script is derived from the standard single address match script in arcview except that it does determine which side of the street the address is on and places the symbol there with an offset distance. Some customization of this script, as it came off the Esri website, was needed to capture geocoding cases which do not fit into the three standard AV dialog box cases. When only one candidate was given or if multiple candidates with their scores being less than the minimum the ArcView dialog box does not appear. If one wanted to make changes or see if any possible candidates were good ArcView would not allow it in this single match mode. Using msgBox.ListAsString a custom dialog box was derived to display these candidates before deciding the address was unmatchable. Script 2 displays this code. This same script is run each additional time the GIS button is pressed from the PowerBuilder window.
Script2.
' Locate an address with an offset distance of 50.
'----------------------
'Get address from PowerBuilder
address = SELF.asList
theView = av.GetProject.FindDoc("Pierce County")
theTheme = theView.FindTheme("County Roads")
aMatchSource = theTheme.GetMatchSource
aMatchSource.setoffset (50)
aMatchKey = MatchKey.Make( aMatchSource.GetStanRules )
' Get and set an address to MatchKey key field.
aMatchKey.SetKey(address.get(0))
aMatchKey.SetZoneKey(address.Get(1))
aMatchCase = MatchCase.Make( aMatchSource, aMatchKey )
aMatchPref = MatchPref.Make ' Use default values.
aMatchSource.Openindex
' Search for candidates.
numCand = aMatchSource.Search( aMatchKey, 76, aMatchCase )
address_str = address.get(0)
address_zone = address.get(1)
p = 0
if ( numCand <> 0) then
' Score and get best candidate.
aMatchCase.ScoreCandidates
bestCand = aMatchCase.GetBestCand
BestScore = bestCand.GetScore
n = 1
count = 0
while (n <= numCand)
aMatchCand = aMatchCase.GetNthCand(n)
CandScore = aMatchCand.GetScore
'Check if candidates score is between 63 & 49 and less than the best
'score before lowering good match score
if ((CandScore < 63) and (CandScore > 39) and (BestScore < 63)) then
count = count + 1
end
n = n + 1
end
end
'If no candidates then open AV's locate dialog box to see what is needed for a match
if ( numCand = 0 ) then
aMatchPref.SetPref (#MATCHPREF_NOREVIEW , False)
aMatchPref.SetPref (#MATCHPREF_NOCAND , True)
aMatchPref.SetPrefVal (#MATCHPREF_SPELLWEIGHT , 76)
LocateDialog.SetMatchPref (aMatchPref)
p = LocateDialog.ShowUsingDefault (theTheme, address_str, address_zone)
if (p.isnull.not) then
numCand = 1
end
'if only one candidate or some with lower than acceptable match score open
'custom dialog box to match
elseif (count > 0) then
n = 1
lst_str = {}
aFieldList = aMatchSource.GetMatchFields
'loop thru all candidates
while (n <= numCand)
aMatchCand = aMatchCase.GetNthCand(n)
CandScore = aMatchCand.GetScore
theSide = aMatchCand.GetSide
'Pad out score for display in message box
if (CandScore >= 10) then
lpad = "["
else
lpad = "[ "
end
fl_str = lpad+CandScore.asString+"] "
'Look only for Left side fields for left side match & Right side
'fields for right side match
if (theSide.asString = "L") then
pos_nmb = {0,2,4,5,6,7,8}
elseif (theSide.asString = "R") then
pos_nmb = {1,3,4,5,6,7,9}
end
'Loop thru each of the fields and get value for msgbox display
'for each nmb in pos_nmb
fld = aFieldList.Get(nmb)
fl = aMatchCand.GetValue(fld)
fl_str = fl_str+fl+" "
end
lst_str.add(fl_str)
n = n + 1
end
'Display msgbox for address candidate choice
msgobj = MsgBox.ListAsString (lst_str, "Press OK or Double Click on Good Address to Match"+nl+
"Address: "+address_str++address_zone+nl+
"Score From To Dir Name Type Dir Zip", "Match Candidates")
'Find candidate chosen for match
lst_pos = lst_str.FindByValue (msgobj)
cand_nbr = lst_pos + 1
if (msgobj = nil) then
MsgBox.Info("No Address Selected.", "Locate")
exit
else
bestCand = aMatchCase.GetNthCand(cand_nbr)
end
else
'Handle other cases where there are multiple best or multiple goods after setting lower match score
if (BestScore < aMatchPref.GetPrefVal(#MATCHPREF_MINMATCHSCORE)) then
aMatchPref.SetPref (#MATCHPREF_NOREVIEW , False)
aMatchPref.SetPref (#MATCHPREF_NOCAND , True)
aMatchPref.SetPref (#MATCHPREF_MULTBEST , True)
aMatchPref.SetPref (#MATCHPREF_MULTGOOD , True)
aMatchPref.SetPrefVal (#MATCHPREF_SPELLWEIGHT , 76)
aMatchPref.SetPrefVal (#MATCHPREF_MINMATCHSCORE , 40)
LocateDialog.SetMatchPref (aMatchPref)
p = LocateDialog.ShowUsingDefault (theTheme, address_str, address_zone)
end
end
'Match good candidate if not already done
if ( numCand <> 0) then
if (p = 0) then
p=aMatchSource.GetPoint(bestCand)
end
if (p <> NIL) then
if (p.IsNull) then
MsgBox.Info("Cannot locate address.", "Locate")
else
'project and make graphic for matched point
proj = theView.GetProjection
_pp = p.ReturnProjected(proj)
g = GraphicShape.Make(_pp)
' Change Symbol Marker, Color & Size
newSymbol=
av.GetSymbolWin.GetPalette.Getlist(#PALETTE_LIST_MARKER).Get(78)
g.SetDisplay(theView.GetDisplay)
g.GetSymbol.Copy(newSymbol)
rgbDictionary = av.Run("RGBColorNameDictionary", nil)
forestgreenRGBList = rgbDictionary.Get("forestgreen")
aColor = g.GetSymbol.GetColor
aColor.setRgbList(forestgreenRGBList)
g.GetSymbol.SetSize(9)
'add graphic to the view
theView.GetGraphics.Add(g)
ext = theView.GetDisplay.ReturnExtent
if (not(_pp.IsContainedIn(theView.GetDisplay.ReturnExtent))) then
theView.GetDisplay.PanTo(_pp)
'when panning update the overview box
av.Run( theView.GetUpdateScript, theView )
end
aMatchSource.EndMatch
exit
end
end
end
return
The next step is to run the script which is the heart of this application, called Addr.ConvToShape, shown below in Script 3. The Script makes an SQL call to the Sybase database, geocodes all of the problem locations, displays them in the View and then updates the database with corrected address information gleaned from the geocoding process. The last step of the script, the updating process is the most interesting, because at the outset of the project I did not think it was possible to update an SQL database from ArcView. However, since ArcView can send any valid SQL statement to the SQL server through ODBC, an update statement, or add or delete for that matter, is persmissible. The first step here is to read the Sybase data, which since the data we desire is contained in columns from multiple tables, a View was created in the SRS database. The view called av_address contains all of the columns needed for display in ArcView. The fact that the View already exists in the database allows the database itself to handle the query of the data. Which it does much more efficiently than a complex SQL statement coming from ArcView. The only query ArcView needs to make is a simple Select * request. The View is then read exactly like any SQL table by ArcView and is brought into the project as a Table object. Once in ArcView the Table is run through the geocoding process, in a batch type of mode, with no user interaction. After the first run the new shapefile is added to the view as a theme and a query run against its attribute table checking for all of the problem locations remaining unmatched. The query also checks for the setting of the prev_match field, which was brought over from the sybase table and indicates whether or not this record has sucessfully been run through the geocoding process previously. Those records which did not match in previous runs are not included in the av_address table since they are filtered out during creation of the view av_address. Those records which did not match in the first pass are the only records run through geocoding the second time in an interactive mode. The match preferences are lowered and the geocoding dialog box is displayed allowing for further editing of the address at this time. After all the records that can be geocoded have been matched. A selection is done on that data to select only those that have not been previously matched, in order that these records will supply data for a later SQL update.
Script 3.
theView = av.GetProject.FindDoc("Pierce County")
matchTheme = theView.FindTheme("County Roads")
aMatchSource = matchTheme.GetMatchSource
if (aMatchSource = Nil) then
MsgBox.Error("Theme"++matchTheme.GetName++"is not matchable.",
"Geocode Table")
exit
end
addressTable = av.GetProject.FindDoc("av_address")
if (addressTable = NIL) then
msgBox.info("Unable to find Address Table"++addressTable,
"Address Table")
exit
end
addressVTab = addressTable.GetVTab
street = addressVTab.FindField("str_address")
zip = addressVTab.FindField("us_zip_code")
aGeoName = GeoName.Make( aMatchSource, addressVTab,
street, zip )
oldTheme = theView.FindTheme("All Problem Locations")
if ( oldTheme <> nil ) then
theView.DeleteTheme(oldTheme)
end
fnOutFile = "c:\win32app\avdata\srs\newprob.shp".asFileName
if (fnOutFile = nil) then
exit
else
aGeoName.SetOutFileName(fnOutFile)
end
aMatchSource.InitGeoTheme(aGeoName)
aGeoName.setoffset (50)
addcov_id = matchTheme.GetFTab.FindField( "addcov_" )
aGeoName.SetJoinField (addcov_id)
aMatchKey = MatchKey.Make(aMatchSource.GetStanRules)
aMatchCase = MatchCase.Make( aMatchSource, aMatchKey )
aMatchPref = MatchPref.Make
aGeoTheme = aMatchSource.InitGeoTheme( aGeoName )
numrecs = addressVTab.GetNumRecords
numMatched = 0
av.ShowMsg("Matching Addresses..."++numrecs.asString++"Total")
for each i in addressVTab.GetDefBitMap
av.SetStatus((i / numrecs) * 100)
' Get an address...
aMatchKey.SetKey( addressVTab.ReturnValueString( street, i ))
aMatchKey.SetZoneKey( addressVTab.ReturnValueString( zip, i ))
' Find candidates for the address...
numCand = aMatchSource.Search( aMatchKey, 70, aMatchCase)
if (numCand = 0) then ' No candidates - skip to next address...
continue
else ' We have at least one candidate...
aMatchCase.ScoreCandidates
cand = aMatchCase.GetBestCand
candScore = cand.GetScore
minScore = aMatchPref.GetPrefVal( #MATCHPREF_MINMATCHSCORE )
' If the min required match score is exceeded - write it!
' If the min is not met then the cand is not written and the record
' will remain unmatched...
if ( candScore >= minScore ) then
aMatchSource.WriteMatch( i, aMatchKey, cand )
numMatched = numMatched + 1
'av.ClearMsg
end
end
end
av.ClearStatus
aMatchSource.EndMatch
'MsgBox.Info("Total records processed:"++numrecs.AsString+NL+
' "Total addresses matched:"++numMatched.AsString, "Geocoding Results")
' Remove av_address table from project
'av.GetProject.RemoveDoc(AddressTable)
' Remove Newprob.shp attribute table if exists
theTable = av.GetProject.FindDoc("Attributes of All Problem Locations")
if (theTable <> nil) then
av.GetProject.RemoveDoc(theTable)
end
' Add Matched theme to Pierce County view
gcodeTheme = Theme.Make( aGeoName )
theView.AddTheme( gcodeTheme )
gcodeTheme.SetName( "All Problem Locations" )
gcodeTheme.SetObjectTag( "Problems" )
' Reset Match Prefs for rematch of non-matches in first pass
theNewThm = theView.FindTheme("All Problem Locations")
theRoadThm = theView.FindTheme("County Roads")
aMatchPref.SetPref (#MATCHPREF_NOREVIEW , False)
aMatchPref.SetPref (#MATCHPREF_NOCAND , True)
aMatchPref.SetPref (#MATCHPREF_MULTBEST , True)
aMatchPref.SetPref (#MATCHPREF_MULTGOOD , True)
aMatchPref.SetPrefVal (#MATCHPREF_SPELLWEIGHT , 76)
theNewThm.GetFtab.SetMatchPref (aMatchPref)
'Find all previously unmatched records
addFTab = theNewThm.GetFtab
theBitmap = addFTab.GetSelection
theQuery = "([av_status] = ""U"") and ([prev_match] = ""N"")"
addFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
addFTab.SetSelection(theBitmap)
addFTab.UpdateSelection
'Perform rematch on unmatched record and then clear the selection set
if (theBitmap.Count > 0) then
aMatchSource.ReMatch (aGeoName, addFTab)
end
'Count the number of matched records after rematch
theQuery = "([av_status] = ""M"")"
addFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
addFTab.SetSelection(theBitmap)
addFTab.UpdateSelection
numMatched = theBitmap.Count
'Get all previously unmatched records
theQuery = "([prev_match] = ""N"")"
addFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
addFTab.SetSelection(theBitmap)
addFTab.UpdateSelection
'Get Ftab for Addcov
roadFTab = theRoadThm.GetFtab
m_prob_id = addFTab.FindField("address_id")
m_addcov_id = addFTab.FindField("addcov_")
m_str_address = addFTab.FindField("str_addres")
m_zip = addFTab.FindField("us_zip_cod")
a_address = addFTab.FindField( "av_add" )
a_side = addFTab.FindField( "av_side" )
s_address = addressVtab.FindField( "str_address" )
s_zip = addressVTab.FindField( "us_zip_code" )
s_prev_match = addressVTab.FindField( "prev_match" )
r_addcov_id = roadFTab.FindField( "addcov_" )
'Join Addcov with New Shape File on addcov_id
addFTab.Join (m_addcov_id, roadFTab, r_addcov_id)
r_rzip = addFTab.FindField( "rzip" )
r_lzip = addFTab.FindField( "lzip" )
r_name = addFTab.FindField( "name" )
r_id = addFTab.FindField( "addcov_" )
r_predir = addFTab.FindField( "predir" )
r_type = addFTab.FindField( "type" )
r_sufdir = addFTab.FindField( "dir" )
for each record in addFTab.GetSelection
m_a = addFTab.ReturnValueString(a_address, record)
m_id = addFTab.ReturnValueNumber(m_prob_id, record)
m_side = addFTab.ReturnValueString(a_side, record)
s_a = addressVTab.ReturnValueString(s_address, record)
s_z = addressVTab.ReturnValueString(s_zip, record)
rr_z = addFTab.ReturnValueString(r_rzip, record)
rl_z = addFTab.ReturnValueString(r_lzip, record)
r_street = addFTab.ReturnValueString(r_name, record)
r_pdir = addFTab.ReturnValueString(r_predir, record)
r_typ = addFTab.ReturnValueString(r_type, record)
r_sdir = addFTab.ReturnValueString(r_sufdir, record)
theVBitmap = addressVTab.GetSelection
theQuery = "([prob_id] = " + m_id.asString + ")"
addressVTab.Query(theQuery, theVBitmap, #VTAB_SELTYPE_NEW)
addressVTab.SetSelection(theVBitmap)
addressVTab.UpdateSelection
addFTab.SetEditable(True)
if (m_side = "R") then
r_z = rr_z
elseif (m_side = "L") then
r_z = rl_z
else
r_z = s_z
end
if ((m_a <> s_a) or (r_z <> s_z)) then
r_nbr = m_a.Extract(0)
if (r_pdir.isNull) then
r_pdir = " "
end
if (r_sdir.isNull) then
r_sdir = " "
end
if (r_typ.isNull) then
r_typ = " "
end
new_add = r_nbr.asString++r_pdir++r_street++r_typ++r_sdir
addFTab.SetValueString(m_str_address,record,new_add)
addFTab.SetValueString(m_zip,record,r_z)
av.run("Addr.SQLUpdate",{m_id,"Y",r_nbr,r_pdir,r_street,r_typ,r_sdir,r_z})
else
av.run("Addr.SQLUpdate",{m_id,"Y"})
end
theVBitmap.ClearAll
end
addFTab.SetEditable(False)
addFTab.UnjoinAll
av.GetProject.RemoveDoc(AddressTable)
theBitmap.ClearAll
av.Run ("Addr.MultShp", "")
theView.DeleteTheme(gcodeTheme)
' Bring the View to the front...
theView.GetWin.Activate
av.ShowMsg("Matched:"++numMatched.AsString++"Out of"++numrecs.AsString)
Updating the Sybase Data
The road centerline theme is then joined to the newly created problem location theme by its primary key addcov_id. A query is run against the av_address table to select the record in question. Then the joined table for the problem location and the av_address table are compared for street name, direction, type and zip in order to decide which records in the Sybase database need to be updated. A for each loop is used to look at each record and pass the correct values to the script which will perform the updates. The script Addr.SQLUpdate performs the update of the Sybase tables. Script 4. shown below describes this process. There are two cases, one in which only the prev_match column is updated. This is because of no changes were made to the address fields. The other case updates all address fields if any one ot them has been changed.The link to the centerline file insures that the updated address reflects the street attributes for the street segment to which it is matched and not merely the final address entered into the dialog box which may or may not be the correct address.
Script 4.
'isThereSQL = SQLCon.HasSQL
'if (not isThereSQL) then
' MsgBox.Info("No SQL Connectivity","")
' exit
'end
'
mySQLConnection = SQLCon.Find("venussyb.crs")
if (mySQLConnection.IsLogin = False) then
mySQLConnection.Login("DSN=venussyb.crs;SRVR=venussyb;DB=crs;UID=sgrocho;
PWD=xxxxx")
end
if (mySQLConnection.HasError) then
' Connection failed
listSQLCon = SQLCon.GetConnections
if (listSQLCon.Count = 0) then
MsgBox.Error("Unable to find any SQL Connection","")
exit
else
mySQLConnection = MsgBox.Choice(listSQLCon,"Select a database:","")
if (nil = mySQLConnection) then
exit
end
if (mySQLConnection.HasError) then
MsgBox.Error("Unable to make the connection","")
exit
end
end
end
cnt = SELF.count
sys_id = SELF.Get(0).asString
prev_match = SELF.Get(1)
'if (SELF.Get(2).isNull.not) then
if (cnt > 2) then
str_nbr = SELF.Get(2)
str_predir = SELF.Get(3)
str_name = SELF.Get(4)
str_type = SELF.Get(5)
str_sufdir = SELF.Get(6)
zip_code = SELF.Get(7)
theSQLQuery = "update service_prob_address" +
" set str_nbr = "+str_nbr+"," +
" str_pre_dir_code = """+str_predir+"""," +
" str_name = """+str_name+"""," +
" str_style_type_code = """+str_type+"""," +
" str_sfx_dir_code = """+str_sufdir+"""," +
" us_zip_code = """+zip_code+"""," +
" jurisd_code = ""Y""" +
" from service_prob_address" +
" where srvc_prob_address_sys_id = "+sys_id
else
theSQLQuery = "update service_prob_address" +
" set jurisd_code = ""Y""" +
" from service_prob_address" +
" where srvc_prob_address_sys_id = "+sys_id
end
'msgbox.report(theSQLQuery,"The Query")
mySQLConnection.ExecuteSQL (theSQLQuery)
'mySQLVTab = VTab.MakeSQL(mySQLConnection, theSQLQuery)
return "Done"
Finally one more script is called(Script 5.) which transforms the one theme from which we have been working into three themes. One theme for each major division using the SRS system. Three separate themes for viewing the data was decided upon to make it easier for each division to display only their data separately. Figure 3. below shows the result of Script 5.
Script 5.
theView = av.GetProject.FindDoc("Pierce County")
swm = "c:\win32app\avdata\srs\swm_prob.shp".asFileName
deveng = "c:\win32app\avdata\srs\dev_prob.shp".asFileName
roadmnt = "c:\win32app\avdata\srs\rdmt_prb.shp".asFileName
oldTheme = theView.FindTheme("SWM")
if ( oldTheme <> nil ) then
theView.DeleteTheme(oldTheme)
end
oldTheme = theView.FindTheme("Dvlpmnt Eng")
if ( oldTheme <> nil ) then
theView.DeleteTheme(oldTheme)
end
oldTheme = theView.FindTheme("Road Maint")
if ( oldTheme <> nil ) then
theView.DeleteTheme(oldTheme)
end
t = theView.FindTheme("All Problem Locations")
theFTab = t.GetFTab
theBitmap = theFTab.GetSelection
theQuery = "([orgn_unit_] = ""RDMT"") and ([prob_statu] = ""Active problem"")"
theFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
theFTab.SetSelection(theBitmap)
theFTab.UpdateSelection
anFTab = theFTab.Export(roadmnt,Shape,theFTab.GetSelection.Count> 0)
rthm = FTheme.Make(anFTab)
rthm.SetName ("Road Maint")
rthm.SetObjectTag( "roadmnt" )
theView.AddTheme (rthm)
fldName = rthm.GetFTab.FindField( "orgn_unit_" )
theLegend = rthm.GetLegend
theLegend.Unique ( anFTab, fldName )
rthm.UpdateLegend
theLegend.SetField( fldName )
aLegendFile = "$CNTYDATA\roadmnt.avl".asFileName
theLegend.Load (aLegendFile)
rthm.UpdateLegend
rthm.SetVisible( true )
' theView.Invalidate
rthm.SetActive(true)
theQuery = "([orgn_unit_] = ""SWM"") and
([prob_statu] = ""Active problem"")"
theFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
theFTab.SetSelection(theBitmap)
theFTab.UpdateSelection
anFTab = theFTab.Export(swm, Shape, theFTab.GetSelection.Count > 0)
fthm = FTheme.Make(anFTab)
fthm.SetName ("SWM")
fthm.SetObjectTag( "SWM" )
theView.AddTheme (fthm)
fldName = fthm.GetFTab.FindField( "orgn_unit_" )
theLegend = fthm.GetLegend
theLegend.Unique ( anFTab, fldName )
fthm.UpdateLegend
theLegend.SetField( fldName )
aLegendFile = "$CNTYDATA\swm.avl".asFileName
theLegend.Load (aLegendFile)
fthm.UpdateLegend
fthm.SetVisible( true )
' theView.Invalidate
fthm.SetActive(true)
theQuery = "([orgn_unit_] = ""DENG"") and
([prob_statu] = ""Active problem"")"
theFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
theFTab.SetSelection(theBitmap)
theFTab.UpdateSelection
anFTab = theFTab.Export(deveng,Shape,theFTab.GetSelection.Count > 0)
dthm = FTheme.Make(anFTab)
dthm.SetName ("Dvlpmnt Eng")
dthm.SetObjectTag( "deveng" )
theView.AddTheme (dthm)
fldName = dthm.GetFTab.FindField( "orgn_unit_" )
theLegend = dthm.GetLegend
theLegend.Unique ( anFTab, fldName )
dthm.UpdateLegend
theLegend.SetField( fldName )
aLegendFile = "$CNTYDATA\deveng.avl".asFileName
theLegend.Load (aLegendFile)
dthm.UpdateLegend
dthm.SetVisible( true )
theView.Invalidate
dthm.SetActive(true)
Figure 3.
Conclusion
The integration of ArcView with PowerBuilder can be accomplished to a degree through DDE but
the issues are many and generally reach beyond the realm of just writing some code. Other important
issues must be considered along with the code that will be written to implement a successful system.
With other newer software and hardware products on the horizon many of these current problems will soon
disappear, however other problems and challenges will take their place. The real issues of integrated systems
are not solved in the future but rather by dealing with the problems as they present themselves today.
Acknowledgements
Special thanks go to Stan Gerber, who gets to hear of all the problems and solutions as they are
occuring, Joyce Clifford, Linda Gerull of the GIS staff. Also to Liz Pfeiffer, Raymond Odem, Neil
Blinheim and Chris Lundquist, of the IS staff who got me through the PowerBuilder questions and
blunders. And to Russ Tena and Chris Guzman of the Client Services staff for their patience in
providing technical support and for having to learn about doing ArcView installations quickly. Also
to everyone else who lent a hand or ear or word of encouragement during this process.