Tunisia Election Data

عرض البيانات هذا  يسلط الضوء على حجم الدعم الانتخابي لحركة النهضة على مستوى الدائرة الانتخابية    في انتخابات المجلس الوطني التأسيسي  لسنة 2011. فازت النهضة ب 89 مقعدا وحصلت على أعلى نسبة من الأصوات في 26 من 27 دائرة انتخابية في تونس. في سيدي بوزيد، تحصلت  النهضة على  ثاني أعلى نسبة من الأصوات بعد العريضة الشعبية . كما تحصلت  النهضة أيضا أعلى نسبة من الأصوات على مستوى  جميع الدوائر الانتخابية  خارج البلاد

جداول البيانات لنتائج الانتخابات لصالح حركة النهضة  في  انتخابات المجلس  الوطني التأسيسي لسنة  2011

www

Tunisia is made up of 24 governorates, which are subdivided into 264 delegations. This map visualizes the disparity between actively registered voters and eligible voters by delegation. Actively registered voters meet the age and citizenship requirements for voting and have registered to vote. Eligible voters meet the requirements to vote, but have not actively registered to vote. </p>

Make sure you have read the documentation regarding installing and setting up a PostGIS-enabled database.

Importing data to PostGIS

This map is comprised of two datasets - delegation shapefiles and tabular eligibility + registration data. To import the shapefiles see the setup.md for instructions on importing geographic data.

To import the tabular registration + eligibility data, first set up the table in the tunisia database. Run the following SQL to set up your table.

create table regis (
	district_id int,
	district text,
	delegation text, 
	actively_registred numeric,
	eligible_voters numeric,
	ratio numeric
	);

To populate the table, run the following from your command line (not the command shell)

	 cat path-to/2011data/RegistrationDistributionPerDelegation/RegistDistibutionperDelegation.csv | psql -d tunisia -c "COPY regis FROM STDIN WITH DELIMITER ',' CSV HEADER"

Joining spatial data to tabular data

Now that both datasets are in your postgres database, you need join the spatial data (the delegation shapefile) to the tabular data (the RegistDistibutionperDelegation.csv). To do this, run the following SQL.

create table eligibility as (
	select
		a.*,
		coalesce(cast(b.actively_registred as text), 'n/a') as actively_registred,
		coalesce(cast(b.eligible_voters as text), 'n/a') as eligible_voters,
		ratio,
		coalesce(cast(round(b.ratio) as text)||'%', 'n/a') as ratio_c
	from delegations a left join regis b
	on replace(replace(lower(cast(circo_id as text)||name_deleg), ' ', ''), '-', '') = replace(replace(replace(lower(cast(district_id as text)||delegation), ' ', ''), '-', ''), '\r', '')
	order by ratio desc
);

In addition to performing a left join on the geometry to preserve geometry where records from the tabular data do not match, this query reformats the actively_registered, eligible_voters, and ratio columns for cartographic purposes.

Note that only about 215 of the 270 or so records (rows) match. To get the rest of the records to match, run manual updates changing the names in only one table.

update regis set delegation = 'Médina' where delegation = 'Medina';

You’re now ready to style your map in TileMill. Create a new project. In TileMill create a PostGIS layer following the online documentation, specifying the eligibility table.

Paste in the appropriate html for the and tooltip (labeled ‘eligibility’) See the Eligibility TileMill project for CartoCss styles.

Description About the data Methodology
A project by Democracy International, New Rights Group and DevelopmentSeed