Rewriting query to match two points with linestring in different database table?

Question

Suppose I have:

• a table "gps_nodes" containing columns:
  
• • "EASTING" (containing longitude data)
    
• • "NORTHING" (containing latitude data)
    
• • "geom_point" (containing POINT geometry data, calculated from EASTING & NORTHING columns).
    
• a table "linestrings" containing a "geom_line" column (containing LINESTRING geometry data, each linestring contains only 2 points).

Given a new long/lat position (eg -1.911782 53.26144), I need to select a point position from the "gps_nodes" table, where a linestring created between these two positions does NOT already exist in the "linestrings" table.

Something like this:

SELECT *
FROM gps_nodes 
WHERE
(SELECT count(*) FROM linestrings
  WHERE ST_equals(geom_line, ST_GeomFromText('LINESTRING(gps_nodes.EASTING gps_nodes.NORTHING, -1.911782 53.26144)'))
) = 0
LIMIT 1

But the problem seems to be that I can't reference the row from the gps_nodes table in the WHERE subquery, as I think that subquery gets executed first, before the SELECT. (Note that there may be minor syntax errors in that query example, as I've just quickly written it here to demonstrate the general issue)

How could I rewrite this query to get the data I need ?

Answer 1

 SELECT node.*
        FROM gps_nodes as node,
     (SELECT count(*) as n, g.gid  FROM linestrings as l , gps_nodes as g
          WHERE ST_equals(l.geom_line, ST_GeomFromText('LINESTRING(g.EASTING g.NORTHING, -1.911782 53.26144)')) GROUP BY g.gid)
 as c
        WHERE node.gid = c.gid AND c.n = 0 

(i didnt test, may not contains necessary amount of ) )

query creates "table" which has n and gid columns ( n is count and gid is unique id in gps_table) , assigns alias c for it and then joins table with gps_nodes table ( alias node) and limits rows to where n = 0

change gid to match your gps_nodes table. note table alias use