locked
Problem importing County Shape Files RRS feed

  • Question

  • I'm importing esri shape files from Harris County Texas to a SQL2012 database with ogr2ogr.   Harris County Texas provides esri shape files here:  http://pdata.hcad.org/GIS/index.html. I'm primarily interested in the parcels  (individually available here:  http://pdata.hcad.org/GIS/Parcels.exe).

    I can execute this:

    ogr2ogr -f "MSSQLSpatial" "MSSQL:server=localhost;database=GeoExperiments;trusted_connection=yes" "C:\Shapes\HCTx\Parcels.shp 

    and it imports all 1.3+ Million rows into sql server promptly, but of course, it's simply importing GEOMETRY.  I want to import GEOGRAPHY.

    If I execute this:

    ogr2ogr -f "MSSQLSpatial" "MSSQL:server=localhost;database=GeoExperiments;trusted_connection=yes" "C:\Shapes\HCTx\Parcels.shp" -a_srs "EPSG:4269" -lco "GEOM_TYPE=geography" -lco "GEOM_NAME=geogr4269" -nln "HarrisCtyParcels" -progress

    It results in this error:

    ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during e

    xecution of user-defined routine or aggregate "geography":
    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
    System.FormatException:
       at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
       at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
       at Microsoft.SqlServer.Types.Forw
    ERROR 1: Terminating translation prematurely after failed
    translation of layer Parcels (use -skipfailures to skip errors

    I did look in the .prj file and went to the epsg-registry.org to find the EPSG code:

    Code: EPSG::6588
    Name: NAD83(2011) / Texas South Central (ftUS)

    but since it's based on the regular 

    "EPSG:4269" and since the 6588 is not in the sys.spatial_reference_systems table, I thought the 4269 code was likely the right one.  I would appreciate your help with that.  None the less, I've tried both codes and gotten the same results.

    How do I fix the problem with the import?

    Thanks much for your help!

    Gay Spencer


    Wednesday, December 11, 2013 10:47 PM

Answers

  • The readme file on at http://pdata.hcad.org/GIS/README.htm indicates the file uses a projected coordinate system, not geography at all. That's why the number its assuming is latitude is not a latitude at all. If you want to confirm this import the file as geometry and inspect the coordinates of some of the spatial features.

    You'll need to reproject it to the coordinate system you want. Looks like that would be the -t_srs (convert to spatial reference system) option of ogr2ogr rather than -a_srs (assign spatial reference system). I'm not an ogr2ogr expert, so you may have to change some other parameters as well. Or make sure you have the .prj file in the right place so that ogr2ogr can read the original spatial reference system.

    Hope this helps, Cheers, Bob

    • Marked as answer by Gay Spencer Thursday, December 12, 2013 6:20 PM
    Thursday, December 12, 2013 3:57 AM

All replies

  • The readme file on at http://pdata.hcad.org/GIS/README.htm indicates the file uses a projected coordinate system, not geography at all. That's why the number its assuming is latitude is not a latitude at all. If you want to confirm this import the file as geometry and inspect the coordinates of some of the spatial features.

    You'll need to reproject it to the coordinate system you want. Looks like that would be the -t_srs (convert to spatial reference system) option of ogr2ogr rather than -a_srs (assign spatial reference system). I'm not an ogr2ogr expert, so you may have to change some other parameters as well. Or make sure you have the .prj file in the right place so that ogr2ogr can read the original spatial reference system.

    Hope this helps, Cheers, Bob

    • Marked as answer by Gay Spencer Thursday, December 12, 2013 6:20 PM
    Thursday, December 12, 2013 3:57 AM
  • Thanks much for the guidance, Bob!

    I will give the "reprojection" a try.  Since the parcels.shp file produces over a million rows, My idea is to experiment with a smaller sql table to sql table reprojection, first.   I'll report back here on results.  I've found the ogr2ogr stuff to be a bit challenging, but Alastair Atchison's Spatial Book has been enormously helpful.

    This isn't the only hurdle.  I'm facing the errors one at a time.

    Cheers,

    G

    Thursday, December 12, 2013 4:47 PM
  • Thanks again, Bob.

    Here's a related quote from Alastair Atchison's Pro Spatial w/SQL Server 2012:

    "Inasmuch as geometry does not enforce any restrictions on the SRID used, any coordinate
    data stored using the geography datatype can also be stored using the geometry datatype. However, in
    order to perform conversion the other way, from geometry to geography, the coordinate values of the
    existing geometry instance must represent latitude–longitude coordinates taken from a supported
    geodetic spatial reference system. If you are storing Northing and Easting coordinates from a
    projected system, or other nongeodetic data, those data can only be stored using the geometry datatype."

    You're right.  Just now they are defined with coordinates like so:

    POLYGON ((3085157.8881188184 13942836.982178211, 3085099.4118811935 13942796.71336633, 3085034.1886138618 13942891.427227721, 3085073.5158415884 13942918.509900987, 3085092.663861379 13942931.696039602, 3085157.8881188184 13942836.982178211))

    and that's a far cry from a longitude or a latitude.  

    Cheers,

    G

    Thursday, December 12, 2013 6:30 PM