Name: Master Address Points

Display Field: TXT_STNAME

Type: Feature Layer

Geometry Type: esriGeometryPoint

Description: Mecklenburg County creates and maintains the Master Address Table. The County provides a text file of the data each evening. This source data is processed within a City-managed ETL process that maps the data into destination fields within MasterAddress_pt. The field names within this table and the analogous fields names within the County's source data are listed below. The ETL process has several geoenrichment steps which update multiple fields using SQL Spatial statements. These statements are listed below.Field NameCounty Source Data Field NameNUM_STREETADDRNUMTXT_STSUFFADDRNUMSUFNUM_ST_CODECOUNTYSTCODEDTE_ADDTEMcreated_dateID_ADD_BYcreated_userDTE_UPDTEMlast_edited_dateID_UPD_BYlast_edited_userTXT_LOTLOTNOCDE_USEMECKUSETXT_CDEUSEMECKUSE_DESCCDE_JURISMUNICIPALITY_CODETXT_JURISMUNICIPALITY_DESCNME_PO_CITYPLACENAMETXT_STDIRPREADDRNUMNUM_ADDRSITEADDIDTXT_STYPE4STANDTYPECDE_STATUSSTATUS_CODETXT_STNAMESTREETNAMETXT_STYPE2STREETTYPENUM_SUBDIVSUBDIVISIONID_PARCELTAXPIDTXT_UNITUNITIDNUM_XCOORDXCOORDNUM_YCOORDYCOORDCDE_ZIP1ZIPCODEDeprecated fields that are no longer updated:ID_GISKEYTXT_MODELID_NCPININD_FOOTPTIND_OCCUPIND_GIS_OVIND_PUBLICNUM_LINKNUM_UNITSTXT_BLOCKCDE_ZIP2The following SQL Spatial statements are used to populate the corresponding fields:--Basin updateupdate MASTERADDRESS_LD SET BASIN = ISNULL(D.NAME,'') FROM MASTERADDRESS_LD M JOIN DRAINAGESPECIALISTS_PY D ON M.Shape.STIntersects(D.Shape)=1 --Police Division updateupdate MASTERADDRESS_LD SET POLICE_DIVISION = ISNULL(D.DIVISION,'') FROM MASTERADDRESS_LD M JOIN POLICEDIVISIONS_PY D ON M.Shape.STIntersects(D.Shape)=1 --FIPS updateupdate MASTERADDRESS_LD SET FIPS = ISNULL(D.GEOID10,'') FROMMASTERADDRESS_LD M JOIN CENSUSTRACTS2010_PY D ON M.Shape.STIntersects(D.Shape)=1 --Council District updateupdate MASTERADDRESS_LD SET COUNCIL_DISTRICT = ISNULL(D.DISTRICT,0) FROM MASTERADDRESS_LD M JOIN COUNCILDISTRICTs_PY D ON M.Shape.STIntersects(D.Shape)=1--NPA (listed as NSA) updateupdate MASTERADDRESS_LD SET NSA = ISNULL(D.ID,0) FROM MASTERADDRESS_LD M JOIN NPA_py D ON M.Shape.STIntersects(D.Shape)=1--Sphere Name updateupdate MASTERADDRESS_LD SET SPHERE_NAME = D.NAME FROMMASTERADDRESS_LD M JOIN SPHEREOFINFLUENCE2016_PY D ON M.Shape.STIntersects(D.Shape)=1--Wrecker Zone updateupdate MASTERADDRESS_LD SET WRECKER_ZONE = ISNULL(D.DIVISION,0) FROMMASTERADDRESS_LD M JOIN WRECKERZONES_PY D ON M.Shape.STIntersects(D.Shape)=1 --E911 updateUPDATE MASTERADDRESS_LDSET E911=ISNULL(R.E911,0)FROM MASTERADDRESS_LD M INNER JOIN (SELECT DISTINCT [NAME], NME_JURIS FROM JURISDICTIONS2016_py) J ON M.TXT_JURIS=J.NAMEINNER JOIN ROAD_ln R ON UPPER(R.WHOLESTNAME) = UPPER(CASE WHEN M.TXT_STDIR'' THEN M.TXT_STDIR+' ' ELSE '' END + M.TXT_STNAME + CASE WHEN M.TXT_STYPE2'' THEN ' ' + M.TXT_STYPE2 ELSE '' END + CASE WHEN M.TXT_STSUFF'' THEN ' ' + M.TXT_STSUFF ELSE '' END ) WHERE ((M.NUM_STREET BETWEEN R.LL_ADD AND R.UL_ADD) OR (M.NUM_STREET BETWEEN R.LR_ADD AND R.UR_ADD))AND (J.NME_JURIS=R.L_JURIS OR J.NME_JURIS=R.R_JURIS) --FULL ADDRESS updateUPDATE DBO.MASTERADDRESS_LDSET FULL_ADDRESS =(case when ([NUM_STREET] is null) then '' else (convert(varchar(20),[NUM_STREET]) + ' ') end + case when ([TXT_STDIR] = '') then '' else ([TXT_STDIR] + ' ') end + [TXT_STNAME] + case when ([TXT_STYPE2] = '') then '' else (' ' + [TXT_STYPE2]) end+ case when ([TXT_STSUFF] = '') then '' else (' ' + [TXT_STSUFF]) end + case when ([TXT_UNIT] = '') then '' else (' #' + [TXT_UNIT]) end ) WHERE ISNULL(FULL_ADDRESS,'') (case when ([NUM_STREET] is null) then '' else (convert(varchar(20),[NUM_STREET]) + ' ') end + case when ([TXT_STDIR] = '') then '' else ([TXT_STDIR] + ' ') end+ [TXT_STNAME] + case when ([TXT_STYPE2] = '') then '' else (' ' + [TXT_STYPE2]) end + case when ([TXT_STSUFF] = '') then '' else (' ' + [TXT_STSUFF]) end + case when ([TXT_UNIT] = '') then '' else (' #' + [TXT_UNIT]) end ) --TAX_PID AND GIS_PID updateUPDATE DBO.MasterAddress_LD SET TAX_PID = null, GIS_PID = null; UPDATE DBO.MasterAddress_LD SET TAX_PID = T.id_pid FROM MasterAddress_LD M inner join CAMAParcelInfo_tb T on M.ID_PARCEL = T.id_Pid; update MasterAddress_LD set GIS_PID = T.id_common_pid FROM MasterAddress_LD M inner join CAMAParcelInfo_tb T on M.ID_PARCEL = T.id_Common_Pid WHERE GIS_PID is null;update MasterAddress_LD set GIS_PID = P.PID FROM MasterAddress_LD Minner join Parcel_py P on M.SHAPE.STIntersects(P.SHAPE)=1;

