Export spatial data from SQL Server to ESRI shapefile

Question

I want to export spatial data from SQL Server to ESRI shapefile and I have an issue :

ogr2ogr -f "ESRI Shapefile" " C:\Users\sqlexport.shp " 
"MSSQL:server=PORT_7FMW8H2;database=testdbspatial;trusted_connection=yes; 
"-sql "select * from DRShape" -overwrite

ERROR 1: Failed to create directory C:\Users\sqlexport.shp for shapefile datastore.

ERROR 1: ESRI Shapefile driver failed to create C:\Users\sqlexport.shp

• user30184: You have written the -sql parameter wrong so it is omitted. When it is omitted ogr2ogr believes that you want to convert all the tables that it finds from your server and for that it wants to create a new directory that would get one shapefile per table.
• amine bak: so how can i write it ?
• user30184: Put a space chartacter before the dash of -sql.

Answer 1

If that is the exact command copied from your terminal, you simply need to be more careful with the spelling; there are spaces between quotation marks and the output file string. Try and replace your command with this one:

ogr2ogr -f "ESRI Shapefile" "C:\Users\sqlexport.shp" "MSSQL:server=PORT_7FMW8H2;database=testdbspatial;trusted_connection=yes;" -sql "select * from DRShape" -overwrite

If you have multiple geometry types in the table then you need to see Selecting feature types when using ogr2ogr to convert to shapefile?

• user30184: This error means that your table "DRShape" seems to contain both polygons and linestrings. They can't be saved into same shapefile. You must either sort the geometries by geometry type or use some other format that supports mixed geometries like GML or GeoPackage.
• geozelot: @aminebak well, solve 1, get 3 is actually not the worst ratio in problem solving...,). you could try adding -nlt POLYGON -skipfailures to the above command (or LINESTRING if that is what you want). this will only process the given geometry type and skips all those that do not match (and other failures, make sure you double check that shapefile). in the end, your table has mixed geometries and cannot be converted into one shapefile as it is.
• geozelot: @aminebak those values are just hexadecimal representations of your geometries; the geom column can hold different geometry types, like LINEs and POLYGONs together, and the error messages tell you that there are indeed two types together. a shapefile does not allow different geometry types together. with -nlt POLYGON added to the ogr2ogr command, you specify that your shapefile will be of type POLYGON, and -skipfailures will ignore all LINEs (which would otherwise lead to above errors).
• Ian Turton: Tbh this question is basically screwed beyond fixing
• Brian W.: @ThingumaBob similar but not the same. ogr2ogr -nlt polygon won't handle a geometryCollection correctly(at least in my experience). I suspect his union aggregate is merging a polygon and a linestring into a single GeometryCollection and not handling the metadata correctly when it breaks that out. He may need to explode collections.

Answer 2

i did a join between 2 tables departements2 which has all department with their id and geometry with BM_REGIONFR with id of department and region ( i have 5 region in each region many departement )

select 
 b.[Region],
 geometry::UnionAggregate(geom.MakeValid()) AS Geo -- Add Alias!!
into 
 dbo.DRShape 
from [dbo].[departements2] a join [dbo].[BM_REGIONFR] b 
on a.[code_insee] = b.[dep_2] 
group by b.Region

After that i wanted to export the shapefile with OSGeo4w in order to have the map with the right decomposition and thats the query i used

ogr2ogr -f "ESRI Shapefile" "C:\Users\sqlexport.shp" "MSSQL:server=PORT_7FMW8H2;database=testdbspatial;trusted_connection=yes;" -sql "select * from DRShape" -overwrite

And i got these ERRORS

ERROR 1: Attempt to write non-polygon (LINESTRING) geometry to POLYGON type shapefile. ERROR 1: Unable to write feature 1 from layer dbo.DRShape. ERROR 1: Terminating translation prematurely after failed

• Inactivated Account: Can you load the data into QGIS? You can always export it from there to SHP...
• geozelot: ladies and gentlemen, this is so much not the place to ask and discuss this...just you wait until a moderator comes along ,) @aminebak please ask this as a new question, let's keep this board like it's supposed to work. you'l get answers then, I promise
• Inactivated Account: @ThingumaBob I'll be waiting!