Feedback for GDB and SHP

[AmandaDoyle]

Feedback for GDB

• Remove geom column
• Capitalization of fields
• the Allow NULL values needs to be set to No for: -- Borough -- Block -- Lot
• ZipCode, Numbldgs, unitsres, unitstotal, borocode, condono are short data types, when it should be a long data types but does this matter? Info on ArcGIS field types is here The greatest number for each of these fields is less than 32,767

Note:

• The precision and scale needs are set to 38, and 8 for fields that are double precision in the layer files, but this is not reflected in the gdb layer where precision and scale are set to 0

[SPTKL]

• I actually made a decision to change ZipCode, Numbldgs, unitsres, unitstotal, borocode, condono to short (smallint) because it doesn't make sense for them to be integers, since they are very small.
• I can set numeric precision and scale in postgres, I just need a field by field precision scale lookup table

you can find postgres -> esri datatype look up translation here https://doc.arcgis.com/en/insights/latest/get-started/supported-types-from-databases.htm

[SPTKL]

• [x] geom column removed
• [x] Capitalization of fields
• [x] set not null constraints
• [x] ZipCode, Numbldgs, unitsres, unitstotal, borocode, condono to integers

[SPTKL]

should I also add Shape__Area and Shape__Length ?

[AmandaDoyle]

Feedback on .shp output Comparing to clipped 20v3 shapefile that I downloaded from Bytes to 20v4 clipped shapefile I downloaded from GitHub

• Field names should be proper case and not all upper case
• BLOCK, ZIPCODE, LOTAREA, BLDGAREA, COMAREA, RESAREA, OFFICEAREA, RETAILAREA, GARAGEAREA, STRGEAREA, FACTRYAREA, OTHERAREA, NUMBLDGS, NUMFLOORS, UNITSRES, UNITSTOTAL, BOROCODE, CONDONO, XCOORD, YCOORD, fields should be long and not double

[AmandaDoyle]

should I also add Shape__Area and Shape__Length ?

I think so. From my experience Esri always magically added these fields, but if you can add them and if they're consistent with Esri's calculation lets include them

[AmandaDoyle]

@SPTKL LS looking over PLUTO and found some schema issues. I didn't see these before so I don't know what changed block, ZIP , and numbldgs are doubles and they should be longs; lot and CD are longs and they should be shorts

Also, the FGD should also include the table of unmapped lots.

[SPTKL]

it seems the metadata for the feature server and pluto metadata is out of sync https://www1.nyc.gov/assets/planning/download/pdf/data-maps/open-data/pluto_datadictionary.pdf?v=20v3

[SPTKL]

~~not sure if I can add unmapped lots to the FGDB, need to look into that ~~

FGD should also include the table of unmapped lots.

addressed

block, ZIP , and numbldgs are doubles and they should be longs; lot and CD are longs and they should be shorts

changed field width according to pluto metadata

[AmandaDoyle]

There are no short field types in the unlipped FGDB version. The schema is incorrect when compared to version AF has created in the past

[SPTKL]

SHP from Bytes

