Using the SQLAlchemy ORM with GeoAlchemy2, I can create the table in the public schema and the geometry_columns view gets updated as expected and the created table also includes a gist index on the geom column automatically:
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry
from sqlalchemy import Column, Integer
engine = create_engine('postgresql://username:pwd@localhost/my_db')
Base = declarative_base()
class Aa19(Base):
__tablename__ = 'aa19'
id = Column(Integer, primary_key=True)
geom = Column(Geometry('POINT', 4326))
Aa19.__table__.create(engine)
But what I want to accomplish is to create a table in one schema, while ensuring the geometry_columns view in the public schema gets updated accordingly. I can't figure this out. I thought by including a variable that sets the search_path maybe I could do this, and then the create_engine() call used above gets replaced like this:
db_schema = 'target_schema,public'
engine = create_engine('postgresql://username:pwd@localhost/my_db',
echo=True,
connect_args = {'options':'-csearch_path={}'.format(db_schema)})
But this doesn't work and I get the error message:
ProgrammingError: (psycopg2.ProgrammingError) relation "public.aa19" does not exist
That's fairly obvious and not surprising. I've also tried to set the search path to just my target_schema, hoping that it would automatically update the geometry_columns view in the public schema. But this doesn't work out either and I get the message:
ProgrammingError: (psycopg2.ProgrammingError) type "geometry" does not exist
LINE 4: geom geometry(POINT,4326)
My question is: How can I create a table with geom column (and gist index) in one schema, AND populate the geometry_columns metadata in the public schema?
I'm new to this technique of interacting with an RDBMS using python. I've gone through the whole ORM tutorial and most of the Core tutorial, as well as having perused the docs. I'm sure the solution is in there somewhere, but I haven't come across it.