英国城市 SQL

Question

有人有一个脚本可以将英国城镇列表插入 SQL Server 数据库吗？

Answer 1

您可以此处找到（全部？）英国的城镇。

您需要通过循环字母表中的字母来获取所有它们，有点像这样：

// PHP pseudo-code
$letters = array('a', 'b', [...], 'z');
foreach($letters as $letter) {
  $html = file_get_contents(
                "http://www.information-britain.co.uk/towns.cfm?alpha=$letter");
  $towns = $this->extractTowns($html);
  $sql = $this->generateSQL($towns);
  $this->db->query($sql);
}

public function extractTowns ($html) {
   // Code to extract towns from $html. Return an array
}

public function generateSQL(array $towns) {
    // Generate SQL here from the town-list on the form
    // INSERT INTO UkTowns (columndefinitions) VALUES ($towns[x]-columns, [...])
}

Answer 2

您可以从 Wikipedia 上的此列表在 MS Excel 中生成一些 INSERT。

这就是您要找的吗？

Answer 3

@JohnMacintyre：

我会使用您在 Wikipedia 上建议的列表中的 HTML，然后使用 RegexBuddy 构建所有插入内容，然后将其剪切并粘贴到 SQL Server 查询分析器中并运行。

Answer 4

我需要一个类似的列表，但也有纬度和经度。 我想我必须从这里复制 26 页： http:// /freepages.genealogy.rootsweb.ancestry.com/~agene/locations/index.html

通常情况下，您会让 Google 为您存储所有内容，但是当您希望在一个网页上显示 50 个地名时，需要 50 个 HTTP调用 Google 的地理编码服务会使页面加载缓慢，以使用户满意（除非我错过了一个技巧？）。

我必须维护我自己的 SQL 表。 看看我们系统上的旧数据，过去只使用了 2000 个不同的地名，所以我不认为事情会变得难以管理。

Answer 5

使用“Machine”提供的优秀链接尝试快速 VB。 我复制并粘贴了结果

这是代码

