4

In the following spatial join, I want to join pd_subdistricts to parcel and keep both matching and unmatching records. Since parcel has 292,109 records, the output should have the exact same amount, given that geometries are valid. After making the geometries valid, I get an unexpected result. The output - test has 249 more records than parcel How is this possible?

I have performed the exact same query to join other tables and the resulting number of records is always equal to parcel

Input   parcel          has 292,109 records
Input   pd_subdistricts has 1,083 records
Output  test            has 292,358 records 

Using this query,

CREATE TABLE test AS
SELECT t.*
FROM parcel AS t
LEFT JOIN pd_subdistricts AS m
  ON ST_Within(t.geom, m.geom)
  • Perhaps parcel ids are not unique – FelixIP Dec 12 '16 at 0:19
6

Since parcel has 292109 records, the output should have the exact same amount, given that geometries are valid.

That's not how a LEFT OUTER JOIN works. From the docs

LEFT OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.

So first an INNER JOIN is performed,

INNER JOIN for each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1.

And, that's your problem.

LEFT JOIN pd_subdistricts AS m
  ON ST_Within(t.geom, m.geom)

Now, let's look at ST_Within So ST_Within(t.geom, m.geom) will evaluate true for each row such that t.geom fits in m.geom, but what happens if one t.geom fits in multiple m.geom(s)? Imagine this,

  1. t.geom is Houston.
  2. These three cases
    1. m.geom represents Texas
    2. m.geom represents United States
    3. m.geom represents North America

Now for each of those three cases ST_Within will return true. So with one row in your t.geom (representing Houston) you'll have three rows in your output (representing Houston being within the three above locations).

You can find just the distinct t.geoms using

SELECT DISTINCT ON(t.geom) t.*
FROM parcel AS t
LEFT JOIN pd_subdistricts AS m
  ON ST_Within(t.geom, m.geom)

You can find the duplicates using this.

SELECT t.geom, count(*)
FROM parcel AS t
LEFT JOIN pd_subdistricts AS m
  ON ST_Within(t.geom, m.geom)
GROUP BY t.geom
HAVING count(*) > 1;
  • Thanks, that makes sense. Is there an equivalent in PostGIS to the QGIS function > join attributes by location > take attributes of first located feature? – the_darkside Dec 12 '16 at 0:34
  • That would avoid the duplication of records – the_darkside Dec 12 '16 at 0:51
  • @the_darkside yes, you can use DISTINCT ON and ORDER BY to achieve the result quickly and easily. – Evan Carroll Dec 12 '16 at 3:21
  • Thank you. Could you modify my code with DISTINCT ON ? I have tried with SELECT DISTINCT t.* ... but it gave the same ouput – the_darkside Dec 12 '16 at 8:56
  • updated the answer – Evan Carroll Dec 12 '16 at 17:00

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.