[pycsw-devel] Preparing Postgres/Postgis DB

[Angelos Tzotsos]

Hi Jeff,

Our Postgresql support is actually confusing :)
We support:
1. Postgresql without PL/Python. This will work through SQLAlchemy + 
Shapely (the same way pycsw works will all SQLAlchemy supported databases)
2. Postgresql + PostGIS + PL/Python: In order to link with PostGIS 
functions for speeding up spatial queries. This will work with WKT 
column only.
3. Postgresql + PostGIS native + PL/Python (from pycsw 1.6.0): This will 
use native PostGIS to read/write spatial data in the db and gives 
maximum performance to spatial queries.

Cheers,
Angelos



On 05/24/2013 04:34 PM, Jeff McKenna wrote:
> I also just found out that for Windows, the PL/Python extension
> (required by pycsw) is only available for Python 3 (I use Python 2.6 on
> Windows), because PostgreSQL 9.2 Windows binaries are packaged with
> Python 3 support.  Something that future readers of this email thread
> should beware of.  I will make a note of this.
>
> -jeff
>
>
>
> On 2013-05-23 7:59 PM, Angelos Tzotsos wrote:
>> Hi Jeff,
>>
>> PostGIS 2.x native support is in git master (to be released as 1.6.0).
>>
>> Cheers,
>> Angelos
>>
>> On 05/23/2013 11:21 PM, Jeff McKenna wrote:
>>> Hello all,
>>>
>>> (I apologize if this is a simple question/answer)
>>>
>>> I have created a new PostgreSQL database (named "pycsw-demo"), and
>>> loaded the PostGIS extension (I'm using PostGIS 2.0.2).  So, this db has
>>> a single existing table, named "spatial_ref_sys".
>>>
>>> However when I try to setup_db it I get the following error, like it is
>>> not happy that this spatial table already exists:
>>>
>>> ****
>>>      python ./sbin/pycsw-admin.py -c setup_db -f default.cfg
>>>
>>>      Initializing static context
>>>      Creating database postgresql://postgres:xxxx@yyyyy:5435/pycsw-demo
>>>      Creating table spatial_ref_sys
>>>      Traceback (most recent call last):
>>>        File "./sbin/pycsw-admin.py", line 239, in <module>
>>>          admin.setup_db(DATABASE, TABLE, HOME)
>>>        File
>>> "C:\Python26\Scripts\pycsw-workshop\lib\site-packages\pycsw\admin.py",
>>> line 62, in
>>>      setup_db
>>>          srs.create()
>>>        File "build\bdist.win32\egg\sqlalchemy\schema.py", line 614, in
>>> create
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 1479, in
>>> _run_visitor
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 1122, in
>>> _run_visitor
>>>        File "build\bdist.win32\egg\sqlalchemy\sql\visitors.py", line
>>> 111, in
>>> traverse_single
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\ddl.py", line 89, in
>>> visit_table
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 662, in
>>> execute
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 720, in
>>> _execute_ddl
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 874, in
>>> _execute_context
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 1024, in
>>> _handle_dbapi_exce
>>>      ption
>>>        File "build\bdist.win32\egg\sqlalchemy\util\compat.py", line
>>> 163, in
>>> raise_from_cause
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line
>>> 867, in
>>> _execute_context
>>>        File "build\bdist.win32\egg\sqlalchemy\engine\default.py", line
>>> 324,
>>> in do_execute
>>>      sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation
>>> "spatial_ref_sys" already exi
>>>      sts
>>>       '\nCREATE TABLE spatial_ref_sys (\n\tsrid SERIAL NOT NULL,
>>> \n\tauth_name VARCHAR(256), \n
>>>      \tauth_srid INTEGER, \n\tsrtext VARCHAR(2048), \n\tPRIMARY KEY
>>> (srid)\n)\n\n' {}
>>> ****
>>>
>>> Is there something like an append setting for setup_db ?
>>>
>>> -jeff
>>>
>>>
>>
>


-- 
Angelos Tzotsos
Remote Sensing Laboratory
National Technical University of Athens
http://users.ntua.gr/tzotsos