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 ...