Import data from external Postgis database

[Marco Afonso]

Hello all,

Can anyone point me the right direction to the simplest way to import data from an external postgresql/postgis database?

I've seen some extensions but it was not clear the way to import from external database. Maybe a tutorial is missing(?)

Thank you,

Marco

[Florian May]

Hi Marco,

there are many ways to do so, depending on how often / automated you need to run this job, whether you wish to publish a snapshot of your data or point to a live webservice.

Option 1: postGIS > GeoJSON file > CKAN file resource

If you want to publish a simple snapshot of one spatially referenced dataset from PostGIS to CKAN:

Since you mentioned PostGIS - is your data spatial? Then you could export your data as a GeoJSON file, and simply upload the GeoJSON file as CKAN resource.

E.g. https://gis.stackexchange.com/questions/14514/how-to-export-feature-geojson-from-postgis:

ogr2ogr -f GeoJSON out.json \
  "PG:host=localhost dbname=gis user=ubuntu password=toomanysecrets" \ 
  -sql "select way,name,amenity from planet_osm_point where amenenity is not null"

The same process goes for non-spatial datasets: dump to CSV, upload as file.

Option 2: postGIS > GeoServer WMS/WFS layer > WxS URL > CKAN URL resource 

If you want to publish one dataset from PostGIS as a live webservice, you could host the PostGIS table on a GeoServer and publish the WMS link as resource URL to CKAN.

Geoserver docs: http://docs.geoserver.org/stable/en/user/data/database/postgis.html

Both options are straight forward on the CKAN side as they boil down to creating a CKAN a resource file or URL, respectively.

If you need to publish more than one dataset one-off, the above is easily scripted in Python or R. 

I found using the harvester extension works fine for simple cases, but too limiting for my particular use cases, as it collided with some customisations to our CKAN dataset schema. My sweet spot turned out to be simple iPython or RMarkdown notebooks / R scripts reading files / URLs and uploading to the CKAN API.

Python example: See https://github.com/datawagovau/harvesters for iPython workbooks with real world examples.

R example: https://bitbucket.org/dpaw/mpa-reporting/src/5a235042720efcc8f1340bd26e74e5255c46f9e9/scripts/indicators/finfish/code.R?at=master&fileviewer=file-view-default

Tangent: The CKAN extension https://github.com/TerriaJS/nationalmap/tree/master/ckanext-cesiumpreview provides a neat spatial preview. e.g.: http://map.beta.data.wa.gov.au/

API docs:

CKAN API http://docs.ckan.org/en/latest/api/index.html

Datastore API http://docs.ckan.org/en/latest/maintaining/datastore.html#the-datastore-api

CKAN API wrapper in R: https://github.com/ropensci/ckanr

Hope that helps!

Florian

--
You received this message because you are subscribed to the Google Groups "CKAN Global User Group (Non-technical questions)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ckan-global-user-group+unsub...@googlegroups.com.
To post to this group, send email to ckan-global-user-group@googlegroups.com.
Visit this group at https://groups.google.com/group/ckan-global-user-group.
To view this discussion on the web, visit https://groups.google.com/d/msgid/ckan-global-user-group/d2688df8-7af5-419b-8291-f91c869718f7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marco Afonso]

Many many thanks for your reply Florian.

I think we will consider the option of create a script to export all postgis tables (more than 200) and import using Datastore API.

Cheers

To post to this group, send email to ckan-global...@googlegroups.com.