|
This table holds IDs and polygons for Census 2000 block groups. IT WILL ALSO HOLD ADDITIONAL ATTRIBUTES NOT LISTED HERE.
To construct this table, one must obtain a tract shapefile for ArcGIS (or a file that can be exported as a shapefile). Then one must run the SQL Server 2008+ Shapefile Uploader tool (part of SqlSpatialTools).
SqlSpatialTools may be found at http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx
Note that the above tool requires a SRID (spatial reference system ID), which should normally be 4326. To be sure that this is the right number, though, go to the SQL Server database you're about to populate and execute
SELECT * FROM sys.spatial_reference_systems
WHERE spatial_reference_id = 4326
You should see data indicating that 4326 is WGS 84, i.e., GEOGCS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84", 6378137, 298.257223563]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]
One must run the Shapefile Uploader tool with privileges sufficient to write to the data warehouse. Options that should be selected include outputting polygon information using the grography type and setting the SRID to 4326. The most important columns - i.e., the ones that should be selected if nothing else is selected - are the FIPS codes for state, county, tract, and blkgrp, plus the the full block groups FIPS (including state, county, tract, and block group codes). The ID column name should be set to BLKGRP_SURROGATEKEY, and the polygon/geometry name should be BLKGRP_POLYGON. The "create spatial index" box should also be checked. The name of the output table should be set to CensusBlockGroupXXXX, where XXXX is the census year (e.g., 2010). Make sure there is no conflict with any existing table (well, unless you mean to blow away an existing table). If all these options are set correctly, and both the input shapefile and output database are entered, then one can simply click the "Upload to Database" button.
The tool cannot create its output table in any schema other than "dbo" so when it's finished, bring up the newly created table in SSMS, right click, and select "Design." Then bring up a properties window, if it's not there already (F4), and change the schema.
To generate datasets, start with ArcGIS. Bring a census block group shapefile into a map in ArcMap. Then do an "Add Data." The data you add will be block-level data generated using some online dataset generator that will give you block groups across the entire country (not just by state). E.g., Social Explorer will do the trick (http://www.socialexplorer.com/pub/reportdata/home.aspx). It can be reached via the Gould Library Home page --> Databases --> Social Explorer --> Reports --> (whichever census you prefer) --> Select a geographic type/Block Groups --> Select All Census Block Groups in the U.S. --> Add --> Choose your variables. Output data as a CSV file, but try not to massage it in Excel (which screws up the data types). When you've created a CSV file, add it to your ArcGIS map using the above-mentioned "Add Data" menu item. Once it is added, you can then go back to your census block shapefile data, right click it, do a join (NOT a spatial join, but a join on a column) and join your block group data with any additional data you've downloaded from Social Explorer using the FIPS field as your join key. You can then right click on your block group data, do an export, and export it as another shapefile. At that point, the shapefile will be ready for use with the previously mentioned "Shapefile Uploader" tool.
Once you've uploaded data to a SQL Server instance, you may add in any indexes that are desired (e.g., on the FIPS column). If you used to tool as described above, there will already be an index on the primary (surrogate) key and the spatial data. As noted above, make sure also to move your table into the correct schema.
To query against this table, using a longitude and latitude, do this:
SELECT
* -- Whatever rows you need
FROM
UtilityViews.VW_DEGREESTUDENTGEOINFORMATION twll
INNER JOIN UtilityTables.TB_CensusBlockGroup2000 cbb
-- Insert your latitude and longitude data here, from TABLE_WITH_LONGITUDE_AND LATITUDE; note the 4326 here (again, as above)
ON twll.Latitude IS NOT NULL
AND twll.Longitude IS NOT NULL
AND cbb.BLKGRP_POLYGON.STIntersects(geography::Point(twll.Latitude, twll.Longitude, 4326)) = 1
The above query will join all the rows in TABLE_WITH_LONGITUDE_AND_LATITUDE that contain points (defined by latitude and longitude columns) to the corresponding census block in UtilityTables.TB_CensusBlockGroup2000. Basically it's looking for census blocks in UtilityTables.TB_CensusBlockGroup2000 that enclose a given latitude/longitude point. Since census blocks do not overlap, the join should be one-to-one (with some rows not joining, because the latitude and longitude are missing, invalid, outside the US, etc.).
|