Installing POSM Server

POSM Server with GIS can be run one of two ways, installed locally on each client as a POSM folder with connections to a mapped drive, or installed on a mapped drive and run from that folder. The POSM Server download is set up ready to share the POSM folder and data. While not required, it’s easiest to have the same drive letter mapped on the workstations that will be using the POSM client on them (for example P:\ )

Space recommendations

We recommend using the full version of SQL server if any of the following apply:

Your database is +25,000 stored inspections

Many users access POSM data simultaneously

The connection is a direct GIS SQL connection

POSM’s database is initially around 2MB and only stores data. A database with approximately 15,000 inspections will still be under 100MB. The largest portion of the system is in the media storage space. We suggest starting with at least 5TB, with a plan in place for future growth of the array. Many of our customers set up POSM to run

on existing SAN arrays to accommodate for storage space and growth potential. POSM allows for control of many variables associated with managing immense amounts of data. Your organization’s long-term plans should factor

in the amount of future storage space needed.

For example, the amount of space needed weighs heavily on video size used (320x240 half-size resolution, or

704x480 full-size resolution), video compression bitrate settings, the number of observations taken, type of media files captured during observations, time spent inspecting, and long-term plans for maintaining video storage. Typically, each terabyte of space will hold approximately 5,000 inspections.

POSM Server with GIS Install Requirements

The POSM Server with GIS Download is pre-set ready to share the POSM folder and data. While not required, it’s easiest to have a drive letter mapped on the workstations to be used on the POSM client (for example P:\ )

POSM can also be set up to run from a UNC path. Users importing data into POSM will need read/write/create access to the folder.

If users are planning to burn DVDs or export PDF reports from POSM, they must map the POSM folder to a drive letter and ensure their connection to POSM uses that mapped drive letter. Since Windows 10, burning DVDs and exporting PDFs are no longer supported with UNC paths on the client.

Windows Server Options

We recommend you use a Windows-based server to host and store POSM data. POSM is typically tested on many versions of Windows Server and will run on all Windows Server versions. Internet Information Server (IIS) is

used to host the HTML reports generated by POSM and must be enabled to the POSM GIS Storage folder. The Storage folder should also be set as a virtual directory on the IIS server or the default folder path. To set up security for POSM, use Windows New Technology File System (NTFS) sharing on the network to assign who has read-only access and read / write access to the POSM Folder. We recommend you create two security groups, a read-only group and a read/write group. This way, you can add users to the group as needed. IIS will need to have the IUSR and IIS_IURS users added to the POSM server folder as read-only users.

POSM databases are hosted and stored on a Microsoft SQL server. Users may set up more than one POSM database if required. A POSM SQL database will run on an existing SQL server with other databases. POSM’s SQL database has been designed and tested to run on SQL 2005 though the most current Microsoft SQL version. POSM Software technicians make every attempt to test POSM on each version of SQL to assure compatibility.

SQL Server Connectivity

POSM may work with MS Windows Authentication or MS SQL Authentication to the SQL Server. MS Windows Authentication is recommended by POSM, as it allows the Domain Controller to control access to the POSM GIS folder and SQL Database.

Alternatively, if user authentication will be used, the user account has privileges to create, update, and modify a database on the server.

It’s also possible to use SQL usernames and passwords if you are not using integrated security from the domain controller. In these cases, set up a read/write user in SQL.

If a copy of SQL Server is unavailable, POSM will run on the free SQL Express without a problem for most users. http://www.microsoft.com/express/database/

Since all of our data is stored in the directory structure, the database size itself is not limited.

Most end users view HTML reports through the web server, which will not affect the database. Starting with ESRI 10, POSM data is synchronized to the GIS Server by means of a SQL view joined to the layer required in GIS. Separate views are normally created for Manhole, Sewer Pipe, and Storm layers. XY observation plot views are also created and linked to the map.

Download the package with the management studio.

POSM creates its database and installs the views for later use in ArcMap.

If you do not have backup software to support the SQL databases, there are a few options. Create a SQL

backup script or use a SQL Express backup product.

POSM Technical Support can also help you set up a daily SQL backup from within the SQL database to the POSM folder. You can find tech support contact info at the top and bottom of this document.

IIS Server must be installed and enabled to share the POSM folder.

