Monday, November 28, 2011

Tiger/Line Shape Files and Oracle



Have you ever needed to load an ESRI "shape file" and wondered if that's an easy effort or a difficult effort?  I know I have and I assumed that it was a pretty difficult effort.  However, I learned today that's actually pretty easy!

The Problem

The problem I was trying to solve was this:

  1. We have property data in our Oracle database.
  2. Each property has a latitude and longitude in the database.
  3. We also store the census tract information in our database.  This includes the following for each property (in increasing granularity levels from top to bottom):
    1. State
    2. County
    3. Census Tract
    4. Census Block Group
    5. Census Block
  4. The government publishes Census Tract information in an ESRI shape file.  You can download the shape files from http://web2.ssd.census.gov/cgi-bin/geo/shapefiles2010/main
  5. You can find more Tiger/Line and Shape files at: http://www.census.gov/geo/www/tiger/tgrshp2010/tgrshp2010.html
  6. We want to look up the census block for each property - i.e. we want to know what tract, block group and block the property resides in.
If we want to process every property in the country, we have to download the Block shape files one state at a time.  This is the data contained in the shapefile:





Loading the Shape File
Loading the shape file is as simple as can be...when you use Oracle's MapBuilder utility.  You can download Oracle MapBuilder from http://www.oracle.com/technetwork/middleware/mapviewer/downloads/index.html      MapBuilder is used to load the shape files mentioned above into the Oracle database.  Once you download it, starting MapBuilder is pretty easy – it’s a command line start: 

java –jar MapBuilder.jar

To load shapefiles, use Tools, Import Shapefile in MapBuilder:


From here, you simply pick the shapefile to load and it loads it into the database as a table with a geometry shape column.  You can create an index on the geometry field:

CREATE INDEX spatial_index ON TL_2010_08_TABBLOCK10 (geometry)
 INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Querying what you want...




4.      From here, all you have to do is select each block record based on the lat and long for each property (you can see the lat and long hardcoded below, but we would select this from our table of properties):

SELECT  *
FROM  TL_2010_08_TABBLOCK10
WHERE SDO_WITHIN_DISTANCE(Geometry,
                SDO_GEOMETRY(2001, 4269, 
                SDO_POINT_TYPE (-104.798643,39.917394,NULL), NULL, NULL),
                'distance=0 unit=mile') = 'TRUE';

5.      This will return the details for the block that the property is in:

     

I don't think it could be any easier!  Download and load up the shape file...then run queries to get the information you want.  You can map the data visually too!  This is a VERY powerful Oracle feature.  It used to be called Oracle Spatial or Oracle Locator and it used to be a separate license.  Today Spatial, which is the "partitioned" version of Locator (higher performance) is an extra license in Oracle Enterprise, but the standard edition of Oracle contains Locator.

Enjoy this functionality!

1 comment:

Nitish Dhapodkar said...

Looks grate, I was looking for such tool from long time. I find one such tool with POSTgis (shp2ddl.exe) that create insert statements from shape files into POSTgres database, but it is very long process and need to work with commands.