|
ArcCatalog VBA Script for generating four relational tables that are used to describe the file-based GIS vector data products available from SGID ftp download site. The four tables are: - sgidlayer: a table of the feature classes on the SGID SDE database server
- sgidcategory: a table of the feature datasets on the SGID SDE database server
- sgidextent: a table of geographic extents for which geographic data is available (clip extents + statewide)
- sgidproduct: a table of the file-based products on the ftp site and their file size...a product is a unique combination of category, layer, file format, and extent
The tables generated by this script are loaded into the same mysql database that contains the content and structure of the joomla!-based gis.utah.gov portal.
VBA Code:
Public Sub makeDownloadTables()
'LOAD DATA LOCAL INFILE "C:/Documents and Settings/Dennis/Desktop/menagerie/pet.txt" INTO TABLE PET;
Open "C:\temp\sgidtest\sgidcategory.txt" For Output As #1
Open "C:\temp\sgidtest\sgidlayer.txt" For Output As #2
Open "C:\temp\sgidtest\sgidproduct.txt" For Output As #3
Open "C:\temp\sgidtest\sgidextent.txt" For Output As #4
Dim pCofcursor As IFeatureCursor
Dim pcofeature As IFeature
Dim countyName As String
Dim ftpPath As String
ftpPath = "J:\ftp\pub"
'ftpPath = "ftp://ftp.agrc.utah.gov"
Call openSDEWS
Set m_CoClipFC = m_pSDEFWS.OpenFeatureClass("SGID.U024.CountyBoundaries")
Dim extentArray(29) As String
extentArray(0) = "statewide"
Dim pTablesort As ITableSort
Set pTablesort = New TableSort
Set pTablesort.Table = m_CoClipFC
pTablesort.Fields = "NAME"
pTablesort.Sort Nothing
Set pCofcursor = pTablesort.Rows
Set pcofeature = pCofcursor.NextFeature
Dim extentCount As Integer
extentCount = 1
Do Until pcofeature Is Nothing
countyName = UCase(Replace(pcofeature.Value(m_CoClipFC.FindField("NAME")), " ", ""))
extentArray(extentCount) = countyName
extentCount = extentCount + 1
Set pcofeature = pCofcursor.NextFeature
Loop
Dim catID As Long
Dim layID As Long
Dim prodID As Long
Dim extID As Long
'Print #1, "CATID" & Chr(9) & "CATFDS" & Chr(9) & "CATNAME"
'Print #2, "LAYID" & Chr(9) & "CATID" & Chr(9) & "LAYNAME" & Chr(9) & "LAYSCALE"
'Print #3, "PRODID" & Chr(9) & "LAYID" & Chr(9) & "EXTID" & Chr(9) & "PRODTYPE" & Chr(9) & "PRODSIZE"
'Print #4, "EXTID" & Chr(9) & "EXTNAME"
For extID = 0 To 29
Print #4, CInt(extID) & Chr(9) & extentArray(extID)
Next extID
Dim pGxApp As IGxApplication
Dim pSelGxObject As IGxObject
Dim pGxSel As IGxSelection
Dim pEnumGxObjSel As IEnumGxObject
Dim catName As String
Dim scaleName As String
Dim layName As String
Set pGxApp = Application
Set pGxSel = pGxApp.Selection
Set pEnumGxObjSel = pGxSel.SelectedObjects
pEnumGxObjSel.Reset
Set pSelGxObject = pEnumGxObjSel.Next
Dim fccnt As Long
Do Until pSelGxObject Is Nothing
If pSelGxObject.Category = "SDE Feature Class" Then
'fccnt = fccnt + 1
'Debug.Print " .. " & pSelGxObject.BaseName & " " & fccnt
'Print #2, " .. " & pSelGxObject.BaseName & " " & fccnt
ElseIf pSelGxObject.Category = "SDE Feature Dataset" Then
catTrueName = Right(pSelGxObject.BaseName, _
Len(pSelGxObject.BaseName) - InStrRev(pSelGxObject.BaseName, "."))
catName = Replace(catTrueName, "Archive", "")
Debug.Print catName
Print #1, CStr(catID) & Chr(9) & pSelGxObject.BaseName & Chr(9) & catName '(FDS)
Dim pGxObjContainer As IGxObjectContainer
Dim pEnumGxObj As IEnumGxObject
Dim pCurrGxObject As IGxObject
Dim fileSize As String
Set pGxObjContainer = pSelGxObject
If pGxObjContainer.HasChildren Then
Set pEnumGxObj = pGxObjContainer.Children
If Not pEnumGxObj Is Nothing Then
Set pCurrGxObject = pEnumGxObj.Next
Do Until pCurrGxObject Is Nothing
If pCurrGxObject.Category = "SDE Feature Class" Then
Debug.Print ".." & pCurrGxObject.BaseName
Dim pCurrGxDataset As IGxDataset
Dim pCurrSDEFC As IFeatureClass
Dim pCurrSDEDS As IDataset
Set pCurrGxDataset = pCurrGxObject
Set pCurrSDEDS = pCurrGxDataset.Dataset
Set pCurrSDEFC = pCurrSDEDS
Print #2, CStr(layID) & Chr(9) & CStr(catID) & Chr(9) & Right(pCurrGxObject.BaseName, _
Len(pCurrGxObject.BaseName) - InStrRev(pCurrGxObject.BaseName, ".")) & _
Chr(9) & Mid(pCurrGxObject.BaseName, 6, 4) & _
Chr(9) & pCurrSDEFC.ShapeType
For extID = 0 To 29
If extID = 0 Then
'SHP
fileSize = checkFileSize(ftpPath & "/SGID_Vector/" & catTrueName & "/" & Replace(pCurrGxObject.BaseName, ".", "_") _
& "/" & extentArray(extID) & "/shapefile/" & Replace(pCurrGxObject.BaseName, ".", "_") & ".zip")
If fileSize <> "" Then
Print #3, CStr(prodID) & Chr(9) & CStr(layID) & Chr(9) & CStr(extID) & Chr(9) & "SHP" & Chr(9) & fileSize
prodID = prodID + 1
End If
'GDB
fileSize = checkFileSize(ftpPath & "/SGID_Vector/" & catTrueName & "/" & Replace(pCurrGxObject.BaseName, ".", "_") _
& "/" & extentArray(extID) & "/geodatabase/" & Replace(pCurrGxObject.BaseName, ".", "_") & ".zip")
If fileSize <> "" Then
Print #3, CStr(prodID) & Chr(9) & CStr(layID) & Chr(9) & CStr(extID) & Chr(9) & "GDB" & Chr(9) & fileSize
prodID = prodID + 1
End If
ElseIf extID > 0 And extID < 30 Then
'SHP
fileSize = checkFileSize(ftpPath & "/SGID_Vector/" & catTrueName & "/" & Replace(pCurrGxObject.BaseName, ".", "_") _
& "/county/" & extentArray(extID) & "/shapefile/" & Replace(pCurrGxObject.BaseName, ".", "_") & ".zip")
If fileSize <> "" Then
Print #3, CStr(prodID) & Chr(9) & CStr(layID) & Chr(9) & CStr(extID) & Chr(9) & "SHP" & Chr(9) & fileSize
prodID = prodID + 1
End If
'GDB
fileSize = checkFileSize(ftpPath & "/SGID_Vector/" & catTrueName & "/" & Replace(pCurrGxObject.BaseName, ".", "_") _
& "/county/" & extentArray(extID) & "/geodatabase/" & Replace(pCurrGxObject.BaseName, ".", "_") & ".zip")
If fileSize <> "" Then
Print #3, CStr(prodID) & Chr(9) & CStr(layID) & Chr(9) & CStr(extID) & Chr(9) & "GDB" & Chr(9) & fileSize
prodID = prodID + 1
End If
End If
Next extID
End If
layID = layID + 1
Set pCurrGxObject = pEnumGxObj.Next
Loop
End If
End If
catID = catID + 1
End If
Set pSelGxObject = pEnumGxObjSel.Next
Loop
Close #1
Close #2
Close #3
Close #4
End Sub
Public Function checkFileSize(inFilePath As String) As String
Dim fso
Dim f
Dim fileSize As Long
'Debug.Print checkFileSize
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.fileexists(inFilePath) Then
Set f = fso.GetFile(inFilePath)
fileSize = f.size / 1000
If (fileSize < 1) Then
checkFileSize = "<1K"
End If
If (fileSize < 1000) Then
checkFileSize = CStr(CInt(fileSize)) + "K"
End If
If ((fileSize >= 1000) And (fileSize < 1000000)) Then
checkFileSize = CStr(CLng(fileSize / 1000)) + "MB"
End If
If (fileSize >= 1000000) Then
checkFileSize = CStr(CLng(fileSize / 1000000)) + "GB"
End If
End If
End Function |
