Skip to content

Instantly share code, notes, and snippets.

@chriswhong

chriswhong/pluto-carto.md

Last active June 15, 2018 23:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chriswhong/f3e9de1cc894ee29661d12047b2b6540 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chriswhong/f3e9de1cc894ee29661d12047b2b6540 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Chris Whong revised this gist Dec 27, 2017. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,15 +3,15 @@ To load MapPLUTO into carto, the best approach is to upload the five borough sha
    - Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    - `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    ```
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM qnmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healthcent,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM qnmappluto
    UNION ALL
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM simappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healthcent,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM simappluto
    UNION ALL
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM mnmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healthcent,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM mnmappluto
    UNION ALL
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bxmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healthcent,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bxmappluto
    UNION ALL
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bkmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healthcent,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bkmappluto
    ```
    - Save the query as a new table.
  2. Chris Whong revised this gist Dec 27, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -15,5 +15,5 @@ SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldi
    ```
    - Save the query as a new table.
    - Do a `SELECT count(*)` to make sure you have everything. For Pluto 16v2 that should be 857237 rows
    - Do a `SELECT count(*)` to make sure you have everything. For Pluto 17v1 that should be 857,271 rows
    - Delete the source tables
  3. Chris Whong revised this gist Mar 10, 2017. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    To load MapPLUTO into carto, the best approach is to upload the five borough shapefiles together, then `UNION ALL` them together.

    1. Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    2. `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    - Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    - `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    ```
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM qnmappluto
    UNION ALL
    @@ -14,6 +14,6 @@ UNION ALL
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bkmappluto
    ```
    3. Save the query as a new table.
    4. Do a `SELECT count(*)` to make sure you have everything. For Pluto 16v2 that should be 857237 rows
    5. Delete the source tables
    - Save the query as a new table.
    - Do a `SELECT count(*)` to make sure you have everything. For Pluto 16v2 that should be 857237 rows
    - Delete the source tables
  4. Chris Whong revised this gist Mar 10, 2017. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -15,4 +15,5 @@ SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldi
    ```
    3. Save the query as a new table.
    4. Delete the source tables
    4. Do a `SELECT count(*)` to make sure you have everything. For Pluto 16v2 that should be 857237 rows
    5. Delete the source tables
  5. Chris Whong revised this gist Mar 10, 2017. 1 changed file with 6 additions and 7 deletions.
    13 changes: 6 additions & 7 deletions pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,17 +3,16 @@ To load MapPLUTO into carto, the best approach is to upload the five borough sha
    1. Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    2. `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    ```
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM (
    SELECT * FROM bxmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM qnmappluto
    UNION ALL
    SELECT * FROM bkmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM simappluto
    UNION ALL
    SELECT * FROM mnmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM mnmappluto
    UNION ALL
    SELECT * FROM qnmappluto
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bxmappluto
    UNION ALL
    SELECT * FROM simappluto
    ) a
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM bkmappluto
    ```
    3. Save the query as a new table.
    4. Delete the source tables
  6. Chris Whong revised this gist Mar 10, 2017. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    To load MapPLUTO into carto, the best approach is to upload the five borough shapefiles together, then `UNION ALL` them together.

    1 - Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    2 - `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    1. Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    2. `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    ```
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM (
    SELECT * FROM bxmappluto
    @@ -15,5 +15,5 @@ UNION ALL
    SELECT * FROM simappluto
    ) a
    ```
    3 - Save the query as a new table.
    4 - Delete the source tables
    3. Save the query as a new table.
    4. Delete the source tables
  7. Chris Whong created this gist Mar 10, 2017.
    19 changes: 19 additions & 0 deletions pluto-carto.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    To load MapPLUTO into carto, the best approach is to upload the five borough shapefiles together, then `UNION ALL` them together.

    1 - Upload all five zipped borough shapefiles from Bytes of the Big Apple. Be sure to uncheck 'Allow Carto to guess column types" when uploading, or you'll get column type mismatches
    2 - `UNION ALL` the tables together with the following query. We can't just `SELECT *` because we'd have duplicate `cartodb_id`s in the result set, and saving as a new table would fail.
    ```
    SELECT the_geom,the_geom_webmercator,borough,block,lot,cd,ct2010,cb2010,schooldist,council,zipcode,firecomp,policeprct,healtharea,sanitboro,sanitdistr,sanitsub,address,zonedist1,zonedist2,zonedist3,zonedist4,overlay1,overlay2,spdist1,spdist2,spdist3,ltdheight,splitzone,bldgclass,landuse,easements,ownertype,ownername,lotarea,bldgarea,comarea,resarea,officearea,retailarea,garagearea,strgearea,factryarea,otherarea,areasource,numbldgs,numfloors,unitsres,unitstotal,lotfront,lotdepth,bldgfront,bldgdepth,ext,proxcode,irrlotcode,lottype,bsmtcode,assessland,assesstot,exemptland,exempttot,yearbuilt,yearalter1,yearalter2,histdist,landmark,builtfar,residfar,commfar,facilfar,borocode,bbl,condono,tract2010,xcoord,ycoord,zonemap,zmcode,sanborn,taxmap,edesignum,appbbl,appdate,plutomapid,version,mappluto_f,shape_leng,shape_area FROM (
    SELECT * FROM bxmappluto
    UNION ALL
    SELECT * FROM bkmappluto
    UNION ALL
    SELECT * FROM mnmappluto
    UNION ALL
    SELECT * FROM qnmappluto
    UNION ALL
    SELECT * FROM simappluto
    ) a
    ```
    3 - Save the query as a new table.
    4 - Delete the source tables