vba 如何在 Google Earth 中创建 kml 对地址进行地理编码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15525071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create kml to geocode addresses in Google Earth?
提问by Bryan
If you have a .csv file with address information in it, you can drag/drop it onto the Google Earth (henceforth referred to as GE) and a wizard will come up so you can assign fields and GE will then start geocoding the locations and create points (latitude and longitude) for each address. You can then export the geocoded file as a kml and get the lat/long for each point. This process however has a limit of 2500 rows.
如果您有一个包含地址信息的 .csv 文件，您可以将其拖放到 Google 地球（以下称为 GE）上，然后会出现一个向导，您可以分配字段，然后 GE 将开始对位置进行地理编码并为每个地址创建点（纬度和经度）。然后，您可以将地理编码文件导出为 kml 并获取每个点的纬度/经度。但是，此过程有 2500 行的限制。
I have heard that a kml (or maybe an xml) file can be created and pulled into GE to start the geocoding process and that this method doesn't have a row limit. Can someone please help me figure out how to do this? I have tried the following code, which does successfully import into GE but no points are created.
我听说可以创建一个 kml（或者一个 xml）文件并将其拉入 GE 以启动地理编码过程，并且这种方法没有行限制。有人可以帮我弄清楚如何做到这一点吗？我尝试了以下代码，它成功导入到 GE 中，但没有创建点。
I am aware of some Google Maps API that can do something similar and has a row limit of 100,000 rows a day for business use but I would really like to get the process working with GE.
我知道一些谷歌地图 API 可以做类似的事情，并且每天有 100,000 行的行限制供商业使用，但我真的很想让这个过程与 GE 一起工作。
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">
<Document>
<name>GEImport1.csv</name>
<Schema name="GEImport1" id="S_GEImport1_S">
<SimpleField type="string" name="Adress"><displayName><b>Adress</b></displayName>
</SimpleField>
</Schema>
<Style id="hlightPointStyle">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/shapes/placemark_circle_highlight.png</href>
</Icon>
</IconStyle>
<BalloonStyle>
<text><![CDATA[<table border="0">
<tr><td><b>Adress</b></td><td>$[GEImport1/Adress]</td></tr>
</table>
]]></text>
</BalloonStyle>
</Style>
<Style id="normPointStyle">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/shapes/placemark_circle.png</href>
</Icon>
</IconStyle>
<BalloonStyle>
<text><![CDATA[<table border="0">
<tr><td><b>Adress</b></td><td>$[GEImport1/Adress]</td></tr>
</table>
]]></text>
</BalloonStyle>
</Style>
<StyleMap id="pointStyleMap">
<Pair>
<key>normal</key>
<styleUrl>#normPointStyle</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#hlightPointStyle</styleUrl>
</Pair>
</StyleMap>
<Folder id="layer 0">
<name>GEImport1</name>
<visibility>0</visibility>
<Placemark>
<visibility>0</visibility>
<styleUrl>#pointStyleMap</styleUrl>
<ExtendedData>
<SchemaData schemaUrl="#S_GEImport1_S">
<SimpleData name="Adress">6212 GATUN CT Port ST Lucie, FL</SimpleData>
</SchemaData>
</ExtendedData>
</Placemark>
<Placemark>
<visibility>0</visibility>
<styleUrl>#pointStyleMap</styleUrl>
<ExtendedData>
<SchemaData schemaUrl="#S_GEImport1_S">
<SimpleData name="Adress">6213 DIANA CT Port ST Lucie, FL</SimpleData>
</SchemaData>
</ExtendedData>
</Placemark>
<Placemark>
<visibility>0</visibility>
<styleUrl>#pointStyleMap</styleUrl>
<ExtendedData>
<SchemaData schemaUrl="#S_GEImport1_S">
<SimpleData name="Adress">6213 DUKE CIR Port ST Lucie, FL</SimpleData>
</SchemaData>
</ExtendedData>
</Placemark>
</Folder>
</Document>
</kml>
Edit: Final Result
JasonM1's solutions worked great!
I ended up with the following format for the kml file:
编辑：最终结果
JasonM1 的解决方案效果很好！我最终得到了 kml 文件的以下格式：
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">
<Document>
<name>AddressImport</name>
<Style id="normPointStyle">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/shapes/placemark_circle.png</href>
</Icon>
</IconStyle>
<BalloonStyle>
<text><![CDATA[<table border="0">
<tr><td><b>Address</b></td><td>$[address]</td></tr>
</table>]]>
</text>
</BalloonStyle>
</Style>
<StyleMap id="pointStyleMap">
<Pair>
<key>normal</key>
<styleUrl>#normPointStyle</styleUrl>
</Pair>
</StyleMap>
<Folder id="layer 0">
<name>AddressImport</name>
<visibility>1</visibility>
<Placemark>
<visibility>1</visibility>
<address>9994 CHADWICK DR Port ST Lucie, FL</address>
<styleUrl>#pointStyleMap</styleUrl>
</Placemark>
<Placemark>
<visibility>1</visibility>
<address>9995 AMBROSE WAY Port ST Lucie, FL</address>
<styleUrl>#pointStyleMap</styleUrl>
</Placemark>
<Placemark>
<visibility>1</visibility>
<address>9997 STONEGATE DR Port ST Lucie, FL</address>
<styleUrl>#pointStyleMap</styleUrl>
</Placemark>
</Folder>
</Document>
</kml>
Also, I used the following Excel VBA code to automatically create the kml file:
此外，我使用以下 Excel VBA 代码自动创建了 kml 文件：
Sub CreateCSV_FSO()
Dim objFSO
Dim objTF
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim strTmp As String
Dim lFnum As Long
Dim sFilePath As String
Dim i As Long
Dim x As Integer 'used to represent the column that contains the address
sFilePath = ActiveWorkbook.Path & "\AddressImport.kml"
Set objFSO = CreateObject("scripting.filesystemobject")
Set objTF = objFSO.createtextfile(sFilePath, True, False)
'Header information
objTF.writeline "<?xml version=""1.0"" encoding=""UTF-8""?>"
objTF.writeline "<kml xmlns=""http://www.opengis.net/kml/2.2"" xmlns:gx=""http://www.google.com/kml/ext/2.2"" xmlns:kml=""http://www.opengis.net/kml/2.2"" xmlns:atom=""http://www.w3.org/2005/Atom"">"
objTF.writeline " <Document>"
objTF.writeline "<name>AddressImport</name>"
objTF.writeline " <Style id=""normPointStyle"">"
objTF.writeline " <IconStyle>"
objTF.writeline " <Icon>"
objTF.writeline " <href>http://maps.google.com/mapfiles/kml/shapes/placemark_circle.png</href>"
objTF.writeline " </Icon>"
objTF.writeline " </IconStyle>"
objTF.writeline " <BalloonStyle>"
objTF.writeline " <text><![CDATA[<table border=""0"">"
objTF.writeline " <tr><td><b>Address</b></td><td>$[address]</td></tr>"
objTF.writeline " </table>]]>"
objTF.writeline " </text>"
objTF.writeline " </BalloonStyle>"
objTF.writeline " </Style>"
objTF.writeline " <StyleMap id=""pointStyleMap"">"
objTF.writeline " <Pair>"
objTF.writeline " <key>normal</key>"
objTF.writeline " <styleUrl>#normPointStyle</styleUrl>"
objTF.writeline " </Pair>"
objTF.writeline " </StyleMap>"
objTF.writeline " <Folder id=""layer 0"">"
objTF.writeline " <name>AddressImport</name>"
objTF.writeline " <visibility>1</visibility>"
'input the number representative of the column that contains the address
x = 7 'a=1,b=2,c=3,d=4.....etc.
i = 2
While Cells(i, x) <> ""
strTmp = ""
strTmp = Cells(i, x)
strTmp = Replace(strTmp, "&", "and")
objTF.writeline " <Placemark>"
objTF.writeline " <visibility>1</visibility>"
objTF.writeline " <address>" & strTmp & "</address>"
objTF.writeline " <styleUrl>#pointStyleMap</styleUrl>"
objTF.writeline " </Placemark>"
strTmp = Cells(i, x)
i = i + 1
Wend
objTF.writeline " </Folder>"
objTF.writeline "</Document>"
objTF.writeline "</kml>"
objTF.Close
Set objFSO = Nothing
MsgBox "Done!", vbOKOnly
End Sub
采纳答案by JasonM1
Use the <address> element in KML in which Google Earth will geocode the address automatically for you.
使用KML 中的 < address> 元素，Google 地球将在其中自动为您对地址进行地理编码。
<Placemark>
<visibility>0</visibility>
<address>6213 DUKE CIR Port ST Lucie, FL</address>
<styleUrl>#pointStyleMap</styleUrl>
</Placemark>
You can use the <address>tag to specify the location of a point instead of using latitude and longitude coordinates. (However, if a <Point>is provided, it takes precedence over the <address>.)
您可以使用<address>标记来指定点的位置，而不是使用纬度和经度坐标。（但是，如果<Point>提供了 a ，则它优先于<address>。）
Also, you no longer would need the Schema and AdressSchemaData elements but can directly inline the $[address]field in the BalloonStyle text:
此外，您不再需要 Schema 和AdressSchemaData 元素，但可以直接内联BalloonStyle 文本中的$[address]字段：
<BalloonStyle>
<text><![CDATA[<table border="0">
<tr><td><b>Adress</b></td><td>$[address]</td></tr>
</table>]]>
</text>
</BalloonStyle>
After you load the KML file in Google Earth and save it, you'll notice that Google Earth will change it to the following with the coordinates filled-in:
在 Google Earth 中加载 KML 文件并保存后，您会注意到 Google Earth 会将其更改为以下内容并填充坐标：
<Placemark>
<address>6213 DUKE CIR Port ST Lucie, FL</address>
<styleUrl>#pointStyleMap</styleUrl>
<MultiGeometry>
<Point>
<coordinates>-80.36086,27.366379,0</coordinates>
</Point>
<LinearRing>
<coordinates>
-80.36086,27.366379,0 -80.36086,27.366379,0 -80.36086,27.366379,0 -80.36086,27.366379,0 -80.36086,27.366379,0
</coordinates>
</LinearRing>
</MultiGeometry>
</Placemark>
