Building a Custom Query

Years ago, I used to do a lot of programming with MS Access. Somewhere along the way I saw an interesting code sample of how to write a custom query. I have used the methodology described ever since. There may be better solutions available, and I am open to them, but I have used variations of the following code in all types of web pages and programs.

First Step:

1. Create Search Form
   
2. Code The Search ButtonPrivate Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
   ‘On Error GoTo ActError

   ‘Query which searchs by user input
   Dim strwhere As String

   ‘Parcel Query Defaults
   strOwner = gtxtOwner
   strHouseNum = gtxtHouseNum
   strStreetName = gtxtStreetName
   strMap = gtxtMap
   strGroup = gtxtGroup
   strParcel = gttxtParcel
   strParcelFile = gtxtParcelFile
   strParcelID = gtxtParcelID
   With cmdSearch

   strwhere = “‘”

   If Len(txtOwnerSearch.Text) > 0 Then

   strwhere = ” and ” & strOwner & ” like ‘” & txtOwnerSearch.Text & “%’”
   End If

   If Len(txtCMAPSearch.Text) > 0 Then

   strwhere = strwhere & ” and ” & strMap & ” = ‘” & txtCMAPSearch.Text & “‘”

   End If

   If Len(txtGPSearch.Text) > 0 Then

   strwhere = strwhere & ” and ” & strGroup & ” = ‘” & txtGPSearch.Text & “‘”

   End If

   If Len(txtParcelSearch.Text) > 0 Then

   strwhere = strwhere & ” and ” & strParcel & ” = ” & txtParcelSearch.Text

   End If

   If Len(txtHouseNumSearch.Text) > 0 Then

   strwhere = strwhere & ” and ” & strHouseNum & ” = ” & txtHouseNumSearch.Text

   End If

   If Len(txtStreetNameSearch.Text) > 0 Then

   strwhere = strwhere & ” and ” & strStreetName & ” like ‘” & txtStreetNameSearch.Text & “%’”
   End If

   End With

   strwhere = UCase$(Mid$(strwhere, 6))

   txtSearch.Text = strwhere
   Call sSearch(strwhere)
   Me.ParcelsTableAdapter.Fill(Me.CurvDBDataSet.parcels)
   End Sub

   Explanation:
   I will attempt to explain the code.  It we look at:

   If Len(txtOwnerSearch.Text) > 0 Then
   strwhere = ” and ” & strOwner & ” like ‘” & txtOwnerSearch.Text & “%’”
   End If

   In the If…Then statement we test to see if txtOwnerSearch.text has a value.  I have been told that checking to see if the length of txtOwnerSearch.text is greater than 0 is slightly quicker than checking to see if txtOwnerSearch.text has a value.  If the length of txtOwnerSearch.text is greater than 0 and therefore has a value we begin to build the query statement.

   You will notice that all query statements begin with an AND regardless if it is the first statement.  This is by design and will be handled.

   strwhere = ” and ” & strOwner & ” like ‘” & txtOwnerSearch.Text & “%’”

   strwhere= Combined Query String
   strOwner=Column to be queried
   ” like ‘” & txtOwnerSearch.Text & “%’”=Search operator(Like), value(txtOwnerSearch.Text) and wildcard(%)

   Results:
   “and NAME_1=’Wilson M’”

   Handling the initial AND:

   strwhere = UCase$(Mid$(strwhere, 6))

   UCase$ = Capitalizes the entire string
   Mid$= Cuts off the first 6 characters, ” and “

   Results:
   strWhere=”NAME_1=’Wilson M’”

   Call sSearch(strwhere) – Call the search Subroutine

