1

I want to convert geometry to geography in SQL Server; I followed this article:

https://blogs.msdn.microsoft.com/edkatibah/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b/

Here is my query:

INSERT INTO gCOMMUNE         
SELECT 
      [dbo].[commune].[ogr_fid], 
      GEOGRAPHY::STGeomFromWKB(commune.ogr_geometry.STAsBinary(),4326)
FROM [IMMATS].[dbo].[commune]

but when I ran the command to convert I got this error:

Msg 213, Level 16, State 1, Line 26 The name or column number of the values provided does not match the definition of the table.

jpw
  • 44,361
  • 6
  • 66
  • 86
lyly
  • 31
  • 1
  • 5
  • 2
    The error message is pretty clear "The name or column number of the values provided does not match the definition of the table." What are the columns of `gCOMMUNE` table? Are they only 2 - first with the same type as `[dbo].[commune].[ogr_fid]` and the second to be `GEOGRAPHY`? If not, you must specify the column names where the data sould be inserted. – Andrey Nikolov Nov 30 '18 at 09:42

1 Answers1

1

It looks like gCOMMUNE table has different number of columns than the query for selecting the data to be inserted in it. You must specify the column names in the INSERT INTO statement. Assuming there are columns named id and geom (from your clarification it turns out they are [ogr_fid] and [ogr_geog]), here is how your statement could look like:

INSERT INTO gCOMMUNE([ogr_fid], [ogr_geog])
SELECT 
      [dbo].[commune].[ogr_fid], 
      GEOGRAPHY::STGeomFromWKB(commune.ogr_geometry.STAsBinary(),4326)
FROM [IMMATS].[dbo].[commune]
Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
  • please look at my comment below – lyly Nov 30 '18 at 12:30
  • I updated my answer to reflect the real column names. – Andrey Nikolov Nov 30 '18 at 12:36
  • I use the request that you give me I get this error it is right – lyly Nov 30 '18 at 12:52
  • Msg 6522, Level 16, State 1, Line 26 A .NET Framework error occurred during the execution of the user defined routine or aggregation function "geography": System.FormatException: 24201: Latitude values ​​must be between -90 and 90 degrees. – lyly Nov 30 '18 at 12:53
  • System.FormatException: at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint (Double x, Double y, Nullable1 z, Nullable1 m) at Microsoft.SqlServer.Types.Validator.BeginFigure (Double x, Double y, Nullable1 z, Nullable1 m) at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure (Double x, Double y, Nullable1 z, Nullable1 m) at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure – lyly Nov 30 '18 at 12:54
  • Please don't add your errors as answers. You can edit your question. But the error message is clear - `Latitude values must be between -90 and 90 degrees.` so your data is not valid geography. Did you meant to use `geometry` instead? – Andrey Nikolov Nov 30 '18 at 13:15
  • I have a shapefile that contains the common of France I wanted to import to my database sql server I did that with GDAL but I need to convert to the geographic format I can not do that – lyly Nov 30 '18 at 13:29