gpt4 book ai didi

sql - 如何将地理位置批量插入到新的SQL Server 2008表中

转载 作者:行者123 更新时间:2023-12-02 05:09:27 25 4
gpt4 key购买 nike

我有一个非常大的形状文件,其中包含成百上千行的多边形和其他相关数据,例如格式化的地址和APN编号。如何在不使用Shape2SQL之类的情况下将这些数据放入具有地理位置的表中?我不能很好地为将要花费的每一行运行插入语句,最佳的解决方案是创建一个csv或格式正确的bin文件,然后进行批量插入,bcp或openrowset,但尝试尝试,请尝试,因为我可能无法获取csv文件或bin文件。有人可以帮忙吗?

以下代码是我能管理的最好的代码。

SqlGeographyBuilder sql_geography_builder = new SqlGeographyBuilder();
sql_geography_builder.SetSrid(4326);
sql_geography_builder.BeginGeography(OpenGisGeographyType.Polygon);
sql_geography_builder.BeginFigure(-84.576064, 39.414853);
sql_geography_builder.AddLine(-84.576496, 39.414800);
sql_geography_builder.AddLine(-84.576522, 39.414932);
sql_geography_builder.AddLine(-84.576528, 39.414964);
sql_geography_builder.AddLine(-84.576095, 39.415015);
sql_geography_builder.AddLine(-84.576064, 39.414853);
sql_geography_builder.EndFigure();
sql_geography_builder.EndGeography();
SqlGeography sql_geography = new SqlGeography();
sql_geography = sql_geography_builder.ConstructedGeography;


FileStream file_stream = new FileStream("C:\\PROJECTS\\test.bin", FileMode.Create);
BinaryWriter binary_writer = new BinaryWriter(file_stream);

sql_geography.Write(binary_writer);
binary_writer.Flush();

binary_writer.Close();
file_stream.Close();
file_stream.Dispose();

SqlConnection sql_connection = new SqlConnection(connection_string);
sql_connection.Open();

SqlCommand sql_command = new SqlCommand();
sql_command.Connection = sql_connection;
sql_command.CommandTimeout = 0;
sql_command.CommandType = CommandType.Text;
sql_command.CommandText = "INSERT INTO [SPATIAL_TEST].[dbo].[Table_1] ([geo]) " +
"SELECT [ors].* " +
"FROM OPENROWSET(BULK 'C:\\PROJECTS\\AMP\\test.bin', SINGLE_BLOB) AS [ors] ";
sql_command.ExecuteNonQuery();

sql_command.Dispose();
sql_connection.Close();
sql_connection.Dispose();

但这只允许我单独导入多边形-我还需要其他所有内容。

最佳答案

经过几天的头痛,我得出了没有答案的结论。甚至没有强大的ESRI也没有任何线索。值得庆幸的是,我确实提出了不同的想法。在表定义中,我创建了一个NVARCHAR(MAX)列来保存我的地理位置的WFT,并将该WFT添加到我的csv文件中,然后在批量插入之后,我运行一个表范围内的更新语句,以将WFT转换为实际的地理位置类型。还要调整csv文件以使用除之外的其他字符,以分隔WFT包含的,

SqlGeographyBuilder sql_geography_builder = new SqlGeographyBuilder();
sql_geography_builder.SetSrid(4326);
sql_geography_builder.BeginGeography(OpenGisGeographyType.Polygon);
sql_geography_builder.BeginFigure(-84.576064, 39.414853);
sql_geography_builder.AddLine(-84.576496, 39.414800);
sql_geography_builder.AddLine(-84.576522, 39.414932);
sql_geography_builder.AddLine(-84.576528, 39.414964);
sql_geography_builder.AddLine(-84.576095, 39.415015);
sql_geography_builder.AddLine(-84.576064, 39.414853);
sql_geography_builder.EndFigure();
sql_geography_builder.EndGeography();
SqlGeography sql_geography = new SqlGeography();
sql_geography = sql_geography_builder.ConstructedGeography;

StreamWriter stream_writer = new StreamWriter("C:\\PROJECTS\\AMP\\test.csv");
stream_writer.AutoFlush = true;
stream_writer.WriteLine("1?123 TEST AVE?" + sql_geography.ToString() + "?");
stream_writer.Flush();
stream_writer.WriteLine("2?456 TEST AVE?" + sql_geography.ToString() + "?");
stream_writer.Flush();
stream_writer.WriteLine("9?789 TEST AVE?" + sql_geography.ToString() + "?");
stream_writer.Flush();
stream_writer.Close();
stream_writer.Dispose();

SqlConnection sql_connection = new SqlConnection(STRING_SQL_CONNECTION);
sql_connection.Open();

SqlCommand sql_command = new SqlCommand();
sql_command.Connection = sql_connection;
sql_command.CommandTimeout = 0;
sql_command.CommandType = CommandType.Text;
sql_command.CommandText = "BULK INSERT [SPATIAL_TEST].[dbo].[Table_1] " +
"FROM 'C:\\PROJECTS\\AMP\\test.csv' " +
"WITH (FIELDTERMINATOR = '?', ROWTERMINATOR = '\n') " +
"" +
"UPDATE [SPATIAL_TEST].[dbo].[Table_1] " +
"SET [geo] = geography::STPolyFromText([geo_string], 4326) ";
sql_command.ExecuteNonQuery();

sql_command.Dispose();
sql_connection.Close();
sql_connection.Dispose();

MessageBox.Show("DONE");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }

关于sql - 如何将地理位置批量插入到新的SQL Server 2008表中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7165482/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com