rbi<ddlZddlmZddlmZddlZddlZddlZddl m Z edZ ddZ dd Z d Zd Zd Zd ZdZ ddZedZdS)N)contextmanager) lru_cache) GeoDataFramec#zKddlm}m}t||rM|s3|5|VddddS#1swxYwYdS|VdSt||r3|5}|VddddS#1swxYwYdSt d|)a Yield a connection within a transaction context. Engine.begin() returns a Connection with an implicit Transaction while Connection.begin() returns the Transaction. This helper will always return a Connection with an implicit (possibly nested) Transaction. Parameters ---------- conn_or_engine : Connection or Engine A sqlalchemy Connection or Engine instance Returns ------- Connection r) ConnectionEngineNzUnknown Connectable: )sqlalchemy.engine.baserr isinstancein_transactionbegin ValueError)conn_or_enginerrconns `/var/www/html/mdtn/previsions/meteo_cartes/venv/lib/python3.11/site-packages/geopandas/io/sql.py _get_connr s$:9999999.*-- C,,.. !%%'' % %$$$$ % % % % % % % % % % % % % % % % % %! NF + +C  ! ! # # tJJJ                  AAABBBs$AAA BB"%B"geomc`||vrtd|d|j|dkrtd|d||}|js!t jj} d}t|j dtr|}n|}| |x||<}|t j |j d}|dkr t||} | js"| d } | d |}nld |d |d } t!j| t$dd|}nA#t&jj$r*d|d } t!j| t$dd|}YnwxYwt-|||S)aTransform a pandas DataFrame into a GeoDataFrame. The column 'geom_col' must be a geometry column in WKB representation. To be used to convert df based on pd.read_sql to gdf. Parameters ---------- df : DataFrame pandas DataFrame with geometry column in WKB representation. geom_col : string, default 'geom' column name to convert to shapely geometries crs : pyproj.CRS, optional CRS to use for the returned GeoDataFrame. The value can be anything accepted by :meth:`pyproj.CRS.from_user_input() `, such as an authority string (eg "EPSG:4326") or a WKT string. If not set, tries to determine CRS from the SRID associated with the first geometry in the database, and assigns that to all geometries. con : sqlalchemy.engine.Connection or sqlalchemy.engine.Engine Active connection to the database to query. Returns ------- GeoDataFrame zQuery missing geometry column ''zDuplicate geometry column 'zC' detected in SQL query output. Onlyone geometry column is allowed.c^tjt|dS)z!Load from binary encoded as text.T)hex)shapelywkbloadsstr)xs rload_geom_textz$_df_to_geodf..load_geom_textVs#;$$SVV$66 6rN auth_name:zCould not find srid z+ in the spatial_ref_sys table. Trying epsg:z as a fallback. stacklevelzepsg:z[Could not find the spatial reference system table (spatial_ref_sys) in PostGIS.Trying epsg:)crsgeometry)r columnsto_listcountdropnaemptyrrrr iatbytesapplyget_srid_get_spatial_ref_sys_dfitemwarningswarn UserWarningpderrors DatabaseErrorr) dfgeom_colr$congeomsload_geom_bytesr load_geomsridspatial_ref_sys_dfr warning_msgs r _df_to_geodfr@.s72rF8FFFGGG z!!(++a// .( . . .   xL   ! !E ;'-!++( 7 7 7 eilE * * ''II&I${{95558 u ;#EIaL11Dqyy-)@d)K)K&.3 -$6{$C$H$H$J$J !*33T33A4AA+/AAA$ ! k;1MMMM,dnn'y.)))='+=== M+{qIIII($..CCC)* h 7 7 77s9E;FFTc |.tj||||||} t| Stj||||||} fd| DS)aReturn a GeoDataFrame corresponding to the result of the query string, which must contain a geometry column in WKB representation. It is also possible to use :meth:`~GeoDataFrame.read_file` to read from a database. Especially for file geodatabases like GeoPackage or SpatiaLite this can be easier. Parameters ---------- sql : string SQL query to execute in selecting entries from database, or name of the table to read from the database. con : sqlalchemy.engine.Connection or sqlalchemy.engine.Engine Active connection to the database to query. geom_col : string, default 'geom' column name to convert to shapely geometries crs : dict or str, optional CRS to use for the returned GeoDataFrame; if not set, tries to determine CRS from the SRID associated with the first geometry in the database, and assigns that to all geometries. chunksize : int, default None If specified, return an iterator where chunksize is the number of rows to include in each chunk. See the documentation for pandas.read_sql for further explanation of the following parameters: index_col, coerce_float, parse_dates, params, chunksize Returns ------- GeoDataFrame Examples -------- PostGIS >>> from sqlalchemy import create_engine # doctest: +SKIP >>> db_connection_url = "postgresql://myusername:mypassword@myhost:5432/mydatabase" >>> con = create_engine(db_connection_url) # doctest: +SKIP >>> sql = "SELECT geom, highway FROM roads" >>> df = geopandas.read_postgis(sql, con) # doctest: +SKIP SpatiaLite >>> sql = "SELECT ST_AsBinary(geom) AS geom, highway FROM roads" >>> df = geopandas.read_postgis(sql, con) # doctest: +SKIP N) index_col coerce_float parse_datesparams chunksizer8r$r9c3>K|]}t|VdS)rGN)r@).0r7r9r$r8s r z _read_postgis..sE  FHLhCS A A A      r)r4read_sqlr@) sqlr9r8r$rBrCrDrErFr7 df_generators ``` r _read_postgisrN~sr [  %#   BsDDDD{  %#         LX    rcTt|jj}d}|D] }|d|vrd} t |dkr7|rd}n4|dt d|d}nd }t|jjr|d z }||fS) aGet basic geometry type of a GeoDataFrame. See more info from: https://geoalchemy-2.readthedocs.io/en/latest/types.html#geoalchemy2.types._GISType Following rules apply: - if geometries all share the same geometry-type, geometries are inserted with the given GeometryType with following types: - Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection. - LinearRing geometries will be converted into LineString -objects. - in all other cases, geometries will be inserted with type GEOMETRY: - a mix of Polygons and MultiPolygons in GeoSeries - a mix of Points and LineStrings in GeoSeries - geometry is of type GeometryCollection, such as GeometryCollection([Point, LineStrings]) - if any of the geometries has Z-coordinate, all records will be written with 3D. FN LinearRingTr LINESTRINGrz No valid geometries in the data.GEOMETRYZ) listr% geom_typeuniquelenr upperanyhas_z)gdf geom_types has_curvegttarget_geom_types r_get_geometry_typer`s(cl,335566JI :  R  I :!  9+  !}$ !CDDD#-a=#6#6#8#8  % 3<  C Y &&rcjd}d}|j dD]V}|j|}|n6|jd|}|t|d}nWn,#t$rt j|td YnwxYw|d }t j|td |S) z6Get EPSG code from CRS if available. If not, return 0.NzNCould not parse CRS from the GeoDataFrame. Inserting data without defined CRS.)dF)min_confidenceESRI)rrerr"r)r$to_epsg to_authorityint Exceptionr1r2r3)r[r=r? confidence auth_srids r_get_srid_from_crsrns DX w B+   wjAA#EG00$Z1 (y|,,DE) B B B M+{q A A A A A A B | k;1==== KsAA''&BBcddlm|jdk}|j||ffd|j||f<|S)Nr) LineStringrPc|S)N)rrps rz3_convert_linearring_to_linestring..)sZZ%%r)shapely.geometryrprUlocr-)r[ geom_namemaskrps @r!_convert_linearring_to_linestringrx!sa++++++ =L (D"wtY7==%%%%  CGD)O Jrctjtj||jj|dd}t j|d}|||<|S)zConvert geometries to ewkb.)r=T)r include_sridF)copy)rto_wkbset_sridvalues_datar4 DataFrame)r[rvr=r:r7s r_convert_to_ewkbr.s_ NY.44@@@    E c & & &BByM Ircddl}ddl}|}||}|||ddd|D}|j} d|jj d|jj d|d} | 5} t| drit| jrU| | 5} | |dddn #1swxYwYn| | |ddddS#1swxYwYdS) Nrz, c3"K|] }d|dV dS)"Nrr)rIks rrJz$_psql_insert_copy..Gs*//QA//////rzCOPY "z"."z" (z) FROM STDIN WITH CSVr{)csvioStringIOwriter writerowsseekjoin connectiontableschemanamecursorhasattrcallabler{writeread copy_expert) tblrkeys data_iterrrs_bufrr& dbapi_connrLcurr{s r_psql_insert_copyr>sJJJ III KKMME ZZ  F Y JJqMMMii//$/////GJW!WWcinWWWWW     ( 3   (HSX$6$6 (# )$ 5::<<((( ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) OOC ' ' ' ((((((((((((((((((s66:E0(D$ E$D( (E+D( ,EEEfailFc r ddlm} ddlm} n#t$rt dwxYw|}|jj} t|} t|\} }|| | | || <n| | | | i}|rt|| }t|| | }||}nd}|dkrt|5}|j |||rj|| d ||| d}|| krd |d | d }t%|dddn #1swxYwYt|5}|||||||||t( ddddS#1swxYwYdS)a Upload GeoDataFrame into PostGIS database. This method requires SQLAlchemy and GeoAlchemy2, and a PostgreSQL Python driver (e.g. psycopg2) to be installed. Parameters ---------- name : str Name of the target table. con : sqlalchemy.engine.Connection or sqlalchemy.engine.Engine Active connection to the PostGIS database. if_exists : {'fail', 'replace', 'append'}, default 'fail' How to behave if the table already exists: - fail: Raise a ValueError. - replace: Drop the table before inserting new values. - append: Insert new values to the existing table. schema : string, optional Specify the schema. If None, use default schema: 'public'. index : bool, default True Write DataFrame index as a column. Uses *index_label* as the column name in the table. index_label : string or sequence, default None Column label for index column(s). If None is given (default) and index is True, then the index names are used. chunksize : int, optional Rows will be written in batches of this size at a time. By default, all rows will be written at once. dtype : dict of column name to SQL type, default None Specifying the datatype for columns. The keys should be the column names and the values should be the SQLAlchemy types. Examples -------- >>> from sqlalchemy import create_engine # doctest: +SKIP >>> engine = create_engine("postgresql://myusername:mypassword@myhost:5432/mydatabase";) # doctest: +SKIP >>> gdf.to_postgis("my_table", engine) # doctest: +SKIP r)Geometry)textz,'to_postgis()' requires geoalchemy2 package.N) geometry_typer=publicappendz2SELECT Find_SRID(:schema_name, :name, :geom_name);) schema_namerrvz"The CRS of the target table (EPSG:z5) differs from the CRS of current GeoDataFrame (EPSG:z).)r if_existsindex index_labelrFdtypemethod) geoalchemy2r sqlalchemyr ImportErrorr{r%rrnr`rxrrdialect has_tableexecute bindparamsfetchoner to_sqlr)r[rr9rrrrrFrrrrvr=rr]rr target_sridmsgs r_write_postgisrVsjJ((((((####### JJJHIIIJ ((**C !I c " "D 2#66M9 #8-dKKKiHH=tLLLM@/Y?? 3 4 0 0C   H s^^ *z!++JfEE *(00DL j$/di! (**Q  $&&O[OOFJOOO%S//)! * * * * * * * * * * * * * * *$ 3  :  #$                     s- )BE!!E%(E%;$F,,F03F0c6d|}tj||S)Nz9SELECT srid, auth_name FROM spatial_ref_sys WHERE srid = )r4rK)r9r=spatial_ref_sys_sqls rr/r/s* KDJJ ;*C 0 00r)rNN)rNNTNNN)NrFNNN)r1 contextlibr functoolsrpandasr4r shapely.wkb geopandasrrr@rNr`rnrxrrrr/rrrrrsk%%%%%%""""""CCC@M8M8M8M8f  S S S S l,',','^<       (((8   u u u u p 11 111r