Population by P class

giacomong — GMT

Hi, I've imported allCountries.txt in a sql server and I'm trying to extract a detailed hierarchy and population of each city/subsection and then handle the data in SSRS. This is the query I'm using: Code:

declare @country as varchar(2) = 'FR'

select	coalesce(adm1.admin1_code,'') as Admin1Cod,
		coalesce(adm1.asciiname,'')  as Admin1Descr,
		coalesce(adm2.admin2_code,'') as Admin2Cod,
		coalesce(adm2.asciiname,'')  as Admin2Descr,
		coalesce(adm3.admin3_code,'') as Admin3Cod,
		coalesce(adm3.asciiname,'')  as Admin3Descr,
		coalesce(adm4.admin4_code,'') as Admin4Cod,
		coalesce(adm4.asciiname,'')  as Admin4Descr,
		p.*
from	geoname_allCountries as p
		left outer join	geoname_allCountries as adm1 on adm1.feature_code = 'ADM1' and p.country_code = adm1.country_code and p.admin1_code = adm1.admin1_code
		left outer join	geoname_allCountries as adm2 on adm2.feature_code = 'ADM2' and p.country_code = adm2.country_code and p.admin1_code = adm2.admin1_code and p.admin2_code = adm2.admin2_code
		left outer join	geoname_allCountries as adm3 on adm3.feature_code = 'ADM3' and p.country_code = adm3.country_code and p.admin1_code = adm3.admin1_code and p.admin2_code = adm3.admin2_code and p.admin3_code = adm3.admin3_code
		left outer join	geoname_allCountries as adm4 on adm4.feature_code = 'ADM4' and p.country_code = adm4.country_code and p.admin1_code = adm4.admin1_code and p.admin2_code = adm4.admin2_code and p.admin3_code = adm4.admin3_code and p.admin4_code = adm4.admin4_code
where	p.feature_class in ('P') 
		and p.admin1_code not in ('00','')	-- geonames with with wrong administrative divisions
		and p.country_code = @country
order by	1,3,5,7,9

Looking at the adm3 "Arrondissement de Marseille", adm4 "Marseille" I found that there's the detail of each 'P' class but also the adm5 as PPL, example: Code:

asciiname		latitude	longitude	feature_class	feature_code	country_code	admin1_code	admin2_code	admin3_code	admin4_code	population
Marseille		43.296950	5.381070	P		PPLA		FR		93		13		133		13055		794811
Beaumont		43.310910	5.434200	P		PPL		FR		93		13		133		13055		0
Arenc			43.300000	5.366670	P		PPLX		FR		93		13		133		13055		0
Marseille 01		43.298140	5.384070	P		PPL		FR		93		13		133		13055		40919
Marseille 02		43.296640	5.370340	P		PPL		FR		93		13		133		13055		25779

"Marseille 01" is a subsection of "Marseille" that has other children, so aggregating the admin4 to sum the population the value gets doubled. Am I doing something wrong or this is happening because of the lack of adm5 ?

Latest posts for the topic "Population by P class"

Population by P class