UserDefinedType CREATE INDEX hook

[Nikola Radovanovic]

Hi all,

I have a need to use PostGIS extension in order to get support for objects like Point and Polygon provided by geoalchemy2 in PostgreSql. 

Now, the issue I encounter is that while migrating model which uses Point/Polygon (classes derived from UserDefinedType) - geoalchemy2 tries to create index for those although I dont set index=True when declaring properties in model; nor I have instruction to create it in migration script. Is there a way to 'override' this behavior somehow - like setting index to true in definition and manually add index creation in migration script? Or at least to find out where this request comes from, so I can track this further?

Thank you in advance.

Best regards 

[Mike Bayer]

My first impression is that if geoalchemy2 is creating indexes that go
along with datatypes, they should allow this to be customizable. Is
geoalchemy2 still maintained? you should at least see if they can
support a flag to turn this off if someone wants to customize things.

I haven't looked at the GIS stuff in years but what are these indexes
that you don't want which ga2 needs to create? If you just need a
simple datatype insturction, I would just skip using the ga2 types in
your migration script and just use a simple UserDefinedType that you
make yourself which emits POINT/POLYGON whatever in your migration
script, you just need that string name if you don't need anything
else. All the other things that ga2 does involve expression activity
which you also don't need in migration scripts.

>
> Thank you in advance.
>
> Best regards
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Nikola Radovanovic]

Hi,

you are right: I found that geoalchemy2 automatically creates indexes; which I eventually turned off in model, using e.g.

polygon = Column(Geometry(geometry_type='POLYGON', srid=3857, spatial_index=False))

and adding them manually to migration script:

sa.Column('polygon', Geometry(geometry_type='POLYGON', srid=3857, spatial_index=False), nullable=True)

op.create_index(op.f('id_tenant_schema_Locations_inhabitant_polygon'), 'Locations', ['polygon'], unique=False, postgresql_using='gist', schema='tenant_schema') 

This works apparently. Only thing I am unsure is this part: 

postgresql_using='gist'

Can you please tell me if this is OK - I need to specify 'gist' when creating index and not sure did I set it properly.

ga2 latest release is 6 or 7 months old, so I think they are alive. What helped me greatly is this from their issue list (its still open)

Best regards

[Mike Bayer]

On Thu, Jan 24, 2019 at 12:15 PM Nikola Radovanovic <niko...@gmail.com> wrote:
>
> Hi,
> you are right: I found that geoalchemy2 automatically creates indexes; which I eventually turned off in model, using e.g.
>
> polygon = Column(Geometry(geometry_type='POLYGON', srid=3857, spatial_index=False))
>
> and adding them manually to migration script:
>
> sa.Column('polygon', Geometry(geometry_type='POLYGON', srid=3857, spatial_index=False), nullable=True)
>
> op.create_index(op.f('id_tenant_schema_Locations_inhabitant_polygon'), 'Locations', ['polygon'], unique=False, postgresql_using='gist', schema='tenant_schema')
>
> This works apparently. Only thing I am unsure is this part:
>
> postgresql_using='gist'
>
> Can you please tell me if this is OK - I need to specify 'gist' when creating index and not sure did I set it properly.

if it was accepted and created the DDL you wanted then it's right!
I don't have much experience with the USING thing in PG other than how
to make it render :)

>
>
> ga2 latest release is 6 or 7 months old, so I think they are alive. What helped me greatly is this from their issue list (its still open)
>
>
> Best regards
>

[Nikola Radovanovic]

Thanks - I am not sure why this 'gist' is used when creating index tbf. I dont see any special difference w/o it. Will investigate further though. Hope this might help if someone bumps into same problem.

Kindest regards

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/yBD4x6IvT4k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.