[ddj] GUI database tools for newsrooms

Thu May 28 10:26:29 UTC 2015

Have had some success here with the Elasticsearch/Kibana combination too.

Recently we've been using Mode Analytics <http://modeanalytics.com/>. You
can plug in most relational databases, and it gives a good interface for
writing SQL queries, and then creating charts from the results or exporting
a CSV. Importantly, you can also make parameterised queries and share them
with others in your organisation, which just shows a text field, and then
inserts the contents into a predefined query -- letting colleagues not
familiar with SQL use a database. The tool sadly propitiatory and paid, but
they've been nice enough to give it to us for free, as we're a nonprofit
crediting them for stories where we use the tool -- if you're interested
email me off-list and I can put you in contact with the person there who
organised that for us.

On 28 May 2015 at 09:24, Alessio Cimarelli | Dataninja.it <
jenkin at dataninja.it> wrote:

> Hi Sam,
>
> if you want to stay in the SQL world, you can use a relational db on the
> back-end (postgresql, but also mysql),
> queried by a simple server-side application (for example using php
> language).
>
> The key part of this simple stack is on the front-end: you can expose to
> your users a graphical query builder
> to write the WHERE clause of an arbitrary sql statement simply pointing
> and clicking: http://mistic100.github.io/jQuery-QueryBuilder/.
>
> Users can build their own query (only WHERE clause, server-side validation
> is required here to avoid sql injection),
> send it to the server and read (and even download) the response (a table).
>
> I think the query builder doesn't support joining, but you can prepare
> virtual table in your db with presetted join and expose
> these ones to your users.
>
> Best :)
>
>
>
> 2015-05-26 9:13 GMT+02:00 Alex Salkever <alex at silk.co>:
>
>> I second Elasticsearch. It's amazing.
>>
>> On Mon, May 25, 2015 at 9:27 PM, Justin Seitz <justin at automatingosint.com
>> > wrote:
>>
>>>  One thing that I have used previously is Elasticsearch and Kibana. It
>>> is a NoSQL database as well, but it allows you to hook up a "river". Rivers
>>> allow you to attach Elasticsearch to a SQL database so that you can have,
>>> for example, a Postgres database that you can query traditional SQL against
>>> but you can have a friendly full text search in the form of Elasticsearch,
>>> exposed over a nice interface like Kibana.
>>>
>>> This seems to fit well with your use case, and I would be more than
>>> happy to help if you had questions setting it up.
>>>
>>>
>>> https://github.com/jprante/elasticsearch-jdbc/wiki/Step-by-step-recipe-for-setting-up-the-river-with-PostgreSQL
>>>
>>> Justin
>>>
>>> On 2015-05-25 10:19 PM, Alex Salkever wrote:
>>>
>>> I'd be cautious about MongoDB. It's NoSQL and doesn't handle nested data
>>> very well. It's easy to use if you are only doing things with document
>>> format but it's not really a SQL tool.
>>>
>>> I think the problem is the GUI part. There are loads of hosted solutions
>>> for DBs but most don't have GUIs that would allow Excel users to work with
>>> data. That's why I see a lot of people using Google Sheets - because it's
>>> an Excel flavor but does synch across users and has versioning that can
>>> easily be rolled back, as well as roles.
>>>
>>>
>>>
>>>
>>>
>>> On Sat, Apr 18, 2015 at 2:18 AM, Michael Saunby <mike at saunby.net> <mike at saunby.net> wrote:
>>>
>>>
>>>  How about using CartoDB?  It's an open source project, so you can host it
>>> yourself - https://github.com/CartoDB/cartodb
>>> but there's also free to use and enterprise services http://cartodb.com/
>>>
>>> Files in Excel and many other formats can be imported easily.
>>>
>>> Michael
>>>
>>> On 17 April 2015 at 18:45, M. Edward (Ed) Borasky <znmeb at znmeb.net> <znmeb at znmeb.net> wrote:
>>>
>>>
>>>  On Fri, Apr 17, 2015 at 12:49 AM, Sam Leon <sam.leon at okfn.org> <sam.leon at okfn.org> wrote:
>>>
>>>  I'm looking for a secure hosted database service ideally running
>>>
>>>  Postgres in
>>>
>>>  the backend which could be queried and updated using SQL commands but
>>>
>>>  also
>>>
>>>  had a graphical interface for users not familiar with SQL who can
>>>
>>>  easily run
>>>
>>>  queries and export to CSV. It's for journalists I'm working with who
>>>
>>>  have
>>>
>>>  various datasets currently in Excel which is a nightmare for
>>>
>>>  simultaneous
>>>
>>>  work and is exceptionally brittle.
>>>
>>> I'm aware of the the PANDA project, wondered if anyone else had any
>>> tooling/service tips?
>>>
>>> Sam
>>>
>>>  Some combination of Excel, ODBC, PgAdmin3 and PostgreSQL is probably
>>> the easiest path out of chaos for you and your users.  A hosted
>>> PostgreSQL isn't going to be cheap, though - I'd start with free
>>> desktop installs to get the workflow stabilized.
>>> http://www.postgresql.org/download/windows/
>>>
>>> If you do go the hosted route, you'll need to hire a strong PostgreSQL
>>> database administrator (DBA) to handle all the backup and security
>>> stuff. Don't make that a "side task" for someone or you'll either lose
>>> data or get hacked or both.
>>>
>>> The front ends are Excel and PgAdmin3. PgAdmin3 is a GUI tool for
>>> managing the database but it also has a visual query builder similar
>>> to the one in MS Access. Excel also has a query builder. ODBC is
>>> "middleware" that will present a uniform database language to any
>>> query tool.
>>>
>>> After you've got all that nailed down, it's just a small step to R,
>>> RStudio and the bright world of analysis and visualization described
>>> in the RStudio cheatsheets
>>> (http://www.rstudio.com/resources/cheatsheets/). ;-)
>>> _______________________________________________
>>> data-driven-journalism mailing listdata-driven-journalism at lists.okfn.orghttps://lists.okfn.org/mailman/listinfo/data-driven-journalism
>>> Unsubscribe:https://lists.okfn.org/mailman/options/data-driven-journalism
>>>
>>>  _______________________________________________
>>> data-driven-journalism mailing listdata-driven-journalism at lists.okfn.orghttps://lists.okfn.org/mailman/listinfo/data-driven-journalism
>>> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>>>
>>>
>>>
>>> _______________________________________________
>>> data-driven-journalism mailing listdata-driven-journalism at lists.okfn.orghttps://lists.okfn.org/mailman/listinfo/data-driven-journalism
>>> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>>>
>>>
>>>
>>> _______________________________________________
>>> data-driven-journalism mailing list
>>> data-driven-journalism at lists.okfn.org
>>> https://lists.okfn.org/mailman/listinfo/data-driven-journalism
>>> Unsubscribe:
>>> https://lists.okfn.org/mailman/options/data-driven-journalism
>>>
>>>
>>
>>
>> --
>> Alex Salkever
>> Growth/BD/Data Journalism
>> 415-503-9035
>> www.silk.co / @silkdotco <http://www.twitter.com/silkdotco> /
>> @silkjournalism <http://www.twitter.com/silkjournalism>
>>
>>
>> _______________________________________________
>> data-driven-journalism mailing list
>> data-driven-journalism at lists.okfn.org
>> https://lists.okfn.org/mailman/listinfo/data-driven-journalism
>> Unsubscribe:
>> https://lists.okfn.org/mailman/options/data-driven-journalism
>>
>>
>
>
> --
> ALESSIO CIMARELLI
> a.k.a. jenkin
>
> Data scientist, web developer e giornalista scientifico free-lance
>
> Blog: dataninja.it <http://www.dataninja.it/>
> Mail: jenkin at dataninja.it
> PGP pub key: 0x46bd7d12
> <http://keyserver.ubuntu.com/pks/lookup?op=get&search=0xE9FD2D2B>
> Twitter: @jenkin27 <https://twitter.com/jenkin27> | Skype:
> alessio.cimarelli
>
> About.me <http://about.me/alessio.cimarelli>
>
> _______________________________________________
> data-driven-journalism mailing list
> data-driven-journalism at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/data-driven-journalism
> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/data-driven-journalism/attachments/20150528/c87955bb/attachment-0003.html>