1

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 ?

  • NORTHING is latitude only, EASTING is longitude only. – fatbadger Feb 7 '14 at 9:40
  • OK, now we've got the data sorted, do you want to do any point in gps_nodes, or the closest that is not already jointed, or some other rule? – BradHards Feb 7 '14 at 9:43
  • Thanks for fixing the mistake - was just about to edit it when I realised you'd done it for me :). I just want any single point, so just a "LIMIT 1" on the end of my example would suffice. I'll edit it... – fatbadger Feb 7 '14 at 9:46
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

  • Many thanks, am trying it out now. Note the typo in "GROUP BY", and you need an extra closing bracket just before "GROUP". – fatbadger Feb 7 '14 at 10:19
  • I've adapted your query to my DB (changed "gid" to "NODE_ID" and "linestrings" table is actually called "gps_node_links"). Am now getting a parse error at the start of the arguments to "'LINESTRING(g.EASTING g.NORTHING, -1.911782 53.26144)'". Any ideas why ? – fatbadger Feb 7 '14 at 10:24
  • Am using the SQL editor with PGAdmin by the way, seems I have to enclose all column names in double quotes - so like g."NODE_ID" etc. I've tried quoting EASTING and NORTHING in the LINESTRING function (like g."EASTING"), but to no avail. – fatbadger Feb 7 '14 at 10:27
  • ) was missing after ST_Equals and GROUB BY is GROUP BY.. query fixed , fails to non existing table on my machine so it should be ok – simplexio Feb 7 '14 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.