mf-chsdi3
mf-chsdi3 copied to clipboard
geoadmin
attribute filter in models
We should be able to define attribute filters in the models. Otherwise we have to create views in the database for every data subset coming from one table.
The filter can be simple attribute p.e. ch.swisstopo.fixpunkte-hfp2
WHERE punkt.ordnung::text = 'HFP2.HFP2'::text OR punkt.ordnung::text = 'HFP2.deklassierterHFP1'::text
or a bit more complex p.e. ch.swisstopo.fixpunkte-lfp1
WHERE punkt.fineltra::text = 'TSP1'::text AND punkt.ordnung::text = 'EUREF.EUREF'::text OR punkt.ordnung::text = 'EUREF.EUREF'::text OR punkt.fineltra::text = 'TSP1'::text AND (punkt.ordnung::text = 'LFP1.Ordnung1'::text OR punkt.ordnung::text = 'LFP1.Ordnung2'::text OR punkt.ordnung::text = 'LFP1.Ordnung3'::text) OR punkt.ordnung::text = 'LFP1.Ordnung1'::text OR punkt.ordnung::text = 'LFP1.Ordnung2'::text OR punkt.ordnung::text = 'LFP1.Ordnung3'::text OR punkt.fineltra::text = 'TSP1'::text AND (punkt.ordnung::text = 'LV95.Hauptpunkt'::text OR punkt.ordnung::text = 'LV95.Verdichtungspunkt'::text) OR punkt.ordnung::text = 'LV95.Hauptpunkt'::text OR punkt.ordnung::text = 'LV95.Verdichtungspunkt'::text OR punkt.fineltra::text = 'TSP1'::text;
Not a big fan, you'll have to handle it on a case by case basis.
From stack overflow it would look like:
http://stackoverflow.com/questions/920724/the-right-way-to-auto-filter-sqlalchemy-queries
mapper(EmailInfo, select([email_join], email_join.c.deleted == False))
Why don't you simply create different tables to begin with? Don't really understand the use case here.
I think it is a major dev and you guys will have trouble using it.
I still think it could be good to be able to filter at the model level. I have to admit that I fail at recognizing the degree of complexity of the development and/or usage at the moment but such an enhancement will allow us to avoid several views in the DBs or splitting a single dataset into multiple tables only for the sake of the tooltip
The mapper object is not an improvement in my eyes. As you have said there is a huge dev effort and we cant use postgres sql queries and the full power of postgres. We are already where you proposed to start with: we are creating an unnecessary amount of views and / or tables in the database for any layer who uses a subset of a table. Would it be possible to use the text construct [1] and replace
__tablename__ = 'punkt_hoehe_hfp2'
__table_args__ = ({'schema': 'fpds', 'autoload': True})
with something like
s = text(
"SELECT
pointid
, nbident
, punktname
, status
, nummer
, x03
, y03
, n95
, e95
, h02
, zugang
, url
, the_geom
FROM fpds.punkt
WHERE ordnung::text = 'HFP2.HFP2' OR ordnung::text = 'HFP2.deklassierterHFP1'::text;")
conn.execute(s).fetchall()
[1] http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-text
I agree on Marcel's general point that we should try to put as much as possible in code instead of in the db. This is good for many things, especially for reviews, reproducability and tracability. In fact, I think we should create complicated views in db using code from chsdi3!
@loicgasser @procrastinatio Is there any way SQLAlchemy might help us here?
yes we would use alembic for that. it is designed to make database migrations. https://alembic.readthedocs.org/en/latest/
I already uses it in an other projects, it s a fine lib.
How this related to the new layerDefs options? (see http://api3.geo.admin.ch/services/sdiservices.html#identify-features)
the initial question here was if it is possible to define WHERE statements or filters on SQLALchemy model level to avoid the creation of views. layerdefs could be used as workaround for identify requests, but what will we do with the other services which are based on the layer model and the tables/views behind (find, search, ...)?