Copyright Text:

Default Visibility: true

MaxRecordCount: 2000

Supported Query Formats: JSON, geoJSON, PBF

Min Scale: 8000

Max Scale: 0

Supports Advanced Queries: true

Supports Statistics: true

Has Labels: false

Can Modify Layer: true

Can Scale Symbols: false

Use Standardized Queries: true

Supports Datum Transformation: true

Extent:

XMin: 1384744.9999572188
YMin: 463459.00011231005
XMax: 1536560.000132382
YMax: 648927.9999084771
Spatial Reference: 102719  (2264)

Drawing Info:

Renderer:
Simple Renderer:
Symbol:
Style: esriSMSCircle
Color: [153, 56, 0, 255]
Size: 4.0
Angle: 0.0
XOffset: 0
YOffset: 0
Outline:
[0, 0, 0, 255] Width: 1
Label: N/A
Description: N/A
Transparency: 0
Labeling Info:

Advanced Query Capabilities:

Supports Statistics: true
Supports OrderBy: true
Supports Distinct: true
Supports Pagination: true
Supports TrueCurve: true
Supports Returning Query Extent: true
Supports Query With Distance: true
Supports Sql Expression: true
Supports Query With ResultType: false
Supports Returning Geometry Centroid: false
Supports Binning LOD: false
Supports Query With LOD Spatial Reference: false
Supports Percentile Statistics: true
Supports Having Clause: true
Supports Count Distinct: true
Supports Time Relation: true
Supports Sql Format: false
Supports Query Analytic: true
Supports Query With Current User: true

HasZ: false

HasM: false

Has Attachments: false

HTML Popup Type: esriServerHTMLPopupTypeAsHTMLText

Type ID Field: null

Fields:

