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
  • 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 Dec 9 '15 at 21:26
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

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.