3

I have several point tabels, lets say 3. Tables are alike with two columns: a integer value and a geometry like:

  • table sand1 (value int, geom geometry)
  • table sand2 (value int, geom geometry)
  • table sand3 (value int, geom geometry)

I need to join the three tables to a new table with four columns like: valueTab1, valueTab2, valueTab3, geometry.

The join has to be on the point geometry. The value field cannot be used since it is a measurement value. Some of the points are positioned directly on top of each other. The ST_Equals points then results in one record.

enter image description here So the picture above with three layers sand1 (three records), sand2 (two records) and sand3 (three records) should produce a new table with four columns and four records.

Columns:

sand1Value, sand2Value, sand3Value, geom

Records:

22, null, null, null, geom 
null, 31, 37, null, geom 
1111, null, 66, geom 
11, 44, 55, geom

I have tried joining with ST_Equals(sand1.geom, sand2.geom), but ST_Equals does only take two parameters. Have tried the tablefunc extension with crosstab and multiple joins on like WHERE ST_AsText(sand1.geom) IN (ST_AsText(sand2.geom), ST_AsText(sand3.geom))

Improve this question
6
  • Are these points the could be anywhere, like GPS coordinates from a cell phone? Or points that correspond to a limited number of known locations, like weather stations?
    – Lee Hachadoorian
    May 26, 2016 at 13:56
  • This is a fun one. I have managed to join tables using union select and then doing a spatial self join with union but somewhere in my array_agg(distinct(concat(sand1.val, sand2.val.....) the nulls are getting eaten up. I'll keep trying.
    – John Powell
    May 26, 2016 at 14:23
  • Does the point always exist in all the tables and it is simply value that may or may not exist. Or might there be missing points also? If the former, then neighdough's answer is correct, otherwise, it is much more complicated.
    – John Powell
    May 27, 2016 at 8:39
  • @Lee Hackadoorian: The points are generated from a groundwater modelling tool. The value [mm / year] is groundwater added to a reservoir in a sand layer at that given point. If sand1, sand2 and sand3 have common points they have the exactly same (x,y)
    – Jakob
    May 27, 2016 at 9:02
  • @John Barça: I believe the union select and a spatial self join is a good way. This puts all the geometries in one table. I will look into this. The number of points varies between the three tables. If only sand1 and sand2 have a commen point, the value for sand3 should be null.
    – Jakob
    May 27, 2016 at 9:08

1 Answer 1

Reset to default
4

You must first UNION all the geometries. Then you can LEFT JOIN the geometries ON ST_Equals to retrieve the values.

Sample code

-- Create dummy test values
WITH
sand1 AS
( SELECT val, ST_MakePoint(x, y) geom
  FROM (VALUES (11, 0, 0), (22, 1, 2), (32, 3, 1)) a(val, x, y) ),
sand2 AS
( SELECT val, ST_MakePoint(x, y) geom
  FROM (VALUES (12, 0, 0), (23, 1, 3), (33, 3, 1)) a(val, x, y) ),
sand3 AS
( SELECT val, ST_MakePoint(x, y) geom
  FROM (VALUES (17, 0, 0), (27, 1, 3), (37, 3, 4)) a(val, x, y) )

SELECT
  -- Rename columns
  sand1.val val1,
  sand2.val val2,
  sand3.val val3,
  ST_AsText(all_geoms.geom) geom_txt
FROM
-- UNION all geometries from the three tables
(
SELECT geom FROM sand1
UNION
SELECT geom FROM sand2
UNION
SELECT geom FROM sand3
) all_geoms
-- Join back the geometries when they are equal
LEFT JOIN
  sand1 ON ST_Equals(all_geoms.geom, sand1.geom)
LEFT JOIN
  sand2 ON ST_Equals(all_geoms.geom, sand2.geom)
LEFT JOIN
  sand3 ON ST_Equals(all_geoms.geom, sand3.geom);

Returns:

Result

Improve this answer
3
  • 1
    Clever, left join on a merged geometry field.
    – John Powell
    May 27, 2016 at 9:23
  • 1
    As a concluding remark: I did a run on real data with 6 sand layer each with around 30000 records. It took 37 minutes on an old i5 laptop.
    – Jakob
    May 27, 2016 at 11:42
  • Indeed this query is not very optimized. Maybe it could run faster by first creating a table/matview with the unioned geometries, add a geometry index and then run the Left Joins.
    – thibautg
    May 27, 2016 at 12:01

Your Answer

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

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