ETL Processing Details

Owned by Paul McCullough (Unlicensed)
Last updated: Aug 26, 2010Version comment
3 min read

Master Address Database (MAD) Periodic Extract, Transform and Load (ETL) Steps

The San Francisco Enterprise GIS Program refreshes a set of core enterprise data sets on a regular basis for use in the Master Address Database and the SFMaps mapservice. This article describes the specific steps that must be performed to fulfill this task for a particular enterprise data set. The core datasets are moved from DPW/BSM SQLServer databases to the Enterprise GIS staging database on SFGIS01, where they are transformed and then loaded into the MAD and the SFMaps in the postgreSQL instances in the Applogic application at 3Tera.

FME Workspaces - Staging

Currently located on the \\sfgis05.sfgov.org\sfgis\fme_workspaces\dev\MAD

Parcel Data

<u>bmsql2_baselots_mstrlots_sdestg.fmw</u>

This workspace moves parcel data from DPW's BSM SQL Server instance to the SFGIS Staging SDE Server.

Source Datasets

  1. Critical - make sure on both FMEDESKTOP and FMESERVER the C:\windows\system32\drivers\etc\hosts file has entry "10.90.21.219 bsmsql2"; this allows FME to see the host server via SDE port.
  2. DTISGIS.SFGIS_Mstr_Lots - in SDE database on bsmsql2 DPW Bureau of Street-Use and Mapping, This is a view of Mstr_Lots table in Basemap_Update database (unable to make connection due to SQLServer database from FMEDESKTOP).
  3. SDE.BASELOTS_2 - in SDE database on bsmsql2 DPW Bureau of Street-Use and Mapping, this is the all lots created, both past and present.
  4. SFGIS.CITYLOTS_7380_036_040 - in SDESTG database on SFGIS01; properly drawn geometry for lots on Brotherhood Way.
  5. SFGIS.CITYLOTS_8722_010 - in SDESTG database on SFGIS01; properly drawn geometry for block 8722 lot 010.

dbo.vwAssessorBLKL0T - in AssessorDpwnload database on GISIMS, all blklots records in Assessor Parcel database.

Destination Datasets

  1. sfgis.MSTR_LOTS- sdestg database, DT.
  1. sfgis.baselots_2 - sdestg database, DT.
  1. sfgis.AssessorBLKLOT - sdestg, DT.

<u>sdestg_parcel_staging.fmw</u>

This workspace transforms parcel data on the SFGIS Staging SDE Server in preparation for load to MAD database on 3Tera/Applogic.

Source Datasets

  1. sfgis.MSTR_LOTS- sdestg database, DT.
  1. sfgis.baselots_2 - sdestg database, DT.
  1. sfgis.AssessorBLKLOT - sdestg, DT.

Destination Datasets

  1. SFGIS.PARCELS_STAGING
  2. sfgis.Baselots_Not_in_MstrLots

Street Data:

<u>bmsql2_basestclines_mstr_sdestg.fmw</u>

This workspace moves street data from DPW's BSM SQL Server instance to the SFGIS Staging SDE Server.

Source Datasets

  1. Critical - make sure on both FMEDESKTOP and FMESERVER the C:\windows\system32\drivers\etc\hosts file has entry "10.90.21.219 bsmsql2"; this allows FME to see the host server via SDE port.
  2. DTISGIS.SFGIS_VW_SFMAD3_STREETNAMES_ALL - in SDE database on bsmsql2 DPW Bureau of Street-Use and Mapping, This is a view of the VW_SFMAD3_STREETNAMES_ALL view in BSMCoreData database (unable to make connection due to SQLServer database from FMEDESKTOP).
  3. SDE.BASECLINES- in SDE database on bsmsql2 DPW Bureau of Street-Use and Mapping, this is the all street segments created, both past and present.
  4. DTISGIS.SFGIS_ALL_STCLINES_MSTR - This is a view of teh ALL_STCLINES_MSTR table in BasemapUpdate database (unable to make connection due to SQLServer database from FMEDESKTOP).

Destination Datasets

  1. SFGIS.ALL_STCLINES_MSTR - sdestg database, DT.
  1. SFGIS.BASECLINES - sdestg database, DT.
  1. SFGIS.VW_SFMAD3_STREETNAMES_ALL, sdestg database, DT.

<u>sdestg_streetsegs_names_staging.fmw</u>

This workspace transforms street data in the SFGIS Staging SDE Server, in preparation for load into MAD db on 3Tera/Applogic.

Source Datasets

  1. SFGIS.ALL_STCLINES_MSTR - sdestg database, DT.
  1. SFGIS.BASECLINES - sdestg database, DT.
  1. SFGIS.VW_SFMAD3_STREETNAMES_ALL, sdestg database, DT.

Destination Datasets

  1. SFGIS.street_segments_staging - sdestg database, DT.
  1. SFGIS.baseclines_not_in_all_stclines_mstr- sdestg database, DT.
  1. SFGIS.streetnames_staging, sdestg database, DT.

  Workspace: load from SFGIS SDE Staging to MAD db on 3Tera/Applogic

MAD_sdestg_2postgisa_3tera.fmw

Source Datasets

  1. SFGIS.PARCELS_STAGING
  2. sfgis.street_segments_staging
  3. SFGIS.STREETNAMES_STAGING

Destination Datasets

  1. public.parcels_staging, MAD
  2. public.street_segments_staging, MAD
  3. public.streetnames_staging, MAD