Why is Spatialite delivering "Invalid SRID" error message

Question

Qspatialite in QGIS 1.8 (and also Spatialite-gui) delivers an "Invalid SRID" error message when I try to load shapefiles with a SRID value of 3814 (Mississippi Transverse Mercatur). The SRID is within the database file spatial_ref_sys table.

For troubleshooting purposes I tried loading the shapefiles directly into a blank QGIS session. QGIS builds its own custom CRS proj4 code and correctly reprojects the shapefiles on the fly against WGS84 Orthophoto (Bing Maps), but it doesn't attribute the projection as EPSG 3813, instead it's called "Custom 100003". I figured it should find EPSG 3814. Also, using the QGIS plugin, Qspatialite has a dialog that allows the user to scroll through a list of available projections, using both common and EPSG names, with SRID value listed as well. SRID 3814 is not listed in dialog, but I double checked and it actually is in the database's spatial_ref_sys table.

Thanks for the user who had helped me discover that the SRID is already in the table, and doesn't need to be added. Can someone help identify the problem here? It's not getting read.

EDIT: Here is the data you requested regarding my prior question (I couldn't copy it into a comment field)

This shapefile correctly reprojects on the fly to WGS 84. EsriShapefile loaded into QGIS 1.8 (Ubuntu 10.04 -- UbuntuGIS Repositories version): USER:100003 - * Generated CRS (+proj=tmerc +lat_0=32.5 +lon_0=-89.75 +k=0.9998335 +x_0=500000 +y_0=1300000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs)

myEsriShapefiles.prj ------ PROJCS["mstm",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",500000.0],PARAMETER["False_Northing",1300000.0],PARAMETER["Central_Meridian",-89.75],PARAMETER["Scale_Factor",0.9998335],PARAMETER["Latitude_Of_Origin",32.5],UNIT["Meter",1.0]]

compare this to http://spatialreference.org/ref/epsg/3814/ 3814.prj -----

PROJCS["NAD83 / Mississippi TM",GEOGCS["GCS_North_American_1983",DATUM["D_North American Datum 1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.017453292519943295]],PROJECTION["Transverse Mercator"],PARAMETER["central_meridian",-89.75],PARAMETER["latitude_of_origin",32.5],PARAMETER["scale_factor",0.9998335],PARAMETER["false_easting",500000.0],PARAMETER["false_northing",1300000.0],UNIT["m",1.0]]

comparison of prj files below: 1>Esri.prj 2>3814.prj (the order of the parameters is different)

PROJCS["mstm",GEOGCS PROJCS["NAD83 / Mississippi TM",GEOGCS

["GCS_North_American_1983",DATUM ["GCS_North_American_1983",DATUM

["D_North_American_1983",SPHEROID ["D_North American Datum 1983",SPHEROID

["GRS_1980",6378137.0,298.257222101]], ["GRS_1980",6378137.0,298.257222101]],

PROJECTION["Transverse_Mercator"], PROJECTION["Transverse Mercator"],

PARAMETER["False_Easting",500000.0], PARAMETER["false_easting",500000.0],

PARAMETER["False_Northing",1300000.0], PARAMETER["false_northing",1300000.0],

PARAMETER["Central_Meridian",-89.75], PARAMETER["central_meridian",-89.75],

PARAMETER["Scale_Factor",0.9998335], PARAMETER["scale_factor",0.9998335],

PARAMETER["Latitude_Of_Origin",32.5], PARAMETER["latitude_of_origin",32.5],

UNIT["Meter",1.0]] UNIT["m",1.0]]

Answer 1

it depends on the Version of Qgis Lisboa you use. There is a bug in Qgis standalone installer for Windows which prevents the synchronisation of Qgis own CRS database with that of Gdal, if the installation path contains blanks (which is default). The Osgeo4w Installer does it correct by now, and supplies EPSG:3814 from GDAL to qgis:

+proj=tmerc +lat_0=32.5 +lon_0=-89.75 +k=0.9998335 +x_0=500000 +y_0=1300000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs

If Qgis is not sure whether the .prj file is identical to a EPSG, it rather creates its own CRS. Missing towgs84 parameters could be a reason. The .prj file contains a WKT formatted CRS definition.

spatialreference.org gives this WKT definition for 3814:

PROJCS["NAD83 / Mississippi TM",
GEOGCS["NAD83",
    DATUM["North American Datum 1983",
        SPHEROID["GRS 1980",6378137.0,298.257222101,
            AUTHORITY["EPSG","7019"]],
        TOWGS84[1.0,1.0,-1.0,0.0,0.0,0.0,0.0],
        AUTHORITY["EPSG","6269"]],
    PRIMEM["Greenwich",0.0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.017453292519943295],
    AXIS["Geodetic latitude",NORTH],
    AXIS["Geodetic longitude",EAST],
    AUTHORITY["EPSG","4269"]],
PROJECTION["Transverse Mercator",
    AUTHORITY["EPSG","9807"]],
PARAMETER["central_meridian",-89.75],
PARAMETER["latitude_of_origin",32.5],
PARAMETER["scale_factor",0.9998335],
PARAMETER["false_easting",500000.0],
PARAMETER["false_northing",1300000.0],
UNIT["m",1.0],
AXIS["Easting",EAST],
AXIS["Northing",NORTH],
AUTHORITY["EPSG","3814"]]

The towgs84 parameters differ. EPSG notes 0;0;0 for mainland USA, and 1;1;-1 only for Hawaii.

The EPSG registry at http://www.epsg-registry.org/ has almost identical values vor EPSG:3813 and 3814.

I'm not sure if qspatialite uses the Qgis CRS database or that of spatialite itself for the input mask.

Can you insert the .prj and qgis made Custom CRS string here?

Answer 2

Use Spatialite_gui 1.5 or better for loading SRID:3814 shapefiles. Qspatialite 6.0.4 plugin for QGIS 1.8 (Lisboa)can't handle that EPSG yet.