0

I am trying to execute a query but I am stuck. I have two tables A and B. The a table has columns gid, geometry(line-string) and value which cells is empty , the B table has got 3 columns gid, geometry(Point) and km. I want to find from the A table the start and end point of every line-string and compare with the points of table B if the start and end points of line-string is match with points from table B then i want to compare the values of points start and end which match with one point for start and one for the end and save the bigger value in the empty column of table A.

This is the query I am using. 

SELECT * FROM r ,e where ST_Equals(ST_Startpoint(r.geom),.geo) LIMIT 10;

But i didnt take a result the query running

Improve this question
12
  • what's the problem? Is there an error? Unexpected result? – ako Aug 26 '15 at 7:46
  • error : column r.startx does not exist LINE 4: (R.startX=E.X AND R.startY=E.Y) OR (R.endX=E.X AND R.endY=E... – vagelis Aug 26 '15 at 8:41
  • You need to quote column names that have upper case letters, this is a common gotcha in postgres. So you need to write R."startX". In general, avoid using upper case in Postgres, as quoting is a major pita imho. Also, while there is nothing wrong with it, it is a bit pointless have a table called r and then aliasing it as R. You would be better off just writing r."startx" – John Powell Aug 26 '15 at 10:20
  • 1
    why not something like : WHERE ST_Equals(ST_Startpoint(r.geom),e.geom) OR ST.Equals(ST_Equals(r.ST_Endpoint(r.geom),e.geom) (if both geoms are in same srid) – simplexio Aug 26 '15 at 10:35
  • @simplexio. I have updated the question with the actual error the OP is getting -- it is to do with upper case table/column names, though I agree that ST_Equals would also be cleaner. – John Powell Aug 26 '15 at 10:43
4

In Postgres column and table names are not case sensitive and everything is converted to lower case before executing the query, see the docs. Therefore,

SELECT SomeColumn FROM SomeTable

becomes

SELECT somecolumn FROM sometable

unless you write it as

SELECT "SomeColumn" FROM "SomeTable"

In my view this is messy, especially when dealing with SQL clients, as you will have to also escape the quotes, so best to just use lower case table/column names from the start. If you are not in a position to change this, then in you case, the offending line can be rewritten as:

FROM r INNER JOIN e ON 
(r."startX"=e."X" AND r."startY"=e."Y") OR (r."endX"=e."X" AND r."endY"=e."Y");

I would also suggest that you not alias a lower case table name such as r or e as R and E, as otherwise you will have to quote those too, and it doesn't make the query clearer or shorter, either.

In short, in Postgres, use upper case for keywords like SELECT, FROM, JOIN and lower case for everything else.

On another note, ST_X returns a number, so ST_X(ST_AsTEXT(....)) will also fail, once you have fixed the upper case issue. Just use ST_X(ST_Transfrom(geom, 4326)) for example.

Improve this answer
0

Since you are searching for identical points only, the bounding boxes intersect operator "&&" will be most efficient. In case your tables don't have spatial indizes, create them:

CREATE INDEX ON r USING gist (geom);
CREATE INDEX ON e USING gist (geo);

Then for each linestring lookup the matching endpoints and the bigger km value:

UPDATE r
    SET value = greatest(e1.km, e2.km)
    FROM e e1, e e2
    WHERE e1.geo && ST_StartPoint(r.geom) AND
          e2.geo && ST_EndPoint(r.geom));

This will give correct results only if every start- and endpoint is found exactly once in your table e.

Improve this answer

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.