field | datatype | width | precision | filetype | source -- | -- | -- | -- | -- | -- Borough | String | 2 | 0 | shp | bytes Block | Integer64 | 10 | 0 | shp | bytes Lot | Integer | 5 | 0 | shp | bytes CD | Integer | 5 | 0 | shp | bytes CT2010 | String | 7 | 0 | shp | bytes CB2010 | String | 5 | 0 | shp | bytes SchoolDist | String | 3 | 0 | shp | bytes Council | Integer | 5 | 0 | shp | bytes ZipCode | Integer64 | 10 | 0 | shp | bytes FireComp | String | 4 | 0 | shp | bytes PolicePrct | Integer | 5 | 0 | shp | bytes HealthCent | Integer | 5 | 0 | shp | bytes HealthArea | Integer | 5 | 0 | shp | bytes Sanitboro | String | 2 | 0 | shp | bytes SanitDistr | String | 2 | 0 | shp | bytes SanitSub | String | 2 | 0 | shp | bytes Address | String | 39 | 0 | shp | bytes ZoneDist1 | String | 9 | 0 | shp | bytes ZoneDist2 | String | 9 | 0 | shp | bytes ZoneDist3 | String | 9 | 0 | shp | bytes ZoneDist4 | String | 9 | 0 | shp | bytes Overlay1 | String | 4 | 0 | shp | bytes Overlay2 | String | 4 | 0 | shp | bytes SPDist1 | String | 12 | 0 | shp | bytes SPDist2 | String | 12 | 0 | shp | bytes SPDist3 | String | 12 | 0 | shp | bytes LtdHeight | String | 5 | 0 | shp | bytes SplitZone | String | 1 | 0 | shp | bytes BldgClass | String | 2 | 0 | shp | bytes LandUse | String | 2 | 0 | shp | bytes Easements | Integer | 5 | 0 | shp | bytes OwnerType | String | 1 | 0 | shp | bytes OwnerName | String | 85 | 0 | shp | bytes LotArea | Integer64 | 10 | 0 | shp | bytes BldgArea | Integer64 | 10 | 0 | shp | bytes ComArea | Integer64 | 10 | 0 | shp | bytes ResArea | Integer64 | 10 | 0 | shp | bytes OfficeArea | Integer64 | 10 | 0 | shp | bytes RetailArea | Integer64 | 10 | 0 | shp | bytes GarageArea | Integer64 | 10 | 0 | shp | bytes StrgeArea | Integer64 | 10 | 0 | shp | bytes FactryArea | Integer64 | 10 | 0 | shp | bytes OtherArea | Integer64 | 10 | 0 | shp | bytes AreaSource | String | 1 | 0 | shp | bytes NumBldgs | Integer64 | 10 | 0 | shp | bytes NumFloors | Real | 19 | 11 | shp | bytes UnitsRes | Integer64 | 10 | 0 | shp | bytes UnitsTotal | Integer64 | 10 | 0 | shp | bytes LotFront | Real | 19 | 11 | shp | bytes LotDepth | Real | 19 | 11 | shp | bytes BldgFront | Real | 19 | 11 | shp | bytes BldgDepth | Real | 19 | 11 | shp | bytes Ext | String | 2 | 0 | shp | bytes ProxCode | String | 1 | 0 | shp | bytes IrrLotCode | String | 1 | 0 | shp | bytes LotType | String | 1 | 0 | shp | bytes BsmtCode | String | 1 | 0 | shp | bytes AssessLand | Real | 19 | 11 | shp | bytes AssessTot | Real | 19 | 11 | shp | bytes ExemptTot | Real | 19 | 11 | shp | bytes YearBuilt | Integer | 5 | 0 | shp | bytes YearAlter1 | Integer | 5 | 0 | shp | bytes YearAlter2 | Integer | 5 | 0 | shp | bytes HistDist | String | 66 | 0 | shp | bytes Landmark | String | 150 | 0 | shp | bytes BuiltFAR | Real | 19 | 11 | shp | bytes ResidFAR | Real | 19 | 11 | shp | bytes CommFAR | Real | 19 | 11 | shp | bytes FacilFAR | Real | 19 | 11 | shp | bytes BoroCode | Integer64 | 10 | 0 | shp | bytes BBL | Real | 19 | 11 | shp | bytes CondoNo | Integer64 | 10 | 0 | shp | bytes Tract2010 | String | 7 | 0 | shp | bytes XCoord | Integer64 | 10 | 0 | shp | bytes YCoord | Integer64 | 10 | 0 | shp | bytes ZoneMap | String | 3 | 0 | shp | bytes ZMCode | String | 1 | 0 | shp | bytes Sanborn | String | 8 | 0 | shp | bytes TaxMap | String | 5 | 0 | shp | bytes EDesigNum | String | 5 | 0 | shp | bytes APPBBL | Real | 19 | 11 | shp | bytes APPDate | String | 10 | 0 | shp | bytes PLUTOMapID | String | 1 | 0 | shp | bytes FIRM07_FLA | String | 1 | 0 | shp | bytes PFIRM15_FL | String | 1 | 0 | shp | bytes Version | String | 6 | 0 | shp | bytes DCPEdited | String | 3 | 0 | shp | bytes Latitude | Real | 19 | 11 | shp | bytes Longitude | Real | 19 | 11 | shp | bytes Notes | String | 20 | 0 | shp | bytes Shape_Leng | Real | 19 | 11 | shp | bytes Shape_Area | Real | 19 | 11 | shp | bytes

