Adapting the census mapping application to use INEGI’s data for Mexico

INEGI, the Mexican institute for national statistics and geography, place large quantities of data in the public domain. Geographical data is now provided through an efficient map server. However most socio economic data is only available directly in the form of Excel spreadsheets from the descarga masiva site.

http://www3.inegi.org.mx/sistemas/descarga/

These data vary in quality but they do form an extremely useful for contextualising environmental research. Initial “eyeballing” of the the data is a first step in deciding how useful it may be. It would be nice to be able to quickly map the variables in a consistent manner.

Paul Ramsey, and the boundless team, provide an impressive example of how large amounts of data held in PostGIS data can be visualised using a common framework in this tutorial.

http://boundlessgeo.com/2013/09/census-mapping-made-easy/

So, how hard would it be to adapt their ground work to visualise INEGI’s data? It turns out that getting a basic application up and running is not too difficult, once the data format has been worked out. However this shows up some of the inconsistencies in the data that would require a more complex application to clean up.

inegi

The first step is to load the data into PostGIS.

This RPub document shows all the code I used to move the set of tab separated variable files into PostGIS. http://rpubs.com/dgolicher/11593

The exercise reveals some major challenges with the data. To begin with the data have a time component, but not all variables have been recorded for all the available years. There are also two layers of administrative districts, states and municipios. The distribution of the variables tends to be highly right skewed and many variables are only comparable after being adjusted for either population or land area.

However … a basic app can still be put together as a first step towards something more useful. I will not repeat the instructions provided in the excellent Boundless tutorial, but just show the changes I needed to make in order to work with these data.

Assuming that you have set up Geoserver and worked through the Census mapping tutorial, the first step in adapting it to use the INEGI data is to set up the SQL view in geoserver that normalises the data.

with stats as(
Select Avg(val) AS avg,
         Stddev(val) AS stddev
         from
(select val from inegi.data
where var_id=%varid% and mun_id>0 and yr = %yr%) s
)
select gid,m.geom,(val-avg)/stddev as normalized_data from stats,
(select * from inegi.data where var_id=%varid% and mun_id>0 and yr = %yr%) a,
inegi.municipios2010 m where m.est_id=a.est_id and m.mun_id=a.mun_id

We can use another sql view to visualise data aggregated at the state level.

with stats as(
Select Avg(val) AS avg,
         Stddev(val) AS stddev
         from
(select val from inegi.data
where var_id=%varid% and mun_id=0 and yr = %yr%) s
)
select gid,m.geom,(val-avg)/stddev as normalized_data from stats,
(select * from inegi.data where var_id=%varid% and mun_id=0 and yr = %yr%) a,
inegi.estados2010 m where m.est_id=a.est_id

Now load in the boundless normalised style and test it using the layer preview.

In order to fully adapt the Boundless interface we need an appropriate JSON data store (in fact we need two, one for the variables and one for the years). This code shows how to use R to build the first one.

http://rpubs.com/dgolicher/11594

We also need a similar data store holding the years we want to look at. It should look like this.

[{“Year”:1990},{“Year”:1991},{“Year”:1992},{“Year”:1993},{“Year”:1994},{“Year”:1995},{“Year”:1996},{“Year”:1997},{“Year”:1998},{“Year”:1999},{“Year”:2000},{“Year”:2001},{“Year”:2002},{“Year”:2003},{“Year”:2004},{“Year”:2005},{“Year”:2006},{“Year”:2007},{“Year”:2008},{“Year”:2009},{“Year”:2010},{“Year”:2011},{“Year”:2012}]

This could be edited by hand and should really be reduced to only the years with the most complete data.

The working apps can be tried here (they may be a bit slow as the server is simply my PC in my office in Ecosur, please do not overuse or abuse).

http://geoserv.ecosur.mx/apps/inegi/inegi_municipios.html

http://geoserv.ecosur.mx/apps/inegi/inegi_states.html

Many combinations of years and variable have no data. Offhand I can’t think of a simple way to adjust the query to use the closest year’s data.

The apps adapted this prebuilt framework provide by Boundless. Note that the version uses Geoext and a Json store. An earlier version of the tutorial that reads variable values from a text file.

