none
Importing Tigerline Shape Files via OGR2OGR RRS feed

  • Question

  • I am trying to use ogr2ogr, GDAL version 1.9.1, to import tigerline shape files into SQL Server 2012.  I am attempting to follow the example worked out by Alastair Aitchison on his blog at http://alastaira.wordpress.com/ogr2ogr-patterns-for-sql-server/ . Specifically, I have the tigerline file obtained at http://www2.census.gov/geo/tiger/TIGER2010/ZCTA5/2010/tl_2010_06_zcta510.zip and I am executing the following command:

    ogr2ogr -f "MSSQLSpatial" "MSSQL:server=localhost;database=SQLLearn01;trusted_connection=yes" "tl_2010_06_zcta510.shp" -a_srs "EPSG:4269" -overwrite -lco "GEOM_TYPE=geography" -lco "GEOM_NAME=geo" -nln "CaliforniaZCTA" -progress

    The command completes successfully, no errors are thrown, and data is loaded into the CaliforniaZCTA table; however, when I view the spatial results in SSDT, all I see is an empty grid with no shapes displayed. Obviously, something is not right. On the other hand, I used the "Shapefile Uploader" utility with analogous settings (EPSG = 4269, Geography mode) and the file loaded correctly. So, something appears to be wrong with the way ogr2ogr is translating the file.

    I have had some measure of success by treating the imported data as "geometry" instead of "geography". For instance, the query

    select top(100) geometry::Parse(geo.STAsText()) from CaliforniaZCTA
    retrieves a spatial result set that can be rendered and it appears to be somewhat correct. Any ideas?



    • Edited by NTDeveloper Wednesday, August 1, 2012 5:44 AM Provided additional details
    Wednesday, August 1, 2012 12:51 AM

Answers

  • Believe me - saying that the files came from the U.S. Census website does not mean that they don't contain errors... ;)

    So, you obviously have invalid data. You can fix this first by:

    UPDATE CaliforniaZCTA
    SET geo = geo.MakeValid();

    Then try fixing the ring orientation:

    UPDATE CaliforniaZCTA
    SET geo = geo.ReorientObject() WHERE geo.EnvelopeAngle() > 90;

    Now try selecting the top 100 records again. Any improvement?


    twitter: @alastaira blog: http://alastaira.wordpress.com/


    Wednesday, August 1, 2012 4:27 PM
    Answerer

All replies

  • SSMS Spatial Results tab is not exactly the most sophisticated GIS visualisation tool out there... ;)

    The fact that your code example parsing as geometry works ok suggests that perhaps there's an issue with the dataset you imported. My first suggestion would be to test whether the imported data is valid, and oriented correctly. What do you get for the following?:

    SELECT TOP 100
      geo.STIsValid(),
      geo.EnvelopeAngle()
    FROM
     CaliforniaZCTA;

    Also, what do you get when just plotting a single element?

    SELECT TOP 1
      geo
    FROM
     CaliforniaZCTA;


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Wednesday, August 1, 2012 6:37 AM
    Answerer
  • Well, the dataset comes directly from the census website so if there's really something wrong with it others would be having an issue with it and it doesn't explain why the Shapefile Loader utility processes it correctly. Also, I've tried similar datasets from the census website and the same thing occurs. When I issue your first query, I get the following error:

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance

    If I execute the MakeValid() command though, it makes no difference with respect to the spatial display; no shapes are outlined. E.G.,

    select top(100)	geo.MakeValid() from californiazcta

    yields the same empty grid that 

    select top(100)	geo from californiazcta

    does. Selecting just the top record doesn't yield any shapes either. Interestingly, the query

    select geo from californiazcta where geo.STIsValid() = 0

    yields 274 records which DO render shapes in the spatial results grid while the query

    select geo from californiazcta where geo.STIsValid() = 1

    yields 1495 rows, none of which render shapes in the spatial results grid! If I execute the query

    SELECT TOP 100 geo.STIsValid(), geo.MakeValid().EnvelopeAngle() FROM CaliforniaZCTA

    I get the following results

    1 180
    1 180
    1 180
    1 180
    1 180
    1 180
    1 180
    1 180
    1 180
    0 0.202416209581244

    ......

    1 180
    0 0.10665708683092
    1 180
    1 180

    Wednesday, August 1, 2012 4:20 PM
  • Believe me - saying that the files came from the U.S. Census website does not mean that they don't contain errors... ;)

    So, you obviously have invalid data. You can fix this first by:

    UPDATE CaliforniaZCTA
    SET geo = geo.MakeValid();

    Then try fixing the ring orientation:

    UPDATE CaliforniaZCTA
    SET geo = geo.ReorientObject() WHERE geo.EnvelopeAngle() > 90;

    Now try selecting the top 100 records again. Any improvement?


    twitter: @alastaira blog: http://alastaira.wordpress.com/


    Wednesday, August 1, 2012 4:27 PM
    Answerer
  • Yes! That seems to fix the problem; the ReorientObject() call was the key. 

    What I still don't understand though is why you didn't run into these issues when you were constructing the example you gave on your blog.  I used exactly the same data you did and followed the example to-the-letter. Anyway, thanks for the help.


    • Edited by NTDeveloper Wednesday, August 1, 2012 4:53 PM
    Wednesday, August 1, 2012 4:52 PM