Index.html must be added as a default document.

Always check that MP4 is setup as a video / MP4 mime type

For users hosting their POSM folder on a Linux NAS drive, a user will need to be created on both the Windows IIS server and the Linux server with the same username and password. This allows IIS to host a shared Linux NAS folder.

ArcMap access is needed for the workstation(s) responsible for working with POSM Data.

The workstation(s) will need to create a MXD map with the POSM views joined to the layers required

If using ArcServer to host maps with POSM data, the POSM data will need to be linked and registered with the SDE SQL server. Registering POSM with the SDE server also creates ObjectID indexes in POSM’s database to speed query joins from POSM to SDE.

The software may also run on SQL Express and full versions of SQL server. For installations with under 25,000 inspections stored, Microsoft SQL Server Express should be sufficient.

Installing SQL Server Express Edition and SQL Server Management Studio.

If a customer does not have SQL Server, install SQL Server Express Edition. POSM Software runs on all versions of Microsoft SQL, including Azure or Amazon Microsoft SQL DB instances. POSM will also happily run alongside other databases on a full Microsoft Server Database server.

https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

Run installer: SQLServer2017-SSEI-Expr.exe

Choose Basic


Click Install SSMS

That will take you to the page to download SQL Server Management Studio. Download SSMS, then close this window.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

Install SQL Server Management Studio

Click Install.

Restart the system after install.

After Restart, launch SQL Server Management Studio.

The information displayed in the Connect to Server window is what you will enter into POSM’s Database Connection Manager. The user can choose between Windows Authentication or Mixed Mode Authentication. POSM supports both.

This example shows Windows Authentication. For cities and companies, it is easiest to use mixed mode connection. This is where authorized Windows Users are authenticated as well as assigned a pre-setup username and password.

Click Connect.

This ensures your SQL and SQL Management studio are up and running.

Connecting POSM to the SQL Database.

Install and update POSM.


Launch POSM.

Go into Database Connection Manager.

Fill in the Database Connection Manager with the information shown in the SQL Server Connect to the Server window.

We recommend that you keep a note file in the Customer Projects folder with all of this connection information for future reference.

Connection Name is arbitrary but used later, so create a good descriptive client.

Path: Browse to where the POSM.mdb is in the POSM install. But on a shared network, the path is typically the

UNC path to the POSM Server application folder.

Server: SQL Server name from the Connect To Server window.

In this example, we used Windows Authentication for a single user, so there is no username or password, but sometimes a username or SA is used to connect to the SQL database.

Press Save and Close.

In SQL Management Studio, right-click on Databases and select Refresh. You will see POSM database.

Relaunch POSM Updater.

Highlight the new POSM SQL Database in the upper right window of the updater. Click on Update SQL Database

Structure. This will convert the new, generic SQL database into the POSM database structure.

The next step loads the templates into the new SQL database.

Start by highlighting POSMSYSTEM, then click Sync Template To Defaults. Next, highlight each additional template and click Sync Template to Defaults until all needed templates are installed on the server. It is best to stick to just the templates the customer is going to use.

The SQL portion of POSM SQL or POSM Server with GIS is now up and running.

Launch POSM.

Highlight the new SQL Database. Check the Bypass This Screen box Click Open Selected Database.

When POSM is open, it will show the SQL Server path in the Database Path at the bottom left of POSM’s main screen.

To ensure no one accidentally opens and saves data to the original POSM Access database, we need to replace the

POSM.mdb with a blank file.

Go to Windows File Explorer, click the View tab, click Options, Change Folder and Search Options. Turn on show extensions by checking Show Hidden Files, Folders and Drives.


Delete the POSM.mdb file in the POSM application folder. Create a new text file and name it POSM.mdb

Register POSM GIS Database Views in ESRI ArcCatalog