GDB from Bytes

field | datatype | width | precision | filetype | source -- | -- | -- | -- | -- | -- Borough | String | 2 | 0 | fgdb | bytes Block | Integer | 0 | 0 | fgdb | bytes Lot | Integer(Int16) | 0 | 0 | fgdb | bytes CD | Integer(Int16) | 0 | 0 | fgdb | bytes CT2010 | String | 7 | 0 | fgdb | bytes CB2010 | String | 5 | 0 | fgdb | bytes SchoolDist | String | 3 | 0 | fgdb | bytes Council | Integer(Int16) | 0 | 0 | fgdb | bytes ZipCode | Integer | 0 | 0 | fgdb | bytes FireComp | String | 4 | 0 | fgdb | bytes PolicePrct | Integer(Int16) | 0 | 0 | fgdb | bytes HealthCenterDistrict | Integer(Int16) | 0 | 0 | fgdb | bytes HealthArea | Integer(Int16) | 0 | 0 | fgdb | bytes Sanitboro | String | 2 | 0 | fgdb | bytes SanitDistrict | String | 2 | 0 | fgdb | bytes SanitSub | String | 2 | 0 | fgdb | bytes Address | String | 39 | 0 | fgdb | bytes ZoneDist1 | String | 9 | 0 | fgdb | bytes ZoneDist2 | String | 9 | 0 | fgdb | bytes ZoneDist3 | String | 9 | 0 | fgdb | bytes ZoneDist4 | String | 9 | 0 | fgdb | bytes Overlay1 | String | 4 | 0 | fgdb | bytes Overlay2 | String | 4 | 0 | fgdb | bytes SPDist1 | String | 12 | 0 | fgdb | bytes SPDist2 | String | 12 | 0 | fgdb | bytes SPDist3 | String | 12 | 0 | fgdb | bytes LtdHeight | String | 5 | 0 | fgdb | bytes SplitZone | String | 1 | 0 | fgdb | bytes BldgClass | String | 2 | 0 | fgdb | bytes LandUse | String | 2 | 0 | fgdb | bytes Easements | Integer(Int16) | 0 | 0 | fgdb | bytes OwnerType | String | 1 | 0 | fgdb | bytes OwnerName | String | 85 | 0 | fgdb | bytes LotArea | Integer | 0 | 0 | fgdb | bytes BldgArea | Integer | 0 | 0 | fgdb | bytes ComArea | Integer | 0 | 0 | fgdb | bytes ResArea | Integer | 0 | 0 | fgdb | bytes OfficeArea | Integer | 0 | 0 | fgdb | bytes RetailArea | Integer | 0 | 0 | fgdb | bytes GarageArea | Integer | 0 | 0 | fgdb | bytes StrgeArea | Integer | 0 | 0 | fgdb | bytes FactryArea | Integer | 0 | 0 | fgdb | bytes OtherArea | Integer | 0 | 0 | fgdb | bytes AreaSource | String | 1 | 0 | fgdb | bytes NumBldgs | Integer | 0 | 0 | fgdb | bytes NumFloors | Real | 0 | 0 | fgdb | bytes UnitsRes | Integer | 0 | 0 | fgdb | bytes UnitsTotal | Integer | 0 | 0 | fgdb | bytes LotFront | Real | 0 | 0 | fgdb | bytes LotDepth | Real | 0 | 0 | fgdb | bytes BldgFront | Real | 0 | 0 | fgdb | bytes BldgDepth | Real | 0 | 0 | fgdb | bytes Ext | String | 2 | 0 | fgdb | bytes ProxCode | String | 1 | 0 | fgdb | bytes IrrLotCode | String | 1 | 0 | fgdb | bytes LotType | String | 1 | 0 | fgdb | bytes BsmtCode | String | 1 | 0 | fgdb | bytes AssessLand | Real | 0 | 0 | fgdb | bytes AssessTot | Real | 0 | 0 | fgdb | bytes ExemptTot | Real | 0 | 0 | fgdb | bytes YearBuilt | Integer(Int16) | 0 | 0 | fgdb | bytes YearAlter1 | Integer(Int16) | 0 | 0 | fgdb | bytes YearAlter2 | Integer(Int16) | 0 | 0 | fgdb | bytes HistDist | String | 66 | 0 | fgdb | bytes Landmark | String | 150 | 0 | fgdb | bytes BuiltFAR | Real | 0 | 0 | fgdb | bytes ResidFAR | Real | 0 | 0 | fgdb | bytes CommFAR | Real | 0 | 0 | fgdb | bytes FacilFAR | Real | 0 | 0 | fgdb | bytes BoroCode | Integer | 0 | 0 | fgdb | bytes BBL | Real | 0 | 0 | fgdb | bytes CondoNo | Integer | 0 | 0 | fgdb | bytes Tract2010 | String | 7 | 0 | fgdb | bytes XCoord | Integer | 0 | 0 | fgdb | bytes YCoord | Integer | 0 | 0 | fgdb | bytes ZoneMap | String | 3 | 0 | fgdb | bytes ZMCode | String | 1 | 0 | fgdb | bytes Sanborn | String | 8 | 0 | fgdb | bytes TaxMap | String | 5 | 0 | fgdb | bytes EDesigNum | String | 5 | 0 | fgdb | bytes APPBBL | Real | 0 | 0 | fgdb | bytes APPDate | String | 10 | 0 | fgdb | bytes PLUTOMapID | String | 1 | 0 | fgdb | bytes FIRM07_FLAG | String | 1 | 0 | fgdb | bytes PFIRM15_FLAG | String | 1 | 0 | fgdb | bytes Version | String | 6 | 0 | fgdb | bytes DCPEdited | String | 3 | 0 | fgdb | bytes Latitude | Real | 0 | 0 | fgdb | bytes Longitude | Real | 0 | 0 | fgdb | bytes Notes | String | 20 | 0 | fgdb | bytes Shape_Length | Real | 0 | 0 | fgdb | bytes Shape_Area | Real | 0 | 0 | fgdb | bytes

