0

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

Community
  • 1
amine bak
  • 13
  • 1
  • 6
  • 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. – user30184 Apr 06 '18 at 14:34
  • so how can i write it ? – amine bak Apr 10 '18 at 14:50
  • Put a space chartacter before the dash of -sql. – user30184 Apr 11 '18 at 18:16

2 Answers2

5

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?

Ian Turton
  • 81,417
  • 6
  • 84
  • 185
geozelot
  • 30,050
  • 4
  • 32
  • 56
  • ERROR 1: Attempt to write non-polygon (LINESTRING) geometry to POLYGON type shapefile. ERROR 1: Unable to write feature 1 from layer SELECT. ERROR 1: Terminating translation prematurely after failed – amine bak Apr 06 '18 at 14:12
  • now i have 3 ERRORS instead of 2 :) – amine bak Apr 06 '18 at 14:16
  • 1
    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. – user30184 Apr 06 '18 at 14:37
  • 1
    @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 Apr 06 '18 at 14:40
  • I'am new to this, but i have only two columns in my db "DRShape" : Region with names of states and geom " 0x000000000104AB530000285D348AE0D21540762BCD0 " with that kind of values. so i dont think i have both in my db – amine bak Apr 06 '18 at 14:44
  • 1
    @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). – geozelot Apr 06 '18 at 14:52
  • @aminebak if you feel you don´t know what to do, consider opening a new question (since, strictly speaking, your question here has been solved); add as much detail about your table/data and your needs and the error messages with the command you used. also, MySQL has the ST_AsText function that will give you a more readable representation of your geometries (SELECT Region, ST_AsText(geom) FROM DRShape)...check what types of geometry there are. don´t forget to upvote/accept ,) – geozelot Apr 06 '18 at 14:59
  • it work with -nlt POLYGON but i dont have the name of the regions anymore :/ – amine bak Apr 06 '18 at 16:10
  • @aminebak I'm sorry, but without knowing your data and in comments, it's just not possible to help you explain and solve those issues. ask a new question, add the exact command you use, where your data came from originally and the error messages...as much detail as possible. – geozelot Apr 06 '18 at 16:51
  • see https://gis.stackexchange.com/questions/26662/selecting-feature-types-when-using-ogr2ogr-to-convert-to-shapefile for an answer to this problem – Ian Turton Apr 18 '18 at 16:01
  • Make sure you have the extended sql spatial tools written by MS. Run your query with a where clause 'where geom.STGeometryType() = 'Polygon' (or 'Linestring" or 'MultiPolygon') depending on what you're trying to export. This will only export the one type of geometry from the table and should fix your issue but will drop features that don't match that type. – Brian W. Apr 20 '18 at 18:00
  • @BrianW. you are answering a question asked in comments to an answer to a question by giving an alternative for getting stuck at the same problem: the attributes are missing. the issue could be that the polygons actually have no attributes, but the lines. or sth. else entirely... – geozelot Apr 20 '18 at 18:53
  • @IanTurton do the offered solutions there to get shapefiles with specified geometries rule out missing attributes (and using -nlt ... doesn't)? this might be a duplicate of a question asked in comments to an...see comment above...why adding up to this? you should rather protect this...or move the question from comments to a new post. or some other moderator magic... – geozelot Apr 20 '18 at 18:58
  • 1
    Tbh this question is basically screwed beyond fixing – Ian Turton Apr 20 '18 at 19:01
  • @IanTurton hehe...moderator used magic: 'final words'. I like. well, I'm out... – geozelot Apr 20 '18 at 19:06
  • 1
    @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. – Brian W. Apr 20 '18 at 19:57
  • @BrianW. fair enough, and good thinking...I was just complaining about the form in this post. the question somewhat adresses the end of a chain of possible sources/reasons for wrong results... – geozelot Apr 20 '18 at 21:09
0

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

amine bak
  • 13
  • 1
  • 6