In order to add ObjectID Indexes and allow POSM data to be passed along with ESRI SDE data, POSM GIS Database views must be registered with ESRI ArcMap/ArcCatalog. Reasons for doing this allow for faster queries into POSM’s database due to the indexed value, updaƟng records in ESRI ArcMap from POSM’s database, linking to other database CMMS systems from ESRI, and using ArcServer to host maps with POSM Data linked to them.
The basic steps how to register POSM’s database to an ESRI SDE database are as follows:
1. Add POSM’s database as a link database server to the GIS Database server (this can be skipped if POSM’s
SQL database resides on the same server as the GIS SDE Database)
2. If POSM’s views have not been created for GIS linking, they must be created first in POSM’s database
3. Create the linked server POSM views on the SDE SQL Server database for the GIS system
4. Create a user sql user account on the POSM and SDE SQL databases that has read access to POSM and
POSM SDE Views
5. In ArcCatalog register the POSM SDE Views
6. Load the new POSM SDE View into a map and create joins or XY/Route Event plots on the map
7. The map is ready to be used or published to the ArcServer

POSM’s database as a link database server to the GIS Database server

If the POSM database and the SDE database reside on separate servers, the POSM database server must be linked to the
SQL server where the ESRI SDE database resides. Using MicrosoŌ SQL Manager two servers may be linked together. Note this is not needed if the POSM SQL and ESRI SQL SDE database are on the same server.

Under Server Objects, right click on Linked Server, then select New Linked Server. This will open the new Linked Server form.

If all databases on the server need to be linked to the SDE Server SQL Server type may be used, if limiƟng the databases
Other data sources may be used and SQL Server NaƟve Client may be used to link the servers


Under the Security Tab specify a user that will be used for this link with the “Be made using this security context”. In this example the sa user was used, it is suggested to create a new user for this link to limit its security. Press OK to save the seƫngs and link the two servers.

Create POSM Views

POSM Server Views have been used to link POSM’s inspecƟon data to GIS Layers in ESRI. To make the data available in format the ESRI can join and link some of POSM’s fields are renamed. The view may link mulƟple tables together from POSM’s database to provide a single table source to the map as well. Lastly where statements provide the most recent data to the map to ensure older inspecƟon data is not shown on the map. Two of the most common views for POSM Server with GIS are a PACP inspecƟon view for Sewers, and a XY Table of observaƟons from a PACP inspecƟon. An example of a PACP view is as follows:
SELECT SpecialFieldID, SessionID, Date AS Insp_Date, ProjectName, UseOfSewer, FlowDirecƟon, AssetID, AssetSize, AssetType, AssetLocaƟon, AssetLength, StartID, EndID, LengthSurveryed, LEFT(PacpQuickStructRaƟng, 1)
AS Struct_RaƟng, PacpQuickStructRaƟng, PacpQuickStructRaƟngNum, LEFT(PacpQuickMaintRaƟng, 1) AS Maint_RaƟng, PacpQuickMaintRaƟng, PacpQuickMaintRaƟngNum, WebPathtoReportIndex, CountGroup1 AS Num_Taps,
CountGroup2 AS Num_Roots, CountGroup3 AS Num_CR_FR, CountGroup4 AS Num_Broken_Holes, CountGroup5 AS Num_Deposits, IsLatestInspecƟon, InspecƟonCount, IsImperial, CerƟficate_Number, AssetIDMatchedToGIS,
ExportedToGIS, WorkOrderNumber
FROM dbo.SpecialFields
WHERE (IsLatestInspecƟon = 1) AND (TemplateName = 'NASSCO PACP')
This view will select fields from POSM’s SpecialFields table from the PACP template that are the most recent inspecƟon. NoƟce POSM’s count groups are renamed from CountGroup1-10 to the appropriate group names, and the PACP raƟngs are also cropped to the leŌ most character to provide 0-5 Maintenance and Structure scores to the map.
To plot observaƟons on a map four tables must be used in a view.
SELECT dbo.Data.DataID, dbo.Data.Distance, dbo.FaultCodes.FaultName, dbo.FaultCodes.Code, dbo.Data.PictureLocaƟon, dbo.Data.VideoLocaƟon, dbo.Data.FaultStartPosiƟon, dbo.Data.FaultEndPosiƟon, dbo.Data.Severity,
dbo.Data.ConƟnuousDefect, dbo.Data.Comments, dbo.Data.Value_1st_Dimension, dbo.Data.Value_2nd_Dimension, dbo.Data.Value_Percent, dbo.Data.Joint, dbo.Data.Step, dbo.Data.CoordX AS X_COORD,
dbo.Data.CoordY AS Y_COORD, 'hƩp://localhost/POSM/Video/' + dbo.Session.MediaFolder + '/' + dbo.Data.PictureLocaƟon AS HTML_Pic_LocaƟon, dbo.Data.MaintWeight, dbo.Data.StructWeight, dbo.SpecialFields.AssetID,
dbo.Data.FaultStartPosiƟon * 30 AS StartClockDegrees
FROM dbo.FaultCodes INNER JOIN
dbo.Data ON dbo.FaultCodes.FaultCodeID = dbo.Data.FaultCodeID INNER JOIN dbo.Session ON dbo.Data.SessionID = dbo.Session.SessionID INNER JOIN dbo.SpecialFields ON dbo.Session.SessionID = dbo.SpecialFields.SessionID
WHERE (dbo.SpecialFields.IsLatestInspecƟon = 1) AND (dbo.Data.FaultCodeID > 3)
Linking the FaultCodes, Data, Session and SpecialFields tables, the user can create a a table of observaƟons and their posiƟons. If XY Plots are to be used its important that POSM has the manhole X and Y coordinates stored so POSM can
calculate the observaƟon posiƟons on the pipe. If a route event layer is to be used, the user must have the AssetID
from SpecialFields in the query to link the observaƟon to an asset for its distance posiƟon calculaƟon in ESRI.

