2

I'm using python to run ogr2ogr commands to load SHP to MSSQLSpatial.

The command works fine when using trusted_connection, but when I use UID and PWD, the SRID is randomly changed to SRID:846 (while the data loads correctly, and the geometry previews correctly, it won't display in QGIS, which tipped me off to the problem)

Here are the commands, with line breaks are they are currently working in Python:

with Trusted Connection:

cmd = 'call "C:\Program Files\QGIS 2.18\OSGeo4W.bat" ' \
      'ogr2ogr -skipfailures -overwrite -progress -nln ' \
      '"plangis.temp_pyogr2ogr" ' \
      '-f MSSQLSpatial "MSSQL:driver={SQL Server};server=servername;database=databasename;trusted_connection=yes" "shp\statistical_neighborhoods.shp" -s_srs EPSG:4326 -t_srs EPSG:2877 -lco geom_name=shape -lco UPLOAD_GEOM_FORMAT=wkt'

with UID/PWD:

cmd2 = 'call "C:\Program Files\QGIS 2.18\OSGeo4W.bat" ' \
      'ogr2ogr -skipfailures -overwrite -progress -nln ' \
      '"plangis.temp_pyogr2ogr" ' \
      '-f MSSQLSpatial "MSSQL:driver={SQL Server};server=servername;database=databasename;UID=username;PWD=password" "shp\statistical_neighborhoods.shp" -s_srs EPSG:4326 -t_srs EPSG:2877 -lco geom_name=shape -lco UPLOAD_GEOM_FORMAT=wkt'

As you can see, both commands are identical except for the connection information.

When I print the assembled command, both are also identical except for the connection information.

I can run these at the OSGeo4W command, and the results are the same.

I'm testing the SRID output in MSSQL Server using shape.STSRID.

  • Perhaps the specified user lacks some permissions to the metadata tables docs.microsoft.com/en-us/sql/relational-databases/…. – user30184 Apr 24 '18 at 19:32
  • I would think the trusted_connection user would lack that, rather than the user specified in the UID - that user has higher privileges... – DPSSpatial Apr 24 '18 at 20:31
  • Ask you db admins to gather log about the SQL statements which are made during the conversion. – user30184 Apr 24 '18 at 20:44
  • @user30184 I'll see what I can track down, Thanks! – DPSSpatial Apr 24 '18 at 21:07
0

It seems this is a error in the coordinate systems that OGR2OGR is using.

If I switch the EPSG code to 2773 from 2877, it works just fine.

As I recall, FME gave us this same issue, so I think I'm going to have to re-standardize all my MSSQL Spatial data to SRID 2773 instead of 2877.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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