Creating geometry from lat/lon column using Python

Question

I used the code:

import geopandas as gpd

geom = gpd.points_from_xy(dat['lon'], dat['lat'], crs=4326)
dat = gpd.GeoDataFrame(data=dat[['value']], geometry=geom)

but I receive the error:

CRSError: Invalid projection: epsg:4326: (Internal Proj Error: proj_create: SQLite error on SELECT name, type, coordinate_system_auth_name, coordinate_system_code, datum_auth_name, datum_code, area_of_use_auth_name, area_of_use_code, text_definition, deprecated FROM geodetic_crs WHERE auth_name = ? AND code = ?: no such column: area_of_use_auth_name)

What is wrong?

The link below is that data I used the link for the file used

Version info:

• pyproj: 2.6.1
• pandas: 1.4.4
• geopandas: 0.12.2
• conda version 22.11.1 import sys import pyproj import geopandas print(sys.version) print(pyproj.version) print(geopandas.version) import sys import pyproj import geopandas print(sys.version) print(pyproj.version) print(geopandas.version) import sys import pyproj import geopandas print(sys.version) print(pyproj.version) print(geopandas.version) 3.9.13 (main, Aug 25 2022, 23:51:50) [MSC v.1916 64 bit (AMD64)] 2.6.1.post1 0.12.2 <pyproj.datadir.get_data_dir()> 'C:\Users\User\anaconda3\lib\site-packages\pyproj\proj_dir\share\proj'

Answer 1

You are certainly facing this issue: https://github.com/geopandas/geopandas/issues/1887

which is also know by pyproj: https://pyproj4.github.io/pyproj/stable/gotchas.html#internal-proj-error-sqlite-error-on-select

The PROJ database is based on the EPSG database. With each release, there is a good chance that there are database updates. If you have multiple versions of PROJ installed on your systems and the search path for the data directory becomes mixed up, you may see an error message like: SQLite error on SELECT. This is likely due to a version of PROJ attempting to use an incompatible database.

So a pyproj / proj cleaning/update will certainly help, because on a "up-to-date" and "clean" system, everything should work out of the box:

E.g. this example from the GeoPandas documentation is not showing the error:

#Python 3.10.6 (main, Nov  2 2022, 18:53:38) [GCC 11.3.0]

import pandas as pd # version: '1.5.2'
import geopandas as gpd # version:  '0.12.2'

df = pd.DataFrame(
    {'City': ['Buenos Aires', 'Brasilia', 'Santiago', 'Bogota', 'Caracas'],
     'Country': ['Argentina', 'Brazil', 'Chile', 'Colombia', 'Venezuela'],
     'Latitude': [-34.58, -15.78, -33.45, 4.60, 10.48],
     'Longitude': [-58.66, -47.91, -70.66, -74.08, -66.86]})
df

df is the following, having a Latitude and a Longitude column:

           City    Country  Latitude  Longitude
0  Buenos Aires  Argentina    -34.58     -58.66
1      Brasilia     Brazil    -15.78     -47.91
2      Santiago      Chile    -33.45     -70.66
3        Bogota   Colombia      4.60     -74.08
4       Caracas  Venezuela     10.48     -66.86

Now you can simply convert this to a GeoDataFrame using the points_from_xy method and it should work out of the box. Maybe you want to try using the full EPSG string: EPSG:4326, or a pyproj.CRS:

import pyproj # version: '3.4.1'

gdf = gpd.GeoDataFrame(
    df,
    geometry = gpd.points_from_xy(
        x = df.Longitude,
        y = df.Latitude,
        crs = 'EPSG:4326', # or: crs = pyproj.CRS.from_user_input(4326)
    )

)

gdf

gdf looks like, with a brand new geometry column:

           City    Country  Latitude  Longitude                     geometry
0  Buenos Aires  Argentina    -34.58     -58.66  POINT (-58.66000 -34.58000)
1      Brasilia     Brazil    -15.78     -47.91  POINT (-47.91000 -15.78000)
2      Santiago      Chile    -33.45     -70.66  POINT (-70.66000 -33.45000)
3        Bogota   Colombia      4.60     -74.08    POINT (-74.08000 4.60000)
4       Caracas  Venezuela     10.48     -66.86   POINT (-66.86000 10.48000)

and it has a crs assigned:

gdf.crs

>: 
  <Geographic 2D CRS: EPSG:4326>
  Name: WGS 84
  Axis Info [ellipsoidal]:
  - Lat[north]: Geodetic latitude (degree)
  - Lon[east]: Geodetic longitude (degree)
  Area of Use:
  - name: World.
  - bounds: (-180.0, -90.0, 180.0, 90.0)
  Datum: World Geodetic System 1984 ensemble
  - Ellipsoid: WGS 84
  - Prime Meridian: Greenwich

See also: Pyproj Invalid projection: +init=epsg:25832

Answer 2

Your python environment is likely corrupted and you have multiple versions of PROJ installed on your system.

https://pyproj4.github.io/pyproj/stable/gotchas.html#internal-proj-error-sqlite-error-on-select

Answer 3

I have been able to find a solution to my question using the following: i updated pyproj, geopandas and pandas with python -m pip install -U pandas (same for geopandas and pyproj) I proceeded to check the list of the packages in jupyter notebook using pip list. Then i restarted the kernel and run the code again.