CODEWITHSUNDEEP
pythonpostgresqlpostgis
Amrmsmb
Amrmsmb

Reputation: 1

query ST_Intersection can not be executed

i would like to know why the below query can not be executed correctly. at run time, i receive the following error:

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

query:

 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))

code:

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

Upvotes: 0

Views: 109

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

You put one single quote too many in your WKT strings. The syntax is the correct ..

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

.. but the WKT literals aren't. The second polygon is wrapped between (' and ',), which is wrong. Correct it and the query should work: '('POLYGON((341581.771159881 5664957.5097057 ...))',)') should become '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))

Demo: db<>fiddle

Upvotes: 0

Related Questions