Linestring in dblink postgresql with postgis doesn't work

Question

I'm trying to do a dblink using a postgis function, but the linestring has given me many problems.

SELECT 
    DV3."XXX",
        s."QQQID",s."X",s."Y",a."PPP"
FROM 
    "QQQ" s INNER JOIN "AAA" a ON a."QQQID" = s."QQQID",
    dblink('dbname=ZZZ',
        'SELECT XXX,the_geom 
        FROM "geometry", "QQQ" s 
        WHERE box2d(geomfromtext(''LINESTRING('|| s."X" ||' '||s."Y" ||','|| s."X" || ' '||s."Y" || ')'',2309))
        && the_geom'
                           ) 
        DV3("XXX" INTEGER,"the_geom"  geometry)


WHERE 
    contains(DV3.the_geom,geomfromtext('POINT('|| s."X" ||' '|| s."Y" ||')',2309))--21
    AND "GGG" IS NOT NULL

ERROR

LINE 9: ... WHERE box2d(geomfromtext(''LINESTRING('|| s."X" + 0....

In your code I see two single quotes after `geomfromtext` rather than a double quote, is that a cut-and-paste typo? — Tanzeeb Khalili, Jul 19 '12 at 05:28
Please show the full, exact text of the error - all of it, not just the "context" line. — Craig Ringer, Jul 25 '12 at 00:51

score 2 · Answer 1 · answered Jul 25 '12 at 06:39

Following what Tanzeeb said, there are two instances of two single quotes right next to each other that look like they are supposed to be double quotes instead of two single quotes.

In

    'SELECT XXX,the_geom 
    FROM "geometry", "QQQ" s 
    WHERE box2d(geomfromtext(''LINESTRING('|| s."X" ||' '||s."Y" ||','|| s."X" || ' '||s."Y" || ')'',2309))
    && the_geom'

Right before LINESTRING and right before ,2309.

Linestring in dblink postgresql with postgis doesn't work

1 Answers1