我有两个表，我想加入：

• sd_lat_long：此表包含英国的位置及其各自的纬度和经度
• sd_geo_uk_counties：此表包含英国各县及其各自的几何

我希望创建一个视图，查找哪些英国位置与哪些县相交。
以下是我想到的一个脚本：

--Define list of uk locations and their lat, longs
WITH geo AS ( 
    SELECT 
        uk_locations,
        ST_SetSRID(ST_Point(latitude ,longitude),4326) AS lat_long --set to SRID 4326
    FROM
        sd_lat_long 
),
--Defining the shapefile and transforming the geometry
shp AS (
SELECT 
    county,
    ST_Transform(geometry, 4326) AS geometry --Transformed to SRID 4326 from 27700
FROM 
    sd_geo_uk_counties
WHERE 1=1
)
--Join on intersection of lat_long and the county geometry to assign a county to the UK locations
SELECT
    geo.uk_locations,
    shp.county
FROM 
    geo
LEFT JOIN
     shp
ON
    ST_Intersects(shp.geometry, geo.lat_long)

字符串
对于shp CTE，我使用了这篇文章来帮助：为什么使用PostGIS时英国县的几何图形会出现在几内亚湾？
我面临的问题是，我不断收到一个错误，似乎与我在脚本中使用的几何图形的SRID有关。请参见下面的错误。x1c 0d1x
我首先查看了上面脚本的geo和shp CTE中几何体字段的SRID。

GEO：lat_long字段看起来很好-即SRID = 4326测试脚本：

WITH geo AS ( 
    SELECT 
        uk_locations,
        ST_SetSRID(ST_Point(latitude ,longitude),4326) AS lat_long --set to SRID 4326
    FROM
        sd_lat_long 
),  
SELECT
    uk_locations AS geo_field,
    ST_SRID(lat_long) AS srid,
    'geo' AS cte
FROM
    geo
GROUP BY 
1,2, 3
HAVING
srid <> 4326

型
这没有返回错误或SRID <> 4326的条目

的

SHP：测试脚本：

WITH shp AS (
SELECT 
    ctyua23nm AS county,
    ST_Transform(geometry, 4326) AS geometry
FROM 
    sd_geo_uk_counties
WHERE 1=1
)
SELECT
    county AS geo_field,
    ST_SRID(geometry) AS srid,
    'shp' AS cte
FROM
    shp
GROUP BY 
1,2,3

型
这抛出了相同的原始错误：

我不知道该怎么办，任何帮助都将是最感激的！