hP<ddlZddlmZddlmZddlZddlZddlZddl m Z edZ ddZ ddZ dZd Zd Zd Zd Z dd ZedZy)N)contextmanager) lru_cache) GeoDataFramec#8Kddlm}m}t||r3|j s|j 5|dddy|yt||r|j 5}|dddyt d|#1swYyxYw#1swYyxYww)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 C/var/www/html/immo/lib/python3.12/site-packages/geopandas/io/sql.py _get_connr s$:.*-,,.%%' %$$ % %! NF +  ! ! # tJ  00@ABB % %   s35BB*B&B+BB BBBcH||vrtd|d|jjj|dkDrtd|d||j }|j st jj} d}t|jdtr|}n|}|j|x||<}|t j|jd}|dk7r_ t||} | j s| dj} | d |}n+d |d |d } t!j"| t$d d|} t-|||S#t&j(j*$r*d|d } t!j"| t$d d|}YTwxYw)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.cVtjjt|dS)z!Load from binary encoded as text.T)hex)shapelywkbloadsstr)xs rload_geom_textz$_df_to_geodf..load_geom_textVs ;;$$SV$6 6r 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?.s2r:8*AFGG zz!!(+a/)(4. .  xL   !E ;;!++++( 7 eiilE *'I&I${{9558 u ;##EIIaL1Dqy-)@d)K&.33$6{$C$H$H$J !* 1TF334&9++/&A$ ! k;1M %dVn h 77+yy..)''+fO= MM+{qI!$.C)s. EAF! F!c |+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 ) index_col coerce_float parse_datesparams chunksizer7r#r8c3<K|]}t|yw)rFN)r?).0r6r8r#r7s r z _read_postgis..s$ FHLhCS A A s)r3read_sqlr?) sqlr8r7r#rArBrCrDrEr6 df_generators ``` r _read_postgisrM~syr [[  %# BsDD{{  %#  LX  rcDt|jjj}d}|D] }|d|vs d}t |dk(r)|rd}n&|d t d|dj }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. F 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(cll,,3356JI :  R I   :! + !}$ !CDD#-a=#6#6#8 % 3<<  C Y &&rcjd}d}|jW dD]Q}|jj|}|n1|jjd|}|Ct|d}n|d }t j |td |S#t$rt j |td YIwxYw) 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)rrdrr!r)r#to_epsg to_authorityint Exceptionr0r1r2)rZr<r> confidence auth_srids r_get_srid_from_crsrms DX ww B+  wwjA#GG00$Z1 (y|,D  | k;1= K B MM+{q A BsAB B %B21B2cddlm|jdk(}|j||fj fd|j||f<|S)Nr) LineStringrOc|S)N)geomros rz3_convert_linearring_to_linestring..)s Z%r)shapely.geometryrorTlocr,)rZ geom_namemaskros @r!_convert_linearring_to_linestringrx!sJ+ ==L (D"wwtY7==% CGGD)O Jrctjtj||jj|dd}t j |d}|||<|S)zConvert geometries to ewkb.)r<T)r include_sridF)copy)rto_wkbset_sridvalues_datar3 DataFrame)rZrvr<r9r6s r_convert_to_ewkbr.sV NNY..444@  E c &BByM Ircddl}ddl}|j}|j|}|j ||j ddj d|D}|j} d|jjd|jjd|d} | j5} t| drOt| jr:| j| 5} | j|j!dddn| j#| |dddy#1swYxYw#1swYyxYw) Nrz, c3(K|] }d|d yw)"Nrq)rHks rrIz$_psql_insert_copy..Gs/Q!A3a/szCOPY "z"."z" (z) FROM STDIN WITH CSVr{)csvioStringIOwriter writerowsseekjoin connectiontableschemanamecursorhasattrcallabler{writeread copy_expert) tblrkeys data_iterrrs_bufrr% dbapi_connrKcurr{s r_psql_insert_copyr>s KKME ZZ F Y JJqMii/$//GJ !!"#ciinn%5S AVW    ( 3 HSXX$6# )$ 5::<( ) ) OOC ' (( ) )((s$43D7' D+D7+D4 0D77Ec  ddlm} ddlm} |j }|j j} t|} t|\} }|| | | || <n | | | | i}|r t|| }t|| | }||}nd}|dk(r|t|5}|jj|||rK|j| d |d |d | d j!d}|| k7rd |d | d}t#|dddt|5}|j%||||||||t& dddy#t$r t dwxYw#1swYTxYw#1swYyxYw)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<publicappendzSELECT Find_SRID('z', 'z');z"The CRS of the target table (EPSG:z5) differs from the CRS of current GeoDataFrame (EPSG:z).)r if_existsindex index_labelrEdtypemethod) geoalchemy2r sqlalchemyr ImportErrorr{r$rrmr_rxrrdialect has_tableexecutefetchoner to_sqlr)rZrr8rrrrrErrrrvr<rr\ schema_namer target_sridmsgs r_write_postgisrVsjJ(# ((*C !!I c "D 2#6M9 #-dKiH=tLM/Y? 3 4 0C   H s^ *z!!++JfE(00-k]$tfD SVWX(*Q $&<[MJFFJV2O%S/) * 3  :  #$     ] JHIIJ@ * *   s$ D?A)EE#?EE #E,c8d|}tj||S)Nz9SELECT srid, auth_name FROM spatial_ref_sys WHERE srid = )r3rJ)r8r<spatial_ref_sys_sqls rr.r.s( DD6J ;;*C 00r)rrNN)rrNNTNNN)NfailFNNN)r0 contextlibr functoolsrpandasr3r shapely.wkb geopandasrrr?rMr_rmrxrrrr.rqrrrs%"CC@M8f  S l,'^<   (8   q h 1 1r