1

I´m trying to connect (through a line) every id point from dataset 01 with its associated ids in the dataset 02.

In the code below, every point in the dataset01 is linked with all points in dataset02, but i´m trying to solve how i can connect each id in dataset01 with just its ids, for example: 8888888 id from dataset01 will be linked with all 8888888 id points from dataset02, etc

dataset01:

id, lat, long
8888888, x1, y1
9999999, x2, y2

dataset02 :

id, lat, long
8888888, x1.1, y1.1
8888888, x1.2, y1.2
8888888, x1.3, y1.3
8888888, x1.4, y1.4
9999999, x2.1, y2.1
9999999, x2.2, y2.2
9999999, x2.3, y2.3

code :

SELECT
ST_MakeLine(
d2.the_geom_webmercator,
  d1.the_geom_webmercator)
AS the_geom_webmercator,
ceil(
  ST_Distance (
    d1.the_geom_webmercator,
    d2.the_geom_webmercator)
    ) as distance
FROM dataset02 as d2,
dataset01 as d1
Improve this question
1
  • 1
    You will need to join your tables on the matching id's. I assume that you'll want all data from both tables where d1=d2, so the Left Join is likely what youll looking for, but here is a visual explanation of SQL Joins for other join types, blog.codinghorror.com/a-visual-explanation-of-sql-joins
    – MaryBeth
    Commented Dec 9, 2015 at 21:26

1 Answer 1

Reset to default
0

You're not joining the tables. Just change this:

FROM dataset02 as d2,
dataset01 as d1

to this:

FROM dataset01 as d1
JOIN dataset02 as d2
ON d1.id = d2.id
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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