查询ST_Intersection无法执行

Question

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

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

Answer 1

你在你的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