[pycsw-devel] CKAN - pycsw integration: last details

[Tom Kralidis]

Adrià: thanks -- great work / news here!

Angelos: thanks for the feedback and making this feature so easy to leverage.

Further comments interleaved:

> Date: Wed, 26 Jun 2013 21:25:30 +0300
> From: gcpp.kalxas at gmail.com
> To: adria.mercader at okfn.org
> CC: pycsw-devel at lists.osgeo.org; david.raznick at okfn.org; tzotsos at gmail.com; tomkralidis at hotmail.com
> Subject: Re: [pycsw-devel] CKAN - pycsw integration: last details
> 
> Hi Adria,
> 
> Thank you very much for this effort!
> 
> Some answers inline:
> 
> 
> On 06/26/2013 05:56 PM, Adrià Mercader wrote:
>> I have a couple of doubts though:
>>
>> * Regarding the different backends for geometries, I see that support
>> for PostGIS backend will be included in pycsw 1.6.0, which is great.
>> What kind of configuration does it require to use a backend or
>> another? I didn't find it in the docs, but looking at the code it
>> seems that if you want native Postgis you just need to create a geom
>> field in the records table (with a different name from wkt_geometry)
>> and that's it?
> Sorry that I did not find the time to update the master (1.6.0) docs for 
> this new feature but the user is not required to do much.
> http://pycsw.org/docs/administration.html#setting-up-the-database
> The way native PostGIS geometries are handled by pycsw is briefly 
> explained here:
> https://github.com/geopython/pycsw/issues/124#issuecomment-16693140
> 
> Basically, during
> 
> pycsw-admin.py -c setup_db -f default.cfg
> 

Looks like ckan-spatial has duplicated pycsw.admin.setup_db (https://github.com/geopython/pycsw/blob/master/pycsw/admin.py#L43) in https://github.com/okfn/ckanext-spatial/blob/24-pycsw-integration-command/bin/ckan_pycsw.py#L16

iirc, this was to address some issues with pycsw.admin.setup_db.  With help from David R, we've implemented functionality to make pycsw.admin.setup_db much more flexible for callers.

Adrià/David: given the enhancements, can ckan-spatial import pycsw.admin.setup_db and use that directly?

> the administration utility detects if PostGIS is installed in the 
> database mentioned in default.cfg. Both PostGIS 1.x and 2.x are detected.
> If PostGIS is detected, the script does not create the SFSQL tables (as 
> they are already in the db) and creates the metadata table as declared 
> in default.cfg. The difference is that it also creates a "wkb_geometry" 
> column and a trigger to update it in case "wkt_geometry" field is 
> changed. So we have both "wkt_geometry" and native geometry 
> ("wkb_geometry") columns in the same table to keep compatibility with 
> previous versions (or in case PostGIS is disabled for some reason).
> 
> Bottom line: no special setup needed. If PostGIS is enabled before 
> setting up the pycsw db, the native support is there :)
> 
> In case you need to migrate from previous deployment, you will need to 
> add the column "wkb_geometry" manually (along with the update trigger) 
> and then create a script to fill the geometries from wkt_geometry field. 
> We can provide such migration script...
> 
> Native column and trigger creation script can be found here:
> https://github.com/geopython/pycsw/blob/master/pycsw/admin.py#L257
> 
> I promise to update the docs... :)
> 

Good idea.  We'll get this feature in docs for 1.6.0.

>> Can you give me an example GetRecords request that filters by bbox
>> that I can use to test our setup?
> 
> This is a demo POST request. You can test using the tester application:
> http://geopython.github.io/pycsw-workshop/docs/intro/intro-exercises.html#tester-application
> 
> <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
> <csw:GetRecords xmlns:csw="http://www.opengis.net/cat/csw/2.0.2" 
> xmlns:ogc="http://www.opengis.net/ogc" service="CSW" version="2.0.2" 
> resultType="results" startPosition="1" maxRecords="5" 
> outputFormat="application/xml" 
> outputSchema="http://www.opengis.net/cat/csw/2.0.2" 
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
> xsi:schemaLocation="http://www.opengis.net/cat/csw/2.0.2 
> http://schemas.opengis.net/csw/2.0.2/CSW-discovery.xsd" 
> xmlns:gml="http://www.opengis.net/gml">
>      <csw:Query typeNames="csw:Record">
>          <csw:ElementSetName>brief</csw:ElementSetName>
>          <csw:Constraint version="1.1.0">
>              <ogc:Filter>
>                  <ogc:BBOX>
> <ogc:PropertyName>ows:BoundingBox</ogc:PropertyName>
>                      <gml:Envelope>
>                          <gml:lowerCorner>47 -5</gml:lowerCorner>
>                          <gml:upperCorner>55 20</gml:upperCorner>

n.b. the default axis order is lat long.

>                      </gml:Envelope>
>                  </ogc:BBOX>
>              </ogc:Filter>
>          </csw:Constraint>
>      </csw:Query>
> </csw:GetRecords>
> 
> 
>>
>> * I seem to remember that there was some talk about offering full text
>> support. Has this been implemented and if so what kind of setup does
>> it require? (ie is there an index that needs to be created in the DB?
> 
> There is an open issue on this. We originally wanted to offer this with 
> 1.6.0 but we did not get any feedback:
> https://github.com/geopython/pycsw/issues/136
> 
> 

We tried our best to get this in for 1.6.0, but ran into issues and had to defer it post 1.6.0.  I will start a separate email discussion here about this once 1.6.0 is released w.r.t. options and issues.

As we saw with CKAN integration for data.gov, with volumes like ~350K metadata records, PostgreSQL FTS is a huge performance boost.