Parse Survey data in multiples tables and columns


(Htuser) #1

Dear Survey Solutions Developer Team,
I would like to know if it’s possible to parse each questionnaire data in a separate schema, but in multiples tables and columns in PostgreSQL (since you already use it in backend)?
Among other, this will facilitate greatly users who will like to use relational data in GIS and statistical packages.

Thanks in advance for considering my suggestion.


(Misha) #2

In Survey Solutions users can access data either through exporting data into Stata, SPSS or Tab delimited formats or through APIs. We do not provide any other ways to interact with the data. The information stored in Postgres needs to be transformed to extract data in a form that can be used by the end user. As is, this information cannot be processed by any statistical packages.


(Htuser) #3

Thank you Misha your your reply.
However, given access to this data in a usable way (multiple tables and columns)is very important.
So, i would suggest you, in the future, to give us an access to a replicate (one way) copy of data.

As mentioned previously, this will allow us to connect multiple statistical package (R, SPSS, STATA,among others) to this live, one way replication of the survey database. So, we can perform advanced analysis to detect some errors and display advanced tabulation, graph (by example using D3.js ) etc.

Thanks in advance,


(Misha) #4

Our API functionality can be tailored to any user demands. There is no need to access the database directly.


(Scott Sheridan) #5

You can write your own parsing program to read the data into your own database (that’s what I’ve done).
The advantage is that you can map the variables and labels however you like.

Also, if you have the money to own one of those statistical packages (my org does not), you can just use it to save the data to the database.
Just download the relevant format (ie SPSS(*.sav)) and use the program to export to DB.
SPSS does this for sure: SPSS Export to DB


(Htuser) #6

Thank you Misha for your response. However, having one way access to parsing data is very very important. By example, you can connect GIS or any statistical software to perform analysis on the fly during fieldwork, this in a multiuser environment with advanced audit. So, i whatever census, survey, local and international team can work in the same database with different statistical and GIS software. Please let’s know more about API and how to use it to perform a such feature. So we can connect Survey Solution specific data format to other DBMS (Postgresql, MariaDB/MySQL).


(Htuser) #7

Dear Macuata,
Thank you for sharing your experience with us. Please can you share source code or more explanation with us?
There’s a lot of free stistical packages and librairies (such as R). And R can connect to Postgresql, MariaDB and MySQL to gather data. Also, we can use Mapwingis, Qgis ect to perform spatial analysis. And for more advanced users, there’s a lot of js librairies such as D3.js who can be used to display advanced graphics, charts.

So, it’s very important to have Census/Survey Data parsing in multiples tables and columns. By Example, Cspro offer the Way to export data to One Table/multiples columns, whatever the complexity of a questionnaire. It also offer to export in multiples relational tables. However, Cspro lack of severals features and functionalities that we can find in Survey Solutions.

Best Regards,


(Scott Sheridan) #8

The exported data is essentially tables already and all you need to do is parse them and put them in a database.
You can even directly import the tab files (though you lose label data).

The advantage of parsing them is that you can separate your data into your own logical tables specific to your survey rather than the generic tables Survey Solutions uses so it is able to be used for any type of survey.

My code is survey specific rather then generic. I read the *.sav files into my own c# data structures and save it to a database.
It is just using SPSSLib at the bottom level, where you easily read the values and labels through its interfaces.
Then you can determine what you want your database tables to look like and store the survey data appropriately.

I’m pretty sure R has the ability to read SPSS files and also to write to a database too. You just have to code it yourself so the database tables are logical for your own use case.

Engineering some software to parse the data and write to a database is a pretty trivial task for a programmer, and you can even automate the task using the API so it polls the server (e.g. once per day) to update the database when there’s new interviews.

It sounds like it might be a good way forward to do this so that you can then use the data in the database to do all of the other tasks you’ve been describing.

Cheers,
Scott