Build a File Geodatabase of WQMPs filtered to one StormwaterJurisdiction, joined to display-name attributes from dbo.vWaterQualityManagementPlanDetailed, in native projection EPSG:2771 (NAD83(HARN) / California zone 6, meters). Mirrors the column set the Neptune index grid shows.
-
Resolve the jurisdiction. The user gives a name; look it up case-insensitively. Confirm with the user before proceeding if there is more than one match.
SELECT sj.StormwaterJurisdictionID, o.OrganizationName
FROM dbo.StormwaterJurisdiction sj
JOIN dbo.Organization o ON sj.OrganizationID = o.OrganizationID
WHERE o.OrganizationName LIKE '%<input>%';
Capture both the integer ID and a short label (strip a leading "City of " / "County of " / "Town of ") for the output filename.
-
Pre-flight: report counts so the user can sanity-check before the export runs. Sparse coverage (lots of null geometries) is normal for some jurisdictions; flag it but continue.
SELECT COUNT(*) AS Total,
SUM(CASE WHEN b.GeometryNative IS NULL THEN 1 ELSE 0 END) AS NullGeom
FROM dbo.WaterQualityManagementPlan w
LEFT JOIN dbo.WaterQualityManagementPlanBoundary b
ON w.WaterQualityManagementPlanID = b.WaterQualityManagementPlanID
WHERE w.StormwaterJurisdictionID = <id>;
-
Create the transient export view by piping the DDL below to sqlcmd. Substitute <JURISDICTION_ID> with the integer from step 1.
Note on invocation: pass the SQL via stdin pipe (cat … | sqlcmd … or a heredoc). sqlcmd -i <path> triggers a Git Bash path-mangling bug that makes sqlcmd think the path is a credential flag.
IF OBJECT_ID('dbo.vWQMPGdbExport', 'V') IS NOT NULL DROP VIEW dbo.vWQMPGdbExport;
GO
CREATE VIEW dbo.vWQMPGdbExport AS
SELECT
v.WaterQualityManagementPlanID AS WQMPID,
v.WaterQualityManagementPlanName AS Name,
v.StormwaterJurisdictionName AS Jurisdict,
v.WaterQualityManagementPlanPriorityDisplayName AS Priority,
v.WaterQualityManagementPlanStatusDisplayName AS Status,
v.WaterQualityManagementPlanDevelopmentTypeDisplayName AS DevType,
v.WaterQualityManagementPlanLandUseDisplayName AS LandUse,
v.WaterQualityManagementPlanPermitTermDisplayName AS PermitTerm,
v.ApprovalDate AS ApprovDate,
v.DateOfConstruction AS ConstrDate,
v.HydromodificationAppliesTypeDisplayName AS HydromodCtrl,
v.HydrologicSubareaName AS HydroSubarea,
v.MaintenanceContactOrganization AS MaintOrg,
v.MaintenanceContactName AS MaintName,
LTRIM(RTRIM(
ISNULL(v.MaintenanceContactAddress1, '') +
CASE WHEN v.MaintenanceContactAddress2 IS NOT NULL AND v.MaintenanceContactAddress2 <> ''
THEN ', ' + v.MaintenanceContactAddress2 ELSE '' END +
CASE WHEN v.MaintenanceContactCity IS NOT NULL AND v.MaintenanceContactCity <> ''
THEN ', ' + v.MaintenanceContactCity ELSE '' END +
CASE WHEN v.MaintenanceContactState IS NOT NULL AND v.MaintenanceContactState <> ''
THEN ', ' + v.MaintenanceContactState ELSE '' END +
CASE WHEN v.MaintenanceContactZip IS NOT NULL AND v.MaintenanceContactZip <> ''
THEN ' ' + v.MaintenanceContactZip ELSE '' END
)) AS MaintAddr,
v.MaintenanceContactPhone AS MaintPhone,
v.TreatmentBMPCount AS InvBMPCnt,
v.QuickBMPCount AS SimpBMPCnt,
v.WaterQualityManagementPlanModelingApproachDisplayName AS ModelApp,
v.DocumentCount AS DocCount,
CAST(v.HasRequiredDocuments AS int) AS HasReqDocs,
v.RecordNumber AS RecordNo,
CAST(v.RecordedWQMPAreaInAcres AS float) AS RecAcres,
CAST(v.CalculatedWQMPAcreage AS float) AS CalcAcres,
v.AssociatedAPNs AS APNs,
v.VerificationDate AS OMVerify,
v.TrashCaptureStatusTypeDisplayName AS TCStatus,
v.TrashCaptureEffectiveness AS TCEffPct,
b.GeometryNative AS GeometryNative
FROM dbo.vWaterQualityManagementPlanDetailed v
LEFT JOIN dbo.WaterQualityManagementPlanBoundary b
ON v.WaterQualityManagementPlanID = b.WaterQualityManagementPlanID
WHERE v.StormwaterJurisdictionID = <JURISDICTION_ID>;
GO
-
Run ogr2ogr. Two MSSQL config flags are essential:
MSSQLSPATIAL_LIST_ALL_TABLES=YES — without this, the driver only sees layers registered in dbo.geometry_columns, and the transient view is not registered.MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO — bypass the metadata-table lookup so the view's geometry column is detected via sys.columns.
-nlt MULTIPOLYGON is required because the view contains a mix of Polygon and MultiPolygon rows; FileGDB layers must declare a single geometry type. (-nlt PROMOTE_TO_MULTI is not supported in this old GDAL 1.11.) Setting -nlt MULTIPOLYGON makes ogr2ogr wrap the Polygons.
export GDAL_DATA="C:/Program Files/QGIS Chugiak/share/gdal"
GDB="C:/Users/<user>/Downloads/WaterQualityManagementPlans_<ShortName>.gdb"
rm -rf "$GDB"
"C:/Program Files/QGIS Chugiak/bin/ogr2ogr.exe" -f "FileGDB" "$GDB" \
--config MSSQLSPATIAL_LIST_ALL_TABLES YES \
--config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO \
"MSSQL:server=.;database=NeptuneDB;trusted_connection=yes" \
-a_srs EPSG:2771 \
-nlt MULTIPOLYGON \
-nln "WaterQualityManagementPlans_<ShortName>" \
dbo.vWQMPGdbExport
-
Verify with ogrinfo -so and confirm Feature Count, geometry type (Multi Polygon), and that the SRS WKT ends with AUTHORITY["EPSG","2771"]. Spot-check a feature with -where "WQMPID=<some id>" to confirm a Name and APNs come through.
-
Drop the view to keep the dev DB clean:
DROP VIEW dbo.vWQMPGdbExport;
-
Report to the user: gdb path, total feature count, null-geometry count, extent, SRID. Then offer to produce a transmittal summary (template below).
