Sql Query in Postgresql

Question

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

Answer 1

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.

Answer 2

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.