# Unexpected records added after spatial join

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

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