POSM GIS Append Take 2
GIS Appends are preƩy easy to setup and can be done from GeoDatabases (SQL Database in ESRI), shape files, CSV data (loaded into access).
Step 1, get external GIS data into the POSMGISData.mdb
By clicking on External Data you can link data or import data from any file source. SQL Servers, Access DBs, Excel spreadsheets, DBF files, or CSV files. In this example, I’m going to link data from an ESRI Access GeoDatabase.
I then add the SanMain and SanManhole layers.
You could choose to import the tables but then its staƟc. If you are seƫng this up for a city that is using a geodatabase or SDE SQL GeoDatabase always use Link. This way it will pull the latest data every Ɵme they update the GIS data.
If it’s a DBF, CSV or Excel spreadsheet you may want to just import it so you can manipulate it beƩer. Now its Ɵme to make the Append Query.
Click on Create Tab, click Design View, Then select Append.
You are going to be appending to the POSMGIS table. Press OK to start.
Add the SanMain table for the pipes.
You are also going to add the manhole tables twice. Select the properƟes of each and rename them to
UpMH and DwnMH.
Make a join by dragging the InventoryID (AssetID, ID etc) from the UpMH to the SanMan InvID_Up.
It’s best to start with the pipe informaƟon first and then move on to the manholes. Start with Asset ID, Asset LocaƟon (address), and other basic pipe informaƟon that is used to start out a PACP inspecƟon. Then add each of the manholes next.
Repeat for the down manhole. Note in the end users these may be named differently. If their pipe layers do no have the upstream and downstream manholes their GIS person is going to need to add them before we can conƟnue. Also make sure they have assigned Pipe IDs (AssetIds to the pipes). Try not to use FIDs these are indexed and change.
You can now start building the append.
Pull down the fields to map and select the mapping to the field in POSMGIS table.
Note you can add text fields too. For instance, select Append To: City if you put the Field: value in ‘’ you can put whatever you want in text between. For example, I put: ‘City of POSM’
It then converts it to an Expression.
You can also use funcƟons. NoƟce I converted the pipe sizes to an integer. If they have 4” in there you could use a string funcƟon replace to remove the “then convert it to an int.
I used a Round(value,1) on the pipe length to make it so it only has .0 for the pipe lengths.
You will also noƟce that my query has arrows poinƟng to the manholes. This is a called a right join to that table. What does is not repeat data that is missing when something is in one table but not another.
You can do with out them, but make
In my example it ended up looking like this.
You can also make a delete query to easily let the user delete the POSMGIS data before appendin(g it. You don’t have to but I normally delete all POSMGIS data. Then I run a Compact and repair Database
under Database tools.
This resets all the indexes to 0 and clears out old deleted data. Then I run the append.
Check the POSMGIS data aŌer appending the data make sure everything imported correctly.