3. Building the Search Sub:

   Sub sSearch(ByVal pstrWhere As String)
   ‘Set mouse cursor as this can take some time with large datasets
   On Error GoTo ErrorHandler
   ‘app.MousePointer = vbHourglass
   Dim intCount As Integer
   ‘Get layer to query
   Dim pARMap As ARMap
   pARMap = frmMain.conARMap.ARPageLayout.FocusARMap

   Call sSetIndexes(strParcelFile)
   pARLayer = pARMap.ARLayer(gintGroupIndex).ChildARLayer(gintLayerIndex)  ‘m_LayersIndex(cboLayers.ListIndex)

   ‘Build the ARSearchDef
   Dim pARSearchDef As ArcReaderSearchDef
   pARSearchDef = New ArcReaderSearchDef

   ‘Build WhereClause that meets search criteria
   Dim sWhereClause As String
   ‘Remove quotes from WhereClause if search is numeric
   sWhereClause = pstrWhere
   pARSearchDef.WhereClause = sWhereClause
   m_pFeatureCursor = pARLayer.SearchARFeatures(pARSearchDef)
   intCount = fCreateParcelDS(m_pFeatureCursor)

   If intCount > 0 Then
   lblMeets.Text = “Features MEETING the search criteria: ” & intCount
   Else
   lblMeets.Text = “Features MEETING the search criteria: 0″
   End If
   Exit Sub

   ErrorHandler:
   Debug.Print(Err.Number)
   Select Case Err.Number
   Case -2147219885
   MsgBox(“Check Field Names under Program Options”)
   ‘Me.MousePointer = vbDefault
   Exit Sub
   End Select

   End Sub

   Explanation:
   This subroutine is used to process the query string.  The code here is slanted to work with ArcReader Datasets and calls several custom functions which can be ignored.  The relevant section where the query is applied is:

   Call sSetIndexes(strParcelFile) – Subroutine to set variables for search layer indexes
   pARLayer = pARMap.ARLayer(gintGroupIndex).ChildARLayer(gintLayerIndex)  ‘m_LayersIndex(cboLayers.ListIndex) – Set the layer to be searched
   
   ‘Build the ARSearchDef
   Dim pARSearchDef As ArcReaderSearchDef
   pARSearchDef = New ArcReaderSearchDef

   ‘Build WhereClause that meets search criteria
   Dim sWhereClause As String
   ‘Remove quotes from WhereClause if search is numeric
   sWhereClause = pstrWhere
   pARSearchDef.WhereClause = sWhereClause - Apply the search string
   m_pFeatureCursor = pARLayer.SearchARFeatures(pARSearchDef)- Build the Querried features
   intCount = fCreateParcelDS(m_pFeatureCursor) - Custom function to process the records and provide record count

Summary:

The methodology described above is extremely flexable.  I have used it in VB, VBA, .NET, PHP, and ASP projects.  I hope it helps you.

Creating a Headline section on a Web Page

When I design a website, I typically like to add a headlines section to the index page.  I do not always have time to either edit the index or create new stories.  For this reason, I started using a blog with RSS feeds.  I can then use PHP to parse the feed and display the headlines.  To keep things neat I limit the number of headline and once I reach my limit, I put a link to additional posts.

The PHP RSS Parser I use is MagpieRSS, as per the MagpieRSS website:

MagpieRSS: RSS for PHP

MagpieRSS provides an XML-based expat RSS parser in PHP.

MagpieRSS is compatible with RSS 0.9 through RSS 1.0. Also parses RSS 1.0’s modules, RSS 2.0, and Atom.

via Magpie RSS – PHP RSS Parser.

In order to diplay just the headlines, I insert the following PHP block into my index page:

<?
require_once ‘rss_fetch.inc’;

// ADD YOUR FEED LINK
$url = ‘http://www.example.com/articles/Articles.rss’;
$rss = fetch_rss($url);
$counter=1;
$i=1;
foreach ($rss->items as $item ) {
$title = $item['title'];
$url   = $item['link'];
if ($i<9) {

//OPEN HEADLINE IN NEW WINDOW
echo “<p>&bull;&nbsp;<a href=’$url’ target=’_Blank’>$title</a><br /></p>\n”;
}
$i++;
$counter++;
}
if ($i>8) {

//ADD YOUR FEED LOCATION
echo “<a href=’http://www.example.com/Articles’  target=’_Blank’>More…</a><br>\n”;
}
?>

Layout Server Object Extension and Print Layout Task Install guide with Notes

Layout Server Object Extension and Print Layout Task Install guide for a 2 System Setup

via Print Layout Task / Server Object Extension for .NET Web ADF (manual install)

The system I am setting this Extension up on is a 2 machine AGS system as illustrated below:

(From http://edndoc.esri.com/arcobjects/9.1/ArcGISServer/HelpPages/Server_typical.gif)

I found this led to some difficulties getting the scripts running and have tried to make notes.  All added notes are in bold. (Original Material from Author markdeaton)

Installing the Layout Server Extension (SOC):

1. Copy LayoutSOE.dll into the machine

2. Register with regasm /codebase /tlb (Details about this at the end of the
doc in the ‘About Registering Assemblies’ section)

3. Add the assembly to the GAC with gacutil /i (Details about this at the end of the
doc in the ‘About Registering Assemblies’ section)

Installing the Layout Server Extension Property Page (Desktop admin):
This machine is where you plan to use ArcCatalog to administer AGS.  It can be any machine with ArcCatalog in stalled, including the webserver or SOM box.

4. Close ArcCatalog on and ArcGIS Desktop machine

5. Make sure ArcGIS Server is up and running.

6. Make sure you are logged as an ArcGIS Server admin (User account used must be in AGSAdmin group on SOM/SOC box).

7. Copy LayoutSOE.dll into the machine

8. Register with regasm /codebase /tlb(Details about this at the end of the
doc in the ‘About Registering Assemblies’ section)

9. Add the assembly to the GAC with gacutil /i (Details about this at the end of the
doc in the ‘About Registering Assemblies’ section)

10. Copy LayoutSOEPropPage.dll into the machine

11. Register with regasm /codebase /tlb (Details about this at the end of the
doc in the ‘About Registering Assemblies’ section)

12. Restart ArcGIS Server. (I stopped and Started via Windows “Services”)

Installing the Print Task (webServer):

1. Copy LayoutSOETask.dll in the webServer.

2. Copy LayoutSOE.dll in the webServer

3. Register both assemblies with regasm /codebase /tlb

4. Add the assembly to the GAC with gacutil /i

5. Run UpdateManager.exe passing true to install and false to uninstall.
UpdateManager will make sure that the print task is recognized by ArcGIS
Manager and that the task will look like any other task in any application
created with Manager (style will match the template you choose).

UpdateManager.exe is run from the cmd prompt:

In cases where you are not using the default web server directory open the code for the “UpdateManager”.  Look for the following code in “Program.cs”:

namespace UpdateManager
{
class Program
{
static void Main(string[] args)
{
if (args.Length != 1)
{
Console.WriteLine(“Must pass true or false”);
Console.WriteLine(“Press any key to continue…”);
Console.ReadLine();
return;
}
Console.WriteLine(“Finding IIS Root Folder…”);
string root = findIISRoot();
if (root == “”)
{
Console.WriteLine(“Error”);
Console.WriteLine(“  Cannot find IIS root folder. Is IIS installed in the machine?”);
Console.WriteLine(“Press any key to continue…”);
Console.ReadLine();

return;            }

Change:

string root = findIISRoot();

To match correct path and rebuild project:

string root = @”f:\inetpub\wwwroot” //findIISRoot();

Replace UpdateManager.exe with rebuilt pjoject

6. Restart IIS.

Getting started:
Open Readme_ArcScripts.doc and follow the Script.  I found that with our current setup, The “Layout SOE” could not be added via AGS Manager.  It could only be added via a custom web application in Visual Studio 2005.

Adding the task to custom web applications (web server):

13. Create a new webApp with Manager and add the mapService that has the
layoutSOE extension enabled. Make sure that the mapService as added via
an AGS Local Connection.

14. Add the ArcGIS Identity to the webApp.

In cases where you cannot change ArcGIS Identity from VS 2005, save the project and change settings on he webserver.  On the webserver go to “Internet Information Services” -> Right Click on your WebApp -> Click “Properties” -> Click on “ASP.Net” tab -> Click on “Edit Configuration”-> Click on “Applications” and Check “Local Impersonation” and add username and password.  User and password need to be members on AGSUsers group on all machaines and have identical passwords.

15. Open default.aspx in design mode with visual studio.

16.Add the task assembly (LayoutSOETask.dll) to the toolbox by right
clicking the toolbox; selecting ‘choose items’ and browsing to the
assembly (LayoutSOETask.dll). The next image shows the LayoutSOETask
once added to the toolbox in Visual Studio.

17. Drag the task from the toolbox into the taskManager (“TaskManager1″) control.

18. Set the Task Results Panel property (a dialog will show up automatically)

19. Push F4 with the LayoutSOETask control selected to open it’s properties.

Make sure that the properties dialog is opened for the LayoutSOETask
control, and not the TaskManager control.

20. In the Properties dialog, go to the ‘resources’ category and set the
MapControlID (the map), then set the resourceName to the service added
in step #2. If this list is empty make sure that you have added an AGS
Map Service with the LayoutSOE capability enabled, AND that this map
service is AGS Local Resource.  I found that in some cases, initially the resource with the Layout SOE enabled under the”MapResourceName” was not avaialble.  To solve this click on “MapControlID” and reselect the map.

21. Also change the visible property of the LayoutSOETask control to false. Otherwise the task will always be visible by default without even opening it. when the application starts.

About Registering Assemblies:
Regasm registration
Go to Start-All Programs- Microsoft .Net Framework 2.0 – SDK Command Prompt
Type: regasm full_path_to_the_assembly /codebase /tlb
For example
Regasm D:\Demos\Layout\LayoutSOE.dll /codebase /tlb
Gacutil registration
Type gacutil /i full_path_to_the_assembly

Note: This document was created by people who have used this utility in the past. If you
have any suggestions-improvements on this document please contact the author so the
document can be updated.  Need a way to contact the Author.