2

I'd like to know if there's one way to join two data bases but keeping all the records for both DB? I have a point layer of 300000 locations and I want to assign them the attributes from a .csv table that contains info about people with health problems.

The .cvs file contains several individuals for the same location and once I've made the join with the point layer, only the first record is joined.

2

Do it the other way round: Instead of joining the CSV to the points, join the points to the CSV.

Ideally, the points table should contain the coordinates. If not, add columns with $X and $Y to the table.

Now save the CSV to disk, and re-add it as delimited text, selecting the point coordinates for X and Y.

  • Simple solution, just a slightly different point of view from your original way. Basically you want to assign a location to each individual, not the other way around. – Barrett Aug 15 '16 at 16:58
  • Of course, the issue with performing the join of locations to individuals is that those locations without any individuals will be lost. -- not present in the final dataset. The OP asked for "all records from both Dbs..." – JasonInVegas Aug 15 '16 at 18:41
2

First, find the distinct list of locations present in the individuals...no need to consider which individual is present at that location, simply whether a location has at least one individual.

Second, find the other locations -- those locations without any individual -- and export them to a separate list.

Third, perform the above join of locations to individuals...so that every individual has a location with X, Y coordinates.

Finally, append the vacant locations -- those without any individuals -- to the list from step three.

The result is a list of Every individual, their location, X,Y coordinates, and those locations without any individuals. (Truly all records from both Dbs.)

In a spatially-enabled Db, the query-code would look nominally like:

SELECT nvl(P.name,'VACANT') as name, L.location, L.location.X, L.location.Y
FROM People P
FULL OUTER JOIN Location L
WHERE P.location = L.location;

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.