Imports System.IO

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim s As String = "Abberley | Abbey Wood | Abbots Bromley | Abbots Langley | Abbots Leigh | Abbots Ripton | Abbotsbury | Abbotsley | Aberaeron | Aberargie | Abercastle | Abercraf | Abercych | Aberdare | Aberdaron | Aberdeen | Aberdour | Aberdovey | Aberfeldy | Aberffraw | Aberfoyle | Abergavenny | Abergele | Abergorlech | Abergwili | Aberlady | Aberlour | Abernethy | Aberporth | Abersoch | Abersychan | Abertillery | Aberystwyth | Abingdon | Abinger | Abinger Hammer | Abington | Aboyne | Abram | Abridge | Acaster Malbis | Accrington | Acharacle | Achiltibuie | Achnasheen | Acklam | Ackworth | Acle | Acocks Green | Acomb | Acrefair | Acton | Acton Bridge | Adare | Adderbury | Addingham | Addington | Addlestone | Adlington | Adstock | Aghadowey | Aghalee | Ahoghill | Airdrie | Airth | Akebar | Akeley | Albrighton | Albury | Alcester | Alconbury | Alconbury Weston | Aldbourne | Aldbrough St John | Aldbury | Aldeburgh | Aldenham | Alderbury | Alderley Edge | Aldermaston | Alderminster | Alderney | Aldersey Green | Aldershot | Aldford | Aldgate | Aldridge | Aldringham | Aldworth | Aldwych | Alexandria | Alfold | Alford | Alfreton | Alfriston | All of London | All Stretton | Allendale | Allenheads | Allerford | Allerston | Allerton | Allhallows | Allhallows-on-Sea | Allington | Alloa | Allostock | Alloway | Almeley | Almondbank | Almondsbury | Alne | Alness | Alnmouth | Alnwick | Alresford | Alrewas | Alsager | Alsagers Bank | Alston | Alstonefield | Althorne | Althorp | Altnagelvin | Alton | Alton Towers | Altrincham | Alum Bay | Alva | Alvechurch | Alvecote | Alvediston | Alveley | Alves | Alveston | Alvington | Alwoodley | Alyth | Ambergate | Amberley | Amble | Ambleside | Ambrosden | Amersham | Amesbury | Amlwch | Ammanford | Ampfield | Ampleforth | Ampthill | Amroth | Amulree | Ancaster | Anderton | Andover | Andoversford | Angle | Angmering | Annacloy | Annalong | Annan | Annbank | Anniesland | Annitsford | Anslow | Anstruther | Ansty | Antrim | Apartments all over London | Apperley Bridge | Appin | Applecross | Appledore | Appletreewick | Appley | Apsley | Arborfield | Arbroath | Archway | Arclid | Ardaneaskan | Ardbeg | Ardeley | Ardens Grafton | Ardentinny | Ardeonaig | Ardfern | Ardgay | Ardglass | Ardingly | Ardington | Ardishaig | Ardleigh | Ardmillan | Ardrishaig | Ardrossan | Ardvasar | Arisaig | Arkendale | Arkengarthdale | Arkesden | Arklow | Arlesey | Arlingham | Arlington | Armadale | Armagh | Armathwiate | Armoy | Armscote | Armthorpe | Arncliffe | Arncott | Arnold | Arnside | Aros | Arreton | Arrochar | Arthingworth | Articlave | Artigarvan | Arundel | Ascog Bay | Ascot | Ash | Ash-cum-Ridley | Ashbourne | Ashburnham Place | Ashburton | Ashcott | Ashford | Ashford in the Water | Ashford-in-the-Water | Ashill | Ashington | Ashkirk | Ashleworth | Ashley | Ashmore Green | Ashorne | Ashover | Ashprington | Ashtead | Ashton | Ashton in Makerfield | Ashton Keynes | Ashton under Lyne | Ashton-in-Makerfield | Ashton-under-Lyne | Ashurst | Ashwater | Ashwell | Ashwicke | Askam-in-Furness | Askernish | Askett | Askham | Askrigg | Aspatria | Aspley Guise | Asthall | Astmoor | Aston | Aston Cantlow | Aston Clinton | Aston Rowant | Astwood | Athelstaneford | Atherstone | Atherton | Athlone | Athy | Attleborough | Auchenblae | Auchenbowie | Auchencairn | Auchindoir | Auchinleck | Auchterarder | Auchtermuchty | Audlem | Aughnacloy | Aughrim | Aughton | Auldearn | Auldgirth | Aultbea | Aust | Austerfield | Austrey | Austwick | Avebury | Aveley | Avening | Aviemore | Avonwick | Awre | Axbridge | Axford | Axminster | Axmouth | Aycliffe | Aylesbury | Aylesford | Aylesham | Aylmerton | Aylsham | Aymestrey | Aynho | Ayot St Lawrence | Ayr | Aysgarth | Ayside |"
        Dim cities() As String = s.Split("|")

        Dim oWrite As System.IO.StreamWriter
        oWrite = File.CreateText("C:\uktowns_sql.txt")

        For Each city As String In cities
            oWrite.WriteLine("INSERT INTO mytable values(" & """" & city.Trim() & """" & ")")
        Next

        oWrite.Flush()
        oWrite.Close()


    End Sub

End Class

这是结果。 只需更改表名即可