• OBJECTID ( type: esriFieldTypeOID, alias: OBJECTID )
• NUM_ADDR ( type: esriFieldTypeString, alias: AddressID, length: 20 )
• ID_GISKEY ( type: esriFieldTypeString, alias: GISKey, length: 14 )
• NUM_X_COOR ( type: esriFieldTypeDouble, alias: XCoord )
• NUM_Y_COOR ( type: esriFieldTypeDouble, alias: YCoord )
• NUM_STREET ( type: esriFieldTypeInteger, alias: HouseNum )
• TXT_STDIR ( type: esriFieldTypeString, alias: Direction, length: 2 )
• TXT_STNAME ( type: esriFieldTypeString, alias: StreetName, length: 50 )
• TXT_STYPE2 ( type: esriFieldTypeString, alias: StreetType, length: 2 )
• TXT_STYPE4 ( type: esriFieldTypeString, alias: StreetType4, length: 5 )
• TXT_STSUFF ( type: esriFieldTypeString, alias: Suffix, length: 8 )
• TXT_UNIT ( type: esriFieldTypeString, alias: Unit, length: 10 )
• CDE_JURIS ( type: esriFieldTypeString, alias: JurisCode, length: 2 )
• TXT_JURIS ( type: esriFieldTypeString, alias: Jurisdiction, length: 30 )
• CDE_USE ( type: esriFieldTypeString, alias: LanduseCode, length: 3 )
• TXT_CDEUSE ( type: esriFieldTypeString, alias: Landuse, length: 30 )
• TXT_MODEL ( type: esriFieldTypeString, alias: TaxModel, length: 30 )
• NUM_ST_COD ( type: esriFieldTypeDouble, alias: StreetCode )
• ID_PARCEL ( type: esriFieldTypeString, alias: ParcelID, length: 9 )
• ID_NCPIN ( type: esriFieldTypeString, alias: NCPIN, length: 10 )
• IND_FOOTPT ( type: esriFieldTypeString, alias: FootPrint, length: 1 )
• IND_OCCUP ( type: esriFieldTypeString, alias: Occupancy, length: 1 )
• ID_ADD_BY ( type: esriFieldTypeString, alias: AddedBy, length: 50 )
• DTE_ADDTEM ( type: esriFieldTypeDate, alias: DateAdded, length: 8 )
• ID_UPD_BY ( type: esriFieldTypeString, alias: UpdatedBy, length: 50 )
• DTE_UPDTEM ( type: esriFieldTypeDate, alias: DateUpdated, length: 8 )
• CDE_STATUS ( type: esriFieldTypeString, alias: StatusCode, length: 1 )
• IND_GIS_OV ( type: esriFieldTypeString, alias: GISOveride, length: 1 )
• IND_PUBLIC ( type: esriFieldTypeString, alias: PublicOwner, length: 1 )
• NUM_LINK ( type: esriFieldTypeString, alias: LinkedActiveID, length: 10 )
• NUM_SUBDIV ( type: esriFieldTypeString, alias: Subdivison, length: 10 )
• NUM_UNITS ( type: esriFieldTypeString, alias: NumOfUnits, length: 5 )
• TXT_BLOCK ( type: esriFieldTypeString, alias: BlockNumber, length: 3 )
• TXT_LOT ( type: esriFieldTypeString, alias: LotNumber, length: 3 )
• NME_PO_CIT ( type: esriFieldTypeString, alias: PostalCity, length: 30 )
• CDE_ZIP1 ( type: esriFieldTypeString, alias: Zipcode, length: 10 )
• CDE_ZIP2 ( type: esriFieldTypeString, alias: Zipcode2, length: 5 )
• LONGITUDE ( type: esriFieldTypeDouble, alias: Longitude )
• LATITUDE ( type: esriFieldTypeDouble, alias: Latitude )
• BASIN ( type: esriFieldTypeString, alias: Basin, length: 50 )
• POLICE_DIVISION ( type: esriFieldTypeString, alias: PoliceDivsion, length: 4 )
• FIPS ( type: esriFieldTypeString, alias: CensusFIPS, length: 16 )
• COUNCIL_DISTRICT ( type: esriFieldTypeSmallInteger, alias: CouncilDistrict )
• NSA ( type: esriFieldTypeSmallInteger, alias: NSA )
• SPHERE_NAME ( type: esriFieldTypeString, alias: SphereName, length: 30 )
• WRECKER_ZONE ( type: esriFieldTypeSmallInteger, alias: WreckerZone )
• E911 ( type: esriFieldTypeInteger, alias: E911 )
• SHAPE ( type: esriFieldTypeGeometry, alias: Shape )
• FULL_ADDRESS ( type: esriFieldTypeString, alias: Full Address, length: 60 )
• TAX_PID ( type: esriFieldTypeString, alias: TAX_PID, length: 9 )
• GIS_PID ( type: esriFieldTypeString, alias: GIS_PID, length: 9 )

Supported Operations:   Query   Query Attachments   Query Analytic   Generate Renderer   Return Updates

  Iteminfo   Thumbnail   Metadata