Invalid SRID passed to SQLAlchemy

[webb-ben]

When using pygeofilter on top of a Postgis table with EPSG:4269 (NAD83), requires we explicitly set the geometry CRS (as implemented in #66) to something other than EPSG:4269. I am unable to run a query because pygeofilter incorrectly extracts the SRID from the CRS URN. Producing the following error:

sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) parse error - invalid geometry
HINT:  "SR" <-- parse error at position 2 within geometry
[SQL: SELECT count(*) AS count_1 
FROM (SELECT ..., wmadata.catchmentsp.the_geom AS wmadata_catchmentsp_the_geom 
FROM wmadata.catchmentsp 
WHERE ST_Intersects(wmadata.catchmentsp.the_geom, ST_GeomFromEWKT(%(ST_GeomFromEWKT_1)s))) AS anon_1]
[parameters: {'ST_GeomFromEWKT_1': 'SRID=CRS83;POINT (-69.39999969709005 46.69999049530215)'}]

As noted in geopython/pygeoapi#2102 and geopython/pygeoapi#2010, pyproj respects axis ordering for CRS objects, requiring explicit distinction between axis order in https://www.opengis.net/def/crs/OGC/1.3/CRS83 and https://www.opengis.net/def/crs/EPSG/0/4326. This breaks down when Postgis, only offers EPSG codes in spatial_ref_sys and requires SRID entries to be integers - requiring both the aforementioned CRS definitions to share the SRID 4326 regardless of axis order.

So long as SFSQL uses an integer SRID as its primary key to parse SRID in EWKT, is there a way to correctly set an SRID in pygeofilter? To functionally handle this, I have implemented get_srid which degrades the CRS object to a proj4 string which allows extracting the correct EPSG SRID. This allows the correct transformations to exist while executing valid spatial SQL.

cc: @dblodgett-usgs

[webb-ben]

Note this is an issue for https://www.opengis.net/def/crs/OGC/1.3/CRS84 and https://www.opengis.net/def/crs/EPSG/0/4326. There are just fallbacks to use 4326 as the "default" SRID, instead of deriving a Postgis compatible SRID from https://www.opengis.net/def/crs/OGC/1.3/CRS84 / urn:ogc:def:crs:OGC::CRS84.