<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
	<channel>
		<title><![CDATA[Latest posts for the topic "Hierarchical list of UK counties -> Cities"]]></title>
		<link>http://forum.geonames.org/gforum/posts/list/4.page</link>
		<description><![CDATA[Latest messages posted in the topic "Hierarchical list of UK counties -> Cities"]]></description>
		<generator>JForum - http://www.jforum.net</generator>
			<item>
				<title>Hierarchical list of UK counties -&amp;gt; Cities</title>
				<description><![CDATA[ Hello,
I'm trying to create a database table which has a countryid column, a parentcountryid column, and a countryname column. The counties will all have their parent Id set to null (or set to a single entry for 'UK'), and all the cities have their parent Ids pointing to their respective counties.

Can anyone help in using the geonames lists to make this table? The web service looks like it almost works, as you can drill down from country into county:
http://ws.geonames.org/children?geonameId=6269131
However, trying to get to cities just leaves odd regions instead, e.g. drilling down into staffordshire gives:
http://ws.geonames.org/children?geonameId=2637141
Which doesn't include the cities, and the granularity seems to stop there.

I note the GB download list does have all the cities listed, but does not seem to link them to a county. Can anyone help?]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1043.page#4575</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1043.page#4575</link>
				<pubDate><![CDATA[Sun, 31 Aug 2008 00:20:55]]> GMT</pubDate>
				<author><![CDATA[ crundy]]></author>
			</item>
			<item>
				<title>Re:Hierarchical list of UK counties -&amp;gt; Cities</title>
				<description><![CDATA[ Crundy

The county information is in the adm2 column of the dump. For missing county links you are welcome to help add the information.

Best

Marc]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1043.page#4578</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1043.page#4578</link>
				<pubDate><![CDATA[Sun, 31 Aug 2008 07:51:42]]> GMT</pubDate>
				<author><![CDATA[ marc]]></author>
			</item>
			<item>
				<title>Re:Hierarchical list of UK counties -&amp;gt; Cities</title>
				<description><![CDATA[ OK, where does the adm2 column map to? Is there a specific list of counties with this column?]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1043.page#4579</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1043.page#4579</link>
				<pubDate><![CDATA[Sun, 31 Aug 2008 10:45:36]]> GMT</pubDate>
				<author><![CDATA[ crundy]]></author>
			</item>
			<item>
				<title>Re:Hierarchical list of UK counties -&amp;gt; Cities</title>
				<description><![CDATA[ It maps to ADM2 records with the same adm2 value. The table is self referencing.]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1043.page#4580</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1043.page#4580</link>
				<pubDate><![CDATA[Sun, 31 Aug 2008 17:46:15]]> GMT</pubDate>
				<author><![CDATA[ marc]]></author>
			</item>
			<item>
				<title>Re:Hierarchical list of UK counties -&amp;gt; Cities</title>
				<description><![CDATA[ OK, I'm having trouble creating the table. I want to first import the counties and then import the cities. I'm getting primary key contraint violations on the city import. I have imported the GB list into a table called GB and this is the script I'm using "sql srver 2005":

-- counties:
insert into
	dbo.tblLocation
select
	geonameid,
	NULL,
	[name]
FROM
	GB
WHERE
	featureclass='A' and featurecode='ADM2' and cc2 is not null order by [name]

--cities:
insert into
	dbo.tblLocation
SELECT
	ci.geonameid,
	co.geonameid,
	ci.[name]
FROM
	GB AS co
INNER JOIN
	GB AS ci
	ON co.admin2=ci.admin2
	AND co.featureclass='A' and co.featurecode='ADM2'
	AND ci.featureclass <> 'A' and ci.featurecode <> 'ADM2' and ci.cc2 is not null
ORDER BY
	ci.[name]

Any ideas? I must be missing a column restriction somewhere.]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1043.page#4614</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1043.page#4614</link>
				<pubDate><![CDATA[Sat, 6 Sep 2008 01:18:25]]> GMT</pubDate>
				<author><![CDATA[ crundy]]></author>
			</item>
			<item>
				<title>Re:Hierarchical list of UK counties -&amp;gt; Cities</title>
				<description><![CDATA[ OK, got it now. I've got a SQL script to create and populate the table. Is it OK to redistribute it? If so then I'll post it here for anyone else who wants it.]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1043.page#4676</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1043.page#4676</link>
				<pubDate><![CDATA[Mon, 15 Sep 2008 00:10:11]]> GMT</pubDate>
				<author><![CDATA[ crundy]]></author>
			</item>
	</channel>
</rss>