Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'd like to create a spatialite database and load it with shapefiles using the Mississippi Transverse Mercator (MSTM) projection, but it doesn't have an EPSG definition in the spatialite library of projections.

When loaded into a QGIS session, it correctly projects the shapefiles, automatically building a custom proj4 code for it from the esri prj files that come along with the data. I checked that QGIS accurately reprojects this data, on the fly, to online WGS84 Orthophoto (Bing Maps). It does.

I'm using qspatialite to load these shapefiles into a single newly created db.sqlite; I try to populate the mandatory SRID field with the custom projection code definition number that QGIS generates, for example SRID= "100003" etc, which is on the dropdown picklist to choose from in the qspatialite plugin . Qspatialite can't load the shapefiles with any custom codes stored in QGIS though; it issues an error and fails to load the data--I figure because that isn't in the Spatialite EPSG library of projections. To double check that the plugin installation works, I did load the shapefiles successfully using a standard EPSG number, but my shapefiles are really in a custom projection CRS.

Is there a way I can copy/paste QGIS's custom proj4 definition to the spatialite EPGS library and create a brand new custom SRID definition, then load my data using that new SRID number in the mandatory data loader field? Or, would spatialite-gui be better than qspatialite? Thanks to anyone who can explain how.

these are the MSTM projection parameters (not proj4) Scale Factor: 0.9998335 Central Meridian: -89 45' 00'' Central Latitude: 32 30' 00'' False Northing: 1,300,000 meters False Easting: 500,000 meters Projection Units: meters Spheroid: GRS80 Datum: NAD83

--EDIT OF POST 1/24/2013 BELOW-- I just wanted to add a workaround method for bulk-batch importing shapefiles into a spatialite database, using Qspatialite's bulk load OGR-file button. This would be useful if the IMPORT OGR FILE command won't accept a custom EPSG number that you need to use. For myself, I just entered a similar but commonly used EPSG number using that also uses the same DATUM. For example, my custom EPSG: 3814 apparently gets little use and hasn't yet been included in QGIS spatial reference database (Lisboa 1.8.0). I found that the "custom" coordinate system uses NAD83. Instead of entering the SRID of 3814 that causes an import failure, I just used a similar NAD83, in this case EPSG:26995, that's preloaded into QGIS. They import just fine like that. Of course the imported shapefiles will not be projected correctly in QGIS relative to a basemap.

To fix that I created a custom coordinate system in QGIS using the +proj code that QGIS generates to correctly project the individual shapefiles that are now importing to a spatialite database, that also included a 3814.prj spatial reference file. THEN after creating that CUSTOM CRS, I was able to load the files from my spatialite database and individually set the projection under the of each layer to my custom spatial reference code that I saved in QGIS. Presto, everything aligns perfectly with my basemap while using spatialite layers. And, I was able to use Qspatialite to bulk-batch over a hundred shapefiles and then a couple of hundred dbf tables of additional information. One by one would have been time consuming.

share|improve this question
1  
If you are familiar with the spatialite GUI, you might as well insert the CRS 3814 into the Qgis database stored at apps\qgis\resources\srs.db. Its a sqlite database as well, and srs definitions are in table tbl_srs. Don't forget to make a copy before editing... –  Andre Joost Jan 24 '13 at 20:23
    
It works. I don't understand the SRS_ID field in tbl_srs, but does it have to match the USER:100000+ QGIS saves for a user entered proj4 CRS? I found a QGIS thread that says "Once proj4 string is read, QGIS searches for a SRS ID in SRS database. SRS ID is unique for every projection. Thus even if two projection are nearly same (e.g. with or without towgs84) they must have different SRS ID to enable QGIS understand the difference" any, I made it unique to match that. and added the proj4 code again, SRID code etc, for all fields. Now Qspatialite can add MSTM. Would any unique sri_id work? –  user12711 Jan 25 '13 at 21:09
1  
I rather thought of adding the ID 3814 to the QGIS database, or modifying the entry for 3645 if you don't need that. Then QGIS and spatialite could talk easily to another. SRS_ID, SRID and auth_ID should be the same, as it is for all EPSG codes. is_geo = 0, because it is not a latlon CRS. –  Andre Joost Jan 26 '13 at 6:22
1  
Just saw that current QGIS master already has EPSG:3814 incorporated. So you could just copy the srs.db from there. At least it will be in the next stable version too. –  Andre Joost Jan 28 '13 at 16:37
1  
No, the srs.db gets synced with the CRS information of the locally installed GDAL version. You can install Master on a different computer, which you normally do not use for GIS things. –  Andre Joost Mar 6 '13 at 19:29

1 Answer 1

up vote 2 down vote accepted

You could add a custom SRID in a spatialite database (adding a row into spatial_ref_sys table), with a simple request, but here, it looks like the CRS you want to add (based on the information you give ) is already in the database :

It's SRID/EPSG 3814/3815/3816 (they all looks like the same to me :/).

HTH.

share|improve this answer
    
Thanks to RobinC for letting me know that it is in the spatial_ref_sys table, but Qspatialite and Spatialete-GUI both error out saying "invalid SRID". For some unknown reason, even though it's in the table, spatialite won't use them. I double checked the table, they are there. Also, I can load the shapefiles using an alternate SRID#, for instance the one for WGS84. Thanks very much continuing to look into this. What could be the problem? –  user12711 Nov 21 '12 at 14:08
1  
Since the SRID is in the spatial_ref_sys table, the original question is answered, and now the problem appears to be related to a different issue--a bit different question asked in the comment above. –  user12711 Nov 21 '12 at 16:38
    
SRID/EPSG 3814/3815/3816 differ in the transformation to WGS84. The original EPSG code does not carry those informations, transformations have their own EPSG code. In proj strings, these informations are mixed, and somtimes ignored. –  Andre Joost Nov 22 '12 at 11:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.