无法执行查询ST_交叉点

问题描述

我想知道为什么无法正确执行以下查询。在运行时，我收到以下错误：

psycopg2.errors.SyntaxError: ERROR: Syntax error at "POLYGON
LINE 3: ...80686137,341582.926185573 5664907.52304833))', '('POLYGON((3...

查询：

 SELECT
        ST_AsGeoJSON(ST_Transform(ST_SetSRID(ST_Intersection('POLYGON((341582.926185573 5664907.52304833,341575.683224445 5664907.35568754,341570.721317796 5664906.4899687,341561.615108002 5664905.7485185,341552.526006123 5664906.67643674,341543.757489292 5664909.24274105,341535.602330588 5664913.36174478,341523.663067557 5664920.83366159,341515.792551265 5664926.92817452,341509.285315952 5664934.46103608,341504.399277492 5664943.13367944,341501.328095218 5664952.60236224,341500.193496197 5664962.49179093,341501.040450539 5664972.40999555,341503.835389 5664981.96386564,341508.467533508 5664990.7747312,341514.753287875 5664998.49337141,341522.443514683 5665004.81385606,341539.413063357 5665016.13260517,341548.559454183 5665020.94886898,341558.500758472 5665023.78141752,341568.812073497 5665024.50918443,341579.052681849 5665023.10106412,341588.784888202 5665019.61724126,341597.592726915 5665014.20661843,341605.099740876 5665007.10045184,341619.7131272 5664990.07359853,341625.55190488 5664981.66019985,341629.55405225 5664972.23367245,341631.551676661 5664962.18946522,341631.46097649 5664951.94893908,341629.285756667 5664941.94169066,341625.117269058 5664932.58753045,341619.130384389 5664924.27887146,341611.57625631 5664917.36426727,341602.771785333 5664912.13378995,341593.08632466 5664908.80686137,341582.926185573 5664907.52304833))', '('POLYGON((341581.771159881 5664957.5097057,341567.157773557 5664974.53655901,341550.188224884 5664963.21780991,341562.127487915 5664955.74589309,341570.781410334 5664957.25576908,341581.771159881 5664957.5097057))',)'),25832),4326))

编码：

def executeWithFetchallForIntersectedGeometry(self,geom1,geom2):
    query = """ 
        SELECT
            ST_AsGeoJSON(ST_Transform(ST_SetSRID(ST_Intersection('{geom1}', '{geom2}'),25832),4326))                
    """.format(geom1=geom1,geom2=geom2)
    logger.debug("*: {0}".format(query))
    data = self.connection.query(query,[])
    # print(data)
    return data

---

解决方案

您在WKT字符串中多放了一个单引号。语法正确..

SELECT
  ST_AsGeoJSON(
    ST_Transform(
      ST_SetSRID(ST_Intersection('{geom1}',('{geom2}')),25832),
    4326));

..但wkt文字不是。第二个多边形被包装在('和',)之间，这是错误的。更正后，查询应可用：'('POLYGON((341581.771159881 5664957.5097057 ...))',)')应变为'POLYGON((341581.771159881 5664957.5097057 ...))'：

SELECT
  ST_AsGeoJSON(
    ST_Transform(
      ST_SetSRID(
         ST_Intersection('POLYGON((341582.926185573 5664907.52304833,341575.683224445 5664907.35568754,341570.721317796 5664906.4899687,341561.615108002 5664905.7485185,341552.526006123 5664906.67643674,341543.757489292 5664909.24274105,341535.602330588 5664913.36174478,341523.663067557 5664920.83366159,341515.792551265 5664926.92817452,341509.285315952 5664934.46103608,341504.399277492 5664943.13367944,341501.328095218 5664952.60236224,341500.193496197 5664962.49179093,341501.040450539 5664972.40999555,341503.835389 5664981.96386564,341508.467533508 5664990.7747312,341514.753287875 5664998.49337141,341522.443514683 5665004.81385606,341539.413063357 5665016.13260517,341548.559454183 5665020.94886898,341558.500758472 5665023.78141752,341568.812073497 5665024.50918443,341579.052681849 5665023.10106412,341588.784888202 5665019.61724126,341597.592726915 5665014.20661843,341605.099740876 5665007.10045184,341619.7131272 5664990.07359853,341625.55190488 5664981.66019985,341629.55405225 5664972.23367245,341631.551676661 5664962.18946522,341631.46097649 5664951.94893908,341629.285756667 5664941.94169066,341625.117269058 5664932.58753045,341619.130384389 5664924.27887146,341611.57625631 5664917.36426727,341602.771785333 5664912.13378995,341593.08632466 5664908.80686137,341582.926185573 5664907.52304833))', 
                        ('POLYGON((341581.771159881 5664957.5097057,341567.157773557 5664974.53655901,341550.188224884 5664963.21780991,341562.127487915 5664955.74589309,341570.781410334 5664957.25576908,341581.771159881 5664957.5097057))')),25832),4326))

演示：db<>fiddle