INSERT INTO mytable values("Abberley")
INSERT INTO mytable values("Abbey Wood")
INSERT INTO mytable values("Abbots Bromley")
INSERT INTO mytable values("Abbots Langley")
INSERT INTO mytable values("Abbots Leigh")
INSERT INTO mytable values("Abbots Ripton")
INSERT INTO mytable values("Abbotsbury")
INSERT INTO mytable values("Abbotsley")
INSERT INTO mytable values("Aberaeron")
INSERT INTO mytable values("Aberargie")
INSERT INTO mytable values("Abercastle")
INSERT INTO mytable values("Abercraf")
INSERT INTO mytable values("Abercych")
INSERT INTO mytable values("Aberdare")
INSERT INTO mytable values("Aberdaron")
INSERT INTO mytable values("Aberdeen")
INSERT INTO mytable values("Aberdour")
INSERT INTO mytable values("Aberdovey")
INSERT INTO mytable values("Aberfeldy")
INSERT INTO mytable values("Aberffraw")
INSERT INTO mytable values("Aberfoyle")
INSERT INTO mytable values("Abergavenny")
INSERT INTO mytable values("Abergele")
INSERT INTO mytable values("Abergorlech")
INSERT INTO mytable values("Abergwili")
INSERT INTO mytable values("Aberlady")
INSERT INTO mytable values("Aberlour")
INSERT INTO mytable values("Abernethy")
INSERT INTO mytable values("Aberporth")
INSERT INTO mytable values("Abersoch")
INSERT INTO mytable values("Abersychan")
INSERT INTO mytable values("Abertillery")
INSERT INTO mytable values("Aberystwyth")
INSERT INTO mytable values("Abingdon")
INSERT INTO mytable values("Abinger")
INSERT INTO mytable values("Abinger Hammer")
INSERT INTO mytable values("Abington")
INSERT INTO mytable values("Aboyne")
INSERT INTO mytable values("Abram")
INSERT INTO mytable values("Abridge")
INSERT INTO mytable values("Acaster Malbis")
INSERT INTO mytable values("Accrington")
INSERT INTO mytable values("Acharacle")
INSERT INTO mytable values("Achiltibuie")
INSERT INTO mytable values("Achnasheen")
INSERT INTO mytable values("Acklam")
INSERT INTO mytable values("Ackworth")
INSERT INTO mytable values("Acle")
INSERT INTO mytable values("Acocks Green")
INSERT INTO mytable values("Acomb")
INSERT INTO mytable values("Acrefair")
INSERT INTO mytable values("Acton")
INSERT INTO mytable values("Acton Bridge")
INSERT INTO mytable values("Adare")
INSERT INTO mytable values("Adderbury")
INSERT INTO mytable values("Addingham")
INSERT INTO mytable values("Addington")
INSERT INTO mytable values("Addlestone")
INSERT INTO mytable values("Adlington")
INSERT INTO mytable values("Adstock")
INSERT INTO mytable values("Aghadowey")
INSERT INTO mytable values("Aghalee")
INSERT INTO mytable values("Ahoghill")
INSERT INTO mytable values("Airdrie")
INSERT INTO mytable values("Airth")
INSERT INTO mytable values("Akebar")
INSERT INTO mytable values("Akeley")
INSERT INTO mytable values("Albrighton")
INSERT INTO mytable values("Albury")
INSERT INTO mytable values("Alcester")
INSERT INTO mytable values("Alconbury")
INSERT INTO mytable values("Alconbury Weston")
INSERT INTO mytable values("Aldbourne")
INSERT INTO mytable values("Aldbrough St John")
INSERT INTO mytable values("Aldbury")
INSERT INTO mytable values("Aldeburgh")
INSERT INTO mytable values("Aldenham")
INSERT INTO mytable values("Alderbury")
INSERT INTO mytable values("Alderley Edge")
INSERT INTO mytable values("Aldermaston")
INSERT INTO mytable values("Alderminster")
INSERT INTO mytable values("Alderney")
INSERT INTO mytable values("Aldersey Green")
INSERT INTO mytable values("Aldershot")
INSERT INTO mytable values("Aldford")
INSERT INTO mytable values("Aldgate")
INSERT INTO mytable values("Aldridge")
INSERT INTO mytable values("Aldringham")
INSERT INTO mytable values("Aldworth")
INSERT INTO mytable values("Aldwych")
INSERT INTO mytable values("Alexandria")
INSERT INTO mytable values("Alfold")
INSERT INTO mytable values("Alford")
INSERT INTO mytable values("Alfreton")
INSERT INTO mytable values("Alfriston")
INSERT INTO mytable values("All of London")
INSERT INTO mytable values("All Stretton")
INSERT INTO mytable values("Allendale")
INSERT INTO mytable values("Allenheads")
INSERT INTO mytable values("Allerford")
INSERT INTO mytable values("Allerston")
INSERT INTO mytable values("Allerton")
INSERT INTO mytable values("Allhallows")
INSERT INTO mytable values("Allhallows-on-Sea")
INSERT INTO mytable values("Allington")
INSERT INTO mytable values("Alloa")
INSERT INTO mytable values("Allostock")
INSERT INTO mytable values("Alloway")
INSERT INTO mytable values("Almeley")
INSERT INTO mytable values("Almondbank")
INSERT INTO mytable values("Almondsbury")
INSERT INTO mytable values("Alne")
INSERT INTO mytable values("Alness")
INSERT INTO mytable values("Alnmouth")
INSERT INTO mytable values("Alnwick")
INSERT INTO mytable values("Alresford")
INSERT INTO mytable values("Alrewas")
INSERT INTO mytable values("Alsager")
INSERT INTO mytable values("Alsagers Bank")
INSERT INTO mytable values("Alston")
INSERT INTO mytable values("Alstonefield")
INSERT INTO mytable values("Althorne")
INSERT INTO mytable values("Althorp")
INSERT INTO mytable values("Altnagelvin")
INSERT INTO mytable values("Alton")
INSERT INTO mytable values("Alton Towers")
INSERT INTO mytable values("Altrincham")
INSERT INTO mytable values("Alum Bay")
INSERT INTO mytable values("Alva")
INSERT INTO mytable values("Alvechurch")
INSERT INTO mytable values("Alvecote")
INSERT INTO mytable values("Alvediston")
INSERT INTO mytable values("Alveley")
INSERT INTO mytable values("Alves")
INSERT INTO mytable values("Alveston")
INSERT INTO mytable values("Alvington")
INSERT INTO mytable values("Alwoodley")
INSERT INTO mytable values("Alyth")
INSERT INTO mytable values("Ambergate")
INSERT INTO mytable values("Amberley")
INSERT INTO mytable values("Amble")
INSERT INTO mytable values("Ambleside")
INSERT INTO mytable values("Ambrosden")
INSERT INTO mytable values("Amersham")
INSERT INTO mytable values("Amesbury")
INSERT INTO mytable values("Amlwch")
INSERT INTO mytable values("Ammanford")
INSERT INTO mytable values("Ampfield")
INSERT INTO mytable values("Ampleforth")
INSERT INTO mytable values("Ampthill")
INSERT INTO mytable values("Amroth")
INSERT INTO mytable values("Amulree")
INSERT INTO mytable values("Ancaster")
INSERT INTO mytable values("Anderton")
INSERT INTO mytable values("Andover")
INSERT INTO mytable values("Andoversford")
INSERT INTO mytable values("Angle")
INSERT INTO mytable values("Angmering")
INSERT INTO mytable values("Annacloy")
INSERT INTO mytable values("Annalong")
INSERT INTO mytable values("Annan")
INSERT INTO mytable values("Annbank")
INSERT INTO mytable values("Anniesland")
INSERT INTO mytable values("Annitsford")
INSERT INTO mytable values("Anslow")
INSERT INTO mytable values("Anstruther")
INSERT INTO mytable values("Ansty")
INSERT INTO mytable values("Antrim")
INSERT INTO mytable values("Apartments all over London")
INSERT INTO mytable values("Apperley Bridge")
INSERT INTO mytable values("Appin")
INSERT INTO mytable values("Applecross")
INSERT INTO mytable values("Appledore")
INSERT INTO mytable values("Appletreewick")
INSERT INTO mytable values("Appley")
INSERT INTO mytable values("Apsley")
INSERT INTO mytable values("Arborfield")
INSERT INTO mytable values("Arbroath")
INSERT INTO mytable values("Archway")
INSERT INTO mytable values("Arclid")
INSERT INTO mytable values("Ardaneaskan")
INSERT INTO mytable values("Ardbeg")
INSERT INTO mytable values("Ardeley")
INSERT INTO mytable values("Ardens Grafton")
INSERT INTO mytable values("Ardentinny")
INSERT INTO mytable values("Ardeonaig")
INSERT INTO mytable values("Ardfern")
INSERT INTO mytable values("Ardgay")
INSERT INTO mytable values("Ardglass")
INSERT INTO mytable values("Ardingly")
INSERT INTO mytable values("Ardington")
INSERT INTO mytable values("Ardishaig")
INSERT INTO mytable values("Ardleigh")
INSERT INTO mytable values("Ardmillan")
INSERT INTO mytable values("Ardrishaig")
INSERT INTO mytable values("Ardrossan")
INSERT INTO mytable values("Ardvasar")
INSERT INTO mytable values("Arisaig")
INSERT INTO mytable values("Arkendale")
INSERT INTO mytable values("Arkengarthdale")
INSERT INTO mytable values("Arkesden")
INSERT INTO mytable values("Arklow")
INSERT INTO mytable values("Arlesey")
INSERT INTO mytable values("Arlingham")
INSERT INTO mytable values("Arlington")
INSERT INTO mytable values("Armadale")
INSERT INTO mytable values("Armagh")
INSERT INTO mytable values("Armathwiate")
INSERT INTO mytable values("Armoy")
INSERT INTO mytable values("Armscote")
INSERT INTO mytable values("Armthorpe")
INSERT INTO mytable values("Arncliffe")
INSERT INTO mytable values("Arncott")
INSERT INTO mytable values("Arnold")
INSERT INTO mytable values("Arnside")
INSERT INTO mytable values("Aros")
INSERT INTO mytable values("Arreton")
INSERT INTO mytable values("Arrochar")
INSERT INTO mytable values("Arthingworth")
INSERT INTO mytable values("Articlave")
INSERT INTO mytable values("Artigarvan")
INSERT INTO mytable values("Arundel")
INSERT INTO mytable values("Ascog Bay")
INSERT INTO mytable values("Ascot")
INSERT INTO mytable values("Ash")
INSERT INTO mytable values("Ash-cum-Ridley")
INSERT INTO mytable values("Ashbourne")
INSERT INTO mytable values("Ashburnham Place")
INSERT INTO mytable values("Ashburton")
INSERT INTO mytable values("Ashcott")
INSERT INTO mytable values("Ashford")
INSERT INTO mytable values("Ashford in the Water")
INSERT INTO mytable values("Ashford-in-the-Water")
INSERT INTO mytable values("Ashill")
INSERT INTO mytable values("Ashington")
INSERT INTO mytable values("Ashkirk")
INSERT INTO mytable values("Ashleworth")
INSERT INTO mytable values("Ashley")
INSERT INTO mytable values("Ashmore Green")
INSERT INTO mytable values("Ashorne")
INSERT INTO mytable values("Ashover")
INSERT INTO mytable values("Ashprington")
INSERT INTO mytable values("Ashtead")
INSERT INTO mytable values("Ashton")
INSERT INTO mytable values("Ashton in Makerfield")
INSERT INTO mytable values("Ashton Keynes")
INSERT INTO mytable values("Ashton under Lyne")
INSERT INTO mytable values("Ashton-in-Makerfield")
INSERT INTO mytable values("Ashton-under-Lyne")
INSERT INTO mytable values("Ashurst")
INSERT INTO mytable values("Ashwater")
INSERT INTO mytable values("Ashwell")
INSERT INTO mytable values("Ashwicke")
INSERT INTO mytable values("Askam-in-Furness")
INSERT INTO mytable values("Askernish")
INSERT INTO mytable values("Askett")
INSERT INTO mytable values("Askham")
INSERT INTO mytable values("Askrigg")
INSERT INTO mytable values("Aspatria")
INSERT INTO mytable values("Aspley Guise")
INSERT INTO mytable values("Asthall")
INSERT INTO mytable values("Astmoor")
INSERT INTO mytable values("Aston")
INSERT INTO mytable values("Aston Cantlow")
INSERT INTO mytable values("Aston Clinton")
INSERT INTO mytable values("Aston Rowant")
INSERT INTO mytable values("Astwood")
INSERT INTO mytable values("Athelstaneford")
INSERT INTO mytable values("Atherstone")
INSERT INTO mytable values("Atherton")
INSERT INTO mytable values("Athlone")
INSERT INTO mytable values("Athy")
INSERT INTO mytable values("Attleborough")
INSERT INTO mytable values("Auchenblae")
INSERT INTO mytable values("Auchenbowie")
INSERT INTO mytable values("Auchencairn")
INSERT INTO mytable values("Auchindoir")
INSERT INTO mytable values("Auchinleck")
INSERT INTO mytable values("Auchterarder")
INSERT INTO mytable values("Auchtermuchty")
INSERT INTO mytable values("Audlem")
INSERT INTO mytable values("Aughnacloy")
INSERT INTO mytable values("Aughrim")
INSERT INTO mytable values("Aughton")
INSERT INTO mytable values("Auldearn")
INSERT INTO mytable values("Auldgirth")
INSERT INTO mytable values("Aultbea")
INSERT INTO mytable values("Aust")
INSERT INTO mytable values("Austerfield")
INSERT INTO mytable values("Austrey")
INSERT INTO mytable values("Austwick")
INSERT INTO mytable values("Avebury")
INSERT INTO mytable values("Aveley")
INSERT INTO mytable values("Avening")
INSERT INTO mytable values("Aviemore")
INSERT INTO mytable values("Avonwick")
INSERT INTO mytable values("Awre")
INSERT INTO mytable values("Axbridge")
INSERT INTO mytable values("Axford")
INSERT INTO mytable values("Axminster")
INSERT INTO mytable values("Axmouth")
INSERT INTO mytable values("Aycliffe")
INSERT INTO mytable values("Aylesbury")
INSERT INTO mytable values("Aylesford")
INSERT INTO mytable values("Aylesham")
INSERT INTO mytable values("Aylmerton")
INSERT INTO mytable values("Aylsham")
INSERT INTO mytable values("Aymestrey")
INSERT INTO mytable values("Aynho")
INSERT INTO mytable values("Ayot St Lawrence")
INSERT INTO mytable values("Ayr")
INSERT INTO mytable values("Aysgarth")
INSERT INTO mytable values("Ayside")
INSERT INTO mytable values("")