Create the linked server POSM views on the SDE SQL Server database for the GIS system

If POSM’s database is not on the same SQL server as the ESRI SDE database, a linked server view must be created for each POSM view on the SDE Database engine. Because this will have one SQL database engine querying another, the queries will change for the POSM views. This type of query will be using an OPENQUERY statement to query another server inside a query.
An example of a POSM View from a linked server would be as follows for linking POSM’s HTML Webreportlocaiton. SELECT SpecialFieldID, AssetID, WebPathtoReportIndex, IsLatestInspecƟon
FROM OPENQUERY([FSD-POSM\POSM], 'SELECT [SpecialFieldID], [AssetID], [WebPathtoReportIndex], [IsLatestInspecƟon] FROM [POSM].[dbo].[SpecialFields]') AS derivedtbl_1
WHERE (IsLatestInspecƟon = 1)
This provides the unique POSM SpecialFieldsID that will become indexed in the SDE Database. The AssetID of the pipe, the POSM Report link, and if this is the most recent inspecƟon.
It is then querying into POSM’s database for this informaƟon. More fields just like the examples in the POSM view can be linked. The key is they have to be double entered into this query.
This is an example of an observaƟon xy plot for Tap obseravƟons to be linked.
SELECT DataID, FaultName, Code, Distance, TimeStamp, FaultStartPosiƟon, FaultEndPosiƟon, Value_1st_Dimension, Value_2nd_Dimension, Value_Percent, Joint, X_COORD, Y_COORD, IsLatestInspecƟon, AssetID
FROM OPENQUERY([FSD-POSM\POSM],
'SELECT DataID, FaultName, Code, Distance, TimeStamp, FaultStartPosiƟon, FaultEndPosiƟon, Value_1st_Dimension,
Value_2nd_Dimension,Value_Percent,Joint, CoordX AS X_COORD, CoordY AS Y_COORD, IsLatestInspecƟon,AssetID
FROM [POSM].[dbo].[Data] INNER JOIN
INNER JOIN
[POSM].[dbo].[FaultCodes] ON [POSM].[dbo].[Data].FaultCodeID = [POSM].[dbo].[FaultCodes].FaultCodeID
[POSM].[dbo].[Session] ON [POSM].[dbo].[Data].SessionID = [POSM].[dbo].[Session].SessionID INNER JOIN
[POSM].[dbo].[SpecialFields] ON [POSM].[dbo].[Session].SessionID = [POSM].[dbo].[SpecialFields].SessionID')
AS derivedtbl_2
WHERE (X_COORD <> 0) AND (Y_COORD <> 0) AND (IsLatestInspecƟon = 1) AND (LEFT(Code, 1) = 'T')
These views will be added to the SDE database. Now the SDE SQL database will have views that link into the POSM
database.

SQL Security Accounts

It is important that a SQL user is created on both servers that has read access to the POSM and SDE POSM Views. This allows the data to be read though the SQL servers, and published out through the ESRI Web Maps or other links.

Register the POSM SDE Views in ArcCatalog

Registering the POSM View with the SDE database sets up the Index fields and the authenƟcaƟon that will be used when viewing the inspecƟon data within the map.
Using ArcCatalog, make a connecƟon the SDE database. The two views that were made in SQL manager should now be part of the table data. Right clicking on these, click on Manager, then Register with Geodatabase.


The Register with Geodatabase form will appear. Select POSM’s SpecialFieldID for the ObjectID to be indexed. Then press OK to conƟnue.
Register all views that were created for POSM the same way.
The data from POSM is now ready to be used in a map. This can be for use in ArcMap, ArcServer or other CMMS systems linked to the SDE database.

Installing and Configuring IIS Web Server.

Check if IIS is installed.

Go to the Control Panel, click System and Security, then Administrative Tools.

If IIS is installed, you will see an entry for Internet Information Services (IIS) Manager. This will load the standalone management page for IIS.

Or

Go to Computer Management under Services and Applications in Internet Information Services

(IIS) Manager.

This will load IIS within Computer Management.


By default, the webpage will not be authorized to show the POSM Web Reports folder. You will need to grant permissions to the web folder where POSM exports its HTML reports.

Do not give Full Control or Modify to the IIS user.

Create index.htm in the POSM application folder. Use preformatted, default POSM index.htm file.

Go to Export GIS and HTML Data in POSM.

For the initial web page creation, Check Check All box and then Press buttons for 1, 2, then 3 steps.

Future exports of web reports you will use Database field: SessionsModified, Data Value is: True, = To just export newly created inspections. Also, Do Not Create HTML Index is checked for subsequent exports so that the entire landing page is not created each time, new sessions are just appended to the index.

GIS

Open SQL Management Studio

Download and Register ESRI ArcMap

My.esri.com

Adding SQL ConnecƟon from ArcMap to POSM Data

By adding an OLE DB Database connecƟon from ArcMap to POSM’s SQL database, it is possible to link pipe inspecƟons via their unique idenƟfier (Pipe / Asset ID). It is also possible to create a layer of observaƟon data from the calculated X and Y coordinates used in POSM’s database.

Create O LE D B Con n ecƟ o n

Open ArcMap and then open a map to add the POSM InspecƟon data

Right Click on Layers and Add Data

From the look in Drop down select Database ConnecƟons

Select Add OLE DB ConnecƟon

Select MicrosoŌ OLE DB Provider for SQL Server

Press next to enter the server / database informaƟon

The server name is: POSMSVR

Use Windows NT Integrated Security

The Database name is: POSM

Rename the connecƟon to POSM_SQL.odc

Click on the POSM_SQL.odc and then click Add

Select the dbo.GIS_Link and dbo.Obs_Link tables and then click Add

Right Click on the dbo.Obs_link and then click Display XY Data

Adding Calculated X and Y Coordinates from POSM to the Map

The two POSM layers should now be added as a data source

Uncheck the Warn me if resulƟng layer box and then press OK

The ObservaƟons will now display in the Table of Contents

To join a POSM GIS Link to Sewer Mains layer

Right Click on sde4sb.SDE.SewerMains

Select Joins and Relates

Select Join

Choose Link for field that will join

The table will be dbo.GIS_Link

The field in the table will be: AssetID

Select OK

The results of this will add all of the fields from the POSM GIS_Link table to the Sewer Main layer. The fields that are passed to the join may be changed in the GIS_Link view from SQL Server.

Formaƫng the Joined Data for added funcƟonality to GIS_Link

Right click on SewerMains and click ProperƟes

Under Display tab, check the hyperlinks box

Select WebPathToReportIndex and then select URL


Adding the Hyperlink field as a URL will allow POSM sessions to be opened by using the URL tool (LighƟng bolt) in the map. The report may also be found by using the display tool, opening the display for an asset and clicking on the WebPathToReportIndex

To Add Structure Score weights to Lines

Select Symbology tab

Select Categories, Unique Values

Select Field Struct_RaƟng

Press Add All Values

Change color values as desired.

Once finished the resulƟng map will display pipe colored links to pipe that have an inspecƟon report in
POSM. Individual observaƟons are on the map as points.