postgis setup advice

[dweaver]

I'm having a bit of trouble running what we have so far. I get an exception from Django[1] and a few postgresql log entries[2] that make me suspect I don't have postgis installed correctly. If I set DATABASE_ENGINE to postgresql rather than postgresql_psycopg2 and make a default app using createapp, the server runs fine. I'd appreciate hints as to what might be wrong with my setup.

dweaver:hackathon Dan$ port installed | grep postgresql
postgis @1.4.0_1+postgresql84 (active)
postgresql84 @8.4.1_0
postgresql84 @8.4.1_1 (active)
py26-psycopg2 @2.0.12_0+postgresql84 (active)

[1] exception from django
dweaver:neighborly Dan$ pwd
/Users/Dan/hackathon/neighborly/src/neighborly
dweaver:neighborly Dan$ python manage.py runserver
Validating models...
Unhandled exception in thread started by <function inner_run at 0x101917b18>
Traceback (most recent call last):
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/core/management/commands/runserver.py", line 48, in inner_run
self.validate(display_num_errors=True)
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/core/management/base.py", line 249, in validate
num_errors = get_validation_errors(s, app)
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/core/management/validation.py", line 28, in get_validation_errors
for (app_name, error) in get_app_errors().items():
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/db/models/loading.py", line 131, in get_app_errors
self._populate()
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/db/models/loading.py", line 58, in _populate
self.load_app(app_name, True)
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/db/models/loading.py", line 74, in load_app
models = import_module('.models', app_name)
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/utils/importlib.py", line 35, in import_module
import(name)
File "/Users/Dan/hackathon/neighborly/src/neighborly/contact/models.py", line 1, in
from django.contrib.gis.db import models
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/models/init.py", line 5, in
from django.contrib.gis.db.models.aggregates import *
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/models/aggregates.py", line 2, in
from django.contrib.gis.db.backend import SpatialBackend
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/init.py", line 12, in
from django.contrib.gis.db.backend.postgis import create_test_spatial_db, get_geo_where_clause, SpatialBackend
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/postgis/init.py", line 4, in
from django.contrib.gis.db.backend.postgis.adaptor import PostGISAdaptor
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/postgis/adaptor.py", line 5, in
from django.contrib.gis.db.backend.postgis.query import GEOM_FROM_WKB
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/postgis/query.py", line 27, in
version_tuple = postgis_version_tuple()
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/postgis/management.py", line 45, in postgis_version_tuple
version = postgis_lib_version()
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/postgis/management.py", line 25, in postgis_lib_version
return _get_postgis_func('postgis_lib_version')
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/contrib/gis/db/backend/postgis/management.py", line 13, in _get_postgis_func
cursor.execute('SELECT %s()' % func)
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/django/db/backends/util.py", line 19, in execute
return self.cursor.execute(sql, params)
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

[2] postgresql log
ERROR: function postgis_lib_version() does not exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT: SELECT postgis_lib_version()
ERROR: current transaction is aborted, commands ignored until end of transaction block
STATEMENT: SELECT postgis_lib_version()

[ianb]

Indeed -- there are several SQL files shipped with PostGIS that you have to run on a new database. On my Ubuntu system it's these files:

/usr/share/postgresql-8.3-postgis/lwpostgis.sql
/usr/share/postgresql-8.3-postgis/lwpostgis_upgrade.sql
/usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql

You might find them someplace else, but with those same filenames.

[dweaver]

Thank you. Taking your advice, I see I must have installed some incompatible versions of postgresql, postgis, and geos libraries. I'll untangle this shortly.

dweaver:contrib Dan$ psql -U postgres -d neighborly -f postgis.sql
BEGIN
psql:postgis.sql:53: NOTICE: type "spheroid" is not yet defined
DETAIL: Creating a shell type definition.
psql:postgis.sql:53: ERROR: could not load library "/usr/local/pgsql/lib/postgis-1.4.so": dlopen(/usr/local/pgsql/lib/postgis-1.4.so, 10): Library not loaded: /opt/local/lib/libgeos_c.1.dylib
Referenced from: /usr/local/pgsql/lib/postgis-1.4.so
Reason: Incompatible library version: postgis-1.4.so requires version 7.0.0 or later, but libgeos_c.1.dylib provides version 3.0.0
psql:postgis.sql:59: ERROR: current transaction is aborted, commands ignored until end of transaction block
...