PDF from Bytes

field | datatype | width | precision | Example -- | -- | -- | -- | -- Borough | Alphanumeric | 2 | 0 | BX Block | Numeric | 5 | 0 | 99999 Lot | Numeric | 4 | 0 | 9999 CD | Numeric | 3 | 0 | 999 CT2010 | Alphanumeric | 7 | 0 | e.g. 203.01 CB2010 | Alphanumeric | 5 | 0 | e.g. 101A SchoolDist | Alphanumeric | 2 | 0 |   Council | Numeric | 2 | 0 | 99 ZipCode | Alphanumeric | 5 | 0 |   FireComp | Alphanumeric | 4 | 0 |   PolicePrct | Numeric | 3 | 0 | 999 HealthCent | Numeric | 2 | 0 | 99 HealthArea | Numeric | 4 | 0 | 9999 Sanitboro | Numeric | 1 | 0 | 1,2,3,4,5 SanitDistr | Numeric | 2 | 0 | 99 SanitSub | Alphanumeric | 2 | 0 |   Address | Alphanumeric | 28 | 0 |   ZoneDist1 | Alphanumeric | 9 | 0 |   ZoneDist2 | Alphanumeric | 9 | 0 |   ZoneDist3 | Alphanumeric | 9 | 0 |   ZoneDist4 | Alphanumeric | 9 | 0 |   Overlay1 | Alphanumeric | 4 | 0 |   Overlay2 | Alphanumeric | 4 | 0 |   SPDist1 | Alphanumeric | 12 | 0 |   SPDist2 | Alphanumeric | 12 | 0 |   SPDist3 | Alphanumeric | 12 | 0 |   LtdHeight | Alphanumeric | 5 | 0 |   SplitZone | Alphanumeric | 1 | 0 |   BldgClass | Alphanumeric | 2 | 0 |   LandUse | Alphanumeric | 2 | 0 |   Easements | Numeric | 2 | 0 | 99 OwnerType | Alphanumeric | 1 | 0 |   OwnerName | Alphanumeric | 81 | 0 |   LotArea | Numeric | 9 | 0 |   BldgArea | Numeric | 11 | 0 | 99999999999 ComArea | Numeric | 11 | 0 | 99999999999 ResArea | Numeric | 11 | 0 | 99999999999 OfficeArea | Numeric | 11 | 0 | 99999999999 RetailArea | Numeric | 11 | 0 | 99999999999 GarageArea | Numeric | 11 | 0 | 99999999999 StrgeArea | Numeric | 11 | 0 | 99999999999 FactryArea | Numeric | 11 | 0 | 99999999999 OtherArea | Numeric | 11 | 0 | 99999999999 AreaSource | Alphanumeric | 1 | 0 |   NumBldgs | Numeric | 5 | 0 | 99999 NumFloors | Numeric | 6 | 2 | 999.99 UnitsRes | Numeric | 5 | 0 | 99999 UnitsTotal | Numeric | 5 | 0 | 99999 LotFront | Numeric | 7 | 2 | 9999.99 LotDepth | Numeric | 7 | 2 | 9999.99 BldgFront | Numeric | 7 | 2 | 9999.99 BldgDepth | Numeric | 7 | 2 | 9999.99 Ext | Alphanumeric | 2 | 0 |   ProxCode | Alphanumeric | 1 | 0 |   IrrLotCode | Alphanumeric | 1 | 0 |   LotType | Alphanumeric | 1 | 0 |   BsmtCode | Alphanumeric | 1 | 0 |   AssessLand | Numeric | 11 | 0 | 99999999999 AssessTot | Numeric | 11 | 0 | 99999999999 ExemptTot | Numeric | 11 | 0 | 99999999999 YearBuilt | Numeric | 4 | 9 | 9999 YearAlter1 | Numeric | 4 | 9 | 9999 YearAlter2 | Numeric | 4 | 9 | 9999 HistDist | Alphanumeric | 40 | 0 |   Landmark | Alphanumeric | 35 | 0 |   BuiltFAR | Numeric | 7 | 2 | 9999.99 ResidFAR | Numeric | 5 | 2 | 99.99 CommFAR | Numeric | 5 | 2 | 99.99 FacilFAR | Numeric | 5 | 2 | 99.99 BoroCode | Numeric | 1 | 0 |   BBL | Numeric | 10 | 0 |   CondoNo | Numeric | 5 | 0 |   Tract2010 | Alphanumeric | 6 | 0 |   XCoord | Numeric | 7 | 0 | 9999999 YCoord | Numeric | 7 | 0 | 9999999 ZoneMap | Alphanumeric | 3 | 0 |   ZMCode | Alphanumeric | 1 | 0 |   Sanborn | Alphanumeric | 8 | 0 |   TaxMap | Alphanumeric | 5 | 0 |   EDesigNum | Alphanumeric | 5 | 0 |   APPBBL | Numeric | 10 | 0 |   APPDate | Numeric | 10 | 0 |   PLUTOMapID | Numeric | 1 | 0 |   FIRM07_FLA | Alphanumeric | 1 | 0 |   PFIRM15_FL | Alphanumeric | 1 | 0 |   Version | Alphanumeric | 6 | 0 |   DCPEdited | Alphanumeric | 3 | 0 |   Latitude | Numeric | ? | ? |   Longitude | Numeric | ? | ? |   Notes | Alphanumeric | 20 | 0 |   Shape_Leng | ? | ? | ? |   Shape_Area | ? | ? | ? |  

[AmandaDoyle]

@SPTKL these are the differences I'm seeing in the FGDB. There are no longs. Left screenshot is what AF creates, right it the latest output. The version from 15 days ago was correct, minus the few fields I pointed out. Maybe there needs to be two different workflows for fgdb and the shapefile? Let's focus on the gdb first

Comparison

     

[AmandaDoyle]

field > what is it > what it needs to be block > double > long

rename geom field to Shape