如何从csv文件大容量导入到sql server作为空间数据类型？

Question

希望创建一个与空间数据类型相对应的列的sql db。

Answer 1

做了一些研究，发现了这篇文章：

将纬度/经度（晚/长）转换为地理点

因此，正如本文所述，我已经创建了表，并使用给定的脚本插入了一些测试数据：

CREATE TABLE [dbo].[Landmark] (
    [ID]                INT IDENTITY(1, 1),
    [LandmarkName]      VARCHAR(100),
    [Location]          VARCHAR(50),
    [Latitude]          FLOAT,
    [Longitude]         FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
       ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
       ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
       ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
       ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
       ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
       ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GO

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] AS geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GO

SELECT *
FROM dbo.Landmark

Statue of Liberty| New York, USA| 40.689168|-74.044563
Eiffel Tower| Paris, France| 48.858454| 2.294694
Leaning Tower of Pisa| Pisa, Italy| 43.72294| 10.396604
Great Pyramids of Giza| Cairo, Egypt| 29.978989| 31.134632
Sydney Opera House| Syndey, Australia| -33.856651| 151.214967
Taj Mahal| Agra, India| 27.175047| 78.042042
Colosseum| Rome, Italy| 41.890178| 12.492378

因此，我使用OPENROWSET打开CSV文件，并将其格式化为行，而不是一个长字符串（不幸的是，OPENROWSET就是这样读取CSV文件的）。我使用了splitstring函数:https://stackoverflow.com/a/19935594/3680098

现在我需要把这些好友变成列，而不是一个字符串。我使用了SO上提供的答案:https://stackoverflow.com/a/15108499/3680098

总结一下，这是一个问题：

SELECT LTRIM(RTRIM(xmlValue.value('/values[1]/value[1]','nvarchar(100)'))) AS LandmarkName
    , LTRIM(RTRIM(xmlValue.value('/values[1]/value[2]','nvarchar(100)'))) AS Location
    , LTRIM(RTRIM(xmlValue.value('/values[1]/value[3]','nvarchar(20)'))) AS Lon
    , LTRIM(RTRIM(xmlValue.value('/values[1]/value[4]','nvarchar(20)'))) AS Lat
    , GEOGRAPHY::STPointFromText('POINT(' + xmlValue.value('/values[1]/value[4]','nvarchar(20)') + ' ' + xmlValue.value('/values[1]/value[3]','nvarchar(20)') + ')', 4326)
FROM dbo.SplitString((SELECT Document.* FROM OPENROWSET(BULK N'C:\Temp\test.csv', SINGLE_CLOB) AS Document), CHAR(10)) AS T
CROSS APPLY (SELECT CAST('<values><value>' + REPLACE(T.Value, '|', '</value><value>') + '</value></values>' AS XML)) AS T1(xmlValue);

CREATE TABLE [dbo].[Landmark] (
    [ID]                INT IDENTITY(1, 1),
    [LandmarkName]      VARCHAR(100),
    [Location]          VARCHAR(50),
    [GeoLocation]       GEOGRAPHY
)
GO

INSERT INTO dbo.Landmark (LandmarkName, Location, GeoLocation)
SELECT LTRIM(RTRIM(xmlValue.value('/values[1]/value[1]','nvarchar(100)'))) AS LandmarkName
    , LTRIM(RTRIM(xmlValue.value('/values[1]/value[2]','nvarchar(100)'))) AS Location
    , GEOGRAPHY::STPointFromText('POINT(' + xmlValue.value('/values[1]/value[4]','nvarchar(20)') + ' ' + xmlValue.value('/values[1]/value[3]','nvarchar(20)') + ')', 4326)
FROM dbo.SplitString((SELECT Document.* FROM OPENROWSET(BULK N'C:\Temp\test.csv', SINGLE_CLOB) AS Document), CHAR(10)) AS T
CROSS APPLY (SELECT CAST('<values><value>' + REPLACE(T.Value, '|', '</value><value>') + '</value></values>' AS XML)) AS T1(xmlValue)