Rahul Rahul - 5 months ago 38
SQL Question

psycopg2.ProgrammingError: cannot cast type record to geometry

I've a query that find nearest POI from a linestring. The query is like this:

import psycopg2

conn = psycopg2.connect("dbname=TestGIS user=postgis")
cur = conn.cursor()

cur.execute("SELECT poi.name,poi.city,ST_AsTEXT(poi.the_geog),
ST_AsText(ST_ClosestPoint(road.the_geog::geometry, poi.the_geog::geometry)),
ST_Distance_Sphere(ST_ClosestPoint(road.the_geog::geometry, poi.the_geog::geometry), poi.the_geog::geometry)/1000
AS Distance FROM road, poi WHERE road.id = 123 AND
ST_DWithin(road.the_geog, poi.the_geog, 1000.0) ORDER BY
ST_LineLocatePoint(road.the_geog::geometry, poi.the_geog::geometry),
ST_Distance(road.the_geog, poi.the_geog);")


Now I want to execute the same query but the linestring(road) is provided in the same query. Something like this:

cur.execute("
SELECT
poi.name,poi.city,ST_AsTEXT(poi.the_geog),
ST_AsText(ST_ClosestPoint(road::geometry, poi.the_geog::geometry)),
ST_Distance_Sphere(ST_ClosestPoint(road::geometry, poi.the_geog::geometry), poi.the_geog::geometry)/1000 AS Distance
FROM (
SELECT ST_GeographyFromText(
'SRID=4326;LINESTRING(85.280194 23.296728,85.281572 23.297479)'
)
) AS road, poi
WHERE
ST_DWithin(road, poi.the_geog, 1000.0) ORDER BY
ST_LineLocatePoint(road::geometry, poi.the_geog::geometry),
ST_Distance(road, poi.the_geog);
")


But I'm getting this error:

psycopg2.ProgrammingError: cannot cast type record to geometry
LINE 1: ...sTEXT(poi.the_geog),ST_AsText(ST_ClosestPoint(road::geometry...


Can anyone tell me what I'm doing wrong ?

UPDATE:
The table is :

cur.execute("CREATE TABLE poi(gid serial PRIMARY KEY, name varchar, city varchar, the_geog geography(POINT,4326) );")
cur.execute('''INSERT INTO poi (gid, name, city, the_geog) VALUES (%s,%s, %s,ST_GeogFromText(%s))''',(int(count),row[0],row[1],coordinates))

Answer

Give the column an alias so you can refer to it:

FROM (
    SELECT ST_GeographyFromText(
        'SRID=4326;LINESTRING(85.280194 23.296728,85.281572 23.297479)'
    ) as road
) AS road