<html>
  <head>
    <title>Datos normalisados de INEGI</title>
   <meta http-equiv="content-type" content="text/html;charset=utf-8" />

    <!-- ExtJS Scripts and Styles -->
    <script src="http://maps.google.com/maps/api/js?v=3.2&sensor=false"></script>
    <script type="text/javascript" src="http://cdn.sencha.com/ext/gpl/3.4.1.1/adapter/ext/ext-base.js"></script>
    <script type="text/javascript" src="http://cdn.sencha.com/ext/gpl/3.4.1.1/ext-all.js"></script>
    
    <link rel="stylesheet" type="text/css" href="http://cdn.sencha.com/ext/gpl/3.4.1.1/resources/css/ext-all.css" />
    <link rel="stylesheet" type="text/css" href="http://cdn.sencha.com/ext/gpl/3.4.1.1/examples/shared/examples.css" />
    <link rel="stylesheet" id="opengeo-theme" href="resources/css/xtheme-opengeo.css" />
    <!-- OpenLayers Script -->
    
    <script src="http://www.openlayers.org/api/2.12/OpenLayers.js"></script>

    <!-- GeoExt Script -->
    <script type="text/javascript" src="http://api.geoext.org/1.1/script/GeoExt.js"></script>

    <!-- Our Application -->
    <script type="text/javascript" src="inegi_municipios.js"></script>

  </head>
  <body>
<div id="scaleline-id"></div>
  </body>
</html>

// Base map
var osmLayer = new OpenLayers.Layer.OSM();

var sat = new OpenLayers.Layer.Google(
                "Google Satellite",
                {type: google.maps.MapTypeId.SATELLITE, numZoomLevels: 22}
            );
var ghyb = new OpenLayers.Layer.Google(
    "Google Hybrid",
    {type: google.maps.MapTypeId.HYBRID, numZoomLevels: 20}
);

var phys = new OpenLayers.Layer.Google(
                "Google Physical",
                {type: google.maps.MapTypeId.TERRAIN}
            );

var streets = new OpenLayers.Layer.Google(
                "Google Streets", // the default
                {numZoomLevels: 20}
            );
var inegi = new OpenLayers.Layer.WMS("Inegi", 
  "http://geoserv.ecosur.mx:8080/geoserver/wms", 
  {
    format: "image/png8",
    transparent: true,
    layers: "reddeam:normalised_municipios"
  }, {
    opacity: 0.6,
  }
);

var clearBaseLayer = new OpenLayers.Layer("None", {isBaseLayer: true}); 
// Map object with projection
olMap = new OpenLayers.Map({
  projection: "EPSG:900913",
  units: "m",
  layers: [osmLayer,sat,ghyb,streets,phys,clearBaseLayer,inegi],
controls: [
                        new OpenLayers.Control.Navigation(),
                        new OpenLayers.Control.PanZoomBar(),
                        new OpenLayers.Control.LayerSwitcher({'ascending':false}),
                        new OpenLayers.Control.Permalink(),
                        new OpenLayers.Control.ScaleLine(
                            {maxWidth:500,
                             div: document.getElementById("scaleline-id")            
                                                                    }),
                        new OpenLayers.Control.Permalink('permalink'),
                        new OpenLayers.Control.MousePosition(),
                        new OpenLayers.Control.OverviewMap(),
                        new OpenLayers.Control.KeyboardDefaults()
                    ]
});
var inegi_variable = new Ext.data.JsonStore({
  autoDestroy: true,
  autoLoad:true,
  url: "INEGI.json",
  fields: ['id','variable'],
  idProperty: 'id',  
});
var Year = new Ext.data.JsonStore({
  autoDestroy: true,
  autoLoad:true,
  url: "Years.json",
  fields: ['Year'],
  idProperty: 'Year',  
});
var chooseYear = new Ext.form.ComboBox({
  autoSelect:true,
  mode: 'local',
  store: Year,
  displayField: "Year",
  valueField: 'Year',
  width: 200,
  triggerAction: "all",
  emptyText:"Escoge el año",
 listeners: {
    select: function(combo, rec, idx) {
      Year=rec.get("Year");
      var vp = { viewparams: "yr:" + Year};
      inegi.mergeNewParams(vp);
    
      // control.vendorParams = vp;
    }
  }
 
});

var chooseVar = new Ext.form.ComboBox({
  autoSelect:true,
  mode: 'local',
  store: inegi_variable,
  displayField: "variable",
  valueField: 'id',
  width: 600,
  triggerAction: "all",
  emptyText:"Escoge el variable",
 listeners: {
    select: function(combo, rec, idx) {
      varid=rec.get("id");
      var vp2 = { viewparams: "varid:" + varid };
      inegi.mergeNewParams(vp2);
     
    }
  }
 
});
// Viewport wraps map panel in full-screen handler
var viewPort = new Ext.Viewport({
  layout: "fit",
  items: [{
    xtype: "gx_mappanel",
    ref: "mappanel",
    title: "INEGI",
    tbar: [chooseYear,chooseVar],
    map: olMap
  }]
});
olMap.setCenter([-10364594.0, 2023072.0],6);
// Fire off the ExtJS 
Ext.onReady(function () {
  viewPort.show();
});

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s